Re: stored procedure and table creation

Two useful responses so far were offered

(Tom Zamani) Use regular tables and truncate them when finished.

(Onno) Use PLSQL tables.

There is also the possibility of using temporary tables with Oracle 8i. These tables are created in advance and all users may store data in them, but users see only their data and when their session ends the data is no longer visible to them when they reconnect. On place to look for these is the SQL Reference Manual under the create table command.

As an example, the following illustrates the use (Linux, Oracle 8.1.5):

SQL> create global temporary table abc (a number, b number);

Table created.

SQL> create or replace procedure testtemp is   2 begin
  3 insert into abc values (1,2);
  4 end;
  5 /

Procedure created.

SQL> exec testtemp;

PL/SQL procedure successfully completed.

SQL> select * from abc;

         A B
---------- ----------

         1 2

SQL> connect sameuser/password_at_tnsname
Connected.
SQL> select * from abc;

no rows selected

Frank Hubeny

orauser_at_my-deja.com wrote:

> How do I code a stored procedure which needs to create tables
> in it (temporary tables required for processing).
>
> If I code the create table within an 'execute immediate', the
> tables do not exist at compile time, and the procedure does not
> compile.
>
> If I create the tables, compile the procedure, and then drop the
> tables so they can get recreated at runtime, the procedure is
> invalidated.
>
> What is the right way to do this? Also, do I have to use
> execute immediate for all sql execution? Or can I do selects,
> inserts and updates directly without the 'execute immediate'?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 15 2000 - 00:00:00 CDT