Re: Temp table in Procedure

Debu Das wrote:

> 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

Please don't post to every usenet group whose name contains Oracle. Read the charters and post to the one, and only one, group that best represents the topic of your question. And now to your question:

My guess is that you have come to Oracle from SQL Server or a similar environment has you absolutely should not, make that NEVER, create a temporary table in a stored procedure in Oracle. First because there is no need and second because it demonstrates a complete lack of understanding or disregard for architecture and concept documents.

Rather than encouraging you to do something you shouldn't do by telling you how to do it ... tell us the problem you are trying to solve, in a single usenet group please, and we will tell you how to address the business problem.

Then get yourself a copy of Tom Kyte's book Expert one-on-one Oracle and read the first three chapters. Especially the part about temp tables.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)