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

[Jeetesh Khatwani]

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]

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

[Jeetesh Khatwani]

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]

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

import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;
// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select ename from emp; end;");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);
// Use the cursor like a normal ResultSet
while (cursor.next ())
{
    {System.out.println (cursor.getString(1));} 
}

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]

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]

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]

This code works for me chk it out ..

 stmt = con.prepareCall("{ call Get_error_details(?,?)}");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setInt(2, transID);
stmt.execute();
logger.log("Executed the Sp");
ResultSet rs = (ResultSet)stmt.getObject(1);; 

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