Thanks, but i need to select not all columns from table in cursor.
So, cursor type will be different from table%rowtype...
Rob Calfee <trace_at_primenet.com> wrote in message
news:37e8f172.6160892_at_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 Thu Sep 23 1999 - 00:47:25 CDT
