Re: PL/SQL and DDL

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1997/07/09
Message-ID: <5pve5e$rn6$1@news01.btx.dtag.de>#1/1

Lawrence Simela wrote:
>
> >>When using PL/SQL or built-in Oracle packages within store procedures
> >>and packages, is there a way for me to use such DDL statements as
> >>Create/Drop Table, Create/Drop Synonym, and Alter Table.
 

> >>Tri
>
> Yes, DDL statements can only be issued using dynamic SQL. Use the
> DBMS_SQL package to execute SQL statements dynamically at runtime.
>
> Lawrence Simela
> Mahalini Consulting Limited.

Thomas Kyte from oracle.us once posted something like this:

210 procedure execute_immediate(stmt varchar2) is

211  	     exec_cursor     integer default dbms_sql.open_cursor;
212  	     rows_processed  number default 0;
213  begin
214  	     dbms_sql.parse(exec_cursor, stmt, dbms_sql.native);
215  	     rows_processed := dbms_sql.execute(exec_cursor);
216  	     dbms_sql.close_cursor(exec_cursor);
217  exception
218  	     when others then
219  	     if dbms_sql.is_open(exec_cursor) then
220  		     dbms_sql.close_cursor(exec_cursor);
221  	     end if;
222  	     raise;

223 end execute_immediate
224 ;

This will execute your ddl-statement.

Call will be something like

        execute_immediate('Create table foo(f number);');

Originaly execute_immediate was a function returning the value of rows_processed.
We use this in an ODBC-environment that can't return values, so we changed it to a procedure.

-- 
M.Greß    :-)

Received on Wed Jul 09 1997 - 00:00:00 CDT