Home » Other » General » java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordinate s (Oracle 10g Linux )
java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordinate s [message #471055] Thu, 12 August 2010 02:06 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

I am calling a function in Java as below

prepareCall()[2097] ( {? = call XXX_pkg.create_new('MT', ?, ?, ?, ?, ? )

but when i try to execute this it throes below error

Caused by: javax.ejb.EJBException: nested exception is: java.sql.SQLException: ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 902
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "XXXX.DDL_EXEC_PKG", line 111
ORA-02089: COMMIT is not allowed in a subordinate session

but when i execute in the DB Toad it works fine and the package executed successfully ,so what colud be the issue for the same.
Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471059 is a reply to message #471055] Thu, 12 August 2010 02:15 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Can you show us the code ?

A quick search on google says that the error is caused by a requested commit of a 2 phase transactions from a non-global level.

Might be that this is happening implicitly when you call a DDL statement.

Regards
Ved
Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471061 is a reply to message #471055] Thu, 12 August 2010 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02089: COMMIT is not allowed in a subordinate session
 *Cause: COMMIT was issued in a session that is not the two-phase commit
         global coordinator.
 *Action: Issue commit at the global coordinator only.

You Java program is in a distributed transaction. Your TOAD session was not.

By the way, DO NOT try to validate something with TOAD, this is not validation from Oracle point of view as you add TOAD bugs to Oracle issues. Use SQL*Plus.

One more reason to NOT commit inside a procedure: ONLY the caller knows when to commit not the called one.

Regards
Michel

[Updated on: Thu, 12 August 2010 02:19]

Report message to a moderator

Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471063 is a reply to message #471055] Thu, 12 August 2010 02:20 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

ORA-06512: at "XXXX.DDL_EXEC_PKG", line 111
ORA-02089: COMMIT is not allowed in a subordinate session


Quote:

Some XA database drivers contain restrictions on code that rollsback or commits a transaction independently of the driver's transaction management. Since DDL statements are implicitly transactional (COMMIT is called whether or not it explicitly appears in the DDL statement), you may have to suspend the transaction with these XA drivers. For example if you send a DDL statement using the Oracle XA thin client without suspending the transaction, the driver throws the following exception:

ORA-02089: COMMIT is not allowed in a subordinate session

Source: http://beehive.apache.org/docs/1.0.2/system-controls/jdbc/guide.html

Regards
Ved

[Updated on: Thu, 12 August 2010 02:21]

Report message to a moderator

Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471067 is a reply to message #471059] Thu, 12 August 2010 02:23 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Inside the function we are doing DML and DDL process whether this colud be the cause orelse is it from Java related issue.
Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471069 is a reply to message #471067] Thu, 12 August 2010 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is. See Ved's post and link.

Regards
Michel
Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471070 is a reply to message #471063] Thu, 12 August 2010 02:29 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi Ved,

Thanks for the kind reply i could check the java code and let you reply if any issues.
Re: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordina [message #471228 is a reply to message #471063] Thu, 12 August 2010 23:41 Go to previous message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
ved,

Thanks ved,after enabling the XA driver we didn't recieve any errors.
Previous Topic: open cursors recurring errors
Next Topic: making heder.txt
Goto Forum:
  


Current Time: Thu Mar 28 18:39:22 CDT 2024