• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Returning Cursor from Stored Procedure

 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author & internet detective
Posts: 42173
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
and POOF! You're gone! But look, this tiny ad is still here:
Clean our rivers and oceans from home
https://www.kickstarter.com/projects/paulwheaton/willow-feeders
reply
    Bookmark Topic Watch Topic
  • New Topic