Re: Temp table in Procedure

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 26 Mar 2004 06:39:48 -0500
Message-ID: <h4GdnQkUBpw7i_ndRVn-tw@comcast.com>

"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 Received on Fri Mar 26 2004 - 05:39:48 CST