How Can I Create A Procedur For Truncate The Table? I Want To Use This Procedur In Information For Truncate The Table Before Load The Target.

Yes, you can truncate any table using TRUNCATE TABLE command using in procedure. Following are the two procedure which can truncate a single table or desired even all tables in schema.

Truncating a single table.

DECLARE
vtable varchar2:=table_name;
BEGIN
TRUNCATE_TABLE vtable;
-- table will be truncate now here u can perform some desired action.
END;

Truncating multiple tables in schema

DECLARE
vschema varchar2:=scott;

BEGIN
FOR V1 IN (SELECT ' TRUNCATE TABLE '||vschema||'.'||table_name||' ;' as vtrunc from all_tables WHERE user = vschema) LOOP

EXECUTE IMMEDIATE v1.vtrunc;
END LOOP;
--All table which will retrive in select statement will be truncated.
END;

Add Comment

Report