Truncate tables using PL/SQL

This tip truncates data from tables using PL/SQL for Oracle8i.

This tip truncates data from tables using PL/SQL for Oracle8i. It first disables all foreign key constraints that would cause the "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" error, truncates the tables, and then re-enables all the foreign keys.

 /** * Eliminates the referential integrity. */ DECLARE CURSOR v_ConstraintRefCur IS SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES); v_SQL VARCHAR2(100); BEGIN FOR v_Cntr IN v_ConstraintRefCur LOOP v_SQL:='ALTER TABLE '||v_Cntr.TABLE_NAME|| ' DISABLE CONSTRAINT '||v_Cntr.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_SQL; END LOOP; END; / /** * Truncate the tables. THIS TRUNCATES * ALL TABLES IN THE EXECUTING SCHEMA!! */ DECLARE CURSOR v_TableRefCur IS SELECT TABLE_NAME FROM USER_TABLES; v_SQL VARCHAR2(100); BEGIN FOR v_Cntr IN v_TableRefCur LOOP v_SQL:='TRUNCATE TABLE '||v_Cntr.TABLE_NAME; EXECUTE IMMEDIATE v_SQL; END LOOP; END; / /** * Re-enable foreign keys. */ DECLARE CURSOR v_ConstraintRefCur IS SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES); v_SQL VARCHAR2(100); BEGIN FOR v_Cntr IN v_ConstraintRefCur LOOP v_SQL:='ALTER TABLE '||v_Cntr.TABLE_NAME|| ' ENABLE CONSTRAINT '||v_Cntr.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_SQL; END LOOP; END; /
This was first published in January 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close