My question: Is it possible to truncate multiple tables in a single line statement?
Firstly, thanks for giving this opportunity to ask a question.
I have developed a code to truncate multiple tables in a single line statement. Please refer the below code.
----create a type and then use it as a input parameter of a procedure.------
create or replace type table_ty is table of varchar2(30);
create or replace PROCEDURE Trunc_Mul_Tab(
table_names IN table_ty
)
IS
BEGIN
FOR i IN table_names.first..table_names.last LOOP
dbms_output.put_line('Table_name:'||table_names(i) );
execute immediate 'truncate table '||table_names(i);
dbms_output.put_line('Table'||table_names(i)|| 'is truncated successfully');
END LOOP;
END;
/
--By using type to pass multiple input values.
EXEC Trunc_Mul_Tab(table_ty('EMP','DEP','STUDENTS','MARKS'));
As per above code i have tested in lower environment. Executed Successfully.
I hope this might be very useful to next versions of oracle to set as a pre-defined package.
For example we need to truncate 10 tables for that we have to write 10 truncate statements instead of that we using above code to truncate 10 tables in a single statement.
Please kindly take a look at it. If you have any better development please implement on that.
It is possible to truncate many tables in one go. Just add the cascade option added in 12c.
This will also remove all rows from any child tables. Provided you declare the foreign keys as ON DELETE CASCADE:
create table t1 ( c1 primary key ) as
select level from dual
connect by level <= 100;
create table t2 (
c1 references t1 ( c1 )
on delete cascade
);
insert into t2 values ( 1 );
truncate table t1
cascade;
select count (*) from t1;
COUNT(*)
0
select count (*) from t2;
COUNT(*)
0
Of course, this a dangerous operation! You can't roll truncate back, so you need to take care doing this.