handle exception of duplicate value on index : Exception Handle « PL SQL « Oracle PL / SQL






handle exception of duplicate value on index

     
SQL>
SQL>
SQL> CREATE TABLE departments
  2  (department_id           number(10)            not null,
  3   department_name         varchar2(50)          not null,
  4   CONSTRAINT departments_pk PRIMARY KEY (department_id)
  5  );

Table created.

SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 1,             'Data Group' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 2,             'Purchasing' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 3,             'Call Center' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 4,             'Communication' );

1 row created.

SQL>
SQL>
SQL>  declare
  2      l_dept departments%rowtype;
  3    begin
  4      l_dept.department_id := 100;
  5      l_dept.department_name := 'Tech Dudes';
  6      insert into departments ( department_id, department_name )
  7      values( l_dept.department_id, l_dept.department_name );
  8    exception
  9      when DUP_VAL_ON_INDEX then
 10        dbms_output.put_line('DUP_VAL_ON_INDEX exception.');
 11        dbms_output.put_line('This is where we''d write out own handler code.');
 12    end;
 13    /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table departments;

Table dropped.

SQL>
SQL>

   
    
    
    
  








Related examples in the same category

1.Check exception type
2.Deal with multiple exception branches
3.when other exceptions then
4.Handle update exception
5.declaration exception
6.when other then not user-defined exception
7.Using PRAGMA EXCEPTION_INIT
8.Different Values of SQLCODE and SQLERRM
9.The OTHERS Exception Handler
10.Error-handling features of PL/SQL: log exception
11.PLS-483 error: Duplicate Handlers
12.NO_DATA_FOUND exception.
13.The scope of exceptions.
14.Sub block in exception section
15.Catch all exceptions
16.Catch user-defined exception
17.Check zero divide exception
18.Combines declaring an EXCEPTION variable
19.Insert error message to a table in exception handler
20.Mapping a user-defined error code to an EXCEPTION variable
21.This script demonstrates the EXCEPTION_INIT pragma.
22.Error Handling Call
23.Raise exception in if statement