How to Create a Temporary Table via a Stored Procedure

[ihda]

I'm new to the use of temporary tables. I'm trying to create a temporary table using the following procedure:


create or replace procedure P_X1
execute immediate "CREATE GLOBAL TEMPORARY TABLE X_TABLE (
x_year NUMBER(4),
x_MONTH VARCHAR2(9))
ON COMMIT PERSERVE ROWS";
END P_X1;

I'm receiving errors on the execute immediate...when that's removed on the create statement. The removal of the quotes does not change the error.

Thank you.

 

[Dagon]

Have you actually got double quotes there ? If so, it should be single quotes.

 

[SantaMufasa]

Also, Idha, in addition to Dagon's correct observation about quotes, you have other issues:

1) You are missing the "IS" semantic for your procedure.
2) You are missing the "BEGIN" semantic for your procedure.
3) 'PERSERVE' is a PERVERSE spelling of PRESERVE.
4) I highly recommend added a 'DROP TABLE...' statement just before you 'CREATE...' the table.

Therefore, here is your code, corrected and enhanced:

create or replace procedure P_X1
is -- <-- You are missing this line
begin -- <-- You are missing this line
    BEGIN
        execute immediate 'drop table x_table';
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
execute immediate
             'CREATE GLOBAL TEMPORARY TABLE X_TABLE ('
           ||'x_year NUMBER(4),'
           ||'x_MONTH VARCHAR2(9))'
           ||'ON COMMIT PRESERVE ROWS' -- 'PRESERVE' was misspelled
           ;
END P_X1;
/

Procedure created.

SQL> exec p_x1

PL/SQL procedure successfully completed.

desc x_table

Name                    Null?    Type
----------------------- -------- -----------
X_YEAR                           NUMBER(4)
X_MONTH                          VARCHAR2(9)

Let us know if this resolves your needs.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

Santa

issue 3

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]