Re: Dynamic tables in PL/SQL proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Mar 1999 21:08:42 GMT
Message-ID: <36e4471b.21005754@192.86.155.100>

A copy of this was sent to arivlin_at_my-dejanews.com (if that email address didn't require changing) On Fri, 05 Mar 1999 19:18:33 GMT, you wrote:

>I need help creating dynamic table within PL/SQL proc.
>
>Procedure replases data in table using delete and 5 "insert into ...
>select..." statements. If one of "insert into ... select" fails, I want to
>keep original data in destination table. For that I need a temporary table,
>created on-fly, to make sure all 5 statements run OK. how can I create table
>inside PL sql proc with a name TEMP_TABLE_FRI_3_5_1999_11_07_23_234_Arivlin
>where stuff after TEMP_TABLE is timestamp and user name?
>

you do not need a temporary table. Just code:

create procedure my_refresh
as
begin

        delete from T;

    insert into t select * from t1;
    insert into t select * from t2;
    insert into t select * from t3;
    insert into t select * from t4;
    insert into t select * from t5;

    commit;
end;

if any of the delete or inserts fail -- the original data will be there. the procedure will run as a single statement, either they ALL work or none do.

>I can build a char variable Table_Name = 'TEMP_TABLE' || to_char ( sysdate...
>) || user. how can I form a create table statement?
>
>
>To make things worse, there is no front end where I can prepare SQL.
>Thank you in advance
>Alex
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Mar 05 1999 - 15:08:42 CST