This chapter explains how to handle pl/sql compile-time warnings and pl/sql runtime errors. The latter are called exceptions.
Exceptions (pl/sql runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.
Any pl/sql block can have an exception-handling part, which can have one or more exception handlers. For example, an exception-handling part could have this syntax:
EXCEPTION WHEN ex_name_1 THEN statements_1 -- Exception handler WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler WHEN OTHERS THEN statements_3 -- Exception handler END;
When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1 was raised, then statements_1 run. If either ex_name_2 or ex_name_3 was raised, then statements_2 run. If any other exception was raised, then statements_3 run.
After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:
If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation
If the exception handler is in an anonymous block, then control transfers to the host environment
The exception categories are:
Internally defined
The runtime system raises internally defined exceptions implicitly (automatically)
Predefined
A predefined exception is an internally defined exception
that pl/sql has given a name
User-defined
You can declare your own exceptions in the declarative part of
any pl/sql anonymous block, subprogram, or package
Using exception handlers for error-handling makes programs easier to write and understand, and reduces the likelihood of unhandled exceptions. Without exception handlers, you must check for every possible error, everywhere that it might occur, and then handle it. It is easy to overlook a possible error or a place where it might occur, especially if the error is not immediately detectable. Error-handling code is scattered throughout the program. With exception handlers, you need not know every possible error or everywhere that it might occur. You need only include an exception-handling part in each block where errors might occur. In the exception-handling part, you can include exception handlers for both specific and unknown errors. If an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it. Error-handling code is isolated in the exception-handling parts of the blocks.
Currently pl/sql supports predefined errors and User-defined errors.
Predefined exceptions are internally defined exceptions that have predefined names. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.
There are 7 predefined errors currently supported:
INVALID_CURSOR
CASE_NOT_FOUND
DUP_VAL_ON_INDEX
NOT_DATA_FOUND
TOO_MANY_ROWS
ZERO_DIVIDE
LOGIN_DENIED
INVALID_CURSOR mainly contend for two situations: explicit cursor and cursor variables.
You close an open explicit cursor with the CLOSE statement, thereby allowing its resources to be reused. After closing a cursor, you cannot fetch records from its result set or reference its attributes. If you try, pl/sql raises the predefined exception INVALID_CURSOR.
If an explicit cursor is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR.
for example. After closing a cursor variable, you cannot fetch records from its result set or reference its attributes.
--preconditional
create table employees(id int, name varchar2(100), job varchar2(100));
insert into employees values (22091401, 'sunli', 'actress');
insert into employees values (22091402, 'dengchao', 'actor');
insert into employees values (22091403, 'lishizhen', 'doctor');
insert into employees values (22091404, 'zhangsanfeng', 'martial artist');
create or replace function invalid_cursor_f return int as
cursor c1 is select * from employees;
emp_cur employees%rowtype;
begin
open c1;
loop
fetch c1 into emp_cur;
exit when c1%notfound;
close c1;
end loop;
close c1;
EXCEPTION
WHEN invalid_cursor THEN
DBMS_OUTPUT.PUT_LINE('invalid cursor');
return 1;
end;
/
select invalid_cursor_f();
for example, If an explicit cursor is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR.
create or replace function invalid_cursor_f return int as
cursor c1 is select * from employees;
emp_cur employees%rowtype;
ora_found boolean := false;
begin
ora_found := c1%found;
EXCEPTION
WHEN invalid_cursor THEN
DBMS_OUTPUT.PUT_LINE('invalid cursor');
return 1;
end;
/
select invalid_cursor_f();
Error handling with pl/sql, and the value of lightdb_dblevel_syntax_compatible_type is oracle.
When you no longer need a cursor variable, close it with the CLOSE statement, thereby allowing its resources to be reused. After closing a cursor variable, you cannot fetch records from its result set or reference its attributes. If you try, pl/sql raises the predefined exception INVALID_CURSOR.
A cursor variable has the same attributes as an explicit cursor. The syntax for the value of a cursor variable attribute is cursor_variable_name immediately followed by attribute (for example, cv%ISOPEN). If a cursor variable is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR
for example, After closing a cursor variable, you cannot fetch records from its result set or reference its attributes.
create or replace function invalid_cursor_f return int as
c1 sys_refcursor;
emp_cur employees%rowtype;
begin
open c1 for select * from employees;
loop
fetch c1 into emp_cur;
exit when c1%notfound;
close c1;
end loop;
close c1;
EXCEPTION
WHEN invalid_cursor THEN
DBMS_OUTPUT.PUT_LINE('invalid cursor');
return 1;
end;
/
select invalid_cursor_f();
for example, If a cursor variable is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR.
create or replace function invalid_cursor_f return int as
c1 sys_refcursor;
emp_cur employees%rowtype;
ora_isopen boolean := false;
begin
ora_isopen := c1%isopen;
return 1;
exception
when INVALID_CURSOR then
dbms_output.put_line('this is a normal error');
end;
/
select invalid_cursor_f();
case statement, if the necessary conditional branch is not included in the when clause, it will trigger. There are two cases of simple_case_statement and search_case_statment.
for example, simple_case_statement.
create or replace function case_not_found_f return int as
grade CHAR(1);
BEGIN
grade := 'G';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such grade');
return 1;
END;
/
select case_not_found_f();
for example, search_case_statment.
create or replace function case_not_found_f return int as
grade CHAR(1);
BEGIN
grade := 'G';
CASE
WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such grade');
return 1;
END;
/
select case_not_found_f();
Triggered when a duplicate value is inserted into the column corresponding to the unique index.
for example:
--preconditional
CREATE TABLE emp_name AS SELECT id, name FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (id);
create or replace procedure dup_val_on_index_p as
emp_id NUMBER(10);
emp_name VARCHAR2(100);
BEGIN
SELECT id, name INTO emp_id, emp_name FROM employees WHERE id = 22091401;
INSERT INTO emp_name (id, name) VALUES (emp_id, emp_name);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');
END;
/
call dup_val_on_index_p();
Triggered when no row is returned when select into is executed.
for example:
-- no_data_found
-- select into
-- number of record selected is zero
create or replace function no_data_found_f return int as
v_emp int;
begin
select id into v_emp from employees where id = 22091501;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No data found');
return 1;
end;
/
select no_data_found_f();
When a select into statement is executed, it will fire if more than one row is returned.
for example:
create or replace function too_many_rows_f return int as
v_emp int;
begin
select id into v_emp from employees;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No data found');
WHEN too_many_rows THEN
DBMS_OUTPUT.PUT_LINE('Too Many Rows');
return 1;
end;
/
select too_many_rows_f();
You can use raise to throw the predefined exception and handle the problem after the exception. (not triggered in pl/sql, but in lightdb)
for example:
create or replace function login_denied_f return int as
begin
raise login_denied;
exception
when login_denied then
dbms_output.put_line('this is a login_denied message.. good luck the next time');
return 1;
end;
/
select login_denied_f();
Triggered when a non-zero number divided by 0 event occurs.
for example:
create or replace function zero_divide_f return int as
a int := 10;
b int := 0;
begin
a := a / b;
exception
when zero_divide then
dbms_output.put_line('this is a normal error');
return 1;
end;
/
select zero_divide_f();
1.Error handling does not support handling problem summary:
The dynamic SQL in the anonymous block contains the anonymous block of commit and rollback, which is not supported;
The dynamic SQL in the stored procedure contains anonymous blocks of commit and rollback, which is not supported;
Stored procedures in the package and anonymous blocks containing commit and rollback in dynamic SQL in anonymous blocks are not supported.
2.There is a problem with the execution order of functions, stored procedures, and anonymous blocks in the package;
3.Currently pl/sql does not support savepoint.
To raise an exception explicitly, use either the RAISE statement or RAISE_APPLICATION_ERROR procedure.
The RAISE statement not only supports throwing built-in standard exceptions,
but also supports throwing user-defined exception variables that initialized by EXCEPTION_INIT command.
You can invoke the RAISE_APPLICATION_ERROR procedure only from a stored subprogram or method.
Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.
To invoke RAISE_APPLICATION_ERROR , use this syntax:
RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);
The error_code is an integer in the range -20000..-20999. If you specify TRUE,
pl/sql will not print message. Otherwise, pl/sql will print message. The FALSE is default.
The DBMS_UTILITY.format_error_backtrace function is a critical subprogram to call when logging exceptions. It returns a string that traces the error back to the line on which it was raised! The back trace function always only traces back to the most recently raised exception. The maximum length that the error allows to return is 10000.
Use function of DBMS_UTILITY.format_error_backtrace, for example:
--preconditional
create table test_error_stack(id number primary key);
insert into test_error_stack values (1);
create table t_errlog(operate_error_msg varchar(1000));
create or replace procedure pr_error_code_test1 is
v_backtrace varchar(1000);
v_error varchar(1000);
begin
INSERT INTO test_error_stack values (2);
INSERT INTO test_error_stack values (1);
commit;
exception when others then
insert into t_errlog(operate_error_msg) select to_char(dbms_utility.format_error_backtrace);
commit;
rollback;
end pr_error_code_test1;
/
call pr_error_code_test1();
select * from t_errlog;
--Use function calls alone
select select to_char(dbms_utility.format_error_backtrace)
Supports obtaining integer error codes when throwing exceptions.
The error code obtained under the non exception block is a value of 0.
The error code of this integer can be converted into a string error code of length 5.
The conversion function SQLCODE_TO_SQLSTATE in orafce.
String error code of length 5 in Appendix A.
for example:
declare
a text;
b int;
begin
raise exception 'sqlcode test';
exception
when others then
a := sqlstate;
b := sqlcode;
raise notice 'sqlstate: %, sqlcode: %, sqlcode: %', a, b, sqlcode;
end;
/
select SQLCODE_TO_SQLSTATE(16777248);
Support getting error messages when throwing exceptions. When the error code obtained in a non exception block is a value of 0, the error message is ORA-0000.