aspose file tools*
The moose likes JDBC and the fly likes WITH clause generates ORA-01009: missing mandatory parameter Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "WITH clause generates ORA-01009: missing mandatory parameter" Watch "WITH clause generates ORA-01009: missing mandatory parameter" New topic
Author

WITH clause generates ORA-01009: missing mandatory parameter

Ronnie Coleman
Greenhorn

Joined: Oct 11, 2010
Posts: 2
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Fabian Boost
Greenhorn

Joined: Oct 11, 2010
Posts: 1
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

Joined: Jun 26, 2007
Posts: 136
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
Ronnie Coleman
Greenhorn

Joined: Oct 11, 2010
Posts: 2
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: WITH clause generates ORA-01009: missing mandatory parameter