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

Database Interaction with PL/SQL: Pre-defined Exceptions

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



Please note that all the examples in this series have been tested only with Oracle 10g and not with all the previous versions of Oracle. Refer to the documentation of the respective version you are using if any programs fail to execute.

Introduction to Exception Handling

Before going to exception handling, let us first determine all the possible ways to get errors. Errors may occur in PL/SQL (of course, not only PL/SQL) in several possible ways including hardware failures, network failures, bugs in programs, out of memory and several other reasons. We may not know which error has been raised at what moment. But we need to handle all such errors and provide some meaningful messages to the user (instead of making the user horrified with jargon-filled error messages).

An exception is generally a runtime error which gets raised by PL/SQL runtime when a PL/SQL block is in the process of execution. Handling the exception consists of trapping the error (or exception) and providing a meaningful explanation to the user without showing the weird error message raised by the PL/SQL run-time. This doesn't mean only providing meaningful explanations; we can take certain actions (programmatically) when an error occurs at runtime.

Basically, there exist two types of exceptions:

  1. Predefined Exceptions

    Predefined exceptions are the ones which are already defined by Oracle team that correspond to the most common oracle errors (like division by zero, out of memory, etc.). We can handle them directly within our program without declaring them, and we have thousands of such type of exceptions. In this article, I cover the most important ones.

  2. User-Defined Exceptions

    User-Defined Exceptions must be declared and raised explicitly by the user (by issuing RAISE statements). These exceptions are created, used, raised and implemented by user himself. Oracle will not know about any of those exceptions (till it finds the declarations of those exceptions within the PL/SQL block). There exists EXCEPTION section within the PL/SQL block to handle any sort of exceptions and the section is optional. Let us start by looking into some of the most commonly used predefined exceptions.

>>> 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: