Re: Stored procedure results via cursor...HOW!?!

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: 1998/12/09
Message-ID: <ADK5RRsK42@protasov.kiev.ua>#1/1

Hi,

I do not understand why ref cursor will not work in your situation:

SVRMGR> create table test_cur
     2> (id integer
     3> );

Statement processed.
SVRMGR> insert into test_cur(id) values(1); 1 row processed.
SVRMGR> insert into test_cur(id) values(2); 1 row processed.
SVRMGR> insert into test_cur(id) values(3); 1 row processed.
SVRMGR> insert into test_cur(id) values(4); 1 row processed.
SVRMGR> insert into test_cur(id) values(5); 1 row processed.
SVRMGR> insert into test_cur(id) values(6); 1 row processed.
SVRMGR> commit;
Statement processed.

SVRMGR> create or replace package p is
     2>  type TCursor is ref cursor return test_cur%rowtype;
     3>  procedure prepare(x in out TCursor);
     4>  procedure browse;
     5> end;
     6> /

Statement processed.

SVRMGR> create or replace package body p as
     2>  procedure prepare(x in out TCursor) is
     3>  begin
     4>   open x for select * from test_cur;
     5>  end;
     6>  procedure browse is
     7>   c TCursor;
     8>   r c%rowtype;
     9>  begin
    10>   prepare(c);
    11>   loop
    12>    fetch c into r;
    13>    exit when c%notfound;
    14>    dbms_output.put_line('ID='||r.id);
    15>   end loop;
    16>   close c;
    17>  end;
    18> end;
    19> /

Statement processed.
SVRMGR> execute p.browse;
Statement processed.

ID=1
ID=2
ID=3
ID=4
ID=5
ID=6

Andrew Protasov

> Ok folks I am at my wits end. I am convinced that what I want to do can
> be done. At this juncture Oracle 7.3.x has me convinced otherwise. So,
> before I tell my boss that I cannot do what I've promised and loose my
> job, I'll try the newsgroups. All I want to do is call a stored
> procedure that will fetch results from another stored*procedure with a
> cursor, insert those results into a table I have setup for reporting.
> The stored*procedure has an in/out cursor defined. A sample of my
> current attempt is as follows:
>
> CREATE OR REPLACE PROCEDURE ReportPrep( szDate IN VarChar2 )
> AS
> BEGIN
> DECLARE
> -- Predefined cursor the matches theProcedure
> theCursor Package.CursorDef;
> -- Define the row structure (array)
> theRecord theCursor%ROWTYPE;
> BEGIN
> -- The procedure that returns the results I'm looking for
> theProcedure( szDate, theCursor );
> IF theCursor%ISOPEN THEN
> -- Fetch the row into the row structure (array)
> FETCH theCursor INTO theRecord;
> WHILE theCursor%FOUND
> LOOP
> -- Insert the results into the reporting table
> INSERT INTO
> theTable ( Column1, Column2, Column3 )
> VALUES ( theRecord.Col1,theRecord.Col2,theRecord.Col3
> );
> -- Fetch the next row
> FETCH theCursor INTO theRecord;
> END LOOP;
> END IF;
> END;
> END RepPrep;
>
> I don't see why this will not work. My local DBA is telling me to look
> in the documentation. I've done that and have gotten no where.
> Therefore, I am asking the community for help.
>
> Thank-you in advance,
> Marshall.
>
Received on Wed Dec 09 1998 - 00:00:00 CST