How to dynamicly create table name using PL/SQL?

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?

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.

Ans:
You can do it using dbms_sql, but that tablename you suggested is too long.
30 characters is the max length of a table name. So if you changed the
name to a shorter one, and use the following code, you can accomplish what
you want.

procedure execute_immediate( p_stmt varchar2 ) is
l_cursor number;
l_status; number;
begin
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor, p_stmt, dbms_sql.native );
l_status := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor( l_cursor );
end execute_immediate;
/

the call to it could then look like...

execute_immediate( 'create table ' || table_name || ' ( n number )' );

NOTE: The owner off this procedure must have create table granted
directly to them.

Ans2:
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.

Ans3:
Really? The procedure will run as a single statement?

If so, then I never realized that! I have to check at the office on Monday...
I always figgered that if, for example, the second INSERT fails, the DELETE
and the first INSERT still need to be rollbacked manually, like below (uhhh, I
do not recall the exact syntax of setting the start of a transaction):

    begin
        set transaction my_start;
        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;
    exception
        when others then
            rollback to my_start;
    end;

Arjan.

Hosted by www.Geocities.ws