7 Replies Latest reply on Nov 20, 2007 5:27 PM by user565033

    How to handle ORA-00001: unique constraint in EXCEPTION block?

    user565033
      Hi,

      Is there a specific exception thats raised when ORA-00001: unique constraint error occurs?
        • 1. Re: How to handle ORA-00001: unique constraint in EXCEPTION block?
          jeneesh
          SQL> create table test(id number primary key);

          Table created.
          SQL> set serverout on
          SQL> declare
            2   ex exception;
            3   pragma exception_init(ex,-1);
            4  begin
            5   insert into test values(1);
            6  exception
            7   when ex then
            8    dbms_output.put_line('dup');
            9   when others then
          10    raise;
          11  end;
          12  /
          dup                                                                            

          PL/SQL procedure successfully completed.
          <br>
          <a href ="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#LNPLS00703">Predefied exceptions </a>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
          • 2. Re: How to handle ORA-00001: unique constraint in EXCEPTION block?
            Sven W.
            I think it is DUP_VAL_ON_INDEX.

            And here ist the 9i list of all predefined PL/SQL exceptions:

            http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm#784

            Message was edited by:
            Sven Weller
            • 3. Re: How to handle ORA-00001: unique constraint in EXCEPTION block?
              570955
              Certainly not unless your are using an user defind exception... :)
              • 5. Re: How to handle ORA-00001: unique constraint in EXCEPTION block?
                jeneesh
                I think it is DUP_VAL_ON_INDEX
                :)
                • 6. Re: How to handle ORA-00001: unique constraint in EXCEPTION block?
                  6363
                  It is dup_val_on_index
                  SQL> create table t (n number, constraint t_pk primary key (n));

                  Table created.

                  SQL> insert into t values (1);

                  1 row created.

                  SQL> insert into t values (1);
                  insert into t values (1)
                  *
                  ERROR at line 1:
                  ORA-00001: unique constraint (TEST.T_PK) violated


                  SQL> edi
                  Wrote file afiedt.sql

                    1  begin
                    2      insert into t values (1);
                    3  exception
                    4      when dup_val_on_index then
                    5          dbms_output.put_line('Cannot insert duplicate');
                    6* end;
                  SQL> /
                  Cannot insert duplicate

                  PL/SQL procedure successfully completed.

                  SQL>
                  Though if you know the condition why not avoid the exception?

                  Re: Question about EXCEPTION on INSERT QUERY

                  Re: How do I use NOT EXISTS effectively to insert a new row?
                  • 7. Re: How to handle ORA-00001: unique constraint in EXCEPTION block?
                    user565033
                    Thanks All. DUP_VAL_ON_INDEX worked.