Disabling Constraints with a Stored Procedure in Oracle

Posted on
Oracle SQL

Sometimes you need to disable constraints on a Oracle Database. Why might this be? Well image the situation that you are exporting data into an intermediate schema, you only want to import data from a certain date range and due to this you have only a subset of the records. You need this subset for analysis but you don't care about referential integrity - in fact if it is on then constraints will be violated. How can we do this?

Here's a stored procedure that disables constraints for tables owned by 'UserName1' or 'UserName2':

CREATE OR REPLACE PROCEDURE extraction.sp_PrepExtractionDatabase 
    v_Statement VARCHAR(5000);
    FOR const in (CURSOR c_Constraints IS
      SELECT constraint_name, table_name, owner
      WHERE owner IN ('UserName1', 'UserName2')) LOOP
      v_Statement := 'ALTER TABLE ' || const.owner 
|| '.' || const.table_name || ' DISABLE CONSTRAINT '
|| const.constraint_name;
      EXECUTE IMMEDIATE v_Statement;

What's the key thing here? 'AUTHID CURRENT_USER'. Without this, running the query itself will work fine, but the stored procedure will find NOTHING in the ALL_CONSTRAINTS view. Run in the context of the current user and then the stored procedure will work fine.