Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Execute DDL from PL/SQL?

Re: Execute DDL from PL/SQL?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/02/04
Message-ID: <32f6a0ae.4381299@nntp.mediasoft.net>#1/1

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;
exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/  

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                          

http://govt.us.oracle.com


statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Feb 04 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US