• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

Stored Procedure does not know about temp table

In my stored procedure i am creating a temp table
EXECUTE IMMEDIATE ('CREATE GLOBAL TEMPORARY TABLE my_temp_table  ON COMMIT PRESERVE ROWS
AS SELECT * FROM TG_RPT_USAGE_DTL_VW');

This works out side the SP but when placed in the SP I get the error further down of
Error(87,13): PL/SQL: ORA-00942: table or view does not exist

From what I can see when I try to compile the SP the temp table does not exist so rightly I get the error stating such.  The reason I am doing this is because the procedure was hammering a view and causing performance issues because the view joins about 10 tables, not to mention the instability of data changing out from under me.  My goal is to capture a snapshot of the data at the start of the procedure and set it aside so that the procedure can do it's job without affecting the rest of the DB.

Thank you
0
ckaley
Asked:
ckaley
  • 3
1 Solution
 
mrjoltcolaCommented:
So don't create your global temp table each time in a procedure. They are meant to be created once, and used repeatedly.

For each session you get a new, empty table to work with. No need to use EXEC IMMEDIATE above.
0
 
ckaleyAuthor Commented:
So then I would still have to create the table at the start of each session, which means my customer would have to remember to create it.  Perhaps I am missing something here.

I tried doing this in the SP
CREATE GLOBAL TEMPORARY TABLE my_temp_table  ON COMMIT PRESERVE ROWS
AS SELECT * FROM TG_RPT_USAGE_DTL_VW

but got the following
Error(63,1): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:     ( begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    continue close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge pipe purge
0
 
mrjoltcolaCommented:
>>So then I would still have to create the table at the start of each session, which means my customer would have to remember to create it.  Perhaps I am missing something here.


Yes, you are missing something, but no problem. Just read up on how global temp tables (GTTs) work. You define them once. But each session, you have your "own copy". Read up a bit here: http://www.orafaq.com/wiki/Temporary_table

The whole point of a GTT is not to have to create a table every session. Each user can see it, but with their own working copy. And it is also held in a temp segment, and more efficient for scratch data.
0
 
mrjoltcolaCommented:
Now, if you want to populate the GTT with data each time, you could do this at the start of the procedure...

INSERT INTO <my GTT> SELECT * FROM ...


Then you can work with that data. The point is, with GTTs the definition is persistent, but the data is not.

The advantage of GTTs is lack of redo / rollback generated, so it can drastically improve the performance of an algorithm that needs to work with temporary data.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now