|
|
| Re: after coding the sucessful cursor what are the steps to get the cursor output [message #37509 is a reply to message #37494] |
Tue, 12 February 2002 11:14  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
1.) using sqlplus specific functionality:
SQL> var ri refcursor
SQL> begin
2 open :ri for select * from dept;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print ri
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT Noida
2.) using good old pl/sql anonomous block:
CREATE TABLE ABC (A NUMBER);
insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);
CREATE OR REPLACE PACKAGE ref_cur_tst
IS
-- dummy cursor to get %rowtype below
CURSOR c1 IS SELECT a FROM abc;
TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;
-- or just use abc%rowtype to match whole table structure.
-- TYPE t_cur IS REF CURSOR RETURN abc%ROWTYPE;
PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/
CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
PROCEDURE get_abc (cv_cur IN OUT t_cur)
IS
BEGIN
OPEN cv_cur FOR SELECT a FROM abc;
END get_abc;
END ref_cur_tst;
/
set serveroutput on;
DECLARE
a abc.a%TYPE;
cv_c1 ref_cur_tst.t_cur;
BEGIN
ref_cur_tst.get_abc (cv_c1);
LOOP
FETCH cv_c1 INTO a;
EXIT WHEN cv_c1%NOTFOUND;
DBMS_OUTPUT.put_line (a);
END LOOP;
CLOSE cv_c1;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
