Truncating a Table from SP [message #36627] |
Thu, 06 December 2001 18:01 |
Elav
Messages: 7 Registered: October 2001
|
Junior Member |
|
|
Hi,
I've written a Stored Procedure to rollback my previous transactions. In that SP i want to Truncate some 11 tables. But when I tried to do so, i got an error. I understood we cann't use a DDL statement in a Stored Procedure. But I want to do it from there only. Is there any other way to do so?
Need your help on this.
Thanx and Rgds
Elav
----------------------------------------------------------------------
|
|
|
Re: Truncating a Table from SP [message #36630 is a reply to message #36627] |
Thu, 06 December 2001 18:47 |
Satish Shrikhande
Messages: 167 Registered: October 2001
|
Senior Member |
|
|
hello ,
If u r using oracle 8 then you will have to go for dbms_sql package
but in oracle 8i u can do it by
execute immediate
First take all the tables in a cursor
open it in loop
prepare a sql (trucate) staetment
and fire it through execute immediate
create or replace procedure a12 as
cursor c1 is
select table_name from user_objects
where
begin
for z in c1 loop
execute immediate 'truncate table '||z.table_name;
end loop;
end;
----------------------------------------------------------------------
|
|
|