 |
|
Oracle Tips by Burleson |
RAISE_APPLICATION_ERROR
The RAISE_APPLICATION_ERRORis 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*Plusdisplayed
the error number and message. The RAISE_APPLICATION_ERRORwill also populate the SQL errors codes so that they can be
programmatically handled.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
