Re: Deleting all rows from a table

On 14 Jun 1996 14:40:01 GMT, bharat_at_allegra.tempo.att.com (Bharat Kumar) wrote:

>
>I'm writing a PL/SQL procedure that requires the use of a couple of temporary
>tables. Currently, I've created those temp. tables outside the procedure,
>and inside the procedure, after I'm done with the main work, I delete all
>the rows in those temp tables (required for subsequent invocations of the
>procedure). I would like to use something similar to a "truncate" command,
>since the "delete from temp_table" command takes a long time. However, I
>can't put the truncate command directly into the procedure. Any suggestions?
>
>Here's the psuedocode for the procedure:
>
> procedure blah
> insert into temp_table (subquery...)
> ... select/project/join queries on the temp_table
> delete from temp_table;
> end blah;
>
>
>Please post replies to this newsgroup.
>
>Thanks,
>-Bharat
>
>-------------------------------------------------------------------------------
>Disclamer: My opinions are mine alone, and no one else can have them.

Here is a pl/sql routine that will execute any insert/update/delete/alter/create/truncate type of command. You build a string and call execute_immediate.

Note that the OWNER of this procedure must have all of the needed priveleges granted directly to THEM, not via a role. That is, just because you are a DBA and can issue truncate table X in sql*plus does not necessarily mean that you can run execute_immediate( 'truncate table X' ); If you get a permissions error running the command, you will need to have to needed priveleges granted directly do you (don't forget a truncate will silently commit as well).

For truncate, the table or cluster must be in your schema OR you need the DROP ANY TABLE system privelege.

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
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Jun 17 1996 - 00:00:00 CEST