Re: Temp table in Procedure

Mark C. Stock wrote:

> "Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message
> news:c414fe$7o3$1_at_news4.tilbu1.nb.home.nl...
> | 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
> |
> | Don't cross post.
> | Create the gtt beforehand - why would you create a temporary table on
> | the fly?
> | --
> |
> | Regards,
> | Frank van Bortel
> |
>
>
> global temporary tables aren't really temporary -- the data values the hold
> are
>
> the compilation error is because you referenced an object in your code that
> did not exist yet
>
> the privilege error is likely because you don't have privilege to create the
> temporary table -- try select * from session_privs to verify
>
> the correct usage of temporary tables is to create the table as a permanent
> object, with the appropriate ON COMMIT...ROWS setting, then manage and use
> it pretty much just like any other table -- except that the data go away at
> the end of a session or a transaction
>
> ;-{ mcs
>
>

Well - that's about a temporary as you want it. The DDL is expensive, performance wise, so I would be against it - besides, it sounds like SQL Server...
On the other hand - bugs in 9i make GTTs slower than their permanent counterparts, so it's to the OP to decide - the greater context is not known.

-- 

Regards,
Frank van Bortel