Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Execute DDL from PL/SQL?
On Mon, 03 Feb 1997 18:45:03 -0700, Jason Pociask <pociask_at_maricopa.edu> wrote:
>What was the name/syntax for that procedure call that would
>let you issue DDL calls from within a PL/SQL routine?
>
>Please email me in addition to Usenet reply, if possible...
>
>Thanks,
>
>Jason Pociask, Tempe Arizona USA
I use the following little procedure to automate executing arbitrary statements (anything but selects) in pl/sql:
create or replace
function execute_immediate( stmt in varchar2 )
return number
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native ); rows_processed := dbms_sql.execute(exec_cursor); dbms_sql.close_cursor( exec_cursor );return rows_processed;
when others then
if dbms_sql.is_open(exec_cursor) then dbms_sql.close_cursor(exec_cursor); end if; raise;
returns the number of rows affected (if applicable).
Just remember-- roles are never active in a stored procedure. If you execute:
SQL> exec execute_immediate( 'create table T ( x int )' )
You will need CREATE TABLE privelege. Having a role that has it won't do. You need the privelege directly.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com