• 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Stored Proc Call with CallableStatement and Parameter Name

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
My, my, aren't you a big fella. Here, have a tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic