Home » SQL & PL/SQL » SQL & PL/SQL » Cursed CURSORS...
Cursed CURSORS... [message #36070] Fri, 02 November 2001 06:21 Go to next message
George Larry
Messages: 10
Registered: October 2001
Junior Member
I'm new to the CURSOR thing, but I need to retrieve results from a SELECT and I think I'm doing it right, but apparently I'm missing something...
Here is my procedure (I have a bad bind variable 'rSet'):
CREATE OR REPLACE PROCEDURE billByDept(
uNum IN ASSIGNMENTS.USERID%TYPE,
rSet OUT REFCURSOR
) AS
BEGIN
OPEN :rSet FOR SELECT ASSIGNREFNUM FROM ASSIGNMENTS WHERE USERID = uNum;
Dbms_output.Put_line(rSet);
END;
/
I'm trying to retrieve the results using this JSP:
cs = con.prepareCall ("{ call billByDept (?,?)}");
cs.setString(1, uNum);
cs.registerOutParameter(2, Types.CURSOR);

cs.execute();
rs = ((OracleCallableStatement)cs).getCursor (1);

while( rs.next() ) {
out.println( "Assignment Num = " + cs.getString(2) + "
" );
}

I'm using Oracles thin driver for 8i.
I'd appreciate any help-
Thanks.

----------------------------------------------------------------------
Re: Cursed CURSORS... [message #36073 is a reply to message #36070] Fri, 02 November 2001 08:52 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
George, you're pretty close, but here's what you need to change (at least on the Oracle side - I can't speak for the JSP syntax):

1) Declare a type in a package spec for the ref cursor:

create or replace type_pkg
is
type rc is ref cursor;
end;
/

2) Then, in your procedure, reference this type
and don't use the colon before the variable:

CREATE OR REPLACE PROCEDURE billByDept(
uNum IN ASSIGNMENTS.USERID%TYPE,
rSet OUT type_pkg.rc
) AS
BEGIN
OPEN rSet FOR SELECT ASSIGNREFNUM FROM ASSIGNMENTS WHERE USERID = uNum;
END;
/

----------------------------------------------------------------------
Re: Cursed CURSORS... [message #36075 is a reply to message #36073] Fri, 02 November 2001 11:09 Go to previous message
George Larry
Messages: 10
Registered: October 2001
Junior Member
Thank you... Thanks to you I got it to work!

----------------------------------------------------------------------
Previous Topic: Re: creating a view within a function
Next Topic: Renditions and Conditions
Goto Forum:
  


Current Time: Mon Oct 26 13:57:56 CDT 2020