Re: Very simple PL/SQL question...

Hi there,

you may use Dynamic SQL to perform this. This will work for all Oracle versions since 7.x. The PL/SQL block below uses Dynamic SQL
to disable all constraints in a user schema. Hope this helps

DECLARE
    CURSOR c_constr
IS

     SELECT constraint_name, table_name FROM user_constraints;
     c_ddl                NUMBER;
     v_rows             NUMBER;
     v_stmt              VARCHAR2(128);
     v_const_name   user_constraints.constraint_name%TYPE;
     v_table_name    user_constraints.table_name%TYPE;
BEGIN
     c_ddl := DBMS_SQL.OPEN_CURSOR;
     v_stmt := 'ALTER TABLE :table DISABLE CONSTRAINT :const';
     DBMS_SQL.PARSE (c_ddl, v_stmt, DBMS_SQL.NATIVE);
     OPEN c_constr;
             LOOP
                  FETCH c_constr INTO v_const_name, v_table_name;
                  EXIT WHEN c_constr%NOTFOUND;
                  DBMS_SQL.BIND_VARIABLE(c_ddl, ':table', v_table_name);
                  DBMS_SQL.BIND_VARIABLE(c_ddl, ':const', v_const_name);
                  v_rows := DBMS_SQL.EXECUTE(c_ddl);
             END LOOP;
     DBMS_SQL.CLOSE_CURSOR(c_ddl);