Database Interaction with PL/SQL: Nested Blocks in Depth - SQLCODE and SQLERRM
(Page 4 of 4 )
Those two are called as error reporting functions in PL/SQL. SQLCODE and SQLERRM give the code and error message respectively. Those values can be directly used within the PL/SQL program during the handling of exceptions. The following example demonstrates this.
Declare
v_sal1 emp.empno%Type := &sal1;
v_ename1 emp.ename%Type;
Begin
Select ename into v_ename1
From emp Where sal=v_sal1;
dbms_output.put_line('Name: '||v_ename1);
Exception
When no_data_found then
dbms_output.put_line('Employee not found with '||v_sal1);
When others then
dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);
End;
The above example does not handle the TOO_MANY_ROWS exception. Indeed, it gets automatically handled with the OTHERS exception. Within that exception, we are displaying both our own message along with the message given by the system for that error. SQLERRM and SQLCODE are generally used in the OTHERS exception, when you really don't know what type of exception gets raised during execution.
SQLERRM already includes SQLCODE within the message. So you need not use SQLCODE in the above scenario. But if you really want to use it, you can follow the example given below:
Declare
v_sal1 emp.empno%Type := &sal1;
v_ename1 emp.ename%Type;
Begin
Select ename into v_ename1
From emp Where sal=v_sal1;
dbms_output.put_line('Name: '||v_ename1);
Exception
When no_data_found then
dbms_output.put_line('Employee not found with '||v_sal1);
When others then
if SQLCODE = -1422 then
dbms_output.put_line('More than one employee exists with the same salary');
else
dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);
end if;
End;
In the above program -1422 is the SQL error code pre-assigned to the exception TOO_MANY_ROWS (which gets shown in the SQLERRM). And I hope the rest is same.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
