Temp table in Procedure

From: Debu Das <debu_at_rheal.com>
Date: 26 Mar 2004 03:26:54 -0800
Message-ID: <298f0e41.0403260326.509f37a4@posting.google.com>

Hi Friends,

In my stored procedure i am trying to create a temp table, populate some data in it and then want to use it in the query.

This is how i am trying to do

EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38),Name VarChar2(50)) ON COMMIT PRESERVE';

  INSERT INTO tt_Local
  SELECT
    ID,
    NAME
  FROM
      SCHEMATABLE
  WHERE
       ID = SuperclassID;

After this i want this tt_Local table to be used in the query which i will open it in a ref_cursor and send as a output paramaeter of the stored procedure.

I am getting this Compilation errors

Error: PL/SQL: ORA-00942: table or view does not exist Error: PL/SQL: SQL Statement ignored

####################################################################

I just tried to create the temporary table in the procedure with the EXECUTE IMMEDIATE it got complied after that i tried to run the procedure then i got this error
ORA-01031: insufficient privileges

Any information provided will be greatly appreciated.

Thanks in advance,

Debu Received on Fri Mar 26 2004 - 05:26:54 CST