Solved

Difference between Raise & Raise_Application_Error

Posted on 2001-06-05
4
11,721 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

617 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