Temp table in Procedure

[Debu Das]

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

[Guest]

debu@rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4@posting.google.com>... 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

search for ora-1031 in the Google archives. Please always do so before
posting questions again for which you can assume it is a FAQ.

Sybrand Bakker
Senior Oracle DBA

[Mark D Powell]

debu@rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4@posting.google.com>... 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

FYI to readers: This was answered on the .server board with several
responses. -- Mark --

[Dave]

debu@rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4@posting.google.com>...


Something is wrong with what you are trying to do. The first time you
create this procedure, the tt_Local table does not exist, therefore
your code will not compile since Oracle will attempt to validate you
SQL statement which references a non-existing table tt_Local. If you
manage to get your table created (manually I guess), then your
procedure will fail at run-time each time you run it because you will
attempt to re-create the tt_Local table which already exists.

Dave

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

[Daniel Morgan]

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

Please don't post to every usenet group whose name contains Oracle.
Read the charters and post to the one, and only one, group that best
represents the topic of your question. And now to your question:

My guess is that you have come to Oracle from SQL Server or a similar
environment has you absolutely should not, make that NEVER, create a
temporary table in a stored procedure in Oracle. First because there
is no need and second because it demonstrates a complete lack of
understanding or disregard for architecture and concept documents.

Rather than encouraging you to do something you shouldn't do by telling
you how to do it ... tell us the problem you are trying to solve, in a
single usenet group please, and we will tell you how to address the
business problem.

Then get yourself a copy of Tom Kyte's book Expert one-on-one Oracle
and read the first three chapters. Especially the part about temp
tables.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[amit]

The Table tt_local will be created when you execute the procedure.
The table does not exist when you compile it.

There are two options

1. Create the global temporary table manually
Remove the creation of the table from the proc and then compile it.
2. Run the insert also as dynamic sql.
Remember dynamic sqls don't go through semantic check at compile
time.
(that happens during run time)

I myself prefer the first solution, Since condition like yours were
one of the reasons why conecept of global temporary table was created.

SQL> create or replace procedure test
2 as
3 begin
4 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID
VarChar2(38)) ON COMMIT PRESERVE';
5
6 INSERT INTO tt_Local
7 SELECT
8 empno
9 FROM
10 emp;
11 end;
12 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4 PL/SQL: SQL Statement ignored
6/16 PLS-00201: identifier 'TT_LOCAL' must be declared
SQL> @b.sql
SQL> create or replace procedure test
2 as
3 stmt varchar2(2000);
4 begin
5 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID
VarChar2(38)) ON COMMIT PRESERVE';
6
7 stmt := 'INSERT INTO tt_Local SELECT empno FROM emp';
8 execute immediate stmt;
9 end;
10 /

Procedure created.

SQL>

The example below has wrong syntax for create global temporary table
but still compiles since the semantic check for dynamic sqls are done
during run time not compile time

SQL> @b.sql
SQL> create or replace procedure test
2 as
3 stmt varchar2(2000);
4 begin
5 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID
VarChar2(38)) COMMIT PRESERVE';
6
7 stmt := 'INSERT INTO tt_Local SELECT empno FROM emp';
8 execute immediate stmt;
9 end;
10 /

Procedure created.

SQL>

Hope this helps

amit



debu@rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4@posting.google.com>... 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