Oracle PL/SQL Tutorial/Cursor/REFCURSOR

An example of using SYS_REFCURSOR for cursor variable processing

   <source lang="sql">

SQL> create table product(

 2     product_id number(4)     not null,
 3     product_description varchar2(20) not null
 4  );

Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> DECLARE

 2    refCursorValue SYS_REFCURSOR;
 3    myRecord product%ROWTYPE;
 4  BEGIN
 5    OPEN refCursorValue FOR SELECT *from product;
 6
 7    LOOP
 8      FETCH refCursorValue INTO myRecord;
 9      EXIT WHEN refCursorValue%NOTFOUND;
10      dbms_output.put_line(to_char(myRecord.product_id)||" "||
11      rpad(myRecord.product_description,20," "));
12    END LOOP;
13    CLOSE refCursorValue;
14  END;
15  /

1 Java 2 Oracle 3 C# 4 Javascript 5 Python PL/SQL procedure successfully completed. SQL> SQL> drop table product; Table dropped.</source>

Cursor expressions as arguments to functions called from SQL

   <source lang="sql">

SQL> SQL> SQL> create table product(

 2     product_id number(4)     not null,
 3     product_description varchar2(20) not null
 4  );

Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> create table company(

 2     product_id        number(4)    not null,
 3     company_id          NUMBER(8)    not null,
 4     company_short_name  varchar2(30) not null,
 5     company_long_name   varchar2(60)
 6  );

Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> create table org_company_site(

 2     company_id number(8) not null,
 3     site_no number(4) not null
 4  );

Table created. SQL> insert into org_company_site values (1001,1); 1 row created. SQL> insert into org_company_site values (1002,2); 1 row created. SQL> insert into org_company_site values (1003,3); 1 row created. SQL> insert into org_company_site values (1004,1); 1 row created. SQL> insert into org_company_site values (1004,2); 1 row created. SQL> insert into org_company_site values (1004,3); 1 row created. SQL> insert into org_company_site values (1005,1); 1 row created. SQL> insert into org_company_site values (1005,4); 1 row created. SQL> insert into org_company_site values (1005,5); 1 row created. SQL> insert into org_company_site values (1006,1); 1 row created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION f_report(p_cursor SYS_REFCURSOR,p_title VARCHAR2)

 2  RETURN NUMBER
 3  IS
 4    v_product_description VARCHAR2(20);
 5    v_company_short_name VARCHAR2(30);
 6    v_ret_code NUMBER;
 7  BEGIN
 8    BEGIN
 9      dbms_output.put_line(p_title);
10
11      LOOP
12        FETCH p_cursor INTO v_product_description,v_company_short_name;
13        EXIT WHEN p_cursor%NOTFOUND;
14        dbms_output.put_line(rpad(v_product_description,20," ")||" "||
15        rpad(v_company_short_name,30," "));
16      END LOOP;
17      v_ret_code :=1;
18    EXCEPTION WHEN OTHERS THEN
19      v_ret_code :=SQLCODE;
20    END;
21    RETURN (v_ret_code);
22  END;
23  /

Function created. SQL> SQL> SQL> SELECT "Report Generated on "||TO_CHAR(SYSDATE,"MM/DD/YYYY ") "Report1"

 2  FROM DUAL
 3  WHERE f_report(
 4          CURSOR(SELECT h.product_description,o.rupany_short_name
 5                 FROM product h,company o
 6                 WHERE o.product_id =h.product_id
 7                 AND 1 < (SELECT count(os.site_no)
 8                          FROM org_company_site os
 9                          WHERE os.rupany_id =o.rupany_id)
10                ),
11          "List of Organizations located in more than one site"
12  )=1;

Report1

---

Report Generated on 07/24/2008 1 row selected. List of Organizations located in more than one site Oracle D Inc. Oracle E Inc. SQL> SQL> drop table company; Table dropped. SQL> SQL> drop table product; Table dropped. SQL> SQL> drop table org_company_site; Table dropped. SQL></source>

Define refcursor variable

   <source lang="sql">

SQL> set echo on SQL> SQL> set timing off SQL> SQL> create table t

 2  as
 3  select * from all_users;

Table created. SQL> SQL> variable x refcursor SQL> SQL> begin

 2          open :x for select * from t;
 3  end;
 4  /

PL/SQL procedure successfully completed. SQL> SQL> delete from t; 17 rows deleted. SQL> SQL> commit; Commit complete. SQL> SQL> print x

USERNAME USER_ID CREATED

---

---------- ---------

USER1 55 02-JUN-07 sqle 38 09-SEP-06 BOB 36 30-AUG-06 FLOWS_020100 35 07-FEB-06 FLOWS_FILES 34 07-FEB-06 HR 33 07-FEB-06 MDSYS 32 07-FEB-06 ANONYMOUS 28 07-FEB-06 XDB 27 07-FEB-06 CTXSYS 25 07-FEB-06 DBSNMP 23 07-FEB-06 ANEWUSER 44 09-SEP-06 TSMSYS 20 07-FEB-06 DIP 18 07-FEB-06 OUTLN 11 07-FEB-06 SYSTEM 5 07-FEB-06 SYS 0 07-FEB-06 17 rows selected. SQL> SQL> drop table t; Table dropped.</source>

Open SYS_REFCURSOR for select ... from

   <source lang="sql">

SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> CREATE OR REPLACE PROCEDURE emp_sel(cv_results IN OUT SYS_REFCURSOR) IS

 2  BEGIN
 3     OPEN cv_results FOR SELECT id, fname, lname FROM emp;
 4  END;
 5  /

Procedure created. SQL> SQL> COL fname FORMAT A12 SQL> VARIABLE x REFCURSOR SQL> EXEC emp_sel(:x) PL/SQL procedure successfully completed. SQL> PRINT x

       ID FNAME        LNAME

---

------------ --------------------------------------------------

        1 A            B
        2 C            D
        3 Enn          F
        4 G            H
        5 G            Z

5 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.</source>

Output value in a refcursor

   <source lang="sql">

SQL> set echo on SQL> SQL> set timing off SQL> SQL> create table t

 2  as
 3  select * from all_users;

Table created. SQL> SQL> variable x refcursor SQL> SQL> begin

 2          open :x for select * from t;
 3  end;
 4  /

PL/SQL procedure successfully completed. SQL> SQL> delete from t; 17 rows deleted. SQL> SQL> commit; Commit complete. SQL> SQL> print x

USERNAME USER_ID CREATED

---

---------- ---------

USER1 55 02-JUN-07 sqle 38 09-SEP-06 BOB 36 30-AUG-06 FLOWS_020100 35 07-FEB-06 FLOWS_FILES 34 07-FEB-06 HR 33 07-FEB-06 MDSYS 32 07-FEB-06 ANONYMOUS 28 07-FEB-06 XDB 27 07-FEB-06 CTXSYS 25 07-FEB-06 DBSNMP 23 07-FEB-06 ANEWUSER 44 09-SEP-06 TSMSYS 20 07-FEB-06 DIP 18 07-FEB-06 OUTLN 11 07-FEB-06 SYSTEM 5 07-FEB-06 SYS 0 07-FEB-06 17 rows selected. SQL> SQL> drop table t; Table dropped.</source>

Row type Reference cursor

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE pizza (

 2  code NUMBER(38) CONSTRAINT pk_pizza PRIMARY KEY NOT NULL
 3  ,flavor VARCHAR2(30) NOT NULL
 4  );

Table created. SQL> SQL> CREATE OR REPLACE PACKAGE pizza_pkg

 2     AUTHID CURRENT_USER
 3  IS
 4     c_chocolate CONSTANT PLS_INTEGER := 16;
 5     c_strawberry CONSTANT PLS_INTEGER := 29;
 6
 7     TYPE codes_nt IS TABLE OF INTEGER;
 8
 9     my_pizza codes_nt;
10
11     TYPE fav_info_rct IS REF CURSOR RETURN pizza%ROWTYPE;
12
13     PROCEDURE show_pizza (list_in IN codes_nt);
14
15     FUNCTION most_popular RETURN fav_info_rct;
16
17  END pizza_pkg;
18  /

Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY pizza_pkg

 2  IS
 3     g_most_popular   PLS_INTEGER;
 4
 5     PROCEDURE show_pizza (list_in IN codes_nt)
 6     IS
 7     BEGIN
 8        FOR indx IN list_in.FIRST .. list_in.LAST
 9        LOOP
10           DBMS_OUTPUT.put_line (list_in (indx));
11        END LOOP;
12     END show_pizza;
13
14     FUNCTION most_popular RETURN fav_info_rct
15     IS
16        retval fav_info_rct;
17        null_cv fav_info_rct;
18     BEGIN
19        OPEN retval FOR
20        SELECT * FROM pizza WHERE code = g_most_popular;
21
22        RETURN retval;
23     EXCEPTION
24        WHEN NO_DATA_FOUND
25        THEN
26           RETURN null_cv;
27     END most_popular;
28
29     PROCEDURE analyze_pizza (year_in IN INTEGER)
30     IS
31     BEGIN
32
33        NULL;
34     END analyze_pizza;
35
36  BEGIN
37     g_most_popular := c_chocolate;
38
39     analyze_pizza (EXTRACT (YEAR FROM SYSDATE));
40  END pizza_pkg;
41  /

SP2-0810: Package Body created with compilation warnings SQL> SQL> SQL> SQL> SQL> SQL> drop table pizza; Table dropped. SQL> SQL></source>

Subprograms returning resultsets by using SYS_REFCURSOR

   <source lang="sql">

SQL> SQL> SQL> create table product(

 2     product_id number(4)     not null,
 3     product_description varchar2(20) not null
 4  );

Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE FUNCTION getAllHierarchies

 2  RETURN SYS_REFCURSOR
 3  IS
 4    refCursorValue SYS_REFCURSOR;
 5  BEGIN
 6    OPEN refCursorValue FOR SELECT * FROM product;
 7
 8    RETURN (refCursorValue);
 9
10  EXCEPTION WHEN OTHERS THEN
11
12    RAISE_APPLICATION_ERROR(-20002,SQLERRM);
13
14  END;
15  /

Function created. SQL> SQL> DECLARE

 2    refCursorValue SYS_REFCURSOR;
 3    myRecord product%ROWTYPE;
 4  BEGIN
 5    refCursorValue :=getAllHierarchies;
 6
 7    LOOP
 8
 9      FETCH refCursorValue INTO myRecord;
10
11      EXIT WHEN refCursorValue%NOTFOUND;
12
13      dbms_output.put_line(TO_CHAR(myRecord.product_id)||" "||myRecord.product_description);
14
15    END LOOP;
16
17  EXCEPTION WHEN OTHERS THEN
18
19    dbms_output.put_line(TO_CHAR(SQLCODE)||" "||SQLERRM);
20
21  END;
22  /

1 Java 2 Oracle 3 C# 4 Javascript 5 Python PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table product; Table dropped. SQL></source>

SYS_REFCURSOR type parameter

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> CREATE OR REPLACE PROCEDURE emp_sel_regexp_like (cv_emp IN OUT SYS_REFCURSOR)

 2  IS
 3  BEGIN
 4
 5     OPEN cv_emp FOR
 6     SELECT id, fname, lname
 7     FROM emp
 8     WHERE REGEXP_LIKE(lname, "^h", "i");
 9
10  EXCEPTION
11     WHEN OTHERS
12     THEN
13        DBMS_OUTPUT.PUT_LINE(sqlerrm);
14  END;
15  /

Procedure created. SQL> SQL> COL fname FORMAT A20 SQL> COL lname FORMAT A20 SQL> SQL> VARIABLE x REFCURSOR SQL> EXEC emp_sel_regexp_like(:x) PL/SQL procedure successfully completed. SQL> SQL> print x no rows selected SQL> drop table emp; Table dropped. SQL></source>

The use of REF CURSOR

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

---

-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> CREATE OR REPLACE PROCEDURE authors_sel (cv_results IN OUT SYS_REFCURSOR)

 2  IS
 3  BEGIN
 4     OPEN cv_results FOR
 5     SELECT id, first_name, last_name
 6     FROM employee;
 7  END;
 8  /

Procedure created. SQL> SQL> VARIABLE x REFCURSOR SQL> EXEC authors_sel(:x) PL/SQL procedure successfully completed. SQL> print x

ID FIRST_NAME LAST_NAME

---

-------------------- --------------------

01 Jason Martin 02 Alison Mathews 03 James Smith 04 Celia Rice 05 Robert Black 06 Linda Green 07 David Larry 08 James Cat 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>