Re: How to return cursor ?

From: Rob Calfee <trace_at_primenet.com>
Date: Wed, 22 Sep 1999 15:12:08 GMT
Message-ID: <37e8f172.6160892@news.primenet.com>

Tolik,

Here are examples of how to use a cursor variable. There is a package header, procedure, a test script, and the ddl for the table. Hope this helps.

Rob Calfee

• Start of DDL script for OPEN_QUERY
• Generated 22-Sep-99 8:04:02 am
• from dsst-SYSMAN:3
• Procedure OPEN_QUERY
• Opens a cursor variable and populates it with the contents of table junk
• and then sends the cursor variable back to the test script (below)

CREATE OR REPLACE
procedure open_query(cur_junk out jk.junk_curtype) is junk_cur jk.junk_curtype;

        begin
        open junk_cur for select * from junk;
        cur_junk := junk_cur;

    end;
/

• End of DDL script for OPEN_QUERY
• Start of DDL script for JK
• Generated 22-Sep-99 8:04:23 am
• from dsst-SYSMAN:3
• Package JK
• DECLARES cursor variable of table junk rowtype CREATE OR REPLACE package jk is type junk_curtype is ref cursor return junk%rowtype; end ; /
• End of DDL script for JK

--TEST SCRIPT

-- calls open_query to populate the cursor variable and send it back

• then displays the contents of the cursor variable declare

junk_rec junk%rowtype;
curjunk jk.junk_curtype;

begin
open_query(curjunk);
loop

    fetch curjunk into junk_rec;

     exit when curjunk%notfound;
     dbms_output.put_line(junk_rec.num1 || ' ... ' || junk_rec.num2);

end loop;
close curjunk;     

end;
.
/

• Start of DDL script for JUNK
• Generated 22-Sep-99 8:07:58 am
• from dsst-SYSMAN:3
• Table JUNK

CREATE TABLE junk
 (

  num1                       NUMBER(2),
  num2                       NUMBER(2)

 )
 PCTFREE 10
 PCTUSED 40
 INITRANS 1
 MAXTRANS 255
 TABLESPACE usr
 STORAGE (

      INITIAL     102400
      NEXT        151552
      PCTINCREASE 1
      MINEXTENTS  1
      MAXEXTENTS  249

   )
/

• End of DDL script for JUNK

On Wed, 22 Sep 1999 17:33:23 +0300, "Tolik K." <tol2000_at_geocities.com> wrote:

>How can i return cursor from PL/SQL function in the package ?
>PLEASE TELL ME HOW TO CORRECT THIS CODE TO RETURN CURSOR
>
>CREATE OR REPLACE PACKAGE BODY reports IS
>
>  FUNCTION rep_book_sell_days(date_begin IN DATE, date_end IN DATE)
>  RETURN ????? IS
>    CURSOR rep_book_sell_days_cursor(date_beg DATE, date_e DATE) IS
>      SELECT ......................
>  BEGIN
>    RETURN rep_book_sell_days_cursor(date_begin, date_end);  ??????
>  END rep_book_sell_days;
>
>END reports;
>
>--
>________________________
>Tolik.
>e-mail: tol2000_at_geocities.com
>
>

Rob Calfee
DBA
rcalfee_at_incsystem.com Received on Wed Sep 22 1999 - 10:12:08 CDT