42.12. Error Handling

42.12.1. Overview of Exception Handling
42.12.2. Predefined Exceptions
42.12.3. Raising Exceptions Explicitly

This chapter explains how to handle PLoraSQL compile-time warnings and PLoraSQL runtime errors. The latter are called exceptions.

42.12.1. Overview of Exception Handling

Exceptions (PLoraSQL 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 PLoraSQL 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

42.12.1.1. Exception Categories

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 PLoraSQL has given a name

  • User-defined You can declare your own exceptions in the declarative part of any PLoraSQL anonymous block, subprogram, or package

42.12.1.2. Advantages of Exception Handlers

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 PLoraSQL only supports predefined errors.

42.12.2. Predefined Exceptions

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

42.12.2.1. INVALID_CURSOR

INVALID_CURSOR mainly contend for two situations: explicit cursor and cursor variables.

42.12.2.1.1. Explicit Cursor

  • 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, PLoraSQL 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();
    

Tip

Error handling with PLoraSQL, and lightdb_syntax_compatible_type and search_path need to be set as follows:

set lightdb_syntax_compatible_type = oracle;

SET search_path TO public, oracle.

42.12.2.1.2. Cursor Variables

  • 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, PLoraSQL 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();
    

42.12.2.2. CASE_NOT_FOUND

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();
    

42.12.2.3. DUP_VAL_ON_INDEX

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();
    

42.12.2.4. NOT_DATA_FOUND

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();
    

42.12.2.5. TOO_MANY_ROWS

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();
    

42.12.2.6. LOGIN_DENIED

You can use raise to throw the predefined exception and handle the problem after the exception. (not triggered in plorasql, 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();
    

42.12.2.7. ZERO_DIVIDE

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();
    

Tip

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 plorasql does not support savepoint.

42.12.3. Raising Exceptions Explicitly

To raise an exception explicitly, use either the RAISE statement or RAISE_APPLICATION_ERROR procedure.

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/oraSQL will not print message. Otherwise, PL/oraSQL will print message. The FALSE is default.