Re: CREATE ... in a stored procedure??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/16
Message-ID: <337c9084.5488401@newshost>#1/1

On 8 May 1997 00:08:31 GMT, michael_rothwell_at_non-hp-usa-om46.om.hp.com (Michael R) wrote:

>I know how to use cursors to select information from tables in a stored
>procedures, but how would I be able to Create an object such as a table from a
>stored procedure??
>
>Thanks.
>
>Michael
>

You can use dbms_sql to do this. The following function, execute_immediate, helps automate this. You would for example, be able to:

   ...
   n := execute_immediate( 'create table foo ( x int )' );    n := execute_immediate( 'insert into foo select user_id from all_users' );    dbms_output.put_line( n || ' rows inserted into foo' );    n := execute_immediate( 'drop table foo' );    ...

NOTE: priveleges gained via roles are never enabled in stored procedures. You might be able to create a table in sql*plus but not in a procedure. To see if a dbms_sql statement will work in a procedure you can test the statement in plus first:

SQL> set role none;
SQL> REM set role none gives me the set of privs available at runtime in a proc.
SQL> create table foo( x int );

If the create table works above, then it'll work in the procedure. If not, you will need to have CREATE TABLE granted directly to you, not to some role you have.

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

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 16 1997 - 00:00:00 CDT