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

From: David Lane <dlane_at_cix.compulink.co.uk>
Date: Thu, 10 Dec 1998 10:48:51 GMT
Message-ID: <F3qwpF.93o@cix.compulink.co.uk>

In article <366D935E.19AC4A8E_at_bellsouth.net>, codenome_at_bellsouth.net (Marshall Hughes) wrote:

>
> --------------5F04803F0A2BD34954297FCE
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> 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.
>
> --------------5F04803F0A2BD34954297FCE
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <HTML>
> 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
>           &nbsp
> ;
> -- Insert the results into the reporting table
>           &nbsp
> ;
> INSERT INTO
>           &nbsp
> ;   
> theTable ( Column1, Column2, Column3 )
>           &nbsp
> ;     
> VALUES ( theRecord.Col1,theRecord.Col2,theRecord.Col3 );
>           &nbsp
> ;
> -- Fetch the next row
>           &nbsp
> ;
> 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.</HTML>
>
> --------------5F04803F0A2BD34954297FCE--
>

It will work if you use a package instead of a procedure .... You need to declare your cursor (and any other variables you need to keep constant between calls) in the package body OUTSIDE of all procedure declarations so that it remains untouched between calls.

Alternatively, you can declare a package header with your cursor declaration defined (you don't need a package body in this case) and refer to package.cursor in your procedure.

The procedure approach by itself wouldn't work as (as far as I'm aware) the procedure variables get cleaned up when you exit the procedure .... at least, the effect is the same!

Dave Lane (dlane_at_pt.lu) Received on Thu Dec 10 1998 - 04:48:51 CST