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 last published in January 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close