too many parse errors

admin

Query With Resultset.TYPE_SCROLL_SENSITIVE Causes ORA-936 (Doc ID 2367548.1)
Applies to:  JDBC - Version 11.2.0.1.0 and later
Information in this document applies to any platform.
SymptomsWhen an application using JDBC driver version 11.2. or later executes a query like:

String sql =select * from <TABLE_NAME> where <COLUMN_NAME>=?";
that is done on a statement of type:

  stmt =conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY),
the following parse error occurs :
ORA-00936: missing expression

Changes
CauseThe issue is caused by SELECT * limitation when using JDBC "ResultSet.TYPE_SCROLL_SENSITIVE.

It is intended behavior that when using JDBC "ResultSet.TYPE_SCROLL_SENSITIVE", a "ROWID" will be automatically added to the query.
That is why the rowid as "__Oracle_JDBC_internal_ROWID__", is appended.

Now, since your queries are of type "select *...", and these cannot have a rowid added to them without becoming syntactically incorrect, the parse fails and generates the ORA-00936: missing expression reported in the database traces:

PARSE ERROR #140638236435256:len=109 dep=0 uid=<UID> oct=3 lid=<UID> tim=1021139536948 err=936
select rowid as "__Oracle_JDBC_interal_ROWID__", * from TBL where TBL_CODE=:1
WAIT #140638236435256: nam='SQL*Net break/reset to client' ela= 4 driver id=1952673792 break?=1 p3=0 obj#=67770 tim=1021139537026
WAIT #140638236435256: nam='SQL*Net break/reset to client' ela= 29066 driver id=1952673792 break?=0 p3=0 obj#=67770 tim=1021139566105
WAIT #140638236435256: nam='SQL*Net message to client' ela= 4 driver id=1952673792 #bytes=1 p3=0 obj#=67770 tim=1021139566166
WAIT #140638236435256: nam='SQL*Net message from client' ela= 49792 driver id=1952673792 #bytes=1 p3=0 obj#=67770 tim=1021139615990
CLOSE #140638236435256:c=0,e=4,dep=0,type=0,tim=1021139616056

Reference: Document: 1566018.1 High Waits for 'library cache lock' and 'library cache: mutex X' Due to Parse Failures When Using JDBC ResultSet.TYPE_SCROLL_SENSITIVE.

SolutionAs a workaround for the SELECT * limitation, use table aliases.
For example, the query used by the application is:
String sql = "select * from <TABLE_NAME> where <COLUMN_NAME>=?";

Use a table alias , like:


String sql = "select * from <TABLE_NAME> t where t.<COLUMN_NAME>=?";