Problem to enable oracle constraints

I'm trying for days to disable the constraints of all my tables, insert data and enable the constraints. It works but I did some tests and when I insert a row that doesnt respect a foreign key, there's no error message when I enable the constraints. All I can see is that that constraint is not validated.

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 don't know how to check all constraints at the end and do a rollback if it doesn't work.

---

First off, your code is catching the error that is raised when the constraint cannot be enabled and it is throwing that error away rather than re-raising it. That is almost always a bug. If you don't want to ignore the fact that enabling the constraint failed, you'd want 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;

Because ALTER TABLE is DDL, you cannot rollback. If you encounter an error enabling one constraint, you can't rollback the enabling of all the prior constraints.

Based on your last update, it sounds like you may not want to disable and enable the constraints at all. You may simply want to create deferrable 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.

Normally, this 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 defer the constraint, it won't be validated until you commit. And if a constraint is violated 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