aspose file tools*
The moose likes JDBC and the fly likes What am i doing wrong here? ( Stored Procedure REF CURSOR Problem) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "What am i doing wrong here? ( Stored Procedure REF CURSOR Problem)" Watch "What am i doing wrong here? ( Stored Procedure REF CURSOR Problem)" New topic
Author

What am i doing wrong here? ( Stored Procedure REF CURSOR Problem)

Jeetesh Khatwani
Greenhorn

Joined: Oct 08, 2002
Posts: 2
Hi All,
I have a stored procedude that has a REF CURSOR as its out parameter.
Here is the code that I am trying:
--------------------------------------
conn = dbUtil.getConnection();
stmt = conn.prepareCall(
"{call P_ZEL_REPORTING.PAGE_SENT(?,?)}");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setString(2, monthYear);
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
-------------------------------------------------
I get the following exception at runtime: SQLException: Unknown type: -10
I have checked everywhere, and it says that I shoud use OracleType.CURSOR and then use the ResultSet object.
I am sure that the problem is not with the code. I am importing all the necessary packages. Is there something I need to have in my environment (some entry in the class path, or something like that) to get this to work. I do have classes12.zip in the classpath.
The wierd thing is I am running many other methods from the same class and calling other stored procedures, and they all work. The only thing different about this one is that it has a REF CURSOR as its about parameter.

I would really appreciate any help. Running out of options here.
Thanks, Jay
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

you need to have the import statement;
import oracle.jdbc.*;
everything else looks fine( see here for an example )
Jamie
Jeetesh Khatwani
Greenhorn

Joined: Oct 08, 2002
Posts: 2
Hey Jamie,
I was importing: oracle.jdbc.driver.*; before and on your suggestion I imported oracle.jdbc.*; and guess what: I get the same exact exception.
Any other ideas? Anyone? Let me know
Thanks, Jay
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I found another way to get the cursor from browsing the Oracle site. I came accross this alternate way to do it:

The only other difference that I see between your code and the Oracle samples is that the Oracle samples use functions that return the ref cursor, instead of having it as an out parameter in a procedure.
I haven't read any documentation that says that it needs to be a return value from a function, but at this point, I'm just trying to make some sense of this.
Jamie
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Which drivers are you using? "-10" is not an error message I'd expect from the Oracle drivers. Usually you get an "ORA-XXXXX" error message. If you are using an older driver such as classes111.zip, your driver may not know what an OracleTypes.CURSOR (or any other jdbc 2.0 datatype )is, but still be able to support your other jdbc 1.0 code.
Jamie
brian vaughan
Greenhorn

Joined: Oct 12, 2004
Posts: 1
maybe he got a non-oracle statement object? -10 is the value of OracleTypes.CURSOR, which a non-oracle statement object wouldn't recognize.
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
This code works for me chk it out ..


But when i tried to use a oracle function instead of a stored procedure it's nt working .. i'm getting some errors while registering the return value.
Srini


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What am i doing wrong here? ( Stored Procedure REF CURSOR Problem)
 
Similar Threads
[Resolved] Oracle CallableStatement Invalid Index
implementing stored procedure in hibernate
cursor?
Invoking a Oracle Stored Proc which takes a REF CURSOR as a IN parameter
Closing cursor in a stored procedure when invoked from a callable statement