Re: Create Table in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/19
Message-ID: <3499e224.46969598@inet16>#1/1

On Thu, 18 Dec 1997 18:43:46 GMT, drfuller_at_wsicorp.com (Donald Fuller) wrote:

>Hi,
> I am trying to create temporary tables in my stored procedures and I
>am finding that I cannot create, drop or truncate tables. Is this
>functionality not available in PL/SQL?
>
>Any suggestion on how I can ? I listed the error message I am receiving.
>
>Thanks,
>Don
>
>ORA-06550: line 3, column 1:
>PLS-00103: Encountered the symbol "CREATE" when expecting one of the
>following:
>
> begin declare exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql commit <a single-quoted SQL string>

for DDL (which create, drop and truncate are considered) you must use dynamic sql. Below is a procedure I commonly use to help do this. With it you can code:

begin
....

    execute_immediate( 'create table foo ( x int )' );
    execute_immediate( 'truncate table foo' );
    execute_immediate( 'drop table foo' );

...
end;

be forwarned however that roles are never enabled in stored procedures. In order for execute_immediate to be able to create a table, the owner of execute_immediate must have been granted create table directly. In sql*plus to see if you'll be able to do something dynamically in the procedure, you might try:

SQL> set role none;
SQL> create table foo ( x int );

If it fails with set role none; it'll fail in the procedure as well.

create or replace procedure 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 );                                       
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  

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 Fri Dec 19 1997 - 00:00:00 CST