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
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 '
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.