Re: Temp table in Procedure

"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message news:c416rp$rj8$1_at_news2.tilbu1.nb.home.nl...
| 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
|

interesting -- can you provide a brief synopsis of the bugs? version? version fixed? work-arounds? how much slower?

;-{ mcs Received on Fri Mar 26 2004 - 08:16:23 CST