My Oracle Support Banner

Cursor Leak / ORA-1000 From PreparedStatement.getMetaData() Method On Malformed Statements (Doc ID 2124104.1)

Last updated on OCTOBER 11, 2018

Applies to:

JDBC - Version 11.2.0.4.0 to 12.1.0.2.0 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms


Cursor leak / ORA-1000 from PreparedStatement.getMetaData() method on malformed statements when using JDBC 11.2.0.4 and 12.1.0.2.

An application allows users to enter SQL statements that will be saved for later use in processing. All these statements need to be validated against a schema, so Oracle JDBC's driver capability to parse/validate the statement without actually executing it through the use of PreparedStatement.getMetaData() method is used. Unfortunately, when the statement is incorrect, the PreparedStatement.getMetaData() method would leak the cursor and the session would eventually become "dead" due to running out of open cursors and generating ORA-01000: maximum open cursors exceeded.
The only way to recover is by closing the physical database connection through restarting the application server.

The issue occurs with JDBC versions 11.2.0.4, 12.1.0.2

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Execute as sys "alter system set open_cursors=50 scope=memory;" to set some low limit for the test.
2) Make sure that your schema does not have table "foo".
3) Compile and execute the following method:

public static void test(Connection conn) throws SQLException {
PreparedStatement stmt = null;
ResultSetMetaData md = null;

for (int i = 0; i < 100; i++) {
try {
System.out.print(i + " : ");
stmt = conn.prepareStatement("SELECT 1 col FROM foo WHERE 1=0");

md = stmt.getMetaData(); // This leaks cursors on invalid statements

System.out.print("[");
for (int j = 1; j <= md.getColumnCount(); j++) {
if (j > 1) { System.out.print(", "); }
System.out.print(md.getColumnName(j));
}
System.out.print("]");

stmt.close(); stmt = null;
System.out.println(" closed");
} catch (SQLException e) {
if (e.getErrorCode() == 942) {
System.out.print("[ignoring error ORA-00942]");
} else {
e.printStackTrace();
throw e;
}
} finally {
if ( stmt != null) {
stmt.close(); stmt = null;
System.out.println(" closed");
}
}
}

System.out.println("Finished");
}



Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.