1 Reply Latest reply: Aug 4, 2013 9:11 PM by rp0428 RSS

    I cannot Disable a constraint in SQL Developer

    05edadc7-a94a-4c84-9a7e-4e4c8d9a249a
      Here is my issue:
      I have created a table already named practice1.
      Using SQL Developer PL/SQL try to run a loop as following:
      DECLARE
      COUNTER1  NUMBER(2);
      BEGIN
      COUNTER1 := 30;
      ALTER TABLE practice1
      DISABLE  CONSTRAINT PRK1;
      LOOP
      COUNTER1 := 30;
      INSERT INTO PRACTICE1
      VALUES (COUNTER1, 'test7', 8, 9);
      EXIT WHEN  COUNTER1 >26;
      END LOOP;
      END;
      In other words I Insert the COUNTER1 variable value as Primary Key in the tables field1 column.
      I run the script successfully without the ALTER TABLE DISABLE CONTSRAINT.. command.
      Everytime I run it I had to increase the starting value of Variable COUNTER1 so it will not attempt to insert a duplicate pre-existed value in Primary Key.
      Then I decided to insert the command ALTER TABLE DISABLE CONSTRAINT in order to not have to worry to change the starting value.
      I am able to disable the constraint by using the same command in isolation . If I run it as part of the script as above I get the following error:
      Error report:
      ORA-06550: line 5, column 1:
      PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
         ( begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-identifier>
         <a bind variable> << continue close current delete fetch lock
         insert open rollback savepoint set sql execute commit forall
         merge pipe purge
      06550. 00000 -  "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:
      I would appreciate any suggestions.
      Thank you.