Re: Can you use a PL/SQL procedure to create a table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Mar 1999 21:20:52 GMT
Message-ID: <36f82f17.32710675@192.86.155.100>

A copy of this was sent to lahiri_at_postoffice.chrr.ohio-state.edu (Santanu Lahiri)
(if that email address didn't require changing) On Thu, 11 Mar 1999 20:14:37 GMT, you wrote:

>Subject line pretty much says it all...
>
>I need to be able to create several different types of tables on demand from
>within an application. Instead of using a CREATE TABLE sql command, followed
>by a series of CREATE INDEX commands etc, I would like to have the code
>encapsulated within a stored proc, which can then be called as needed.
>
>I can not find anything in the docs I have as to how or even if it can be
>done.
>
>Any suggestions?
>
>Thanks.
>
>Santanu

Yes, you need to use dynamic sql. In Oracle8i it would just be:

begin
...

   execute immediate 'create table t ( x int )'; ...
end;

here is how to do it in 8.0 and less....

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.

this demonstrates I can create a table:

SQL> drop table t;

Table dropped.

SQL>
SQL> create table t ( x int );

Table created.

SQL>
SQL> drop table t;

Table dropped.

This shows I need to have cREATE TABLE granted directly to ME, not via the DBA role i have:

SQL>
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.

and now it works....  

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 Thu Mar 11 1999 - 15:20:52 CST