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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 22 Aug 2002 06:53:41 -0700
Message-ID: <ak2qd5017l6@drn.newsguy.com>

In article <f724a6c4.0208220426.400e3667_at_posting.google.com>, c266366430_at_hotmail.com says...
>
>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

that doesn't in fact compile (or it shouldn't, if it does -- the bug is that it compiled)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show errors Errors for PACKAGE BODY DEECAY_TEST_PACKAGE:

LINE/COL ERROR

-------- -----------------------------------------------------------------
19/17    PL/SQL: SQL Statement ignored

20/25 PL/SQL: SQL Statement ignored
20/39 PLS-00320: the declaration of the type of this expression is

         incomplete or malformed

20/46 PLS-00642: local collection types not allowed in SQL statements

You must use SQL types -- not plsql types -- in order to do this in sql.

Here is your example:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type resultRec as object   2 (

  3                  sec_num         VARCHAR2(20),
  4                  net_num         VARCHAR2(20),
  5                  A_ID            VARCHAR2(20),
  6                  A_NAME          VARCHAR2(20),
  7                  B_ID            VARCHAR2(20),
  8                  B_REMARK        VARCHAR2(20)

  9 );
 10 /

Type created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type resultRecTabTyp as table of resultRec
  2 /

Type created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE PACKAGE DeeCay_Test_Package   2 AS
  3
  4 TYPE refcur IS REF CURSOR;
  5

  6          PROCEDURE DeeCay_Test
  7          (
  8                  P_cursor OUT    refcur
  9          );

 10
 11 END DeeCay_Test_Package;
 12 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE PACKAGE BODY DeeCay_Test_Package AS
  2

  3          PROCEDURE DeeCay_Test
  4          (
  5                  P_cursor OUT    refcur
  6          )
  7  
  8          IS
  9                  ResultRecTable ResultRecTabTyp := resultRecTabTyp();
 10                  TempRec ResultRec;
 11          BEGIN
 12              TempRec := resultRec( 'a', 'b', 'c', 'd', 'e', 'f' );
 13              ResultRecTable := ResultRecTabTyp(TempRec);
 14              OPEN p_cursor FOR
15                          SELECT * FROM TABLE( cast( ResultRecTable as
resultrecTabTyp) );
 16          END DeeCay_Test;

 17
 18 END DeeCay_Test_Package ;
 19 /

Package body created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show errors No errors.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x refcursor
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec deecay_test_package.deecay_test( :x )

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> print x

SEC_NUM              NET_NUM              A_ID                 A_NAME           
B_ID                 B_REMARK
-------------------- -------------------- --------------------
-------------------- -------------------- --------------------
a                    b                    c                    d                
e                    f

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 

Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Thu Aug 22 2002 - 08:53:41 CDT