OraFAQ Forum: General » java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordinate s

Home » Other » General » java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordinate s (Oracle 10g Linux )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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