Re: DDL in stored procedures?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 20 May 1998 00:58:34 GMT
Message-ID: <35622a20.365725_at_192.86.155.100>

A copy of this was sent to Yakov Fain <yakov_at_ibs-inc.com> (if that email address didn't require changing) On Wed, 13 May 1998 18:20:58 GMT, you wrote:

>Hello,
>
>I was trying to create a table (using CREATE TABLE statement) from
>oracle's stored porcedure but it wold not compile. Is this illegal in
>Oracle? In Sybase it works just fine.
>
>Thank you,
>
>Yakov Fain

You must use dbms_sql to do DDL in stored procedures. You might use a simple routine like:

create or replace procedure execute_immediate( 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;                                                                            
/                                                                               

then, you can code:

...

   execute immediate( 'create table foo ( x int )' ); ...

and so on. Also (just to avoid the next issue you'll run into), roles are never enabled during the execution of a procedure. CREATE TABLE for example is usually gotten via the DBA or RESOURCE role.

Try this:

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 May 20 1998 - 02:58:34 CEST