Comprehensive Analysis of Oracle developer's Exception Handling Mechanism

Oracle developer is famous for its rapid data processing and development, and its exception handling mechanism is also relatively complete.


1. Advantages of exceptions

If no exception exists, check whether each command succeeds or fails in the program, as shown in figure

BEGIN

SELECT...

-- Check for 'no data found 'error

SELECT...

-- Check for 'no data found 'error

SELECT...

-- Check for 'no data found 'error

The disadvantage of this implementation method is that error processing is not separated from normal processing, with poor readability and exception usage, which facilitates error handling. Besides, exception handling programs are separated from normal transaction logic, improving readability, for example

BEGIN

SELECT...

SELECT...

SELECT...

...

EXCEPTION

WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors

2. Exception Classification

There are two types of exceptions: Internal exceptions and custom exceptions, internal exceptions are ORACLE errors returned to PL/SQL blocks during execution or errors caused by an operation of PL/SQL code, such as Zero Divisor or memory overflow. Custom exceptions are displayed and defined by developers. Information is transmitted in PL/SQL blocks to control application error handling.

Every time PL/SQL violates ORACLE principles or goes beyond system dependencies, internal exceptions are implicitly generated. Because each ORACLE error has a number and the PL/SQL exception is handled by the name, ORACLE provides a predefined internal exception. If the select into statement does not return a row, the ORACLE exception NO_DATA_FOUND is generated. For pre-defined exceptions, the most common exceptions are listed as follows:

Exception oracle error sqlcode value condition

No_data_found ora-01403 + 100 select into Statement no matching record returned

Too_mang_rows ora-01422-1422 select into statement multiple records that meet the condition are returned

Dup_val_on_index ora-00001-1 for a column in a database table, this column has been restricted to a unique index, and the program tries to store two duplicate values

Value_error ora-06502-6502 this exception occurs when converting character types, truncation, or length constraints, such as a character assigned to a variable that declares a shorter length than the character, this exception is thrown.

Storage_error ora-06500-6500 memory overflow

Zero_divide ora-01476-1476 divisor 0

Case_not_found ora-06592-6530 for the select case statement, there are no matching conditions and no else statement captures other conditions

• 9 pages in total:
• Previous Page
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• Next Page