Solved

Difference between Raise & Raise_Application_Error

Posted on 2001-06-05
4
9,853 Views
Last Modified: 2010-05-18
What is the difference between RAISE & a RAISE_APPLICATION_ERROR in a PL/SQL Block.
0
Comment
Question by:rvenkat2
4 Comments
 
LVL 2

Expert Comment

by:AllaI
ID: 6158069
You use RAISE statement to raise a programmer-defined exception declared in declaration section of the pl/sql block:
RAISE your_exception;
 You also can use unqualified RAISE statement in an exception handler to re-raise the same exception:
RAISE;
When you need to raise application-specific error from within the server (from database trigger, for example) and pass this error back to the client application process, you use RAISE_APPLICATION_ERROR. You need this special procedure because you cannot name or declare an exception within server-based program or db trigger and have the client-side tool handle that named exception. When you call RAISE_APPLOICATION_ERROR it is as though an exception was raised by the RAISE statement. Execution of the current PL/SQL block halts, and this built-in returns a programmer-defined error number and message back to the client:
RAISE_APPLICATION_ERROR(-20999, 'Eror_message');
On the client side you declare an exception and associate the name of the exception with the error number specified in RAISE_APPLICATION_ERROR using PRAGMA_EXCEPTION_INIT. Then you write exception handler for this named exception.
0
 
LVL 1

Expert Comment

by:hemlatha_rk
ID: 6158269
This is from the Oracle documentation ...
---------------------------------------

Using raise_application_error
-----------------------------
Package DBMS_STANDARD, which is supplied with Oracle, provides language facilities that help your application interact with Oracle. For example, the procedure raise_application_error lets you issue user-defined error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To call raise_application_error, use the syntax

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


where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors. Package DBMS_STANDARD is an extension of package STANDARD, so you need not qualify references to its contents.

An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.

In the following example, you call raise_application_error if an employee's salary is missing:

CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS
   curr_sal NUMBER;
BEGIN
   SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id;
   IF curr_sal IS NULL THEN
      /* Issue user-defined error message. */
      raise_application_error(-20101, 'Salary is missing');
   ELSE
      UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id;
   END IF;
END raise_salary;


The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. Also, it can use the pragma EXCEPTION_INIT to map specific error numbers returned by raise_application_error to exceptions of its own, as the following Pro*C example shows:

EXEC SQL EXECUTE
   /* Execute embedded PL/SQL block using host
      variables my_emp_id and my_amount, which were
      assigned values in the host environment. */
   DECLARE
      ...
      null_salary EXCEPTION;
      /* Map error number returned by raise_application_error
         to user-defined exception. */
      PRAGMA EXCEPTION_INIT(null_salary, -20101);
   BEGIN
      ...
      raise_salary(:my_emp_id, :my_amount);
   EXCEPTION
      WHEN null_salary THEN
         INSERT INTO emp_audit VALUES (:my_emp_id, ...);
      ...
   END;
END-EXEC;


This technique allows the calling application to handle error conditions in specific exception handlers.

How Exceptions Are Raised
-------------------------
Internal exceptions are raised implicitly by the run-time system, as are user-defined exceptions that you have associated with an Oracle error number using EXCEPTION_INIT. However, other user-defined exceptions must be raised explicitly by RAISE statements.

Using the RAISE Statement
-------------------------
PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. You can place RAISE statements for a given exception anywhere within the scope of that exception. In the following example, you alert your PL/SQL block to a user-defined exception named out_of_stock:

DECLARE
   out_of_stock   EXCEPTION;
   number_on_hand NUMBER(4);
BEGIN
   ...
   IF number_on_hand < 1 THEN
      RAISE out_of_stock;
   END IF;
EXCEPTION
   WHEN out_of_stock THEN
      -- handle the error
END;


You can also raise a predefined exception explicitly. That way, an exception handler written for the predefined exception can process other errors, as the following example shows:

DECLARE
   acct_type INTEGER;
BEGIN
   ...
   IF acct_type NOT IN (1, 2, 3) THEN
      RAISE INVALID_NUMBER;  -- raise predefined exception
   END IF;
EXCEPTION
   WHEN INVALID_NUMBER THEN
      ROLLBACK;
   ...
END;

0
 
LVL 1

Accepted Solution

by:
waseemq earned 50 total points
ID: 6160981
Raise_application_error

The Raise_application_error lets us issue user-defined error messages from stored subprograms. That way, we can report errors to our application and avoid
returning unhandled exceptions.

To call raise_application_error, use the syntax

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


where error_number is a negative integer in the range -20000 .. -20999


An application can call raise_application_error only from an executing stored subprogram (or method).
When called, raise_application_error ends the subprogram and returns a user-defined error number and
message to the application.

RAISE Statement

PL/SQL Block terminates the processing when there is some error. If we want to Raise an exception and change the path of processing
we can place RAISE statements. By Raise statement, you can raise user defined exceptions.
 
This is a bried difference between Raise_application_error and Raise Statement
For detail you can consult Oracle Documentation
0
 

Author Comment

by:rvenkat2
ID: 6165075
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now