Database 11gR2 Returning Different Error To The JDBC Application ORA-604 Instead Of ORA-1000 (Doc ID 1328537.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 03-Dec-2012***

Symptoms


An application that connects to the database using jdbc

When connecting to a 10.2.0.5.0 database
The application received the returning values this way:

toString(): java.sql.SQLException: ORA-01000: maximum open cursors exceeded
getErrorCode(): 1000
getSQLState(): 72000
getNextException(): null

When connecting to an 11.2.0.2.0 database the error received is not ORA-1000 but ORA-604:

toString(): java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

getErrorCode(): 604
getSQLState: 60000
getNextException(): null

The cause is the trigger MDSYS.SDO_GEOR_ERR_TRIGGER

MDSYS has AFTER ERROR trigger SDO_GEOR_ERR_TRIGGER. This is defined, in 11.2.0.2.0 as:

select trigger_body from user_triggers
where trigger_name='SDO_GEOR_ERR_TRIGGER';

declare
m_stmt varchar2(512);
begin
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;

Explanation:
In 11.2.0.2.0 we can see why we get ORA-604 for an ORA-1000. The db raises the error from the select but this trigger fires for any error. It then needs a cursor itself to perform a delete so this also fails with ORA-1000, so you get ORA-604 telling you a recursive call, in this case system event trigger, failed.
The first ORA-1000 is from the cursor in the trigger and the second from the select issued by JDBC.

This is a problem for the customer because they have to categorize the error received in the application.

Changes

Upgrade from 10.2.0.5.0 to 11.2.0.2.0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms