The problem of including oracle restrictions

Question

I have been trying for several days to disable restrictions on all of my tables, insert data, and resolve restrictions. It works, but I did some tests, and when I insert a line that does not respect the foreign key, when the restrictions are turned on, an error message does not appear. All I see is that this restriction is not checked.

This is the code to disable

begin BEGIN FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints WHERE constraint_type = 'R' OR constraint_type = 'P' ) LOOP BEGIN EXECUTE IMMEDIATE 'alter table '||rec.table_name||' disable constraint '||rec.constraint_name; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'ERROR: alter table '||rec.table_name||' DISABLE constraint '||rec.constraint_name||';' ); END; END LOOP; DBMS_OUTPUT.PUT_LINE('CONSTRAINTS DISABLED'); END; BEGIN FOR rec IN ( SELECT trigger_name FROM user_triggers WHERE TRIGGER_NAME NOT LIKE 'BIN$%' ) LOOP EXECUTE IMMEDIATE 'alter trigger '||rec.trigger_name||' disable'; END LOOP; END; DBMS_OUTPUT.PUT_LINE('TRIGGERS DISABLED'); end; / 

This is the code to enable

 begin BEGIN FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints where status = 'DISABLED' and constraint_type = 'R' OR constraint_type = 'P' ) LOOP BEGIN EXECUTE IMMEDIATE 'alter table '||rec.table_name||' enable constraint '||rec.constraint_name; dbms_output.put_line('alter table '||rec.table_name||' enable constraint '||rec.constraint_name); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('ERROR: alter table '||rec.table_name||' enable constraint '||rec.constraint_name||' ;' ); END; END LOOP; END; DBMS_OUTPUT.PUT_LINE('CONSTRAINTS ENABLED'); BEGIN FOR rec IN ( SELECT trigger_name FROM user_triggers WHERE TRIGGER_NAME NOT LIKE 'BIN$%' ) LOOP EXECUTE IMMEDIATE 'alter trigger '||rec.trigger_name||' enable'; END LOOP; END; DBMS_OUTPUT.PUT_LINE('TRIGGERS ENABLED'); END; 

I do not know how to check all the restrictions at the end and roll back if it does not work.

Answer 1

First, your code catches an error that occurs when a restriction cannot be enabled, and it throws that error, rather than raising it again. This is almost always a mistake. If you do not want to ignore the fact that the inclusion of the restriction failed, you need something like

 FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints where status = 'DISABLED' and constraint_type = 'R' OR constraint_type = 'P' ) LOOP BEGIN l_sql_stmt := 'alter table '||rec.table_name|| ' enable constraint ' || rec.constraint_name; dbms_output.put_line( l_sql_stmt ); EXECUTE IMMEDIATE l_sql_stmt; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('ERROR: ' || l_sql_stmt ); raise; -- re-raise the exception END; END LOOP; 

Since ALTER TABLE is DDL, you cannot roll back. If you encounter an error that includes one restriction, you cannot cancel the inclusion of all previous restrictions.

Based on the latest update, it looks like you don't want to disable and allow restrictions at all. You may just want to create pending constraints.

 SQL> create table foo ( 2 col1 number 3 ); Table created. SQL> ed Wrote file afiedt.buf 1 alter table foo 2 add constraint pk_foo 3 primary key( col1 ) 4* deferrable SQL> / Table altered. 

This usually behaves like any other primary key constraint.

 SQL> insert into foo values( 1 ); 1 row created. SQL> insert into foo values( 1 ); insert into foo values( 1 ) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.PK_FOO) violated SQL> rollback; Rollback complete. 

But if you postpone the restriction, it will not be checked until commit. And if the restriction is aborted when the transaction ends, the transaction is rolled back.

 SQL> alter session set constraints = deferred; Session altered. SQL> select * from foo; no rows selected SQL> insert into foo values( 1 ); 1 row created. SQL> insert into foo values( 1 ); 1 row created. SQL> insert into foo values( 1 ); 1 row created. SQL> commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (SCOTT.PK_FOO) violated