| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic tables in PL/SQL proc
On Fri, 05 Mar 1999 19:18:33 GMT, arivlin_at_my-dejanews.com 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?
>
>I can build a char variable Table_Name = 'TEMP_TABLE' || to_char ( sysdate...
>) || user. how can I form a create table statement?
>
>
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.
hope this helps.
chris.
>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
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |