This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
Setup jdbc provider and datasource for oracle correctly, tested connection and its working fine on admin console.
In my application, lookup datasource to get connection, then have a call to the database connection on a stored function in oracle. It returns a REFCURSOR so my java line to return the result set is:
result = ((OracleCallableStatement)cs).getCursor(1);
This gives me the exception above.
i have been searching for solutions on this problem for nearly a day now and found one:
Error on datasource:
You can't count on the implmentation of the CallableStatement interface being the Oracle version, even though your underlying database is Oracle. When you use a WebSphere Data Source, neither the connection nor the objects it creates will cast to Oracle objects.
Because of these issues, IBM has provided a way to get at the "native" (in this case, Oracle) DataBase connection, and consequently at the native objects it creates. The help is in the form of the com.ibm.ws.rsadapter.jdbc.WSJdbcUtil class, which is in the WebSphere class path. Just use it's getNativeConnection method to get the underlying Oracle connection, and then the CallableStatement you produce from it will cast correctly. Example:
Shane Lee wrote:Hi Unu ,
Ha thats a while back since i worked on that! Looked at my existing code that i have on my laptop and this is a code snippet of how i called a stored procedure.
As you can see from the above comment, i use java.sql.CallableStatement now instead of OracleCallableStatement.
Brilliant! Not only can I avoid using yet another Oracle-specific type, the code is cleaner and easier to understand. I was looking at some rather nasty changes to setting up the connection pool, which I had no assurance would work. Now if we could only get Oracle to (a) change their documentation on how to use a Cursor, and (b) allow returning pure result sets instead of having to return a cursor object.
but does not seem to have worked for me.I will still run some more tests and let you know about the results.
It worked for me this more generic approach in the end. I was having problems with duplicated entry in the classpath but it is fixed now and it is working with the generic approach I have mentioned here.