Raise application error 20001

How RAISE_APPLICATION_ERROR ( ) used in ORACLE? How we
define Custom Error messages in ORACLE?

RAISE_APPLICATION_ERROR
( ) allows
users to create custom error messages. 

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is
used to display the user-defined error messages along with the error number
whose range is in between -20000 and -20999.

Whenever a
message is displayed using RAISE_APPLICATION_ERROR, all previous transactions
which are not committed within the PL/SQL Block are rolled back automatically
(i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR
raises an exception but does not handle it. RAISE_APPLICATION_ERROR
is used for the following reasons,
a) To create a unique
id for a user-defined exception.
b) To make the user-defined exception look like an Oracle error.

C) Used to replace
generic Oracle exception messages with our own, more meaningful messages.

The
General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (Error_Number, Error_Message);

• The Error number must be between
-20000 and -20999 
• The Error_message is the message you want to display when the error occurs.

Steps to
be followed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a
user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the
execution section.
3. Finally, catch the exception and link the exception to a user-defined error
number in RAISE_APPLICATION_ERROR.

Let’s use this and see how it works-

SQL> SELECT * FROM EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

9 rows selected.

SQL> DECLARE
  2       Low_Sal_Exc EXCEPTION;
  3       Min_sal NUMBER:= 8000;
  4       New_Sal NUMBER:= 4700;
  5      BEGIN
  6       INSERT INTO EMP_TEMP(ID, NAME, DEPTNO, SALARY)
  7                        VALUES (108,'Tom',30,New_Sal);
  8
  9       IF New_Sal < Min_Sal THEN
 10          RAISE Low_Sal_Exc;
 11      END IF;
 12      DBMS_OUTPUT.PUT_LINE ('Record Inserted Successfully');
 13
 14      Commit;
 15     EXCEPTION
 16     WHEN Low_Sal_Exc THEN
 17        Rollback;
 18        RAISE_APPLICATION_ERROR (-20102, 'Salary is Less than '||Min_Sal);
 19     WHEN OTHERS THEN
 20        DBMS_OUTPUT.PUT_LINE (SQLERRM);
 21  END;
 22  /
DECLARE
*
ERROR at line 1:
ORA-20102: Salary is Less than 8000
ORA-06512: at line 18

SQL> DECLARE
  2     L_DEPTID NUMBER := 40;
  3     L_ENAME VARCHAR2(20);
  4     L_SAL NUMBER;
  5  CURSOR C IS
  6     SELECT NAME, SALARY   FROM EMP_TEMP
  7     WHERE DEPTNO = L_DEPTID;
  8  BEGIN
  9     DBMS_OUTPUT.PUT_LINE('Opening Cursor');
 10     OPEN C;
 11     FETCH C INTO L_ENAME, L_SAL;
 12     IF C%ROWCOUNT =0 THEN
 13        RAISE_APPLICATION_ERROR(-20001,' No Employees in the Department :'||L_DEPTID);
 14     END IF;
 15     CLOSE C;
 16     DBMS_OUTPUT.PUT_LINE('Closing Cursor');
 17  END;
 18  /
Opening Cursor
DECLARE
*
ERROR at line 1:
ORA-20001:  No Employees in the Department :40
ORA-06512: at line 13

Most programmers don’t like exceptions, but you should consider them as your closest friends. They are the ones that honestly say what is wrong with your program. We cannot foresee all possible problematic events, and even the best programmers write bugs.

Exceptions

There are three kinds of exceptions

• Internally defined: A system error, defined by Oracle, that occurs.
• Predefined: The most common internally defined exceptions that are given predefined names.
• User defined: A logical error which you define and raise yourself

System errors could occur from improper coding, like the “ORA-01001: Invalid cursor”, which you should try to fix as soon as possible in your code. And the “TOO_MANY_ROWS”-error might give you clues about bad data quality. To resolve these bugs, it is important to know where, when and why it happened.

But system errors could also occur from hardware failures, like the “ORA-12541: TNS: no listener”, when an ftp-server might be unreachable over the network. In that case, all you can do, and should do, is provide proper error handling and transaction management, and give as detailed information as possible about this situation to the people that need to know.

These system-errors always have an error number assigned, so you can easily identify the error. The 22 predefined exceptions also have a name assigned, which allows for easier, and more readable exception handling. For the other, non-predefined, system-errors, a name can be linked by using the pragma “EXCEPTION_INIT”.

User defined errors we will raise ourselves. They can be given a number and a name.

To raise a user defined error with a chosen number and error message, we call the procedure “RAISE_APPLICATION_ERROR”. Oracle allows us the create error numbers in the range of -20000 to -20999. This allows us to create 1000 unique error codes for our logical errors throughout our application. Just like we did for system errors, we can name our user defined errors by using the pragma “EXCEPTION_INIT”.

If we do not care about the error code and error message, and we will foresee an exception block to directly handle the error, we could also raise errors by the keyword “RAISE”, followed by the exception name. If we do not handle the error, the error ORA-65000, “Unhandled user exception” is propagated.

Besides user defined errors, we could also raise one of the predefined errors. Although it is recommended to use proper a description for your errors, instead of recycling error codes.
In an exception block, the keyword “RAISE” could also be used without an exception name, which can be useful to add logging or to execute clean-up code, before propagating the error.

Error Propagation

When an error occurs, further execution of the execution block is halted, and an appropriate exception handler is searched. If no handler is found in the current block, the error is propagated to the calling block. As a side note, errors that occur in the declaration section are also handled in the calling block.

Transactions stay pending when errors are raised, it is our own responsibility to rollback or commit, although most frameworks will rollback an unhandled exception themselves.

Handling errors

Errors will occur, and when they do, it is important that we know about them, and get as much details from them as possible. When troubleshooting we need the “what”, “where”, “when” and “why”.

For this reason, I add following block to every public function or procedure:

The “log_error”-procedure defined as autonomous transaction, writing the information we need for troubleshooting to a table. Simplified, it looks like this:

Going over the different parameters:

Seq:

Adding a sequence will allow us to order the message in the order they occurred

Object_name/Line:

By using the PL/SQL-directives $$PLSQL_UNIT and $$PLSQL_LINE, we know where the log was created.

Error_stack/Error_backtrace/Call_stack:

In the dbms_utilty package, we find three functions that give us valuable information about the error that was raised. The error stack gives us an overview of all the errors that were raised, giving more information than “SQLCODE” and “SQLERRM”. The error stack gives us the exact line number where the error occurred. The call stack will give us information about which code called the procedure or function raising the error. Later on, I will explain this in more detail.

Attribute1-4:

To reproduce the error, it can be helpful to know which parameters where used when the error occurred.

Log_dt/Log_user:

Further helpful information are the “who” and “when”. Depending on the technology used, you might want to use your own logic to retrieve the application user instead of the Oracle user.

Dbms_utilty example

Let’s take a look at what these functions produce by executing following block of code:

As you can see, I’ve created four procedures, calling each other. Proc1 raises an error, which is caught in proc3, logged, and followed by the raise of a different error. There is a second log written in the anonymous block, so we end up with two records.

The two call stacks are

“ORA-01403: no data found”

And

“ORA-20001: Unhandled exception occured.
ORA-06512: at line 21
ORA-01403: no data found”

As you can see in the code of proc3, I have added a third parameter to the “RAISE_APPLICATION_ERROR”-procedure, telling it to keep the error stack. Our first message tells us a “no data found”-error occurred, our second message tells us we had two errors, first the ORA-01403, followed by the user-defined ORA-20001. The ORA-06512 is merely telling us the line number.

The two backtraces are:

“ORA-06512: at line 5
ORA-06512: at line 11
ORA-06512: at line 17″

And

“ORA-06512: at line 21
ORA-06512: at line 27
ORA-06512: at line 30″

The first back trace tells us that the error occurred at line 5, after proc1 was called at line 11, after proc2 was called on line 17. It points us to the error. If we look at the second backtrace, it points us to line 21, were we find the call to “RAISE_APPLICATION_ERROR”. Notice how it loses the information of the original error on line 5, so it is vital to store the back trace whenever we catch an exception.

Finally, looking at the first call stack, we find following information:

“—– PL/SQL Call Stack —–
object      line  object
handle    number  name
1CA24A88         1  anonymous block
1C9CDCC0        10  procedure SCOTT.LOG_ERROR
1CA121EC        20  anonymous block
1CA121EC        27  anonymous block
1CA121EC        30  anonymous block”

It points us to the original call. The actual log is written in the procedure “log_error”, which was called in proc3 at line 20. Proc3 was called by proc4 at line 27, and proc4 was called at line 30. Where the back trace tells us everything that happened between the error and the logging, the call stack tells us everything that happened before the error.

Conclusion

Troubleshooting errors can be difficult, especially if you don’t know what was going on. But by preparing yourself for these situations, and making sure you have all the information you need, you will be able to pinpoint the problem much faster.

I tried to highlight the most important aspect of error handling in my opinion. If you feel something is missing, please share your knowledge by leaving a comment.

Most of the time that an exception is raised in your application, Oracle Database will do the raising. That is, some kind of problem has occurred during the execution of your code. You have no control over that; once the exception has been raised, all you can do is handle the exception — or let it «escape» unhandled to the host environment.

You can, however, raise exceptions yourself in your own code. Why would you want to do this? Because not every error in an application is the result of a failure of internal processing in the Oracle database. It is also possible that a certain data condition constitutes an error in your application, in which case you need to stop the processing of your algorithms and, quite likely, notify the user that something is wrong.

PL/SQL offers two ways for you to raise an exception:

1. The RAISE statement
2. The RAISE_APPLICATION_ERROR built-in procedure

Of course, you could also force the raising of an exception by Oracle, with code like this:

BEGIN
   my_variable := 1/0;
END;

But I am going to show you how to terminate block execution with lots more control and information than that will do for you.

RAISE

Use RAISE to raise a previously-defined exception. It could be a system exception, provided by PL/SQL itself, such as NO_DATA_FOUND. Or it could be an exception that you declare yourself.

Suppose I have a procedure that accepts a new salary to be assigned to an employee. The rule you want to enforce is that the new salary cannot be negative. This is a sort of «value error», so you could simply raise that pre-defined exception.

[Note: all code in this post may found in my LiveSQL script.]

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF salary_in < 0 
   THEN
      RAISE VALUE_ERROR;
   END IF;

   ... rest of procedure
END;

When you run this procedure as follows:

BEGIN
   use_salary (salary_in => -1);
END;

You will see this error:

 ORA-06502: PL/SQL: numeric or value error  

So you stopped the program from continuing, and that’s great. It can, however, be confusing to people maintaining your code and users seeing your error message to fall back on the generic PL/SQL exception. So you could declare your own exception in exactly the same way that PL/SQL defines exceptions like VALUE_ERROR.

Which probably has you thinking: how does PL/SQL define these exceptions? They are, for the most part, found in a special package named STANDARD. You probably haven’t heard of it, and that’s OK. It is one of the two default packages of PL/SQL: STANDARD and DBMS_STANDARD.

By default, I mean that you can reference elements in these packages without including their package name. For example, I could reference VALUE_ERROR in the use_salary procedure as follows:

PROCEDURE use_salary (salary_in IN NUMBER) 

   AUTHID DEFINER

IS

BEGIN

   IF salary_in < 0 

   THEN

      RAISE STANDARD.VALUE_ERROR;

   END IF;

But I don’t have to. If the compiler cannot otherwise resolve the reference to VALUE_ERROR (without the package name), it will then see if that identifier can be resolved in STANDARD or DBMS_STANDARD.

And if we look inside the STANDARD package, we find code like this:

  VALUE_ERROR exception;
  pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');

Let’s apply that same technique in use_salary:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
   AUTHID DEFINER
IS
   negative_salary EXCEPTION;
   PRAGMA EXCEPTION_INIT (negative_salary, '-6502');
BEGIN
   IF salary_in < 0 
   THEN
      RAISE negative_salary;
   END IF;

   ... rest of procedure
END;

And when I execute the procedure and pass it -1, I see the same error information.

ORA-06502: PL/SQL: numeric or value error 

So what’s the advantage of switching to own exception? In this case (so far), very minor: Your code raises an exception which by name tells you and anyone maintaining the code later what the problem is.The information presented to the user, however, is no more informative than before. If you want to change that message to something more understandable to users, you will want to «switch» to RAISE_APPLICATION_ERROR, which I cover below.

Before we move on, though, here are some thoughts on why you might want to define your own exceptions: Suppose I have several error conditions, and for each error condition, I need to take different actions. I could write code like this in my executable section:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
BEGIN
   CASE
      WHEN salary_in < 0 
      THEN 
         notify_support (
            'Negative salary submitted ' || salary_in); 
         RAISE VALUE_ERROR;
      WHEN salary_in > 10000 
      THEN 
         notify_support (
            'Too large salary submitted ' || salary_in); 
         RAISE VALUE_ERROR;
      WHEN salary_in < 100 
      THEN 
         notify_hr (
            'No one should be treated so shabbily! ' || 
            salary_in);  
         RAISE VALUE_ERROR;
      ELSE
         /* No problems, proceed with normal execution*/
         NULL;
   END CASE;

/* Rest of procedure */

END;

But then I have filled up the first part of the executable section with error-handling code. That makes it harder to see through the «noise» and focus on the actual algorithm I wrote to implement use_salary. A better approach is as follows.

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
   negative_salary EXCEPTION;
   too_large_salary EXCEPTION;
   too_small_salary EXCEPTION;
BEGIN
   CASE
      WHEN salary_in < 0 THEN RAISE negative_salary;
      WHEN salary_in > 10000 THEN RAISE too_large_salary;
      WHEN salary_in < 100 THEN RAISE too_small_salary;
      ELSE NULL;
   END CASE;

   /* Rest of procedure */

EXCEPTION
   WHEN negative_salary
   THEN
      notify_support (
         'Negative salary submitted ' || salary_in); 
      RAISE VALUE_ERROR;

   WHEN too_large_salary
   THEN
      notify_support (
         'Too large salary submitted ' || salary_in); 
      RAISE VALUE_ERROR;

   WHEN too_small_salary
   THEN
      notify_hr (
         'No one should be treated so shabbily! ' || salary_in);  
      RAISE VALUE_ERROR;
END;

Now all my exception-handling code is in the exception section where it belongs.

Notice that I do not use the pragma to assign an error code to my exceptions. That means that the value returned by SQLCODE will be 1. All the time. Which is not too helpful in terms of distinguishing between the errors. In this case, it does not matter, because we are logging the specifics with support or HR and then causing the procedure to fail with a generic message via VALUE_ERROR (often a good idea from the standpoint of security).

RAISE_APPLICATION_ERROR

This built-in, actually defined in the DBMS_STANDARD package, should be used when you need to communicate an application-specific error message to your users.

With RAISE_APPLICATION_ERROR, you provide both the error code and error message as follows:

BEGIN
   RAISE_APPLICATION_ERROR (-20000, 'Say whatever you want');
END;

And when you run the above block it will fail with an unhandled exception, displaying this information:

 ORA-20000: Say whatever you want  

So if I do want to communicate a context-sensitive and user-informative message when there is a failure in my use_salary procedure, I would  do something like this:

CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
   negative_salary EXCEPTION;
   too_large_salary EXCEPTION;
   too_small_salary EXCEPTION;
BEGIN
   CASE
      WHEN salary_in < 0 THEN RAISE negative_salary;
      WHEN salary_in > 10000 THEN RAISE too_large_salary;
      WHEN salary_in < 100 THEN RAISE too_small_salary;
      ELSE NULL;
   END CASE;

   /* Rest of procedure */
EXCEPTION
   WHEN negative_salary
   THEN
      notify_support (
         'Negative salary submitted ' || salary_in); 
      RAISE_APPLICATION_ERROR (-20001,
         'Negative salaries are not allowed. Please re-enter.');

   WHEN too_large_salary
   THEN
      notify_support (
         'Too large salary submitted ' || salary_in); 
      RAISE_APPLICATION_ERROR (-20002,
         'We are not nearly that generous. Please re-enter.');

   WHEN too_small_salary
   THEN
      notify_hr (
         'No one should be treated so shabbily! ' || salary_in);
      RAISE_APPLICATION_ERROR (-20003,
         'C''mon, a person''s gotta eat! Please re-enter.');
END;

And then when I execute the procedure and pass in -1, I will see:

 ORA-20001: Negative salaries are not allowed. Please re-enter. 

Some things to remember about RAISE_APPLICATION_ERROR:

• You must provide an error code between -20999 and -20000.
• If you do not pass TRUE for the third parameter, then the full error stack will not be kept, and only the most recent error message will be shown.
• You can pass a string of up to 32767 characters to the built-in, but that string will be truncated to either 512 or 1899 bytes when calling SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK respectively.

To Conclude

Usually, we leave it to the PL/SQL runtime engine to raise exceptions when an error occurs. Sometimes, however, you will need to raise your own exceptions. To do this, you can use RAISE or RAISE_APPLICATION_ERROR. The latter is especially helpful when you need to communicate an application-specific error message to your users.

Take advantage of PL/SQL’s separate exception handling section to centralize as much of your error-related logic as possible. Keep your executable section clean and focused on executing the «positive» statements — what should be happening when there are not any errors.

If you are a programmer, you might be familiar with the concept of exception handling is an integral part of any programming language. As errors are inevitable and even the smartest of us can make mistakes while writing code, we must be acquainted with how to handle them. In this article, we will be learning particularly about the exception handling in PL/SQL.

Below are the topics covered in this article :

• What is an Exception?
• Syntax of Exception Handling
• Types of Exceptions
  • System defined
    • Named system exceptions
    • Unnamed system exceptions
  • User-defined
    • Steps to declare User-defined functions
    • Examples of User-defined functions

What is an Exception?

Any abnormal condition or event that interrupts the normal flow of our program instructions at run time or in simple words an exception is an error.

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2&nbsp; THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........

WHEN others THEN
exception3-handling-statements
END;

Here, we can list down as many exceptions as we want to handle. The default exception will be handled using ‘WHEN others THEN’

Example of Exception Handling in PL/SQL

The below program displays the name and address of a student whose ID is given. Since there is no student with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

DECLARE 
   s_id studentS.id%type := 8; 
   s_name studentS.Name%type; 
   s_loc studentS.loc%type; 
BEGIN 
   SELECT  name, loation INTO  s_name, s_loc 
   FROM students 
   WHERE id = s_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  s_name); 
   DBMS_OUTPUT.PUT_LINE ('Location: ' || s_loc);
EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such student!'); 
   WHEN others THEN 
      dbms_output.put_line('Oops, Error!'); 
END; 

Output

No such student!
PL/SQL procedure successfully completed.  

Here, we can list down as many exceptions as we want to handle. The default exception will be handled using ‘WHEN others THEN’

Types of Exceptions in PL/SQL

• System defined
• User defied

Next in this article on exception handling in PL/SQL, let us discuss about both of these types in detail.

System defined

Defined and maintained implicitly by the Oracle server, these exceptions are mainly defined in the Oracle Standard Package. Whenever an exception occurs inside the program, Oracle server matches and identifies the appropriate exception from the available set of exceptions available in the oracle standard package. Basically, these exceptions are predefined in PL/SQL which gets raised WHEN particular database rule is violated.

The System-defined exceptions are further divided into two categories:

• Named system exceptions
• Unnamed system exceptions

Named system Exceptions

The named PL/SQL exceptions are named in the standard package of PL/SQL, hence the developer does not need to define the PL/SQL exceptions in their code. PL/SQL provides many pre-defined named exceptions, which are executed when any database rule is violated by a program. The following table lists a few of the important pre-defined exceptions −

Example

CREATE OR REPLACE PROCEDURE add_new_student
      (student _id_in IN NUMBER, student _name_in IN VARCHAR2)
IS
BEGIN
       INSERT INTO student (student _id, student _name )
       VALUES ( student _id_in, student _name_in );
EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
 raise_application_error (-20001,'Duplicate student _id');
       WHEN OTHERS THEN
raise_application_error (-20002,'An error occurred.');
END;

Moving on in this article on exception handling in PL/SQL, let us understand what are unnamed system exceptions.

Unnamed System Exceptions

The system exceptions for which Oracle does not have a name are known as unnamed system exceptions. These exceptions do not occur frequently and are written with a code and an associated message.

There are basically two ways to handle unnamed system exceptions:

1. Using the WHEN OTHERS exception handler

2. Associating the exception code to a name and using it as a named exception.

Some steps followed for unnamed system exceptions are:

• Raise them implicitly.
• In case they are not handled in ‘WHEN Others’ then, they have to be handled explicitly.
• To handle the exception explicitly, they can be declared using Pragma EXCEPTION_INIT and handled by referencing the user-defined exception name in the exception section.

An example of handling unnamed exceptions using Pragma EXCEPTION_INIT is provided later in the article. Moving on in this article on exception handling in PL/SQL, let us understand the User-defined excetpions.

User-defined

Like all other programming languages, Oracle also allows you to declare ad implement your own exceptions. Unlike System defined exceptions, these exceptions are raised explicitly in the PL/SQL block.

Steps to declare User-defined exceptions in the Oracle database

We can define User-defined exceptions in Oracle database in the following 3 ways:

• Using Variable of EXCEPTION type

Here, we can declare a User-defined exception by declaring a variable of EXCEPTION datatype in our code and raise it explicitly in our program using RAISE statement.

• Using PRAGMA EXCEPTION_INIT function

We can define a non-predefined error number with the variable of EXCEPTION datatype

• Using RAISE_APPLICATION_ERROR method

Using this method, we can declare a User-defined exception with our own customized error number and message.

Till now you might have got a rough idea on the ways in which we can raise User-defined exceptions in PL/SQL. We will learn about each of the above-mentioned methods with examples further in this article on exception handling in PL/SQL.

Next in this article, let us proceed with the demonstrations of User-defined exception handling.

Demonstration of User-defined Exceptions

Moving on in this article on Exception Handling in PL/SQL, let us understand how to use the variable of EXCEPTION type.

Using Variable of EXCEPTION type

The process of declaring user-defined exception is divided into three parts and these 3 parts are:

• Declare a variable exception datatype
• Raise the Exception
• Handle the Exception

Let’s write a code to demonstrate the above steps in detail.

DECLARE
       var_dividend NUMBER :=10;
       var_divisor  NUMBER :=0
       var_result NUMBER;
       ex-DivZero EXCEPTION

In the above declaration block, we have four variables, among which the first three are normal number datatype variables and the fourth one which is ex_DivZero is the special exception datatype variable. The fourth one is our user-defined exception.

DECLARE
       var_dividend NUMBER :=10;
       var_divisor  NUMBER :=0
       var_result NUMBER;
       ex-DivZero EXCEPTION

The above execution part of this anonymous block, will come into action only when the divisor is 0. If the divisor is zero as it is in our case, the error will be raised and the control of the program will skip all the next steps and  will look for matching exception handler. In the case where it finds any other, it will perform the action accordingly, otherwise it will either terminate the program or prompt us with an unhandled system defined error.

EXCEPTION WHEN ex_DivZero THEN
DBMS_OUTPUT.PUT_LINE(‘ ERROR, The divisor can’t be zero’);

This the exception handler. As soon as the user enter divisor as 0, the above message string will be prompted.

Final Code:

DECLARE
       var_dividend NUMBER :=10;
       var_divisor  NUMBER :=0
       var_result NUMBER;
       ex-DivZero EXCEPTION
BEGIN
IF var_divisor =0 THEN
RAISE ex-DivZero;
END IF;
Var_result := var_dividend/var_divisor;
DBMS_OUTPUT.PUT_LINE (‘Result = ‘ || var_result);
BEGIN
IF var_divisor =0 THEN
RAISE ex-DivZero;
END IF;
Var_result := var_dividend/var_divisor;
DBMS_OUTPUT.PUT_LINE (‘Result = ‘ || var_result);
END;

Moving on in this article on exception handling in PL/SQL, let us understand how to use the PRAGMA_EXCEPTION_INIT method.

Using PRAGMA EXCEPTION_INIT function

In the PRAGMA EXCEPTION_INIT function,  an exception name is associated with an Oracle error number. This name can be used in designing the exception handler for the error. For huge projects with many user defined errors, PRAGMA EXCEPTION_INIT  is the most useful and suitable method.

Syntax:

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

Example

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
 NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      NULL; -- handle the error
END;

The PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number as mentioned earlier. It  lets you refer to any internal exception by name and write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that can be trapped and handled.

Moving on in this article on Exception Handling in PL/SQL, let us understand how to use the RAISE_APPLICATION_ERROR method.

Using RAISE_APPLICATION_ERROR method

It’s a procedure that comes inbuilt with the oracle software. Using this procedure we can associate an error number with a custom error message. Combining both the error number and the custom error message, an error string can be composed which looks similar to those default error strings which are displayed by oracle when an error is encountered. RAISE_APPLICATION_ERROR procedure is found inside DBMS_STANDARD package

Syntax

raise_application_error (error_number, message [, {TRUE | FALSE}]);

Example

/* A trigger trg_emp_detail_chk is created.*/
CREATE OR REPLACE TRIGGER trg_emp_detail_chk

/* The trigger timing is declared as BEFORE UPDATE on the EMPLOYEES table.*/
Before UPDATE ON employees

DECLARE
permission_denied EXCEPTION;
BEGIN

/*Start of the IF condition checking whether the day of the system time is either Saturday or Sunday or not.*/
IF trim(TO_CHAR(sysdate,'Day')) IN ('Saturday', 'Sunday') THEN
raise_application_error(-20000, 'You are not authorized to do any modification in the weekends!!');

/* The procedure raise_application_error is called with the first parameter value as -20000 and the second parameter
with a default text stating that the user is not authorized to do any modification in the weekends. */
END IF;
END;

With this we come to an end of this article on “Exception handling in PL/SQL”. I hope this topic is understood well and helped you. Try to write your own codes and incorporate the methods explained in this article.

If you want to get trained from professionals on this technology, you can opt for structured training from edureka! Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions etc. End of the training you will be able to create and administer your own MySQL Database and manage data.

Got a question for us? Please mention it in the comments section of this “Exception Handling in PL/SQL” article and we will get back to you as soon as possible.

Exception can be defined as the state of an entity, which is different from its conventional and normal behavior. In context to programming language, exception refers to the abnormal situation in the normal flow of the program. Oracle server raises exception whenever it encounters a logical violation of the flow during program execution.

I have listed the common situations which end up raising exceptions and subsequently termination of the program.

1. Design Faults and illogical flow
2. Exception propagation from referenced program unit
3. Coding mistakes
4. Hardware failures

Exception Propagation in a Program Unit

The figure below shows the program flow, which is followed when an exception situation occurs in the PL/SQL block and the exception has been handled. Once the exception has been raised and trapped, and the control moves to EXCEPTION section, the program propagates in the forward direction.

If the exception has not been handled, the program terminates abruptly or propagates to the calling environment.

Fig 1a: Program flow when the Exception has been trapped and handled

Fig 1b: Program flow when the Exception has not been handled

Exception Handling

Exceptions can be trapped in the EXCEPTION section of a PL/SQL block. Oracle supports two ways to raise exception in a program, implicitly or explicitly.

Exceptions which are automatically raised by the oracle server fall under the category of implicit way raising an exception. PL/SQL runtime engine identifies the abnormal flow and raises the exception. For example, NO_DATA_FOUND or TOO_MANY_ROWS are the system defined exceptions which are raised by the server during program execution.

Exceptions which are trapped in executable section and handled in the EXCEPTION block by the programmer are explicit ways raising exceptions. In this category, a user can either explicitly raise an already existing system defined exception or create a new exception and invoke in the program.

Syntax [1]

EXCEPTION
  WHEN exception1 [OR exception2 . . .] THEN
    statement1;
    statement2;
    . . .
  [WHEN exception3 [OR exception4 . . .] THEN
    statement1;
    statement2;
    . . .]
  [WHEN OTHERS THEN
    statement1;
    statement2;
    . . .]

In the syntax, a single WHEN-THEN statement is called as Exception Handler. Likewise, there can be multiple exception handlers in the EXCEPTION section of a PL/SQL block. An exception handler consists of exception name and set of statements, which would be executed once the exception has been raised. An exception handler can have more than one exception aligned using OR operator. So, the same action would be applicable for multiple exceptions.

Out of multiple exception handlers, only one can be executed at a time before the termination of the block.

One exception handler handles a single exception. Repetitive handling of an exception is not allowed in a single block.

Exception Trapping Functions: SQLCODE and SQLERRM

Oracle uses two built in functions for catching exceptions and getting its information, SQLCODE and SQLERRM.

SQLCODE returns the error number for the latest occurred exception in the PL/SQL block, while SQLERRM returns the error message associated with the latter error number.

Code [1]:

The screen dump below shows the usage of SQLCODE and SQLERRM in a program.

Pre Defined Exceptions

Oracle maintains set of defined exceptions, which are implicitly raised by the server, if an abnormal situation occurs in the program. The table below lists some of the commonly occurring exceptions.

Raising Exceptions Implicitly

These exceptions are automatically processed and raised by Oracle server.

As soon as Oracle server encounters any illogical flow in the program flow, it stops further execution of program, identifies and throws the appropriate exception; program terminates abruptly.

Refer the example illustration below.

Code [2]:

In the below PL/SQL block, a test message is displayed before and after the SELECT statement. The SELECT statement selects multiple rows through an SQL cursor. Since the query returns multiple rows, it raises an exception. As soon as the exception is raised, control jumps to the EXCEPTION section, skipping the further statements in executable section of the block. The message after the query was not displayed.

DECLARE
   L_DEPTID NUMBER := 10;
   L_ENAME VARCHAR2(100);
   L_SAL NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before SQL query');
 
   SELECT EMPLOYEE_NAME,SALARY
   INTO L_ENAME, L_SAL
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = L_DEPTID;
 
   DBMS_OUTPUT.PUT_LINE('After SQL query');
END;
/
 
BEFORE SQL query
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch RETURNS more than requested NUMBER OF ROWS
ORA-06512: at line 7

The above block also complies with the fact that the once the exception scenario is identified, server skips the execution of the further statements in the block and terminates immediately. For this reason, the second test message doesn’t appear in the output.

Raising Exception Explicitly: System Defined and User Defined Exceptions

A developer can explicitly raise the system defined exceptions. In the Code [], the TOO_MANY_ROWS exception can be captured by defining an exception handler in the EXCEPTION section.

Code [3]

In the below PL/SQL code, TOO_MANY_ROWS exception has been explicitly trapped by the developer bypass the situation (abnormal termination) with an informative message in the application.

DECLARE
   L_DEPTID NUMBER := 10;
   L_ENAME VARCHAR2(100);
   L_SAL NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before SQL query');
   SELECT EMPLOYEE_NAME,SALARY
   INTO L_ENAME, L_SAL
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = L_DEPTID;
   DBMS_OUTPUT.PUT_LINE('After SQL query');
EXCEPTION
   WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.PUT_LINE ('Use Oracle Bulk Collect feature or Cursor to select multiple rows from a table');
END;
/
 
BEFORE SQL query
USE Oracle Bulk Collect feature OR Cursor TO SELECT multiple ROWS FROM a TABLE
 
PL/SQL PROCEDURE successfully completed.

An exception handler may contain alternate operations to be performed at the situations of exception in the program.

System defined exceptions can be the part of business logic too. If a business logic implementation or conditional logic requires a system defined exception to be raised, it can be achieved using RAISE statement. Check the example below.

Code [4]:

Oracle raises NO_DATA_FOUND exception, when the SQL query in the executable section returns no records. To avoid the exception, it can be rewritten using a PL/SQL cursor, without omitting the exception action. System defined exception can be explicitly raised by the programmer like below.

DECLARE
   L_DEPTID NUMBER := 15;
   L_ENAME VARCHAR2(100);
   L_SAL NUMBER;
CURSOR C IS
   SELECT EMPLOYEE_NAME,SALARY
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = L_DEPTID;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before SQL query');
   OPEN C;
   FETCH C INTO L_ENAME, L_SAL;
   IF C%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
   END IF;
   CLOSE C;
   DBMS_OUTPUT.PUT_LINE('After SQL query');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE ('No Employees in the department');
END;
/
 
BEFORE SQL query
No Employees IN the department
 
PL/SQL PROCEDURE successfully completed.

User Defined Exceptions

User defined exceptions allow the developers to create their own customized exceptions and raise them within the program wherever required. They are declared in the DECLARE section of the block with type as EXCEPTION and raised using RAISE statement.

This feature gives them flexibility to create exception with their convenient name, which can be more handful for use and easy to remember in large formulated applications.

Code [5]:

The PL/SQL block used in the earlier examples has been reused to declare a user defined exception LOCAL_EXCEPTION. It is raised at the same logic (when no employees exist in a department) using RAISE statement.

DECLARE
   L_DEPTID NUMBER := 15;
   L_ENAME VARCHAR2(100);
   L_SAL NUMBER;
   LOCAL_EXCEPTION EXCEPTION;
CURSOR C IS
   SELECT EMPLOYEE_NAME,SALARY
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = L_DEPTID;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before SQL query');
   OPEN C;
   FETCH C INTO L_ENAME, L_SAL;
   IF C%ROWCOUNT =0 THEN
      RAISE LOCAL_EXCEPTION;
   END IF;
   CLOSE C;
   DBMS_OUTPUT.PUT_LINE('After SQL query');
EXCEPTION
   WHEN LOCAL_EXCEPTION THEN
     DBMS_OUTPUT.PUT_LINE ('No Employees in the department');
END;
/
 
BEFORE SQL query
No Employees IN the department
 
PL/SQL PROCEDURE successfully completed.

Associating a User Defined Exception with an Error Number (or Exception Code)

A user defined exception can be associated with an error number using PRAGMA EXCEPTION_INIT. The pragma is a compiler directive which hints the compiler to accept the directions provided in the program. The PRAGMA EXCEPTION_INIT directs the compiler to align the user defined exception with a self assigned error number. The error number must be one of the valid ORA error codes, which are defined by the server.

Syntax [2]

PRAGMA EXCEPTION_INIT([EX NAME],[ERROR NUMBER], [TRUE | FALSE])

Code [6]

In the PL/SQL block below, the LOCAL_EXCEPTION exception is linked with the error number -100 through the PRAGMA EXCEPTION_INIT. Note the usage of SQLCODE, which returns the same associated error number.

DECLARE
   L_DEPTID NUMBER := 15;
   L_ENAME VARCHAR2(100);
   L_SAL NUMBER;
   LOCAL_EXCEPTION EXCEPTION;
   PRAGMA EXCEPTION_INIT(LOCAL_EXCEPTION, -100);
CURSOR C IS
   SELECT EMPLOYEE_NAME,SALARY
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = L_DEPTID;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before SQL query');
   OPEN C;
   FETCH C INTO L_ENAME, L_SAL;
   IF C%ROWCOUNT =0 THEN
      RAISE LOCAL_EXCEPTION;
   END IF;
   CLOSE C;
   DBMS_OUTPUT.PUT_LINE('After SQL query');
EXCEPTION
   WHEN LOCAL_EXCEPTION THEN
     DBMS_OUTPUT.PUT_LINE (‘Exception Error NUMBER:’||SQLCODE);
     DBMS_OUTPUT.PUT_LINE ('No Employees in the department');
END;
/
 
BEFORE SQL query
Exception Error NUMBER:-100
No Employees IN the department
 
PL/SQL PROCEDURE successfully completed.

Customizing the Error Numbers: RAISE_APPLICATION_ERROR

Oracle facilitates the developer by privileging them to create an error number of their own choice and associating them with a customized message. RAISE_APPLICATION_ERROR is an Oracle API, which allows choosing the error numbers in range of -20000 to -20999 and fix them with an error message

It can be used in executable section or exception section in a PL/SQL block.

Syntax [3]

RAISE_APPLICATION_ERROR (error_number, error_message[, {TRUE | FALSE}])

Code [7]

DECLARE
   L_DEPTID NUMBER := 15;
   L_ENAME VARCHAR2(100);
   L_SAL NUMBER;
CURSOR C IS
   SELECT EMPLOYEE_NAME,SALARY
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = L_DEPTID;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Before SQL query');
   OPEN C;
   FETCH C INTO L_ENAME, L_SAL;
   IF C%ROWCOUNT =0 THEN
      RAISE_APPLICATION_ERROR(-20001,' No Employees in the department');
   END IF;
   CLOSE C;
   DBMS_OUTPUT.PUT_LINE('After SQL query');
END;
/
 
BEFORE SQL query
DECLARE
*
ERROR at line 1:
ORA-20001:  No Employees IN the department
ORA-06512: at line 14

Как отлавливать ошибки в Oracle (PLSQL) EXCEPTION,SQLERRM,SQLCODE

Осваиваем Oracle и PL/SQL

EXCEPTION
   WHEN наименование_ошибки_1 THEN
      [statements]

   WHEN наименование_ошибки_2 THEN
      [statements]

   WHEN наименование_ошибки_N THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [наименование_процедуры];

Вы можете использовать функции SQLERRM и SQLCODE для вызова сообщения об ошибке например таким образом:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'Произошла ошибка - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
-- В данном случае появится всплывающее сообщение.

Или вы можете сохранить сообщение об ошибке в таблицу таким образом:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 200);

      INSERT INTO error_log_table (error_time, error_number, error_message)
      VALUES (sysdate, err_code, err_msg);
END;
--В данном случае ошибка будет помещена в таблицу error_log_table

Варианты основных возможных ошибок:

DUP_VAL_ON_INDEX
ORA-00001
При попытке произвести вставку INSERT или изменение UPDATE данных которое создает дублирующую запись нарушающую уникальный индекс (unique index).

TIMEOUT_ON_RESOURCE
ORA-00051
Происходит когда ресурс над которым производится операция заблокирован и произошел сброс по таймауту.

TRANSACTION_BACKED_OUT
ORA-00061
Произошёл частичный откат транзакции.

INVALID_CURSOR
ORA-01001
Попытка использовать курсор которого не существует. Может происходить если вы пытаетесь использовать FETCH курсор или закрыть CLOSE курсор до того как вы этот курсор открыли OPEN.

NOT_LOGGED_ON
ORA-01012
Попытка произвести действия не залогинившись.

LOGIN_DENIED
ORA-01017
Неудачная попытка залогиниться, в доступе отказано, не верный пользователь или пароль.

NO_DATA_FOUND
ORA-01403
Что то из перечисленного: Попытка произвести вставку SELECT INTO несуществующего набора значений (select — ничего не возвращает). Попытка доступа к неинициализированной строке/записи в таблице. Попытка чтения записи после окончания файла при помощи пакета UTL_FILE.

TOO_MANY_ROWS
ORA-01422
Попытка вставить значение в переменную при помощи SELECT INTO и select вернул более чем одно значение.

ZERO_DIVIDE
ORA-01476
Попытка деления на ноль.

INVALID_NUMBER
ORA-01722
Попытка выполнить SQL запрос который производит конвертацию строки (STRING) в число (NUMBER) при этом такое преобразование невозможно.

STORAGE_ERROR
ORA-06500
Либо нехватка памяти, либо ошибка в памяти.

PROGRAM_ERROR
ORA-06501
Внутренняя программная ошибка рекомендуется с такой ошибкой обращаться в службу поддержки Oracle.

VALUE_ERROR
ORA-06502
Попытка выполнить операцию конвертации данных которая закончилась с ошибкой (например: округление, преобразование типов, и т.п.).

CURSOR_ALREADY_OPEN
ORA-06511
Вы пытаетесь открыть курсор который уже открыт.

Вот пожалуй и всё.

Oracle
PLSQL
SQL
EXCEPTION
SQLERRM
SQLCODE
ошибки
программирование