Error message and exception handling for Plsql

7 error messages and exception handling

7.1 Introduction of the exception

(1) The handling of exceptions is divided into three steps:
A Declaration exception
B throws an exception
C Handling Exceptions

(2) Characteristics of the anomaly
A Error Type: ora-xxxxx run-time error
PLS-XXXXX compilation Error
B Error code: XXXXX
Text description of the C error

Case 1: Case of compilation error PLS

sql> Create or Replace procedure P1 is
2 begin
3 null;
4 End;
5/

Procedure created.

sql> Create or Replace procedure P1 is
2 begin
3 null--intentionally does not write a semicolon
4 End;
5/

Warning:procedure created with compilation errors. --A warning appears

Sql> Show Error--Check for incorrect messages
Errors for PROCEDURE P1:

Line/col ERROR
-------- -----------------------------------------------------------------
4/1 pls-00103:encountered the symbol "END" when expecting one of the
Following:
;
The symbol ";" is substituted for "END" to continue.

Case 2: Run-time error-there is no error at compile time, but an error occurred while executing.

sql> Create or Replace procedure P2 is
2 v_descr varchar2 (20);
3 begin
4 Select HRC_DESCR
5 into V_DESCR
6 from Hrc_tab
7 where hrc_code=8;
8 Dbms_output.put_line (To_char (V_DESCR));
9 End;
10/

Procedure created.

sql> exec p2; --Run a stored procedure
BEGIN P2; END;

*
ERROR at line 1:
Ora-01403:no Data found
Ora-06512:at "Plsql. P2 ", line 4
Ora-06512:at Line 1

Summarize:
A Plsql Error--compilation error, before execution has been error, need to check the program, modify the program, debug
B Ora Error--run-time error, this error will require manual processing, you can use the third-party software single-Step debugging mode processing

(2) Declaration in exception handling, divided into three parts

A exception declares that this method needs to be used when declaring its own definition of an exception.
B Raise statement: Throwing an exception in the display
C pragma excetption_init this directive can associate Oracle errors with their own definition exceptions.

function (you need to define a variable to receive)

Sqlcode--Returns the code number of the error and returns 0 if there is no error, you can query the official document according to the value returned by Sqlcode for a more detailed description of the error
SQLERRM--Returns the text description of the error if no error is returned to normal or successful completion, which is the official document definition error

(3) Common cases of exception handling
Declare
V_DESCR varchar2 (20);
Begin
Select HRC_DESCR
Into V_DESCR
From Hrc_tab
where hrc_code=8;
Dbms_output.put_line (V_DESCR);
Exception when No_data_found then--the name of the exception
Dbms_output.put_line (' not exists ');
End

No_data_found--oracle The name of the predefined exception, the Oracle Official document has a description of each exception name and an introduction to the scenario that was raised
"PL/SQL User's Guide and Reference" on page No. 264 of the PDF version

The use of two functions of Sqlcode and SQLERRM

Declare
V_DESCR varchar2 (20);
V_sqlcode number;
V_SQLERRM VARCHAR2 (200);
Begin
Select HRC_DESCR
Into V_DESCR
From Hrc_tab
where hrc_code=8;
Dbms_output.put_line (V_DESCR);
Exception when No_data_found then
V_sqlcode:=sqlcode;
V_SQLERRM:=SQLERRM;
Dbms_output.put_line (' not exists ');
Dbms_output.put_line (' Err:an error with info: ' | | To_char (V_sqlcode));
Dbms_output.put_line (V_SQLERRM);
End

Output:
NOT EXISTS
Err:an error with info:100--100 is the wrong code number, the other error sqlcode is the number behind the ora-, this exception special
Ora-01403:no data found--description of the error

The exception of the program is handled so that the program does not

Declare
V_DESCR varchar2 (20);
V_sqlcode number;
V_SQLERRM VARCHAR2 (200);
Begin
Select HRC_DESCR
Into V_DESCR
ASDFASDG from Hrc_tab
where hrc_code=8;
Dbms_output.put_line (V_DESCR);
Exception when No_data_found then
V_sqlcode:=sqlcode;
V_SQLERRM:=SQLERRM;
Dbms_output.put_line (' not exists ');
Dbms_output.put_line (' Err:an error with info: ' | | To_char (V_sqlcode));
Dbms_output.put_line (V_SQLERRM);
INSERT into Hrc_tab values (8, ' ASDFASDG ');
Commit
End

Run for the first time

Output:
NOT EXISTS
Err:an Error with info:100
Ora-01403:no Data found

Run once again
Output:
Asdfasdg

(4) Functional classification of Plsql anomalies

A pre-defined exception Oracle's own predefined
B user-defined exception

User-defined exceptions

Declare
Site_s_undefined_for_org exception;
V_CNT number;
Begin
Select COUNT (*) into v_cnt from Org_site_tab where org_id=1007; --The query itself is no problem.
If V_cnt=0 then-throws an exception only when the V_CNT value is 0
Raise site_s_undefined_for_org;
End If;
Exception when site_s_undefined_for_org then
Dbms_output.put_line (' empty table! ');
When others then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

--Define the exception yourself, throw the exception yourself, handle the exception yourself

System pre-defined exceptions

Dup_val_on_index-Duplicate collisions with column values on uniqueness constraints

Declare
Site_s_undefined_for_org exception;
V_CNT number;
Begin
Select COUNT (*) into v_cnt from Org_site_tab where org_id=1007; --The query itself is no problem.
INSERT into Hrc_tab values (8, ' Asfdadsagsa ');--there is an exception, the program goes into the exception handling section, and no longer executes
Commit
If V_cnt=0 then-throws an exception only when the V_CNT value is 0
Raise site_s_undefined_for_org;
End If;
Exception when site_s_undefined_for_org then
Dbms_output.put_line (' empty table! ');
When Dup_val_on_index Then
Dbms_output.put_line (' Value repeat! ');
When others then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

Output: Value repeat!

(3) pragma exception_init directive
This directive is about associating Oracle's errors with user-defined exceptions.

[[email protected] ~]$ oerr ora 02290--Know the error number, you can use this command to view detailed errors
02290, 00000, "Check constraint (%s.%s) violated"
*cause:the values being inserted do not satisfy the named check
Constraint.
*action:do not inserts values that violate the constraint.


SELECT * from user_constraints where table_name= ' org_level ';

Sql> Conn Plsql/plsql
Connected.
sql> INSERT into org_level values (1001, ' P ');
INSERT into org_level values (1001, ' P ')
*
ERROR at line 1:
Ora-02290:check constraint (PLSQL.ORG_LEVEL_CK) violated


Declare
Invalid_org_level exception;
pragma exception_init (invalid_org_level,-2290); --After association, you don't need raise to throw an exception
Begin
CREATE TABLE Exception_monitor (
Excep_tab_name VARCHAR2 (30),
Excep_key VARCHAR2 (50),
Excep_program VARCHAR2 (30),
Excep_name VARCHAR2 (30),
Excep_code number,
Excep_txt VARCHAR2 (200),
Excep_date Date
);
INSERT into org_level values (1001, ' P ');
Commit
Exception when Invalid_org_level then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
When others then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

Can let the program throw itself

Begin
INSERT into org_level values (1001, ' P ');
Commit
exception
When others then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

##########################################################################################

7.2 Exception Monitoring Table

(1) Create an Exception Monitor table: Exception_monitor

Field
Name of the table where the exception occurred: Excep_tab_name
Primary key of the row where the exception occurred: Excep_key
The name of the program that has the exception: Excep_program, NULL if it is an anonymous block
Name of exception: Excep_name write ' others ' if not defined
Sqlcode:excep_code of Abnormal
Text description of the exception: Excep_txt
time when the exception occurred: Excep_date

After writing the program, you will write the exception Processing section, get to the above information, insert this table

To create an exception monitoring table:
CREATE TABLE Exception_monitor (
Excep_tab_name VARCHAR2 (30),
Excep_key VARCHAR2 (50),
Excep_program VARCHAR2 (30),
Excep_name VARCHAR2 (30),
Excep_code number,
Excep_txt VARCHAR2 (200),
Excep_date Date
);

Rewrite The example above:
Declare
Invalid_org_level exception;
pragma exception_init (invalid_org_level,-2290);
V_sqlcode number;
V_SQLERRM VARCHAR2 (200);
Begin
INSERT into org_level values (1001, ' P ');
Commit
Exception when Invalid_org_level then
V_sqlcode:=sqlcode;
V_SQLERRM:=SQLERRM;
INSERT into exception_monitor values (' Org_level ', ' 1001 ', Null,upper (' Invalid_org_level '), V_SQLCODE,V_SQLERRM, Sysdate);
Commit
When others then
V_sqlcode:=sqlcode;
V_SQLERRM:=SQLERRM;
INSERT into exception_monitor values (' Org_level ', ' 1001 ', Null,upper (' others '), v_sqlcode,v_sqlerrm,sysdate);
Commit
End

Exercise 7: Modify the Exception handling section of the Practice 6 program to capture the error to the monitoring table

The 20000~21299 of the error number is the open range of the error number, which is used to raise the error using the built-in function to customize the error.

Declare
Site_s_undefined_fo_org exception;
pragma exception_init (site_s_undefined_fo_org,-20001);
V_CNT number;
Begin
Select COUNT (1) into v_cnt from Org_site_tab where org_id=1007;
If V_cnt=0 Then
Raise_application_error ( -20001, ' This table rows are empty! ');
End If;
Exception when site_s_undefined_fo_org then
Dbms_output.put_line (SQLERRM);
When others then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

The name of the non-associative exception can also:

Declare
V_CNT number;
Begin
Select COUNT (1) into v_cnt from Org_site_tab where org_id=1007;
If V_cnt=0 Then
Raise_application_error ( -20001, ' This table rows are empty! ');
End If;
exception
When others then
Dbms_output.put_line (' Err:an error with info: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

##########################################################################################

7.3 Handling of exceptions thrown in the Declaration section

Note: Exceptions need to be captured between Begin and exception.

Declare
V_CNT Number (2): = 100;
Begin
Null
Exception when others then
Dbms_output.put_line (' ERR CODE: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

Rewrite:

Begin
Declare
V_CNT Number (2): = 100;
Begin
Null
Exception when others then
Dbms_output.put_line (' ERR CODE: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End
Exception when others then
Dbms_output.put_line (' ERR CODE: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

Workaround: Nest the original block of code between a begin and exception to capture

Attention:
A program is executed from begin, declare part is not part of program execution
B the interval of the exception capture is the code between begin and exception

7.5 Handling of exceptions thrown in the exception section

Declare
Condition Boolean:=true;
EXCEP1 exception;
EXCEP2 exception;
Begin
If condition Then
Raise Excep1;
End If;
Exception when Excep1 then
Raise EXCEP2;
End

Rewrite:

Declare
Condition Boolean:=true;
EXCEP1 exception;
EXCEP2 exception;
Begin
If condition Then
Raise Excep1;
End If;
Exception when Excep1 then
Begin
Raise EXCEP2;
Exception when EXCEP2 then
Dbms_output.put_line (' ERR CODE: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End
End

Or

Declare
Condition Boolean:=true;
EXCEP1 exception;
EXCEP2 exception;
Begin
Begin
If condition Then
Raise Excep1;
End If;
Exception when Excep1 then
Raise EXCEP2;
End
Exception when EXCEP2 then
Dbms_output.put_line (' ERR CODE: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

7.6 An exception can be thrown multiple times

Declare
Condition Boolean:=true;
EXCEP1 exception;
Begin
Begin
If condition Then
Raise Excep1;
End If;
Exception when Excep1 then
Raise Excep1;
End
Exception when Excep1 then
Dbms_output.put_line (' ERR CODE: ' | | To_char (Sqlcode));
Dbms_output.put_line (SQLERRM);
End

Error message and exception handling for Plsql