Introduction:
In this article demonstrates,how to effectively using exception blocks in pl/sql procedures and packages.
Main:
Its really difficult to find,the error query in some bigsize pl/sql procedures/packages.Exception blocks are really very helpful to capture those kind of errors.
See the below simple examble,
SET SERVEROUTPUT ON
BEGIN
declare
x number(10);
sql_b varchar2(10);
CURSOR c_list IS
select acct_no from bank where balance>10000; --sample schenario, we are getting account numbers have balance more than 10,000.
c_list%ROWTYPE;
BEGIN
FOR d_list IN c_list LOOP
BEGIN
x=ad_list.acct_no;
sql_b='1.0'; --assign values to each and every query block;
1st query block
.
.
.
.
sql='n';
nth query block
Exception
when others then
dbms_output.put_line('Error in sql block:'|| sql_b || 'Account Number:'|| x);
END LOOP;
END;
END;
/
SET SERVEROUTPUT ON BEGIN declare x number(10); sql_b varchar2(10); CURSOR c_list IS select acct_no from bank where balance>10000; --sample schenario, we are getting account numbers have balance more than 10,000. c_list%ROWTYPE; BEGIN FOR d_list IN c_list LOOP BEGIN x=ad_list.acct_no; sql_b='1.0'; --assign values to each and every query block; 1st query block . . . . sql='n'; nth query block Exception when others then dbms_output.put_line('Error in sql block:'|| sql_b || 'Account Number:'|| x); END LOOP; END; END; / |
Now you can easily find the error query using ‘sql_b’ value.
Conclusion:
Hope this helps,
Happy Coding.