Returning Cursor from Stored Procedure

[Patrick Ferguson]

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]

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]

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.