Forum Stats

  • 3,727,564 Users
  • 2,245,413 Discussions
  • 7,852,880 Comments

Discussions

RAISE and RAISE_APPLICATION_ERROR

2779499
2779499 Member Posts: 121
edited July 2015 in SQL & PL/SQL

Is there any difference between RAISE and RAISE_APPLICATION_ERROR?

As per my knowledge by  RAISE we can throw  an user defined exception.

PkaJohnyRaj NathYahoo!Karthick20032779499invalidsearchBilly VerreynneSven W.William Robertsongoodluck247
«1

Answers

  • James Su
    James Su Member Posts: 1,015 Silver Trophy
    edited June 2015

    In RAISE_APPLICATION_ERROR you can throw a user defined message.

  • Unknown
    edited June 2015
    Is there any difference between RAISE and RAISE_APPLICATION_ERROR?
    

    The Oracle documentation clearly explains each of those and how the are different.

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/raise_statement.htm

    RAISE Statement

    The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler.
    RAISE statements can raise predefined exceptions, such as ZERO_DIVIDE or NO_DATA_FOUND, or user-defined exceptions whose names you decide.
    . . .
    In an exception handler, you can omit the exception name in a RAISE statement, which raises the current exception again.

    RAISE is a statement

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#i1871

    RAISE_APPLICATION_ERROR Procedure

    You can invoke the RAISE_APPLICATION_ERROR procedure (defined in the DBMS_STANDARD package) only from a stored subprogram or method. Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.
    

    The other is a procedure.

    The syntax diagrams for each of those shows the differences.

    PkRaj Nath2779499
  • Ashokram
    Ashokram Member Posts: 61
    edited July 2015

    Using  Raise --------- you can Raise only Oracle Errors (Predefined Exceptions) (Syntax/Compilation)  Or

    raise an exception that means error in your business case, i.e. User_defined exceptions and Display a message just using dbms_output.put_line

    If you want your exception to display application error message same as Oracle In-built error you can use RAISE_APPLICATION_ERROR

  • sgalaxy
    sgalaxy Member Posts: 5,686 Bronze Trophy
    edited July 2015
  • Cool
    Cool Member Posts: 72
    edited July 2015

    Hi,

    RAISE_APPLICATION_ERROR usually would be used to throw an exception based on application logic. using this we can map an custom error message and number to the exception, so that the application can map user friendly error message in the UI.

    Regards,

    Cool

  • Unknown
    edited July 2015

    Hi,

    I have seen your query that the difference between the raise and raise application error.

    The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user.

    Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger:

    TRIGGER employees_minsal_tr
      BEFORE INSERT OR UPDATE
      ON employees
      FOR EACH ROW
    BEGIN
      IF :new.salary < 100000
      THEN
      /* communicate error */
      NULL;
      END IF;
    END;

    I can stop the DML from completing by issuing a RAISE statement, such as:

    RAISE PROGRAM_ERROR;

    But I would not be able to communicate back to the user what the actual problem was.

    If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in:

    TRIGGER employees_minsal_tr
      BEFORE INSERT OR UPDATE
      ON employees
      FOR EACH ROW
    BEGIN
      IF :new.salary < 1000000
      THEN
      RAISE_APPLICATION_ERROR (-20000,
      'Salary of '|| :new.salary ||
      ' is too low. It must be at least $100,000.');
      END IF;
    END;

    And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.

    Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in:

    DECLARE
      e_bad_value EXCEPTION;
    BEGIN
      RAISE e_bad_value;
    END;

    but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error.

    I hope this will be helpful for you for the clear understanding and from this you can able to know the topics very clearly.

    Let me know if there is any more issues.

    Regards,

    Vinoth.

  • Yahoo!
    Yahoo! Member Posts: 195 Blue Ribbon
    edited July 2015
    2962860 wrote:
    
    Hi,
    
    I have seen your query that the difference between the raise and raise application error.
    
    The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user.
    
    Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger:
    
    TRIGGER employees_minsal_tr
      BEFORE INSERT OR UPDATE
      ON employees
      FOR EACH ROW
    BEGIN
      IF :new.salary < 100000
      THEN
      /* communicate error */
      NULL;
      END IF;
    END;

    I can stop the DML from completing by issuing a RAISE statement, such as:

    RAISE PROGRAM_ERROR;

    But I would not be able to communicate back to the user what the actual problem was.

    If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in:

    TRIGGER employees_minsal_tr
      BEFORE INSERT OR UPDATE
      ON employees
      FOR EACH ROW
    BEGIN
      IF :new.salary < 1000000
      THEN
      RAISE_APPLICATION_ERROR (-20000,
      'Salary of '|| :new.salary ||
      ' is too low. It must be at least $100,000.');
      END IF;
    END;

    And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.

    Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in:

    DECLARE
      e_bad_value EXCEPTION;
    BEGIN
      RAISE e_bad_value;
    END;

    but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error.

    I hope this will be helpful for you for the clear understanding and from this you can able to know the topics very clearly.

    Let me know if there is any more issues.

    Regards,

    Vinoth.

    Hi,

    IMHO, when copy and paste the text from some other site, you should give the link to that URL (so credit will go to them). Like rp and sgalaxy did.

    RAISE vs RAISE_APPLICATION_ERROR? - Oracle - Oracle - Toad World

    aJohnygoodluck247
  • Unknown
    edited July 2015
  • Saubhik
    Saubhik Member Posts: 5,797 Gold Crown
    edited July 2015

    This is consider very unethical to copy paste some others work without mentioning the original author's name. Please understand that every body in this forum has sufficient expertise to surf the internet, So if you are not adding anything additional then it's better to refrain from posting.

    invalidsearch
  • 2981581
    2981581 Member Posts: 51
    edited July 2015

    To add to others, You can re-raise the last exception using RAISE;

    Below from Oracle documentation:

    DECLARE

      salary_too_high EXCEPTION;

      current_salary NUMBER := 20000;

      max_salary NUMBER := 10000;

      erroneous_salary NUMBER;

    BEGIN

      BEGIN

      IF current_salary > max_salary THEN

       RAISE salary_too_high; -- raise exception

      END IF;

      EXCEPTION

       WHEN salary_too_high THEN -- start handling exception

      erroneous_salary := current_salary;

      DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.');

      DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.');

       RAISE; -- reraise current exception (exception name is optional)

      END;

    EXCEPTION

      WHEN salary_too_high THEN -- finish handling exception

      current_salary := max_salary;

      DBMS_OUTPUT.PUT_LINE (

      'Revising salary from ' || erroneous_salary ||

      ' to ' || current_salary || '.'

      );

    END;

  • sgalaxy
    sgalaxy Member Posts: 5,686 Bronze Trophy
    edited July 2015

    "forgot to include it."

    Don't worry... I have already posted it over 1/2 of hour ago.

    aJohnyYahoo!
  • Unknown
    edited July 2015
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited July 2015

    RAISE is used to invoke named exceptions. The benefit of this is that you can handle that specific exception in your EXCEPTION block.

    For example if a students total score is greater than max score then you want to raise an exception. So you can do something like

    declare
      invalid_score exception;
      ...
    begin
      if score > max_score then
          raise invalid_score;
      end if;
      ...
    
    

    Now this named exception INVALID_SCORE can be handled in the exception block like this

    exception
      when invalid_score then
        ... do something...
    end;
    
    

    But when you use RAISE_APPLICATION_ERROR the only way to catch it is to use WHEN OTHERS.

    So generally the purpose of using RAISE_APPLICATION_ERROR is not to handle a exception but to return a error message to the client.

    So in your exception block you will do something like this

    exception 
      when invalid_score then
         raise_application_error(-20001, 'Entered score is greater than MAX score');
    end;
    

    In some of the projects that I have worked in the past did have a standard of not using RAISE_APPLICAITON_EXCEPTION in the Body of a procedure. This will be used only in the exception block. And exceptional case will be raised using RAISE in the body and will be handled in the exception block of the code. In the exception block if necessary RAISE_APPLICATION_ERROR would be used to return a custom message to the client.

  • James Su
    James Su Member Posts: 1,015 Silver Trophy
    edited July 2015

    "But when you use RAISE_APPLICATION_ERROR the only way to catch it is to use WHEN OTHERS."

    Please see the below example:

    DECLARE

      invalid_score   EXCEPTION;

      PRAGMA EXCEPTION_INIT (invalid_score, -20001);

    BEGIN

      raise_application_error(-20001, 'Entered score is greater than MAX score');

    EXCEPTION

      WHEN invalid_score THEN

           DBMS_OUTPUT.PUT_LINE('exception captured! '||SQLCODE||' '||SQLERRM);

    END;

    /

    exception captured! -20001 ORA-20001: Entered score is greater than MAX score

    PL/SQL procedure successfully completed.

    Karthick2003
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited July 2015

    Yes, now its named exception. But I get the point you are trying to make. My earlier sentence could have been misleading.

  • 2779499
    2779499 Member Posts: 121
    edited July 2015

    This is not your explanation.Kindly refrain from plagiarism

  • Unknown
    edited July 2015

    OK!! I'll refrain that and thanks alot for replying me so late by spending your time!!!

  • 2779499
    2779499 Member Posts: 121
    edited July 2015

    Hi Karthick ,

    IF :new.score < 1000000
      THEN
      RAISE_APPLICATION_ERROR (-20000,'Score is too low.');
    
    
    

    Is the above code snippet same as the below one.

    I mean both the snippets will have same functionality of displaying an error message to the user.

    declare
      invalid_score exception;
      ...
    begin
      if score > max_score then
          raise invalid_score;
      end if;
    
    
      exception
      when invalid_score then
         RAISE_APPLICATION_ERROR (-20000,'Score is too low.')
    end;
    
    
    
    
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited July 2015

    Consider there are 40 places in your code where you check for score and you send user 'Score is too low.' Message. And lets say you have a error message table which stores error number and message (in your case -20000 and 'Score is too low'). Which approach do you think will be good?

    My personal choice is not to use RAISE_APPLICATOIN_ERROR outside of EXCEPTION blocks.

    2779499
  • 2779499
    2779499 Member Posts: 121
    edited July 2015

    You didn't answer my question.

    Obviously 2nd  choice is better.

    So both code will work in the same way with a difference that second is more efficient than on.

    Suppose i have a table err_msg (err_code,err_msg)

    How can i use the table err_msg with  RAISE_APPLICATION_ERROR

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited July 2015
    post.user_wrote.label:
    
    You didn't answer my question.
    Obviously 2nd  choice is better.
    
    So both code will work in the same way
    

    If you are asking for the specific piece of code that you have posted then yes they do the same thing in terms of the end result. But both do have different condition checks (I guess its just a simple mistake).

    2779499
  • 2779499
    2779499 Member Posts: 121
    edited July 2015

    Thanks.

    It will be very cumbersome and impossible to debug if we hard-code the error code and error messages directly in RAISE_APPLICATOIN_ERROR .

    Suppose we store error code in table err_msg (err_code,err_msg).

    How can i pass the value to  RAISE_APPLICATION_ERROR

  • Cool
    Cool Member Posts: 72
    edited July 2015

    Hi,

    I guess you can use a table like you mentioned in order keep track of the list of application_errors used and purpose. But in the procedure I guess you would have to hard code it ( you can query the table and put it, but not sure what is the value add here).

    Regards,

    Wilson

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited July 2015

    You need to device your own error management API.

    Here is a rough example.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table error_master (error_code number, error_message varchar2(100)); Table created. SQL> insert into error_master values (-20001, 'Score too low'); 1 row created. SQL> insert into error_master values (-20002, 'Score too high'); 1 row created. SQL> create or replace package manage_error
      2  as
      3    error_code number;
      4    function set_error_code(error_code number) return number;
      5    function get_error_message return varchar2;
      6  end;
      7  / Package created. SQL> create or replace package body manage_error
      2  as
      3    function set_error_code(error_code number) return number
      4    is
      5    begin
      6      manage_error.error_code := set_error_code.error_code;
      7
      8      return manage_error.error_code;
      9    end;
    10
    11    function get_error_message return varchar2
    12    is
    13      error_message error_master.error_message%type;
    14    begin
    15      select e.error_message into get_error_message.error_message
    16        from error_master e
    17       where e.error_code = manage_error.error_code;
    18
    19      return get_error_message.error_message;
    20    end;
    21  end;
    22  / Package body created. SQL> exec raise_application_error(manage_error.set_error_code(-20001), manage_error.get_error_message)
    BEGIN raise_application_error(manage_error.set_error_code(-20001), manage_error.get_error_message); END; *
    ERROR at line 1:
    ORA-20001: Score too low
    ORA-06512: at line 1
    SQL> exec raise_application_error(manage_error.set_error_code(-20002), manage_error.get_error_message)
    BEGIN raise_application_error(manage_error.set_error_code(-20002), manage_error.get_error_message); END; *
    ERROR at line 1:
    ORA-20002: Score too high
    ORA-06512: at line 1
    SQL>
    2779499
  • Billy Verreynne
    Billy Verreynne Member Posts: 28,157 Red Diamond
    edited July 2015
    2779499 wrote:
    
    You didn't answer my question.
    Obviously 2nd  choice is better.
    
    So both code will work in the same way with a difference that second is more efficient than on.
    
    
    Suppose i have a table err_msg (err_code,err_msg)
    
    How can i use the table err_msg  with  RAISE_APPLICATION_ERROR
    
    

    I prefer the following method.

    It is significantly easier for a developer to use. No meta data layer to update, manage via version control, and deploy, when defining and using application exceptions (and custom formatted messaging).

    It also provides a standard abstraction interface (similar to exception classes in other languages' frameworks), and can be implemented as an abstract class (not final) that can be subclassed for implementing application specific requirements.

    Directly calling Raise_Application_Error() in user or application code, violates basic software engineering principles.

This discussion has been closed.