We help IT Professionals succeed at work.

Stored Procedure does not know about temp table

ckaley
ckaley asked
on
Medium Priority
720 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

Top Expert 2009

Commented:
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.

Author

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
Top Expert 2009

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.


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.
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.