Re: Temp table in Procedure

[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.

SQLcreate 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.

SQLshow 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
SQ*@b.sql
SQLcreate 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

SQ*@b.sql
SQLcreate 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

de**@rheal.com (Debu Das) wrote in message news:<29**************************@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