Re: Creating and accessing a table from within a procedure

A copy of this was sent to vmisetich_at_my-deja.com (if that email address didn't require changing) On Fri, 03 Dec 1999 17:42:24 GMT, you wrote:

>Hello!
>
>Can someone tell me if it's possible to both create and insert into a
>temporary table from within one stored procedure? I've already tried
>the following, but it doesn't seem to work:
>
>create or replace procedure test_temp_table
>as
>
>begin
> execute immediate 'create global temporary table ttt(testfield
>number(10))';
> insert into ttt(testfield) values(1);
>
>end;
>/
>
>When I try to compile the proc, I get:
>PL/SQL: SQL Statement ignored
>PLS-00201: identifier 'TTT' must be declared
>
>Can someone show me the correct way (if there is one) to do this?
>
>Thanks in advance,
>Vince
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

the global temporary table command creates a PERMANENT schema object (temporary tables are created once per database -- not in each procedure that needs them). The data is temporary -- the table is permanent.

If you coded your procedure to dynamically insert into TTT (eg: execute immediate 'insert into ttt(testfield) values(1)';) it would compile and run -- exactly ONCE. Then, it would start crashing with:

tkyte_at_ORA8IDEV.WORLD> create or replace procedure test_temp_table   2 as
  3
  4 begin
  5 execute immediate 'create global temporary table ttt(testfield number(10))';
  6 execute immediate 'insert into ttt(testfield) values(1)';   7 end;
  8 /

Procedure created.

tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> exec test_temp_table

PL/SQL procedure successfully completed.

tkyte_at_ORA8IDEV.WORLD> exec test_temp_table BEGIN test_temp_table; END;

*
ERROR at line 1:

ORA-00955: name is already used by an existing object
ORA-06512: at "TKYTE.TEST_TEMP_TABLE", line 5
ORA-06512: at line 1

The table should be created once And then the procedure should be created. It should be:

tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> create or replace procedure test_temp_table   2 as
  3
  4 begin
  5 insert into ttt(testfield) values(1);   6 end;
  7 /

Procedure created.

tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> exec test_temp_table

PL/SQL procedure successfully completed.

tkyte_at_ORA8IDEV.WORLD> exec test_temp_table

PL/SQL procedure successfully completed.

Everyone will use the same temporary table -- they will see their own data only, they will have no concurrency issues.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 03 1999 - 12:30:35 CST