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

You can't pass a cursor into a procedure. You can use the persistence feature inherent with packages to declare and manipulate a globally defined, persistent cursor (by persistent I mean its state is maintained for the duration of the session). Then have one procedure open it, fetch from it and close it, according to the present state.

Here is an example, trimmed down, from one of my chapters I wrote in OCP Training Guide: Oracle DBA, a new release from New Riders publishing (a MacMillan imprint). I hope they don't mind my putting my code here -- I gave them a plug for the new book!

CREATE OR REPLACE PACKAGE dept pkg AS
/*
  Module: dept pkg.sqh (Dept Table Operations Specification)   Author: Daniel J. Clamage
  Description:
  This package defines all the table operations desired for   table SCOTT.DEPT.
  History:
  10-JUN-1998 - djc - Original draft.
*/

• global public variables dept rec dept%ROWTYPE; -- handy global record variable
• global public subroutines
• return every row one at a time
• state is for test purposes to prove correct operation
• on an error state shows what operation was being attempted PROCEDURE get dept(Prec OUT dept%ROWTYPE, Pstate OUT NUMBER, Pstatus IN OUT NUMBER);

END dept pkg;
/

CREATE OR REPLACE PACKAGE BODY dept pkg AS /*
  Module: dept pkg.sql (Dept Table Operations)   Author: Daniel J. Clamage
  Description:
  This package defines all the table operations desired for   table SCOTT.DEPT.
  History:
  10-JUN-1998 - djc - Original draft.
*/

• global private declarations
• used by get dept (all rows) CURSOR dept all IS SELECT * FROM dept ORDER BY deptno;
• global public subroutines
• return every row one at a time
• state is for test purposes to prove correct operation
• on an error state shows what operation was being attempted PROCEDURE get dept(Prec OUT dept%ROWTYPE, Pstate OUT NUMBER, Pstatus IN OUT NUMBER) IS BEGIN Pstatus := 0; IF (NOT dept all%ISOPEN) THEN -- State 1: CLOSED Pstate := 1; OPEN dept all; END IF; Pstate := 2; FETCH dept all INTO Prec; -- State 2: FETCHING IF (dept all%NOTFOUND) THEN -- State 3: RESTART Pstate := 3; CLOSE dept all; OPEN dept all; -- reopen FETCH dept all INTO Prec; IF (dept all%NOTFOUND) THEN -- State 4: EMPTY TABLE Pstate := 4; RAISE NO DATA FOUND; END IF; END IF; EXCEPTION WHEN OTHERS THEN Pstatus := SQLCODE; END get dept;

END dept pkg;
/

DECLARE status NUMBER;

state NUMBER; -- subprogram state

BEGIN

• TEST get dept (all rows)

FOR rec IN 1..10 LOOP

dept pkg.get dept(dept pkg.dept rec, state, status);

DBMS OUTPUT.put line('DEPT=' ||

dept pkg.dept rec.deptno ||

', STATE=' || TO CHAR(state));

END LOOP; END; /

The server responds with

DEPT=10, STATE=2 DEPT=20, STATE=2 DEPT=30, STATE=2 DEPT=40, STATE=2 DEPT=10, STATE=3 DEPT=20, STATE=2 DEPT=30, STATE=2 DEPT=40, STATE=2 DEPT=10, STATE=3 DEPT=20, STATE=2 PL/SQL procedure successfully completed.

This should give you the general idea.

-- 
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
    Marshall Hughes wrote in message 
<366D935E.19AC4A8E_at_bellsouth.net>...
    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. 


--