wood burning stoves 2.0*
The moose likes JDBC and the fly likes CallableStatement w Oracle 9i Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CallableStatement w Oracle 9i" Watch "CallableStatement w Oracle 9i" New topic
Author

CallableStatement w Oracle 9i

Mary Shell
Greenhorn

Joined: Jan 15, 2003
Posts: 15

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


Thanks,
Mary
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Jan 15, 2003
Posts: 15

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

Joined: Oct 06, 2007
Posts: 338
Mary,

I believe your connection for the Oracle OCI driver should use a URL of the form jdbcracleci8:@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

Joined: Jan 15, 2003
Posts: 15

Paul,

I double-checked my connection string and it is:
jdbcracleci8:@<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

Joined: Jan 15, 2003
Posts: 15

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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Jan 15, 2003
Posts: 15

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:
 
 
subject: CallableStatement w Oracle 9i
 
Similar Threads
Read File in PL/SQL
1Z0-007 instead of 1Z0-001 for DBA 8i?
Calling Oracle functions
Searching words in VARCHAR2 & CLOB
CallableStatement Execute gets FATAL ERROR oraclient10.dll