Accessing REFCURSOR in another PL/SQL program
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Accessing REFCURSOR in another PL/SQL program

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    Accessing REFCURSOR in another PL/SQL program

    Typically a REFCURSOR is read by java and the set of data is processed within java. In our case we want to use that same REFCURSOR in another PL/SQL procedure.

    How can we assign a REFCURSOR that resulted from package-1 to a cursor(expicit or implied) in a second package-2 ?

    I hope i make sense.
    thanks for your help.

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    create or replace package pk1 as
    type t1 is ref cursor;
    procedure p1( x out t1);
    end pk1;
    /

    create or replace package body pk1 as

    procedure p1 (x out t1) as

    begin

    open x for select * from emp;

    end p1;

    end pk1;

    set serverout on
    declare
    a emp%rowtype;
    c pk1.t1;

    begin
    pk1.p1(c);
    loop
    fetch c into a;
    exit when c%NOTFOUND;
    dbms_output.put_line( a.ename||' '||a.sal||' '||a.empno);
    end loop;

    end;
    /

    Cheers
    Slash

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    In your refcursor you demonstrate:
    open x for select * from emp;

    what happens if we had a refcursor such as:
    open x for select .... from emp, dept, address where ....;

    how do we define:
    a emp%rowtype; ????

    Thank you for your time and help.

  4. #4
    Join Date
    Aug 2003
    Posts
    11
    Hi,
    You can declare local variables or a record type.

    create or replace package pk1 as
    type t1 is ref cursor;

    procedure p1( x out t1);

    type r is record
    (
    vn_empno emp.empno%type,
    vs_ename emp.ename%type,
    vn_sal emp.sal%type
    );

    end pk1;
    /

    create or replace package body pk1 as

    procedure p1 (x out t1) as

    begin

    open x for select empno,ename,sal from emp;

    end p1;

    end pk1;

    declare
    type s is ref cursor;
    c s;
    r1 pk1.r;
    begin
    pk1.p1(c);
    loop
    fetch c into r1;
    exit when c%NOTFOUND;
    dbms_output.put_line( r1.vs_ename||' '||r1.vn_sal||' '||r1.vn_empno);
    end loop;

    end;

    Cheers
    Slash

  5. #5
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    Thumbs up

    Thanks a lot - it worked! You might wonder why we do this? It's becuase we want to reuse a ref cursor that has exised in a proc.
    Here was my simplified experiment:
    ______________________________________________________________


    create or replace package frank_getrid1
    AS
    type cust_cursor is ref cursor;
    type r is record
    (
    customer_nbr customer_information.customer_nbr%type,
    customer_name customer_information.customer_name%type,
    warehouse_nbr customer_profile.warehouse_nbr%type
    );

    procedure get_records ( p_cust_ref OUT cust_CURSOR );
    procedure get_it;
    END;


    create or replace package body frank_getrid1
    AS

    procedure get_records (p_cust_ref OUT cust_cursor )
    IS
    BEGIN
    OPEN p_cust_ref FOR
    select a.customer_nbr, a.customer_name, b.warehouse_nbr
    from customer_information a, customer_profile b
    where a.customer_nbr = b.customer_nbr
    and a.customer_nbr < 200;
    END get_records;


    procedure get_it IS
    r1 frank_getrid1.r;
    cust_cursor1 cust_cursor;

    BEGIN
    get_records(cust_cursor1);
    LOOP
    fetch cust_cursor1 into r1;
    exit when cust_cursor1%notfound;
    dbms_output.put_line(r1.customer_nbr || ' '|| r1.customer_name ||
    ' ' || r1.warehouse_nbr);
    END LOOP;

    END get_it;
    END frank_getrid1;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width