Stored Procedure does not know about temp table
Posted on 2011-04-28
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.