1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ref Cursor

Discussion in 'SQL PL/SQL' started by suresh88, Mar 1, 2014.

  1. suresh88

    suresh88 Guest

    Can anyone explain what is the use of "Ref Cursor" in pl sql
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.

    A REF Cursor is a DATATYPE that holds a CURSOR value in the same way that a VARCHAR2 variable will hold a string value.
    A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.

    see link :
    http://docs.oracle.com/html/E10927_01/featRefCursor.htm

    http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

    sample example :
    Code (SQL):

    --Create a function that opens a cursor and returns a reference to it:
     CREATE OR REPLACE FUNCTION f
    RETURN SYS_REFCURSOR
    AS  
    c SYS_REFCURSOR;
    BEGIN
      OPEN c FOR SELECT * FROM dual;  
    RETURN c;
    END; /

     --Call the above function and fetch all rows from the cursor it returns:
     SET serveroutput ON
    DECLARE  c SYS_REFCURSOR;  
    v VARCHAR2(1);
    BEGIN  c := f();
     -- Get ref cursor from function  
    LOOP     FETCH c INTO v;    
    EXIT WHEN c%NOTFOUND;    
    dbms_output.put_line('Value from cursor: '||v);  
    END LOOP; END;
    /
     
     
    suresh88 likes this.