• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

WITH clause generates ORA-01009: missing mandatory parameter

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic