• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jeetesh Khatwani
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you need to have the import statement;
import oracle.jdbc.*;
everything else looks fine( see here for an example )
Jamie
 
Jeetesh Khatwani
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic