How to create a temp table within Stored Proc and then return it to the outside?

Dear gurus,

I want to do something like this in a stored Procedure:

1. Create a temp nested table
2. Stuff the nested table with values
3. Return the table through cursor so that Crystal Report and pick the data up and generate a report.

I wrote the following code for sake of proof-of-concept:

• (Start Code)

  ---

  CREATE OR REPLACE PACKAGE DeeCay_Test_Package AS

        TYPE ResultRec IS RECORD 


        ( 

                sec_num         VARCHAR2(20), 
                net_num         VARCHAR2(20), 
                A_ID            VARCHAR2(20), 
                A_NAME          VARCHAR2(20), 
                B_ID            VARCHAR2(20), 
                B_REMARK        VARCHAR2(20) 
        ); 
        TYPE refcur IS REF CURSOR RETURN ResultRec; 
        TYPE ResultRecTabTyp IS TABLE OF ResultRec; 

        PROCEDURE DeeCay_Test 


        (  

                P_cursor OUT    refcur 
        ); 

END DeeCay_Test_Package;
/

CREATE OR REPLACE PACKAGE BODY DeeCay_Test_Package AS

        PROCEDURE DeeCay_Test 


        ( 

                P_cursor OUT    refcur 
        ) 
        
        IS 
                ResultRecTable ResultRecTabTyp; 
                TempRec ResultRec; 
        BEGIN 
                TempRec.sec_num         := 'a'; 
                TempRec.net_num         := 'b'; 
                TempRec.A_ID            := 'c'; 
                TempRec.A_NAME          := 'd'; 
                TempRec.B_ID            := 'e'; 
                TempRec.B_REMARK        := 'f';         
                 ResultRecTable := ResultRecTabTyp(TempRec); 
                OPEN p_cursor FOR 
                        SELECT * FROM TABLE(ResultRecTable); 
        END DeeCay_Test; 
        

END DeeCay_Test_Package ;

/-------------------------------- (End Code)
--------------------------------

The code compiled OK. But when I tried to run it:

variable aCursor REFCURSOR
exec DeeCay_Test_Package.DeeCay_Test(:aCursor)

I got the following error:

BEGIN DeeCay_Test_Package.DeeCay_Test(:aCursor); END; *
ERROR at line 1:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [] ORA-06544: PL/SQL: internal error, arguments: [pfrrun.c:pfrbnd1()], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [0]

So .... What going on? And is there any way to accomplish what I want to do?

Deecay One Received on Thu Aug 22 2002 - 07:26:45 CDT