Home arrow Oracle arrow Page 3 - Database Interaction with PL/SQL: Pre-defined Exceptions

Dup Val on Index, Zero Divide, Invalid Number, Value Error - Oracle

This is part 6 of a series focusing on database interactions with Oracle PL/SQL. The previous articles discussed several types of collections in PL/SQL. Now Jagadish will look into exception handling using the predefined exceptions available in Oracle PL/SQL.

  1. Database Interaction with PL/SQL: Pre-defined Exceptions
  2. No Data Found, Too Many Rows
  3. Dup Val on Index, Zero Divide, Invalid Number, Value Error
  4. A Word About OTHERS Exception
By: Jagadish Chatarji
Rating: starstarstarstarstar / 22
June 28, 2005

print this article




This is bit different from the above two exceptions. This exception is not at all related to SELECT..INTO statement (as above exceptions). This exception gets raised when you try to update (or even insert) a row with a duplicate value in to a column constrained by a unique index (obviously Primary key as well). Let us consider the following program.

  v_empno emp.empno%type:=&empno;
  v_ename emp.ename%type:='&ename';
  v_deptno emp.deptno%type:=&deptno;
  Insert into emp(empno,ename,deptno)
  Values (v_empno,v_ename,v_deptno);
  dbms_output.put_line('Employee Successfully Inserted');
  When DUP_VAL_ON_INDEX then
    dbms_output.put_line('Employee ID already exists');
End ;

The flow of execution of program will be something like the following:

  • Declares the variables and accepts input from the user
  • Tries to execute the INSERT statement based on the values provided by user
  • If INSERT statement gets successfully executed, it displays a positive message to user that the row has been inserted and directly jumps to END statement (skipping the exception section).
  • If INSERT fails due to duplication in a column, constrained with unique index (or primary key), it immediately jumps to exception section (skipping the DBMS_OUTPUT statement next to INSERT statement) and checks whether the exception (or error) is DUP_VAL_ON_INDEX or not. If so, displays the message 'Employee ID already exists'.


The above two predefined exceptions worked well with SELECT..INTO statement. But some predefined exceptions may not be related to SELECT..INTO statement. Let us see ZERO_DIVIDE, INVALID_NUMBER and VALUE_ERROR together within this section.

No number can be divided with zero as it results in infinite. To handle such type of situation, there exists ZERO_DIVIDE exception. Let us consider the following example.

  x number := &x;
  y number := &y;
  z number;
  z := x/y;
  dbms_output.put_line ('Result: ' || z);
  When ZERO_DIVIDE then
    dbms_output.put_line ('Division by zero occured');
End ;

The above program illustrates simple variable declarations of numeric data type. Here, we declare three variables (x, y and z). The values of variables 'x' and 'y' are accepted from the user. Observe that all the variables are of type number (any numeric data type is acceptable). Within the body, we divide 'x' with 'y' and store the result in 'z'.

Everything works fine as long as you provide proper values. If you try to provide the value of 'y' as zero, then ZERO_DIVIDE exception gets raised and a message gets displayed to user.

Coming to INVALID_NUMBER, I hope you can easily understand that whenever PL/SQL runtime is unable to convert a character string to number, the exception gets raised. The following example demonstrates the same.

  v_empno varchar2(4):='&empno';
  v_ename varchar2(20):='&ename';
  v_deptno varchar2(2):='&deptno';
  Insert into emp(empno,ename,deptno)
  Values (v_empno,v_ename,v_deptno);
     dbms_output.put_line('Given Employee Number or deptno is Invalid');
End ;

Coming to VALUE_ERROR, this is almost similar to INVALID_NUMBER except that it works with almost any data type. INVALID_NUMBER gets raised only when SQL statement is issued whereas VALUE_ERROR gets raised with PL/SQL statements. Let us go through the following example.

  z number;
  z := '&x' + '&y';
  dbms_output.put_line ('Result: ' || z);
  When VALUE_ERROR then
    dbms_output.put_line ('Invalid values');
End ;

The above program accepts the value of 'x' and 'y' in the form of character strings and implicitly converts them to numeric. If user provides a non-numeric value, the VALUE_ERROR gets raised. In this case, INVALID_NUMBER will not be raised as nothing is related with SQL statement.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: