Create Temproary table inside the procedure

[meenu0903]

How can i create Temproary table inside the procedure in Oracle?

[davey23uk]

why you want to it is a better question

[PAVB]

How can i create Temproary table inside the procedure in Oracle?

If you are asking about a proper temporary table as defined by Oracle I'm in full agreement with Dave; you don't want to to that. DBA creates temporary table and when a piece of code references the temporary table, an incarnation of the table is made available.

If you are asking about how to create a table you later want to drop - even if this is not best practice - you can always execute "create table" statement as "execute immediate".

[meenu0903]

I can create the temporary table using this script in SQL+.
CREATE TABLE TEMP_SURGN AS SELECT * FROM FSURGN WHERE SU_CA_SEQU =2;

But if i use this script inside the procedure, its not working. So, i have modified the script as

TEMP_TABLE_NAME VARCHAR2(50) := 'TEMP_SURGN';
TABLE_NAME VARCHAR2(50) := 'FSURGN';

EXECUTE IMMEDIATE 'CREATE TABLE' || TEMP_TABLE_NAME || ' AS SELECT * FROM FSURGN WHERE SU_CA_SEQU =2';

Still its not working. Im in need to duplicate particular records and update some values in those records. Finally i need to insert the updated records into original table. Thts the case i plan to use Temporary table in SP.

Is there any other way?

[ales]

You're trying to create the permanent table, not the temporary.

1. Create the true temporary table in your schema using SQL*Plus:
CREATE global temporary TABLE TEMP_SURGN on commit delete rows AS SELECT * FROM FSURGN WHERE 1=0;

Now you have the table ready to hold your private data that other sessions cannot see and that you cannot see after commit. The rows are temporary but the definition of the table resides in the dictionary. The compiler must see it if you want to compile the procedure.

Inside your procedure:

2. insert the data from the original table:
insert into TEMP_SURGN select * from FSURGN where SU_CA_SEQU =2;

3. perform the desired data manipulation on the TEMP_SURGN
4. insert the rows into original table
5. commit - the data in the TEMP_SURGN go away

6. Do NOT drop the temporary table as the procedure would get invalid and unusable

7. read something about global temporary tables concept

[PAVB]

EXECUTE IMMEDIATE 'CREATE TABLE' || TEMP_TABLE_NAME || ' AS SELECT * FROM FSURGN WHERE SU_CA_SEQU =2';

Still its not working.

It doesn't work because you missed a blank space after 'CREATE TABLE'...

Let me show you how to fish... when you are building dynamic sql you can test it by selecting from dual, like:

select  'CREATE TABLE' || 'TEMP_SURGN' || ' AS SELECT * FROM FSURGN WHERE SU_CA_SEQU =2'
from    dual;

[ales]

It doesn't work because you missed a blank space after 'CREATE TABLE'...

Let me show you how to fish...

Yes, the right way ... to the wrong place.

[PAVB]

Yes, the right way ... to the wrong place.

huh?

[meenu0903]

I accepted your suggestions Ales.. But i need to use the same procedure for more than 75 Lookups (FSURGN is one of the lookup). As you said, Its very tedious to create Temproary table for all tables knw. If i use dynamic sql in SP, i can easily pass the lookup name as parameter rt?

Thanx Ales.


Yes PAVB.. You are correct i missed the space and its working fine now..

Thanks a lot..

[meenu0903]

I accepted your suggestions Ales.. But i need to use the same procedure for more than 75 Lookups (FSURGN is one of the lookup). As you said, Its very tedious to create Temproary table for all tables knw. If i use dynamic sql in SP, i can easily pass the lookup name as parameter rt?

Thanx Ales.


Yes PAVB.. You are correct i missed the space and its working fine now..

Thanks a lot..