Re: how to execute DDL from PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 18 Jun 1999 13:34:53 GMT
Message-ID: <37724ab0.6311174@newshost.us.oracle.com>

A copy of this was sent to Pascal Byrne <pbyrne_at_ie.oracle.com> (if that email address didn't require changing) On Fri, 18 Jun 1999 14:37:24 +0100, you wrote:

> Hi,
>
>I'm using Oracle server 8.0.4 on Solaris. I'm trying to execute an
>'alter trigger' command from inside a PL/SQL procedure but get a syntax
>error. Is there some special syntax for executing DDL commands?
>
>Thanks,
>Pascal

You need to use dynamic sql to do DDL.

For example:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;
/

will let you execute DDL in plsql. dont forget -- roles are never enabled in stored procedures so the owner of this procedure needs direct grants, not grants from a role... (else insufficient priveleges will result)

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

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 Fri Jun 18 1999 - 08:34:53 CDT