Truncate Table

create or replace PROCEDURE truncate_table (v_tab_name in VARCHAR2 ) is

/*internal procedure to truncate table */

CURSOR rcons_cur(pconsname IN VARCHAR2) IS

(SELECT *

FROM user_cons_columns

WHERE (constraint_name,table_name) IN (

(SELECT constraint_name, table_name

FROM user_constraints

WHERE constraint_type = 'R'

AND r_constraint_name = pconsname))

);

/*type to store list of tables and foreign keys */

TYPE cons_col_tab_type IS TABLE OF user_cons_columns%ROWTYPE;

TabColList cons_col_tab_type;

exec_sql VARCHAR2(2000);

BEGIN

/*select all constraints referenced by this table */

FOR pcons_rec IN

(SELECT constraint_name,column_name

FROM user_cons_columns

WHERE TABLE_NAME = v_tab_name

AND constraint_name =

(SELECT constraint_name

FROM user_constraints

WHERE TABLE_NAME = v_tab_name

AND constraint_type = 'P')

and rownum =1 )

LOOP

/* find all tables that references current table */

OPEN rcons_cur (pcons_rec.constraint_name);

FETCH rcons_cur bulk collect INTO TabColList;

CLOSE rcons_cur;

/* enumerate table of constraints */

/*check if collection has elements */

if TabColList.exists(1) then

for i in TabColList.first .. TabColList.last

loop

/*truncate child table first*/

exec_sql := 'ALTER TABLE '||TabColList(i).TABLE_NAME ||' DISABLE CONSTRAINT '||TabColList(i).CONSTRAINT_NAME;

dbms_output.put_line(exec_sql);

execute immediate(exec_sql);

end loop;

/*Truncate table */

exec_sql := 'TRUNCATE TABLE ' ||v_tab_name;

dbms_output.put_line(exec_sql);

execute immediate(exec_sql);

for i in TabColList.first .. TabColList.last

loop

begin

exec_sql := 'ALTER TABLE '||TabColList(i).TABLE_NAME ||' ENABLE CONSTRAINT '||TabColList(i).CONSTRAINT_NAME;

execute immediate(exec_sql);

dbms_output.put_line(exec_sql);

exception when others then

dbms_output.put_line(' could not execute This code ' ||exec_sql);

end;

end loop;

else

exec_sql := 'TRUNCATE TABLE ' ||v_tab_name;

dbms_output.put_line(exec_sql);

execute immediate(exec_sql);

end if;

END LOOP;

END ; -- procedure