I ran in a strange case of a ORA-01009 code. I am executing a prepared statement on a query which uses the WITH clause. That query executes fine in SQLDeveloper, but the ResultSet.next() fails when ran from the java application.
Here is the code:
I am not doing any INSERT with '?'s.
And the StackTrace:
I found that odbc drivers fails when running a query surrounded by brackets '('')', could this be the case?
As per my understanding JDBC is not database specific that means it will only provide support to standard SQL. Here standard means not database specific queries (like "select * from tab" in Oracle or "list active databases" in DB2). So if you want to use the "with" clause then first pick the proper jar which contains the jdbc driver and other useful classes and check whether they support the "with" or any other keyword what you intend to use.
Java Programmer | SCJP 1.5 | SCWCD 1.4
Joined: Oct 11, 2010
Fabian Boost wrote:The with clause is not implemented in every database. I know it works in Oracle 9i R2 and higher.
The syntax is:
[code.] WITH tab1 as (SELECT <columns> FROM <table> ... ) SELECT <columns> FROM tab1[/code]
You can select a subset of columns from the inner select clause in the outer one. The Oracle error refers to the incomplete outer select clause in your statement.
In fact I misquoted, the query is syntactically correct, as previously stated... I can't provide it now, because it's work material, and I am ill at home now.