Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

WITH clause generates ORA-01009: missing mandatory parameter

 
Ronnie Coleman
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everybody,

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?

Thanks in advance.

 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Ronnie.

I have never seen this type of select syntax: WITH <table> AS (SELECT...
Can you point us to the definition of this type of sql statement?

Regards, Jan
 
Fabian Boost
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The with clause is not implemented in every database. I know it works in Oracle 9i R2 and higher.
The syntax is:

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.
 
Soumyajit Hazra
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ronnie Coleman
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Thank you all, anyway.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic