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

Stored Proc Call with CallableStatement and Parameter Name

 
Favil Von
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic