Re: Temp table in Procedure

debu_at_rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4_at_posting.google.com>...

Something is wrong with what you are trying to do. The first time you create this procedure, the tt_Local table does not exist, therefore your code will not compile since Oracle will attempt to validate you SQL statement which references a non-existing table tt_Local. If you manage to get your table created (manually I guess), then your procedure will fail at run-time each time you run it because you will attempt to re-create the tt_Local table which already exists.

Dave

> 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 - 09:44:30 CST