Disabling Constraints with a Stored Procedure in Oracle

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 

AUTHID CURRENT_USER

IS 

    v_Statement VARCHAR(5000);

BEGIN  

    FOR const in (CURSOR c_Constraints IS

      SELECT constraint_name, table_name, owner

      FROM ALL_CONSTRAINTS

      WHERE owner IN ('UserName1', 'UserName2')) LOOP

      v_Statement := 'ALTER TABLE ' || const.owner 
|| '.' || const.table_name || ' DISABLE CONSTRAINT '
 || const.constraint_name;

      EXECUTE IMMEDIATE v_Statement;

    END LOOP;

END;

/

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.