Re: Can you CREATE Table through PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 26 Feb 1999 20:53:54 GMT
Message-ID: <36d8098b.27778303@192.86.155.100>

A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan) (if that email address didn't require changing) On 26 Feb 99 20:10:09 GMT, you wrote:

> I can not find in any documentation whether you can create a table "on-the-fly" through PL/SQL procedures. I have tried to implement an idea in some pseudo code but it doesn't compile. I'm creating a table based on a SELECT call that pulls certain data from many tables before the data is cleared out of the tables. Then the "saved" data resides in a table all by itself for an export. Any ideas?
>
>Thanks again,
>Sean Dolan
>sean_at_3si.com

you can do it with dynamic sql:

SQL> create or replace procedure execute_immediate( sql_stmt in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5    begin
  6      dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
  7      rows_processed := dbms_sql.execute(exec_cursor);
  8          dbms_output.put_line( 'rows processed = ' || rows_processed );
  9      dbms_sql.close_cursor( exec_cursor );

 10 end;
 11 /

Procedure created.

Just make sure you have the appropriate privs granted DIRECTLY to you, not via a role (such as DBA for example). Consider:

SQL> exec execute_immediate( 'create table t( x int )' ); begin execute_immediate( 'create table t( x int )' ); end;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "TKYTE.EXECUTE_IMMEDIATE", line 6
ORA-06512: at line 1

SQL> grant create table to tkyte;
Grant succeeded.

SQL> exec execute_immediate( 'create table t( x int )' ); rows processed = 0
PL/SQL procedure successfully completed.  

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 Feb 26 1999 - 14:53:54 CST