Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Temp table in Procedure

Re: Temp table in Procedure

From: <sybrandb_at_yahoo.com>
Date: 26 Mar 2004 06:01:39 -0800
Message-ID: <a1d154f4.0403260601.7a45f5fa@posting.google.com>


debu_at_rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4_at_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

search for ora-1031 in the Google archives. Please always do so before posting questions again for which you can assume it is a FAQ.

Sybrand Bakker
Senior Oracle DBA Received on Fri Mar 26 2004 - 08:01:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US