Exceptions are categorized into three types: internal exceptions, predefined exceptions, and user-defined exceptions.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
Internal exceptions
Internal exceptions are generally SQL execution errors, such as deadlocks. OceanBase Database's PL engine automatically throws these exceptions, but they do not have defined exception names. Programs can use SQLCODE and SQLERRM to obtain details.
There are two ways to handle these exceptions:
Declare them using
PRAGMA EXCEPTION_INITand then capture their names inEXCEPTION HANDLE.Use
EXCEPTION HANDLEwithOTHERSto capture them, and retrieve details usingSQLCODEandSQLERRM.
Named capture
The syntax for EXCEPTION_INIT is as follows:
PRAGMA EXCEPTION_INIT(handle_name, sql_err_code);
Here, handle_name is the name of the exception, which can be captured in EXCEPTION, and sql_err_code is the corresponding database error number.
Example: Capturing an internal exception 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
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
Duplicated department id!
Capturing with OTHERS
Example: Capturing an internal exception using OTHERS.
obclient> DECLARE
BEGIN
UPDATE dept SET dept_id=110
where dept_id=100;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err Found: '||SQLCODE);
END;
/
Query OK, 0 rows affected
Err Found: -5024
Predefined exceptions
Predefined exceptions in OceanBase Database's PL engine are common issues during PL program execution. These exceptions are automatically thrown by OceanBase Database and have clear names, allowing direct capture in the program. For example, SELECT... INTO... may trigger the NO_DATA_FOUND exception.
To handle such exceptions, simply reference the exception name in the PL block's exception handling section and perform the appropriate error handling.
Here's an example:
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
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
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 triggered.
DBMS_OUTPUT.PUT_LINE('Employee id '||v_empid||' not found');
WHEN TOO_MANY_ROWS THEN
-- If the employee ID v_empid is not unique, the TOO_MANY_ROWS exception is triggered.
DBMS_OUTPUT.PUT_LINE('Duplicated id: '||v_empid);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
/
Query OK, 0 rows affected
Employee id 100 not found
The following table lists the predefined exceptions:
| Predefined exception | Error code |
|---|---|
| ACCESS_INTO_NULL | -6530 |
| CASE_NOT_FOUND | -6592 |
| COLLECTION_IS_NULL | -6531 |
| CURSOR_ALREADY_OPEN | -6511 |
| DUP_VAL_ON_INDEX | -1 |
| INVALID_CURSOR | -1001 |
| INVALID_NUMBER | -1722 |
| LOGIN_DENIED | -1017 |
| NO_DATA_FOUND | +100 |
| NO_DATA_NEEDED | -6548 |
| NOT_LOGGED_ON | -1012 |
| PROGRAM_ERROR | -6501 |
| ROWTYPE_MISMATCH | -6504 |
| SELF_IS_NULL | -30625 |
| STORAGE_ERROR | -6500 |
| SUBSCRIPT_BEYOND_COUNT | -6533 |
| SUBSCRIPT_OUTSIDE_LIMIT | -6532 |
| SYS_INVALID_ROWID | -1410 |
| TIMEOUT_ON_RESOURCE | -51 |
| TOO_MANY_ROWS | -1422 |
| VALUE_ERROR | -6502 |
| ZERO_DIVIDE | -1476 |
User-defined exceptions
When an exception occurs during program execution that the programmer considers to be an error, the user must define the exception in the program and explicitly raise it, and then handle it accordingly.
User-defined exceptions can be triggered by explicitly calling the RAISE statement. They are typically used to handle exceptions in application logic. The basic steps are as follows:
Define the exception name in the
DECLAREsection of the PL program, using the syntaxexception_name EXCEPTION.Explicitly trigger the exception in the PL program, using the syntax
RAISE exception_name.Handle the exception in the
EXCEPTIONsection of the PL program, using the syntaxWHEN exception_name THEN.
Here is an example:
obclient> DECLARE
v_empid employees.empno%TYPE;
v_sal employees.salary%TYPE;
-- 1. Define the exception name 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. Trigger 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
Salary not set: 113
Custom error codes
OceanBase Database provides the RAISE_APPLICATION_ERROR procedure and error message to allow users to define custom error codes and messages, making error handling more flexible. For example, you can throw a custom error in a function and then capture and handle the exception during the function call.
The syntax is as follows:
RAISE_APPLICATION_ERROR(error_number,error_message ) ;
where:
error_numberis the error code, which ranges from -20000 to -20999.error_messageis the corresponding error message, with a maximum length of 2048 bytes.
Here is an example:
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 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;
/
OBE-20999: The salary of employee is not found