Re: drop and create tables in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Dec 1999 08:18:41 -0500
Message-ID: <qqmm6ss2gbuk44h6386dtik03a6u2tsmre@4ax.com>

A copy of this was sent to "Dennis" <echome_at_ms16.hinet.net> (if that email address didn't require changing) On 30 Dec 1999 07:02:06 GMT, you wrote:

>Dear all,
> I attempt to drop and create a table in PL/SQL, but PL/SQL doesn't
>support DDL commands. The Oracle-supplied package (DBMS_DDL) doesn't do
>this job either. Do you have any ideas? Thanks in advance.
>
>Dennis
>

dbms_sql.

tkyte_at_8.0> 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_sql.close_cursor( exec_cursor );

  9 end;
 10 /

Procedure created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> begin
  2          execute_immediate( 'create table t ( x int primary key )' );

  3 end;
  4 /

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> desc t

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                               NOT NULL NUMBER(38)

tkyte_at_8.0>
tkyte_at_8.0> begin
  2 execute_immediate( 'drop table t' );   3 end;
  4 /

PL/SQL procedure successfully completed.

In Oracle8i, release 8.1, you do not need the procedure, you can just:

begin
  execute immediate 'drop table t';
end;

as execute immediate is a new directive...

see
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

if you find yourself asking the question "Why do I get a "ORA-01031: insufficient privileges" or "PLS-00201: identifier 'x' must be declared" in my stored procedures?" when using dynamic sql.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 30 1999 - 07:18:41 CST