Exceptions are categorized into three types: system exceptions, predefined exceptions, and user-defined exceptions.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Internal exceptions
Internal exceptions are implicitly defined by the system and generally occur during SQL execution, such as deadlocks. These exceptions are automatically thrown by the PL engine of OceanBase Database but do not have predefined names. Programs can retrieve details using SQLCODE and SQLERRM.
There are two ways to handle these exceptions:
Declare them using
PRAGMA EXCEPTION_INITand then capture them inEXCEPTION HANDLEby name.Use
OTHERSinEXCEPTION HANDLEto 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 code.
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 the PL engine of OceanBase Database are common issues during PL program execution. These exceptions are automatically thrown by OceanBase Database and have clear names that can be directly captured in the program. For example, SELECT... INTO... may trigger the NO_DATA_FOUND exception. To handle such exceptions, simply reference the corresponding exception name in the exception handling section of the PL block and perform the necessary 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, the programmer must define the exception in the program and explicitly raise it, then handle it accordingly.
User-defined exceptions can be triggered by explicitly calling the RAISE statement. They are typically used to handle application logic exceptions. 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 this 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 for more flexible error handling. 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:
```javascript
RAISE_APPLICATION_ERROR(error_number,error_message ) ;
where:
error_numberis the error code, ranging 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;
/
ORA-20999: The salary of employee is not found
```
END;
/
ORA-20999: The salary of employee is not found