Iterate through a ref cursor??????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Iterate through a ref cursor??????

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question Iterate through a ref cursor??????

    I have a package that returns a ref cursor. For example:

    create or replace package test as

    type gencursor is ref cursor;

    procedure mytest(
    x out gencursor);

    end test;
    /

    create or replace package body test as

    procedure mytest(
    x out gencursor) as

    rc gencursor;

    begin

    open rc for
    select a,b,c,d from test_tables;

    return;
    end mytest;

    end test;
    /

    Then in SQLPlus I have

    declare
    x test.gencursor;
    begin
    test.mytest(x);
    for v1 in x loop
    dbms_output.put_line(x.a||' '||x.b||' '||x.c||' '||x.d);
    end loop;
    end;
    /

    for v1 in x loop
    *
    ERROR at line 5:
    ORA-06550: line 5, column 11:
    PLS-00221: 'X' is not a procedure or is undefined
    ORA-06550: line 5, column 1:
    PL/SQL: Statement ignored


    How do I iterate through the cursor that is being passed back from my procedure???


    Thanks for your help.

  2. #2
    Join Date
    Jan 2001
    Posts
    515

    correction

    That should say

    open x for
    select a,b,c,d from test_tables;

    Thanks again.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    example

    Code:
    DECLARE
      type ref_cursor is ref cursor;
      c1             ref_cursor;
      cursor ch_cursor is select co_id, ch_seqno, ch_status, userlastmod, entdate 
                            from contract_history ch1 
                           where rownum = 1;
      r_contract_histroy ch_cursor%rowtype;
      fr_entdate         CONSTANT DATE := trunc(sysdate - 7);
      to_entdate         CONSTANT DATE := trunc(sysdate - 1);
    BEGIN
      OPEN c1 for 'select co_id, ch_seqno, ch_status, userlastmod, entdate
                     from (select co_id, ch_seqno, ch_status, userlastmod, entdate,
                            dense_rank() over (partition by co_id order by ch_seqno desc) rango
                             from contract_history
                            where co_id < 11
                              and trunc(entdate) <= :to_entdate) iv_ch
                    where rango = 1
                       or (ch_seqno = 1 and trunc(entdate) <= :to_entdate)
                    order by co_id, ch_seqno' using to_entdate, to_entdate;
      LOOP
        FETCH c1 into r_contract_histroy;
        EXIT when c1%notfound;
        dbms_output.put_line(r_contract_histroy.co_id || r_contract_histroy.ch_seqno ||
                             r_contract_histroy.ch_status || r_contract_histroy.userlastmod ||
                             r_contract_histroy.entdate);
      END LOOP;
      CLOSE c1;
    END;
    /

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