 |
|
Oracle Tips by Burleson |
SQLCODE and SQLERRM
Oracle provides a way to capture the error codes
when a SQL statement raises an exception by using the SQLCODEand
SQLERRM globally-defined variables. Both SQLCODEand
SQLERRM can be useful in tracking exceptions that are handled by the
OTHERS clauseof the exception handler. SQLCODE returns the current
error code from the error stack. SQLERRM returns the error message
from the current error code as shown below.
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 exception
8 when others then
9 begin
10 dbms_output.put_line('SQLCODE: '||SQLCODE);
11 dbms_output.put_line('Message: '||SQLERRM);
12 end;
13 end;
14 /
Enter value for number: 105
old 2: n_numb number := &Number;
new 2: n_numb number := 105;
SQLCODE: -20010
Message: ORA-20010: Number Too Large
Using the same function from the previous example,
a value greater than 100 will raise an exception. The OTHERS clausein
the exception handler caught the exception and printed out the SQLCODEand
SQLERRM. Once you know the error code, you can programmatically
handle the exception, like this:
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 exception
8 when others then
9 begin
10 if SQLCODE = -20010
11 then dbms_output.put_line('Value Too
Large');
12 else dbms_output.put_line('Unknown
Exception');
13 end if;
14 end;
15 end;
16 /
Enter value
for number: 105
old 2:
n_numb number := &Number;
new 2: n_numb number := 105;
Value Too
Large
Handling exceptions is an important programming
practice that is all too often overlooked. Many times, exception
handling is added too late, after the code is written and deployed, or
when the developer is attempting to stop or locate programming
errors. Exception handling should be a part of the program design and
should be included in all appropriate PL/SQL blocks. Properly raising
and handling exceptions will allow the developer to effectively track
and maintain the code. The ability to programmatically recover from
system, application, and user errors is important in keeping the
application running and the users happy.
 |
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. |
|
