aspose file tools*
The moose likes JDBC and the fly likes Returning Cursor from Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Returning Cursor from Stored Procedure" Watch "Returning Cursor from Stored Procedure" New topic
Author

Returning Cursor from Stored Procedure

Patrick Ferguson
Ranch Hand

Joined: Jun 15, 2004
Posts: 30
This seems to be a common problem and I have done it in the past but cannot for the life of me remember how I did it.

I have a simple stored procedure which does a select statement and needs to send me the resultset back. I've got a cursor set up and when I run it through the sqlplus it works just fine, but when I try to run my java against it I get a "PLS-00306: wrong number or types of arguments in call to 'PROCEDURE'" error.

Here are the snippets of my code, any help would be greatly appreciated.

Stored Proc:

CREATE OR REPLACE PACKAGE Types IS

TYPE cursor_type IS REF CURSOR;

END Types;
/

CREATE OR REPLACE PACKAGE Security AS

PROCEDURE thisProcedure(as_ssn IN VARCHAR2, test OUT Types.cursor_type);


END Security;
/

CREATE OR REPLACE PACKAGE BODY Security AS

PROCEDURE thisProcedure
(
as_ssn IN VARCHAR2,
c1 OUT Types.cursor_type
)
IS

BEGIN

OPEN c1 FOR
SELECT * FROM GROUPID WHERE SSN = as_ssn;
END thisProcedure;

END Security;
/


Java:

proc = con.prepareCall("{ ? = call Security.thisProcedure(?) }");
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.setString(2, as_userid);
proc.execute();
rs = (ResultSet) proc.getObject(1);

If anyone sees a problem, PLEASE help. Thanks.
Patrick Ferguson
Ranch Hand

Joined: Jun 15, 2004
Posts: 30
I would like to thank all of the people who helped out with this.

/sarcasm off

The solution to this was the format of the con.prepareCall string. I found this format to work

proc = con.prepareCall("BEGIN Jass_Security.SECUR_D_LOGIN_INFO(?,?); END;");

I hope this helps others.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30913
    
158

Patrick,
Your code looked like it should have worked, which is why I suspect you didn't get any responses.

Thanks for posting the answer so future people who read this thread don't have to struggle with it.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Returning Cursor from Stored Procedure