Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: using truncate in oracle7 procedures
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