때찌네

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.  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 above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm

You can buy it direct from the publisher for 30%-off and get instant access to the code depot.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.