Exceptions include internal 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 system exceptions
Generally, implicitly defined system exceptions are errors that occur during the execution of SQL statements, such as deadlocks. Internal system exceptions are automatically raised by the PL engine of OceanBase Database, but they do not have names. The program can obtain the details by using the SQLCODE and SQLERRM functions.
An internal system exception can be handled in the following two ways:
Declare it through
PRAGMA EXCEPTION_INIT, and then capture its name inEXCEPTION HANDLE.Use
OTHERSinEXCEPTION HANDLEto capture the exception, and retrieve the details by usingSQLCODEandSQLERRM.
Capture the exception by name
EXCEPTION_INIT has the following syntax:
PRAGMA EXCEPTION_INIT(handle_name, sql_err_code);
handle_name specifies the name of the exception, which can be used to capture the exception in EXCEPTION. sql_err_code is the corresponding database error code.
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
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!
Capture exceptions by using OTHERS
Example: Capture system exceptions by 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 occur from time to time in the execution of PL programs. Predefined exceptions are automatically raised by OceanBase Database and have explicit names, so they can be directly captured in the program. For example, SELECT... INTO... may trigger a NO_DATA_FOUND exception.
To handle this exception, you just need to reference the corresponding exception name in the exception-handling part of the PL block and complete the corresponding exception error handling process.
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 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
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
Exceptions that occur during program execution must be defined and explicitly raised in the program for subsequent processing.
A user-defined exception can be triggered by explicitly calling the RAISE statement, which is often used to handle exceptions of the application logic. Procedure:
Define the exception name in the
DECLAREpart of the PL program by using the syntax ofexception_name EXCEPTION.Explicitly trigger the exception in the PL program by using the syntax of
RAISE exception_name.Handle the exception in the
EXCEPTIONpart of the PL program by using the syntaxWHEN exception_name THEN.
Example:
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
Salary not set: 113
User-defined error codes
OceanBase Database provides error messages and the RAISE_APPLICATION_ERROR procedure for you to define error codes, which increases the flexibility of error handling. For example, user-defined error codes can be thrown in functions, and exceptions can be captured and handled during function calls.
Syntax:
RAISE_APPLICATION_ERROR(error_number,error_message ) ;
Parameters:
error_numberspecifies the error code. The value ranges from -20000 to -20999.error_messagespecifies the error message whose maximum length is 2,048 bytes.
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 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