Exception handling

Precautions

We recommend that you use an exception handler only in the following scenarios:

• An exception is expected and must be handled.

  For example, it is predictable that the final SELECT INTO statement may return blank lines and that OceanBase Database may report the predefined exception NO_DATA_FOUND. You wish to use a subprogram or a STORED PROCEDURE block to handle the exception (which is not an error) so that the program can continue execution. For example:

  create or replace procedure select_dept(
    num_deptno in number,-- Define the in mode variable, which specifies the department number.
    var_dname out dept.dname%type,-- Define the out mode variable, which can store and output department names.
    var_loc out dept.loc%type) is
  begin
    select dname,loc
    into var_dname,var_loc
    from dept
    where deptno = num_deptno;-- Retrieve the information about the department with the specified department number.
  exception
    when no_data_found then -- If no rows are returned for the SELECT statement.
      dbms_output.put_line ('The department number does not exist.');-- Output information
  end select_dept;
  

• A resource must be discarded or closed. For example:

  ...
  file := UTL_FILE.OPEN ...
  BEGIN
  statement statement]...
  EXCEPTION
  WHEN OTHERS THEN
  UTL_FILE.FCLOSE(file); -- then you want to close the file.
  RAISE; -- Continue to raise exceptions.
  END;
  UTL_FILE.FCLOSE(file);
  ...
  

• An error log must be recorded at the top level of the subprogram code. For example:

  BEGIN
  proc(...); -- Call other subprograms.
  EXCEPTION
  WHEN OTHERS THEN
  log_error_using_autonomous_transaction(...); -- Use autonomous transaction logging.
  RAISE; -- Continue to raise exceptions.
  END;
  /
  

Exception handling examples

OceanBase Database provides two exception handling mechanisms. One is to capture exceptions by using the "exception" identifier in PL during SQL statement writing. The other is to have corresponding exception handling methods when different drivers are used for application layer code development.

PL exception handling

PL exceptions include internal system exceptions, predefined exceptions, and user-defined exceptions. The following provides examples of the three types.

• An internal system error occurs.

  For example, capture internal system exceptions by name.

  obclient>CREATE TABLE dept(  
      dept_id           NUMBER(10,0),  
      dname            VARCHAR(15),  
      loc            VARCHAR(20),   
     CONSTRAINT pk_dept PRIMARY KEY(dept_id)
    );
  Query OK, 0 rows affected (0.07 sec)
  
  obclient>INSERT INTO dept VALUES (100,'ACCOUNTING','Los Angeles'),(110,'OPERATIONS','CHICAGO'),
           (111,'SALES','NEW YORK');
  
  obclient>DECLARE
           DUPLICATED_DEPT_ID EXCEPTION;
           PRAGMA EXCEPTION_INIT(DUPLICATED_DEPT_ID, -5024);
       BEGIN
           UPDATE  dept SET dept_id=110
            where dept_id=100;
       EXCEPTION
          WHEN DUPLICATED_DEPT_ID THEN
             DBMS_OUTPUT.PUT_LINE('Duplicated Department ID!')
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
       END;
       /
  Query OK, 0 rows affected (0.03 sec)
  
  Duplicated department id!
  

• Predefined exceptions

  obclient>CREATE TABLE employees(  
         empno  NUMBER(4,0),  
         empname     VARCHAR(10),  
         job               VARCHAR(10),    
        deptno         NUMBER(2,0),
        salary          NUMBER(7,2),
  
       CONSTRAINT PK_emp PRIMARY KEY (empno)
  
   );
  Query OK, 0 rows affected (0.07 sec)
  
  obclient>INSERT INTO employees VALUES (200,'Jennifer','AD_ASST',1,15000),
         (202,'Pat','MK_REP',3,12000),(113,'Karen','PU_CLERK', 4,null),(201,'Michael','MK_MAN',3,9000);
  Query OK, 4 rows affected (0.06 sec)
  Records: 4  Duplicates: 0  Warnings: 0
  
  obclient>DECLARE
          v_empid employees.empno%TYPE;
          v_sal   employees.salary%TYPE;
       BEGIN
           v_empid := 100;
           SELECT salary INTO v_sal FROM employees
            WHERE empno=v_empid;
           IF v_sal<=10000 THEN
               UPDATE employees SET salary=salary+100 WHERE empno=v_empid;
               DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' updated');
           ELSE
               DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' ignored');
           END IF;
       EXCEPTION
           WHEN NO_DATA_FOUND THEN
  
               -- If the employee ID v_empid does not exist, the NO_DATA_FOUND exception is raised.
               DBMS_OUTPUT.PUT_LINE('Employee id '||v_empid||' not found');
           WHEN TOO_MANY_ROWS THEN
  
               -- If more than one v_empid record is found, the TOO_MANY_ROWS exception is raised.
               DBMS_OUTPUT.PUT_LINE('Duplicated id: '||v_empid);
           WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
       END;
       /
  Query OK, 0 rows affected (0.06 sec)
  
  Employee id 100 not found
  

• User-defined exceptions include user-defined exceptions and user-defined error codes.

  • User-defined exceptions

    obclient>DECLARE
            v_empid employees.empno%TYPE;
            v_sal   employees.salary%TYPE;
    
            -- 1. Define the exception name as SALARY_NOT_SET.
            SALARY_NOT_SET EXCEPTION;
         BEGIN
             v_empid := 113;
             SELECT salary INTO v_sal FROM employees
              WHERE empno=v_empid;
             IF v_sal<=10000 THEN
                 UPDATE employees SET salary=salary+100 WHERE empno=v_empid;
                 DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' updated');
             ELSIF v_sal is NULL THEN
    
                 -- 2. Raise the exception when v_sal is NULL.
                 RAISE SALARY_NOT_SET;
             ELSE
                 DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' ignored');
             END IF;
         EXCEPTION
             WHEN NO_DATA_FOUND THEN
                 DBMS_OUTPUT.PUT_LINE('Employee id '||v_empid||' not found');
    
             -- 3. Handle the SALARY_NOT_SET exception.
             WHEN SALARY_NOT_SET THEN
                 DBMS_OUTPUT.PUT_LINE('Salary not set: '||v_empid);
             WHEN OTHERS THEN
                 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
         END;
         /
    Query OK, 0 rows affected (0.07 sec)
    
    Salary not set: 113
    

  • User-defined error codes

    obclient>DECLARE
            v_empid employees.empno%TYPE;
            v_sal   employees.salary%TYPE;
         BEGIN
             v_empid := 103;
             SELECT salary INTO v_sal FROM employees
             WHERE empno=v_empid;
             IF v_sal is NULL THEN
    
                 -- Throw error code -20999
                 RAISE_APPLICATION_ERROR(-20999, 'The salary of employee is not found');
             ELSIF v_sal<=1500 THEN
                 UPDATE employees SET salary=salary+100 WHERE empno=v_empid;
                 DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' updated');
             ELSE
                 DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' ignored');
             END IF;
         END;
         /
    ORA-20999: The salary of employee is not found
    

For more information about PL exception handling, see PL exception handling in PL Reference.

OceanBase JDBC driver exception handling

For SQL statements that directly depend on the underlying driver, we recommend that you use a standard template class such as JDBCTemplate, SqlMapClientTemplate, or SqlMapClientDaoSupport for the object-relational mapping (ORM) framework. These implementations can convert underlying errors into the standard exception types of the Spring Framework. If you use a standard template class, OceanBase Database does not need to directly perceive the underlying driver type com.alipay.oceanbase.obproxy.mysql.exceptions.jdbc4.MySQLIntegrityConstraintViolationException, as shown in the following example:

try {
    // Business logic
} catch (Exception e) {
    if (e instanceof DataIntegrityViolationException
        && ((DataIntegrityViolationException) e).contains(DuplicateKeyException.class)) {
        // Uniqueness conflict handling
    }
}

Check for com.alipay.oceanbase.obproxy.mysql.exceptions.*, which indicates the exceptions of other drivers. If other drivers are used, convert these exceptions into corresponding standard exceptions of the Spring Framework.

OceanBase JDBC driver is used as an example in this topic. For more information about how drivers handle exceptions, check the official website of the corresponding driver.