Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

RAISE_APPLICATION_ERROR  tips

Oracle Database Tips by Donald BurlesonMarch 3, 2015

Question:  What does the PL/SQL raise_application_error code do?

Answer:  The raise_application_error  is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999.

Oracle provides the raise_application_error procedure to allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use). Below we illustrate the use of the raise_application_error procedure.

Using the raise_application_error procedure:

DECLARE
Balance integer := 24;

BEGIN
IF (nBalance <= 100) THEN
Raise_Application_Error (-20343, 'The balance is too low.');
END IF;
END;
In this example, error number -20343 is raised if the value of nBalance isn't greater than 100, yielding a message that looks like this:
ORA-20343: The balance is too low.

All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000). The final parameter passed to the procedure is a Boolean(true/false) that tells the procedure to add this error to the error stack or replace all errors in the stack with this error. Passing the value of 'True' adds the error to the current stack, while the default is 'False'.

SQL> create or replace procedure test_var
2 (n_test IN number := 0,
3 n_result OUT number)
4 as
5 begin
6 if n_test > 100 then
7 raise_application_error(-20010,'Number Too
Large');
8 end if;
9 n_result := n_test;
10 end;
11 /

Procedure created.

SQL> declare
2 n_numb number := &Number;
3 n_2 number := 0;
4 begin
5 test_var(n_numb, n_2);
6 dbms_output.put_line(n_2);
7 end;
8 /
Enter value for number: 5
old 2: n_numb number := &Number;
new 2: n_numb number := 5;
5

PL/SQL procedure successfully completed.

SQL> /
Enter value for number: 105
old 2: n_numb number := &Number;
new 2: n_numb number := 105;
declare
*
ERROR at line 1:
ORA-20010: Number Too Large
ORA-06512: at "PUBS.TEST_VAR", line 7
ORA-06512: at line 5

The number value over 100 resulted in the exception being raised and SQL*Plus displayed the error number and message. The raise_application_error  will also populate the SQL errors codes so that they can be programmatically handled.

==================================================

The built in procedure RAISE_APPLICATION_ERROR in the DBMS_STANDARD package can be used for displaying the user defined error message and the error number whose range must be between -20000 to -20999. The exception raised by this procedure cannot be handled explicitly with a name as it does not have one and must be handled only through the OTHERS handler. Whenever this exception occurs, all the uncommitted transactions in the current session will be rolled back to its previous state.

 

% Note: The error code of the predefined exceptions cannot be used in this procedure.

The prototype for defining this exception is shown below,

 

RAISE_APPLICATION_ERROR(<Error_code>, <Error_message> [, True | False]);

 

The first parameter mandatorily accepts an error code between the range of -20000 to -20999. The second parameter accepts a user defined error message of 2048 kb of string at most. The third parameter is an optional one which accepts a Boolean value. When True is passed as the third parameter, this error is added to the top of the list of all other errors which has occurred in this program unit during the execution. By default, it is False.

 

1.  CREATE OR REPLACE TRIGGER trg_emp_detail_chk

2.  Before UPDATE ON employees

3.  DECLARE

4.  permission_denied EXCEPTION;

5.  BEGIN

6.  IF trim(TO_CHAR(sysdate,'Day')) IN ('Saturday', 'Sunday') THEN

7.  raise_application_error(-20000, 'You are not authorized to do any modification in the weekends!!');

8.  END IF;

9.  END;

10. /

Script Explanation:

Line No.

Description

1

A trigger trg_emp_detail_chk is created.

2

The trigger timing is declared as BEFORE UPDATE on the EMPLOYEES table.

3

Start of the declare section of the trigger.

4

A local variable permission_denied of exception data type is declared.

5

Start of the execution section of the trigger.

6

Start of the IF condition checking whether the day of the system time is either Saturday or Sunday or not.

7

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.

8

End of the IF statement.

9,10

End of the exception section of the block.

 

In the above example, a trigger has been created in the schema A to stop any modification to the EMPLOYEES table's data during the weekend. This trigger fires the user defined error message when the below UPDATE statement is executed during the weekend.

 

UPDATE employees SET salary=salary+1000 WHERE employee_id=100;

 

Error report:

SQL Error: ORA-20000: You are not authorized to do any modification in the weekends!!

ORA-06512: at "A.TRG_EMP_DETAILL_CHK", line 4

ORA-04088: error during execution of trigger 'A.TRG_EMP_DETAILL_CHK'

20000. 00000 -  "%s"

*Cause:    The stored procedure 'raise_application_error'

           was called which causes this error to be generated.

*Action:   Correct the problem as described in the error message or contact

           the application administrator or DBA for more information.

RAISE_APPLICATION_ERROR

The user defined exception can be combined with the RAISE_APPLICATION_ERROR procedure to result into an exception with a user defined name, user defined error code and a user defined message.

 

In the below example, the employee, whose salary is more than 10000 will be facing an exception which is handled explicitly in the WHEN clause using the user defined exception name. The result of the exception when an appropriate employee ID is passed to the bind variable is printed below.

 

1.  SET SERVEROUTPUT ON SIZE 200000

2.  DECLARE

3.  l_n_salary NUMBER;

4.  sal_high   EXCEPTION;

5.  pragma exception_init(sal_high,-20001);

6.  BEGIN

7.  SELECT salary INTO l_n_salary FROM employees WHERE employee_id=:employee_id;

8.  IF l_n_salary>10000 THEN

9.  raise_application_error(-20001,'Salary is high');

10. END IF;

11. EXCEPTION

12. WHEN sal_high THEN

13. dbms_output.put_line(SQLCODE);

14. dbms_output.put_line(sqlerrm);

15. END;

16. /

 

Result

 

-20001

ORA-20001: Salary is high

 

Script Explanation:

Line No.

Description

1

This environment variable opens up an output buffer of size limit of 200000.

2

Start of the declaration section of the block.

3

A local variable l_n_salary of the number data type is declared.

4

A local variable sal_high of exception data type is declared.

5

The pragma exception_init with the first parameter as the user defined exception sal_high and the second parameter -20001 is declared.

6

Start of the execution section of the block.

7

The salary of the employee from the EMPLOYEES table for the employee_id bind variable is assigned to the local variable l_n_salary.

8

Start of the IF statement checking if the local variable l_n_salary is greater than 10000 or not.

9

The procedure raise_application_error is called with the first parameter value as -20001 and the second parameter with a default text stating that the employee's salary is high.

10

End of the IF statement

11

Start of the exception section of the block.

12

A when clause handling the predefined exception sal_high is defined.

13,14

The error code (SQLCODE) and the error message (SQLERRM) values are printed using the dbms_output.put_line procedure.

15,16

End of the execution section of the block.


   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster