Shulou(Shulou.com)06/01 Report--

7 error messages and exception handling

Introduction of 7.1 exception

(1) handling exceptions is divided into three steps:

A declared exception

B throws an exception

C handles exceptions

(2) the characteristics of anomalies

An error type: ORA-xxxxx runtime error

PLS-xxxxx compilation error

B error code: xxxxx

Text description of C error

Case 1: compilation error case 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-deliberately do not write semicolon

4 end

5 /

Warning: Procedure created with compilation errors. -- warning appears

SQL > show error-- View error messages

Errors for PROCEDURE P1:

LINE/COL ERROR

--

4 PLS-00103 1: Encountered the symbol "END" when expecting one of the

Following:

The symbol ";" was substituted for "END" to continue.

Case 2: runtime errors-there are no errors at compile time, but errors occur at execution time.

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

Summary:

A PLSQL error-A compilation error, which has been reported before execution, needs to check the program, modify the program, debug

B ORA error-Runtime error, which needs to be handled manually when it occurs, and can be handled by the single-step debugging method of third-party software

(2) the declaration in exception handling is divided into three parts.

An exception declares that you need to use this method when declaring that you define an exception.

B raise statement: throw an exception explicitly

C pragma excetption_init this instruction can associate oracle errors with self-defined exceptions

Function (variables need to be defined to receive)

Sqlcode-returns the code number of the error, and 0 if there is no error. You can query the official document based on the value returned by sqlcode to get a more detailed description of the error.

Sqlerrm-returns the text description of the error. If there is no error, it returns normal or successful completion, which is the error defined by the official document.

(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-name of the exception

Dbms_output.put_line ('not exists')

End

No_data_found-the name of the oracle predefined exception. There is a description of each exception name and an introduction to the scenario thrown in the oracle official document.

Page 264 in the "PL/SQL User's Guide and Reference" pdf version

The use of sqlcode and sqlerrm functions

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, other error sqlcode is the number after ora-, this is very special

ORA-01403: no data found-- description of the error

Handle the exception of the program so that the exception will not occur again

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 million fascicles)

Commit

End

First run

Output:

Not exists

ERR: an error with info: 100

ORA-01403: no data found

Run again

Output:

Asdfasdg

(4) functional classification of PLSQL anomalies.

A predefined exception oracle itself predefined

B user-defined exception

User-defined exception

Declare

Site_s_undefined_for_org exception

V_cnt number

Begin

Select count (*) into v_cnt from org_site_tab where org_id=1007;-there is no problem with the query itself

If v_cnt=0 then-- an exception is thrown only when the vcnt value is 0

Raise site_s_undefined_for_org

End if

Exception when site_s_undefined_for_org then

Dbms_output.put_line ('empty tableting')

When others then

Dbms_output.put_line ('ERR: an error with info:' | | to_char (sqlcode))

Dbms_output.put_line (sqlerrm)

End

Define your own exceptions, throw them, and handle them yourself.

System predefined exception

DUP_VAL_ON_INDEX-repeated conflicts of 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;-there is no problem with the query itself

Insert into hrc_tab values;-- if an exception occurs here, the program will enter the exception handling section and will not be executed again.

Commit

If v_cnt=0 then-- an exception is thrown only when the vcnt value is 0

Raise site_s_undefined_for_org

End if

Exception when site_s_undefined_for_org then

Dbms_output.put_line ('empty tableting')

When DUP_VAL_ON_INDEX then

Dbms_output.put_line ('value repeats')

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 instruction

This directive is to associate oracle errors with user-defined exceptions.

[oracle@test ~] $oerr ora 02290-if you 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 insert 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)

Insert into org_level values (1001)

*

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, there is no need for 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)

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

You can let the program throw itself.

Begin

Insert into org_level values (1001)

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 monitoring table: exception_monitor

Field

Name of the table where the exception occurred: excep_tab_name

Primary key of the line on which the exception occurred: excep_key

The name of the program that caused the exception: excep_program. If it is an anonymous block, set it to null.

The name of the exception: write 'others'' if excep_name is not defined.

Abnormal sqlcode: excep_code

Text description of the exception: excep_txt

Time when the exception occurred: excep_date

Later, when you write a program, you have to write the exception handling part, get the above information, and insert the table.

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 above example:

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)

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'), vaccounsqlcodewritsqlerrmbooksysdate)

Commit

When others then

V_sqlcode:=sqlcode

V_sqlerrm:=sqlerrm

Insert into exception_monitor values ('ORG_LEVEL','1001',null,upper (' others'), vaccounsqlcodewritsqlerrmbooksysdate)

Commit

End

Exercise 7: modify the exception handling part of the exercise 6 program to catch errors to the monitoring table

The 20000-21299 of the error number is the vacant range of the error number, which is used to customize the error and raise it with built-in functions.

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 (- 2000) this table rows is emptiness

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 exception that is not associated with it can also be:

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 (- 2000) this table rows is emptiness

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 caught 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

Solution: nesting the original code block between begin and exception can capture the

Note:

A program is executed from begin, and the declare part is not the execution part of the program.

The interval of B exception catch is the code between begin and exception

7.5 handling of throwing an exception in the exception part

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.