Re: Temp table in Procedure

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

FYI to readers: This was answered on the .server board with several responses. -- Mark -- Received on Fri Mar 26 2004 - 15:24:16 CET