3.8 Exception Handling

The PL/SQL built-in exception DUP_VAL_ON_INDEX is raised whenever a SQL statement violates a primary key constraint. This is actually a PL/SQL built-in exception that is raised because you attempted to duplicate a column value for which there is a unique index ”that index being the index generated on your behalf when you created the primary key. At the time such an error occurs, the Oracle error will be ORA-00001 and you can capture that error code with the PL/SQL SQLCODE built-in function. The following stored procedure implements the same functionality as the aforementioned Java procedure.

 create or replace procedure INSERT_STUDENT (     v_student_name   students.student_name%TYPE,     v_college_major  students.college_major%TYPE,     v_status         students.status%TYPE ) IS BEGIN     INSERT INTO students (student_id, student_name,         college_major, status)     VALUES (students_pk_seq.nextval,             v_student_name,             v_college_major,             v_status); EXCEPTION     WHEN DUP_VAL_ON_INDEX THEN         dbms_output.put_line('We have a duplicate insert');         dbms_output.put_line('SQLERRM:'SQLERRM);         dbms_output.put_line('SQLCODE:'SQLCODE); END insert_student; 

Should the aforementioned procedure fail due to a duplicate primary key value, the output will be the following:

 We have a duplicate insert SQLERRM:ORA-00001: unique constraint (SCOTT.PK_STUDENTS) violated SQLCODE:-1 

The SQLCODE is an Oracle predefined function, that only has scope within an exception handler. The value of SQLCODE is not always the same as the Oracle ORA error number. In PL/SQL, your best approach to capture this specific constraint violation is to have an exception handler on the DUP_VAL_ON_INDEX exception. If your code ever enters that program scope, then you are sure you committed to either a primary key or unique constraint violation.

When developing applications with other languages, you need to look at the drivers. The Java code we see in Section 3.1.5, "Sequences in Code," uses the getErrorCode() method, which does not return a minus 1 but the five-digit number ”for a primary key constraint violation, a 1.

We have discussed error handling from an end user perspective; that is, capture primary constraint violations, no matter how rare they might be, and respond to the user with a meaningful message, still leaving the user connected to the application.