This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Stored Proc Call with CallableStatement and Parameter Name Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored Proc Call with CallableStatement and Parameter Name" Watch "Stored Proc Call with CallableStatement and Parameter Name" New topic
Author

Stored Proc Call with CallableStatement and Parameter Name

Favil Von
Greenhorn

Joined: Dec 10, 2013
Posts: 13
Hi,

I'm having issues when I set values for the CallableStatement of Oracle JDBC driver when the parameter names are used.



Running the code would result in the following SQLException:



If I comment out the first setting, I get the same error for the second parameter (and subsequent ones if I go down the list commenting out the next parameter). I can run the procs through a SQL command without an issue.
Tina Smith
Ranch Hand

Joined: Jul 21, 2011
Posts: 171
    
    5

What is the signature of the stored procedure that you are attempting to call? Oracle usually has pretty descriptive error messages. Odds are that one of these parameter names does not match the parameter names of the stored procedure, or you have not passed all the parameters the stored procedure requires.

In my experience, case does not matter.


Everything is theoretically impossible, until it is done. ~Robert A. Heinlein
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

According to this table, it is possible to get or set stored procedure parameters by name only since Oracle JDBC driver version 10.1.0. (Generally, Oracle releases a new JDBC driver together with the database, so they usually share the database version number. The files themselves are always named ojdbc5.jar/ojdbc6.jar - hopefully you're not using the really ancient classes*.jar - so you've got to dig into the MANIFEST.MF file inside the jar to determine the driver's version number.)

I'd suggest upgrading to the newest Oracle JDBC driver your target database will support (see Which version?). Perhaps the code will just start working with a newer driver version. If it doesn't, well, you can always set parameters by position, instead of by name (which is what I always do). It should even possible to use positional parameters in JDBC to make a by-name parameters call in the database:

(I haven't tested this myself, I've just found it here.)
Favil Von
Greenhorn

Joined: Dec 10, 2013
Posts: 13
Tina Smith wrote:What is the signature of the stored procedure that you are attempting to call? Oracle usually has pretty descriptive error messages. Odds are that one of these parameter names does not match the parameter names of the stored procedure, or you have not passed all the parameters the stored procedure requires.

In my experience, case does not matter.


I have copied and pasted the parameter names from the package.procedure's own body in the database, so they are bounded to be corresponding (I've checked them multiple times).

There are several values that need to be passed as NULL due to values not being available. I have placed "null" in their spot, i.e. call MY_PKG.MY_STORE_PROC(?, ?, ?, ?, ?, ?, null)}", which produces the same error. I even passed a dummy value, but the problem persists. It always points to the very first parameter that it attempts to set, no matter which one it is.
Favil Von
Greenhorn

Joined: Dec 10, 2013
Posts: 13
Martin Vajsar wrote:According to this table, it is possible to get or set stored procedure parameters by name only since Oracle JDBC driver version 10.1.0. (Generally, Oracle releases a new JDBC driver together with the database, so they usually share the database version number. The files themselves are always named ojdbc5.jar/ojdbc6.jar - hopefully you're not using the really ancient classes*.jar - so you've got to dig into the MANIFEST.MF file inside the jar to determine the driver's version number.)

I'd suggest upgrading to the newest Oracle JDBC driver your target database will support (see Which version?). Perhaps the code will just start working with a newer driver version. If it doesn't, well, you can always set parameters by position, instead of by name (which is what I always do). It should even possible to use positional parameters in JDBC to make a by-name parameters call in the database:

(I haven't tested this myself, I've just found it here.)


According to the ojdbc6.jar that I unraveled, the driver is 11.2, so it should support parameter name (as a matter of fact, it calls addParam() method of OracleCallableStatement). So, I don't believe is the version I am using.

Setting parameters based on position does not produce that error, however, with having 40+ parameters in the proc and 90% not being set (at least from our code), it becomes prone to error if indexing is utilized.

I had actually tried to get it to work by emulating the suggested answer on that page, but was running to some other issues. I had not, however tried your way (I will do that by EOD and let you know).
 
Consider Paul's rocket mass heater.
 
subject: Stored Proc Call with CallableStatement and Parameter Name
 
Similar Threads
Runtime exception java.lang.ClassNotFoundException on executing Stored Proc
Number of parameters in CallableStatement is less than the number of arguments in SP
Problem using Callable Statement
A comment inside a CallableStatement messes with parameters
CallableStatement throwing data conversion error (Oracle)