Re: DDL from PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Apr 1998 11:42:43 GMT
Message-ID: <353fd6fc.2814096@192.86.155.100>

A copy of this was sent to Igor Sereda <sereda_at_spb.runnet.ru> (if that email address didn't require changing) On Wed, 22 Apr 1998 14:38:22 -0400, you wrote:

>Hello,
>
>is there a way of issuing DDL statement or system statement (like ALTER
>SYSTEM)
>inside pl/sql block? Seems like there're restrictions about this, but
>perhaps there is a way...
>
>Thank you,
>Igor

You can use dbms_sql, for example:

create or replace procedure exec( stmt in varchar2 ) 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 );

exception

    when others then

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

end;
/

but remember roles are never enabled during the execution of a procedure.

SQL> begin exec( 'create table x ( x int )' ); end;   2 /
begin exec( 'create table x ( x int )' ); end; *
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "TKYTE.EXEC", line 14
ORA-06512: at line 1

SQL> grant create table to tkyte;

Grant succeeded.

SQL> begin exec( 'create table x ( x int )' ); end;   2 /

PL/SQL procedure successfully completed.

SQL> desc x

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

So, always try this before putting a statement into your pl/sql block:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either.

You probably have the privelege to do what you are trying to do in dbms_sql via a role. Grant the privelege directly to the owner of the procedure and it'll work.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Apr 22 1998 - 06:42:43 CDT