Home » SQL & PL/SQL » SQL & PL/SQL » after coding the sucessful cursor what are the steps to get the cursor output
after coding the sucessful cursor what are the steps to get the cursor output [message #37494] Tue, 12 February 2002 01:53 Go to next message
ranjan kumar dash
Messages: 15
Registered: February 2002
Junior Member
I wanted to know suppose i have created a cursor in SQL prompt then how can i see the output for which i have written the cursor.Means i want to know the steps after writting the sucessful cursor.
For example after writting the stored procedure we can see the effect by writting in sql prompt
SQL> set serveroutput on
SQL> exec 'procedure name'
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 Go to previous message
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;
/
Previous Topic: table exists .. or not
Next Topic: One URGENT question about SQL
Goto Forum:
  


Current Time: Sat Sep 25 00:55:15 CDT 2021