Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: using truncate in oracle7 procedures

Re: using truncate in oracle7 procedures

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/12/18
Message-ID: <32b842c2.17750303@dcsun4>#1/1

On 17 Dec 1996 04:36:44 GMT, "heath" <heaths_at_powerup.com.au> wrote:

>can TRUNCATE TABLE be used in database procedures? i need to regularly
>remove all rows in a quite large table and recreate the table. DELETE
>works but takes a long time ... when i put TRUNCATE in the procedure,
>compilation fails.
>
>
>thank, ben

You can use dbms_sql to do this. Perhaps the easiest way is to install a procedure like:

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

And then, for example:

SQL> begin
  2 dbms_output.put_line(execute_immediate('truncate table test_date_tbl'));   3 end;
  4 /

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com


statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Dec 18 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US