• 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

CallableStatement w Oracle 9i

 
Greenhorn
Posts: 17
Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am making a function call to an oracle package/function that is returning a value. I am getting at the execute statement an UnsatisfiedLinkError on oracle.jdbc.oci8.OCIDBAccess.copy_value_from_binds(Native Method). I am using an 8i driver for Oracle 9i and have retreived the latest classes12 from Oracle (8i for 9i). getConfigValue just returns a string.

My Code:
public String getSystemConfigInfo(String pConfigName){
String sTmp = "";
try{
String sql = "{? = call rts_utility.getConfigValue(?)}";

CallableStatement st = getConnection().prepareCall(sql);

st.setString(2, pConfigName);
st.registerOutParameter(1, Types.VARCHAR);
st.execute();
sTmp = st.getString(1);
st.close();//071706
}
catch(Exception ex){
//bury the error... just return ""
sTmp = "";
}
return sTmp;
}

Any assistance will be much appreciated.

Thank you,
Mary
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Mary Shell:

String sql = "{? = call rts_utility.getConfigValue(?)}";

Mary



Mary, I'm no JDBC/Java expert, but that isn't valid SQL syntax for using a function in a SQL statement... even if it is going to be used in a WHERE clause... which I'm guessing is what it is being used for with you substituting the remaining SQL.
[ October 23, 2007: Message edited by: Paul Campbell ]
 
Mary Shell
Greenhorn
Posts: 17
Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried the statement as "begin ? := rts_utility.getConfigValue(?); end;"

and I still got the same error. This statement was working with the 8i driver/8i database combo. It isn't working with the 8i driver/9i database. I have no choice in the driver/database setup as this is happening at a customer site.

Thank you for your quick response.

Mary
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mary,

I believe your connection for the Oracle OCI driver should use a URL of the form jdbcracle:oci8:@tnsname.

Are you doing this? Is there any reason you can't use the thin driver... I currently have 3 sites that are using the 8i driver against 9i utilizing thin.

I'm more a help at this point with SQL and data base concepts... I'm still a neophyte at java. So for a database connection issue with JDBC Jeanne and the others are more of a help than I.

Paul
 
Mary Shell
Greenhorn
Posts: 17
Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul,

I double-checked my connection string and it is:
jdbc:oracle:oci8:@<tns alias>

All, the other sql statements work like resultsets,etc. Just the callablestatement is the only thing that isn't. Someone on another forum suggested the 'library' is missing something. I'm having to set up boxes to test this situation.

Thanks for your help. It's good to know that it should work.

Are you using the classes12 or the other library?

Thanks again.
Mary
 
Mary Shell
Greenhorn
Posts: 17
Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul,
Sorry, just realized you asked about using the thin driver. The decision to go OCI was made years ago. Our customer has to be able to switch between databases and open many sessions. Oracle recommends OCI over Thin in that case.
I may have to upgrade to the ojdbc14.jar over classes12. Again that will have to wait until I can recreate my customer's situation, which I hope will be soon.

Thanks again,
Mary
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey Mary,

Everything I see regarding OCI in Oracles documentation suggests you need to go to 9i... I'm not sure that helps your cause much at the moment.

Paul
 
Mary Shell
Greenhorn
Posts: 17
Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

FYI, I switched out the classes12.zip I had downloaded from oracle (8i for 9i) with the one we had been using from 2001? and low and behold -> IT WORKED! It took us longer to get the test machines and set it up than to actually solve the issue. Just goes to show newer isn't always better.

Thanks for the help.

:roll:
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic