| Cursed CURSORS... [message #36070] |
Fri, 02 November 2001 06:21  |
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  |
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;
/
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
