PL/SQL Exception error handling

PL/SQL Exception error handling

A good program should be able to correctly handle all kinds of errors and recover from errors as much as possible. Oracle provides EXCEPTION and exception handler for error handling.

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------

① There are three types of exception errors:
1. Predefined Error
There are about 24 predefined ORACLE exceptions. To handle this exception, you do not need to define it in the program. ORACLE will automatically throw it.

2. Predefined Error
That is, other standard ORACLE errors. To handle this exception, you need to define it in the program, and then the ORACLE will automatically cause it.

3. user defined (User_define) Error
During program execution, the programmer may think that the program is abnormal. To handle this exception, you need to define it in the program and then explicitly raise it in the program.

② The Exception Handling part is generally placed in the back half of the PL/SQL program body. The structure is:
EXCEPTION
WHEN first_exception THEN <code to handle first exception>
WHEN second_exception THEN <code to handle second exception>
When others then <code to handle others exception>
END;
Exception Handling can be arranged in any order, but OTHERS must be placed at the end.

③ Pre-defined exception handling
To handle this exception, you only need to directly reference the corresponding Exception name in the Exception Handling Section of the PL/SQL block and handle the corresponding Exception error.

[Pre-defined exception]
Declare

V_sal employees. salary % type;
Begin
Select salary into v_sal
From employees
Where employee_id> 100;

Dbms_output.put_line (v_sal );

Exception
When Too_many_rows then dbms_output.put_line ('Too many output rows ');
End;

④ Non-predefined Exception Handling
To handle such exceptions, you must first define non-defined ORACLE errors. The procedure is as follows:
1. Define exceptions in the definition section of PL/SQL blocks:
<EXCEPTION> EXCEPTION;

2. Associate the defined exceptions with standard ORACLE errors and use the PRAGMA EXCEPTION_INIT statement:
PRAGMA EXCEPTION_INIT (<exception status>, <error code> );

3. handle exceptions in PL/SQL blocks.

[Non-predefined exception]
Declare

V_sal employees. salary % type;
-- Declare an exception
Delete_mgr_excep exception;
-- Associate custom exceptions with oracle errors
PRAGMA EXCEPTION_INIT (delete_mgr_excep,-2292 );
Begin
Delete from employees
Where employee_id = 100;

Select salary into v_sal
From employees
Where employee_id> 100;

Dbms_output.put_line (v_sal );

Exception
When Too_many_rows then dbms_output.put_line ('Too many output rows ');
When delete_mgr_excep then dbms_output.put_line ('manager cannot be deleted directly ');
End;

⑤ Custom exception handling
When an error related to an exception occurs, it is implicitly triggered. A user-defined exception error is triggered by explicitly using the RAISE statement. When an EXCEPTION error is thrown, the control switches to the EXCEPTION part of the EXCEPTION block and runs the error handling code.
To handle such exceptions, follow these steps:
1. Define exceptions in the definition section of PL/SQL blocks:

<EXCEPTION> EXCEPTION;
2. RAISE <exception>;

3. handle exceptions in PL/SQL blocks.

[User-defined exception]
Declare

V_sal employees. salary % type;
-- Declare an exception
Delete_mgr_excep exception;
-- Associate custom exceptions with oracle errors
PRAGMA EXCEPTION_INIT (delete_mgr_excep,-2292 );

-- Declare an exception
Too_high_sal exception;
Begin

Select salary into v_sal
From employees
Where employee_id = 100;

If v_sal> 1000 then
Raise too_high_sal;
End if;

Delete from employees
Where employee_id = 100;

Dbms_output.put_line (v_sal );

Exception
When Too_many_rows then dbms_output.put_line ('Too many output rows ');
When delete_mgr_excep then dbms_output.put_line ('manager cannot be deleted directly ');
-- Handle exceptions
When too_high_sal then dbms_output.put_line ('overpay ');
End;

⑥ Use SQLCODE and SQLERRM in PL/SQL

Error code number returned by SQLCODE
SQLERRM returns an error message.

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );

For more details, please continue to read the highlights on the next page:

1 2 Next Page