RE: Beginning SQL statements with a parenthesis in JDBC

["Bradley Roberts" <Bradley@Roberts.net>]

You might examine the SQLSTATE variable to identify this error further. This
would indicate what the Oracle engine found in the statement that it didn't
like. Then look up that code in your Oracle reference manual. That will
usually provide a clue as to how to resolve the issue.

Your statement is no doubt rather complex. And it may that there is an error
in the parsing algorithm that passes the statement through JDBC. You should
report that, so that the bug can be fixed. However, to get around the
problem, you can prepare the statement in advance, or you can make the
statement a stored procedure that you then call through the SQL interface.
Of course, I don't know what kind of data you are expecting back from this.
It may be that you are also asking for too much data. I've seen DBMS engines
get into trouble when a huge amount of data was requested. They simply run
out of temporary log space to store the intermediate results. Be sure to
estimate the results of your query, and ensure that it is reasonable.

Hope this helped.

Brad Roberts
Bradley@Roberts.net



> -----Original Message-----
> From: Mose, Lisle [mailto:Lisle@mediaocean.com]
> Sent: Tuesday, February 18, 2003 3:43 PM
> To: ajug-members@ajug.org
> Subject: Beginning SQL statements with a parenthesis in JDBC
>
>
> Hello all,
>
> I'm running into a problem beginning a JDBC SQL statement with a
> parenthesis.  I need to do something similar to the following:
> "(SELECT ...) UNION (SELECT ...)"     - the parenthesis are
> necessary for grouping purposes.
>
> For the sake of testing, I tried the following and get the same error:
> "(SELECT SYSDATE FROM DUAL)"     - of course this works fine
> without the parenthesis.  Also, it works fine with the
> parenthesis when using sqlplus instead of JDBC.
>
>
> Here is the exception and stacktrace:
>
> Exception in thread "main" java.sql.SQLException: ORA-01009:
> missing mandatory parameter
>
>         at
> oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
>         at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
>         at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
>         at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
>         at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:889)
>         at
> oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:242)
>         at JDBCTest.main(JDBCTest.java:17)
>
>
> I'm running against Oracle 8.1.7 using Weblogic 6.1's JDBC thin driver.
>
> Has anyone encountered this problem before?  Is there a workaround?
>
> Thanks!
> Lisle Mose
>
>