# oceanbase-errorhandling

Part of **OCEANBASE**

<!-- intent-backlink:auto -->

> 💡 **Path Selection**: This skill is one implementation path for [Manage distributed database transactions](../../intent/oceanbase-manage-transactions/SKILL.md). If you're unsure which path to take, check the routing skill first.

# OceanBase Database Error Handling Console Guide

## Operations Overview

| Operation | Console Entry | Prerequisites | Description |
|----------|---------------|---------------|-------------|
| Handle Predefined Exceptions | Console > SQL Workspace > New Query | Valid database connection, sample tables created | Write and execute PL/SQL blocks that catch built-in exceptions like NO_DATA_FOUND or TOO_MANY_ROWS |
| Handle System Exceptions via Error Codes | Console > SQL Workspace > New Query | Knowledge of Oracle-compatible error codes (e.g., -5024) | Use PRAGMA EXCEPTION_INIT to bind system error codes to custom exception names |
| Raise and Handle User-Defined Exceptions | Console > SQL Workspace > New Query | Basic PL/SQL knowledge | Define custom exceptions, trigger them with RAISE, and handle in EXCEPTION block |
| Use RAISE_APPLICATION_ERROR for Custom Messages | Console > SQL Workspace > New Query | None | Generate application-level errors with custom messages and error code -20999 |

## Operation Steps

### Handle Predefined Exceptions

**Navigation**: Console > SQL Workspace > New Query

**Prerequisites**:
- Connected to an OceanBase tenant
- Sample `employees` table exists (as shown in documentation examples)

1. Open a new SQL query editor tab  
   - Element: **New Query** (button) — top-left toolbar in SQL Workspace
   - Notes: Ensure you are in the correct tenant and database context

2. Paste the provided PL/SQL block that handles NO_DATA_FOUND and TOO_MANY_ROWS  
   - Element: **SQL Editor** (textarea) — main central panel
   - Example code includes:  
     ```sql
     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;
         ...
     EXCEPTION
         WHEN NO_DATA_FOUND THEN ...
         WHEN TOO_MANY_ROWS THEN ...
     END;
     ```

3. Click **Run** to execute the block  
   - Element: **Run** (button) — top-right of SQL editor
   - Notes: A results panel appears below showing output from DBMS_OUTPUT.PUT_LINE

4. Observe the output message based on employee ID used  
   - Element: **Query Result** (panel) — below editor
   - Conditional: If `v_empid` doesn’t exist, "Employee id ... not found" appears

### Handle System Exceptions via Error Codes

**Navigation**: Console > SQL Workspace > New Query

**Prerequisites**:
- `dept` table with primary key on `dept_id` must exist
- Attempting to violate unique constraint (e.g., duplicate dept_id)

1. Create a PL/SQL block declaring a custom exception and binding it to error code -5024  
   - Element: **SQL Editor** (textarea)
   - Include:  
     ```sql
     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 ...
     END;
     ```

2. Execute the block using **Run**  
   - Element: **Run** (button)
   - Notes: The update will fail due to primary key violation, triggering the bound exception

3. Verify the custom message appears in output  
   - Expected: "Duplicated Department ID!" if exception handled correctly

### Raise and Handle User-Defined Exceptions

**Navigation**: Console > SQL Workspace > New Query

**Prerequisites**:
- `employees` table with nullable `salary` column
- Employee record with NULL salary (e.g., empno=113)

1. Write a PL/SQL block that defines `SALARY_NOT_SET` exception  
   - Element: **SQL Editor** (textarea)
   - Include declaration: `SALARY_NOT_SET EXCEPTION;`

2. Add logic to check if `v_sal IS NULL`, then **RAISE SALARY_NOT_SET**  
   - Element: **SQL Editor** (textarea)
   - Location: Inside the IF-ELSIF block

3. Add exception handler for `WHEN SALARY_NOT_SET THEN`  
   - Element: **SQL Editor** (textarea)
   - Output: `DBMS_OUTPUT.PUT_LINE('Salary not set: '||v_empid);`

4. Run the query with `v_empid := 113`  
   - Element: **Run** (button)
   - Expected output: "Salary not set: 113"

### Use RAISE_APPLICATION_ERROR for Custom Messages

**Navigation**: Console > SQL Workspace > New Query

1. Create a PL/SQL block without an EXCEPTION section  
   - Element: **SQL Editor** (textarea)
   - Include: `RAISE_APPLICATION_ERROR(-20999, 'The salary of employee is not found');`

2. Set `v_empid` to a non-existent or NULL-salary employee  
   - Element: **SQL Editor** (textarea)

3. Execute the block  
   - Element: **Run** (button)
   - Notes: Since no exception handler exists, the error propagates and displays as:  
     `ORA-20999: The salary of employee is not found`

4. Observe the error in the **Messages** tab of results  
   - Element: **Messages** (tab) — within query result panel

## Common Questions

Q: Where do I write and test exception-handling code in the OceanBase console?  
A: Use the **SQL Workspace > New Query** interface to write, run, and debug PL/SQL anonymous blocks with EXCEPTION sections.

Q: Can I handle multiple exceptions in one block?  
A: Yes. Use separate `WHEN` clauses for each exception (e.g., `WHEN NO_DATA_FOUND`, `WHEN TOO_MANY_ROWS`, `WHEN OTHERS`) in the EXCEPTION block.

Q: What does SQLCODE and SQLERRM return in the WHEN OTHERS clause?  
A: `SQLCODE` returns the numeric error code (e.g., -5024), and `SQLERRM` returns the full error message string. They help diagnose unhandled errors.

Q: Is RAISE_APPLICATION_ERROR only usable inside PL/SQL blocks?  
A: Yes. It must be called within a PL/SQL execution context (anonymous block, procedure, or function) and cannot be used in standalone SQL statements.

Q: Do I need special permissions to use DBMS_OUTPUT.PUT_LINE?  
A: No. `DBMS_OUTPUT` is available by default in OceanBase’s Oracle-compatible mode, but ensure your client (or console) is configured to display server output.

## Pricing & Billing

### Billing Model
Free

### Free Tier
All database error handling operations via the console are included at no additional cost.

### Billing Notes
Error handling using PL/SQL constructs (EXCEPTION blocks, RAISE_APPLICATION_ERROR, etc.) consumes standard compute resources associated with SQL execution, billed under your existing OceanBase instance pricing. No separate charges apply for error handling features.