Re: Temp table in Procedure

From: amit <amit.poddar_at_yale.edu>
Date: 5 Apr 2004 14:34:45 -0700
Message-ID: <9db1b842.0404051334.79058a1c@posting.google.com>

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_at_rheal.com (Debu Das) wrote in message news:<298f0e41.0403260326.509f37a4_at_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
Received on Mon Apr 05 2004 - 16:34:45 CDT