Oracle 10g Express commit statement error

220 pts.
Tags:
Oracle 10g
Oracle 10g error messages
Oracle 10g Express
Oracle error messages
I m using Oracle 10g Express Edition.
when i type and execute the command COMMIT, I got the following error:
 
"Commit statement not applicable. All statements are automatically committed."
please help me to get out of this.


Software/Hardware used:
COMMIT STATEMENT
ASKED: November 22, 2010  10:35 AM
UPDATED: November 22, 2010  7:58 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

That is definitely a message indicating that autocommit is ON. No other circumstance would produce that message.

If you were running this in SQL*Plus or SQL Command Line, then the “show all” command (or “show autocommit”) suggested by Rechil will show you the autcommit status, but issuing a commit in a SQL*Plus or SQL Command Line session does not produce any error, regardless the autocommit setting.

If you are running this from the web interface (SQL Workshop or similar) then there must be an “Autocommit” checkbox that is currently checked. You need to look for that option and uncheck it. There is no other solution (apart from running your commands in SQL Command Line or some other tool).

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Subhendu Sen
    It sounds like most probably the auto commit turns in implicitly use. DDL and DML are different. DDL is data definition language and it has autocommit, but in DML there is no auto commit. First u have to check the all parameters of commands by : sql> show all; and observe what is the status of Auto Commit. if possible plz provide more details !
    26,090 pointsBadges:
    report
  • Prcvjr
    autocommit is OFF. i m using GUI mode to execute queries.
    220 pointsBadges:
    report
  • Subhendu Sen
    Have u written trigger etc. that is why I asked for more details. If it is trigger then u cannot execute a commit inside of a trigger, this is a trigger limitation. All Data Definition Language (DDL) statements execute an implicit commit. Even if u create a separate procedure, to execute the DDL statement, the execution of the separate procedure within the trigger is still in the same transaction, forcing a commit of any statement executed in the trigger and hence causing an error.
    26,090 pointsBadges:
    report
  • carlosdl
    "All Data Definition Language (DDL) statements execute an implicit commit. Even if u create a separate procedure, to execute the DDL statement, the execution of the separate procedure within the trigger is still in the same transaction, forcing a commit of any statement executed in the trigger and hence causing an error" This is not true when using Oracle's autonomous transactions. With this feature one can execute some DMLs, then execute some DDLs as autonomous transactions, and then rollback the main transaction from where the DDLs were executed. It also allows for including COMMITs inside triggers.
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following