| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
 |
 |