In Oracle, exceptions are mechanisms to handle errors and exceptional conditions that may arise during the execution of a PL/SQL block. Here's a brief overview of Oracle exceptions:
1. **Exception Handling:**
- Oracle allows developers to handle errors and exceptions through the use of the `EXCEPTION` block within PL/SQL.
2. **Predefined Exceptions:**
- Oracle provides a set of predefined exceptions, such as `NO_DATA_FOUND`, `TOO_MANY_ROWS`, and `ZERO_DIVIDE`. These exceptions are raised implicitly in certain error situations.
3. **User-Defined Exceptions:**
- Developers can also define their own exceptions using the `DECLARE` section of a PL/SQL block.
```sql
DECLARE
custom_exception EXCEPTION;
BEGIN
-- Some logic that might raise the custom_exception
IF some_condition THEN
RAISE custom_exception;
END IF;
EXCEPTION
WHEN custom_exception THEN
-- Handle the custom exception
DBMS_OUTPUT.PUT_LINE('Custom Exception Raised');
END;
```
4. **RAISE Statement:**
- The `RAISE` statement is used to explicitly raise an exception. This can be a predefined or user-defined exception.
```sql
DECLARE
custom_exception EXCEPTION;
BEGIN
-- Some logic that might raise the custom_exception
IF some_condition THEN
RAISE custom_exception;
END IF;
EXCEPTION
WHEN custom_exception THEN
-- Handle the custom exception
DBMS_OUTPUT.PUT_LINE('Custom Exception Raised');
END;
```
5. **Exception Propagation:**
- Exceptions can be propagated to the calling environment if not handled within the current block. This allows for centralized error handling.
```sql
CREATE OR REPLACE PROCEDURE outer_procedure AS
BEGIN
inner_procedure;
EXCEPTION
WHEN OTHERS THEN
-- Handle exception from inner_procedure
DBMS_OUTPUT.PUT_LINE('Exception in outer_procedure');
END;
CREATE OR REPLACE PROCEDURE inner_procedure AS
BEGIN
-- Some logic that might raise an exception
RAISE NO_DATA_FOUND;
END;
```
6. **Exception Handlers:**
- Handlers can be specified for specific exceptions to provide tailored responses.
```sql
BEGIN
-- Some logic that might raise an exception
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Handle the NO_DATA_FOUND exception
DBMS_OUTPUT.PUT_LINE('No data found');
WHEN OTHERS THEN
-- Handle other exceptions
DBMS_OUTPUT.PUT_LINE('Other exception');
END;
```
7. **Pragma EXCEPTION_INIT:**
- The `PRAGMA EXCEPTION_INIT` directive is used to associate an exception with an error code.
```sql
DECLARE
custom_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(custom_exception, -20001);
BEGIN
-- Some logic that might raise the custom_exception
IF some_condition THEN
RAISE_APPLICATION_ERROR(-20001, 'Custom Error Message');
END IF;
EXCEPTION
WHEN custom_exception THEN
-- Handle the custom exception
DBMS_OUTPUT.PUT_LINE('Custom Exception Raised');
END;
No comments:
Post a Comment