Use errorstack to diagnose specific error messages

Quick locating of Error Data and error statements is the key to improving work efficiency. This article describes how to use Oracle event,

Quick locating of Error Data and error statements is the key to improving work efficiency. This article describes how to use Oracle event,

In actual application development, we have a lot of energy devoted to debugging programs and troubleshooting exceptions. The code developed using the design logic and scenario is often broken by the production data ". Quick locating of Error Data and error statements is the key to improving work efficiency. This article describes how to use the Oracle event, 'error'); "error stack method to locate the SQL statements and values that report errors in the package.

1. Scenario presentation

We chose Oracle 11g for the test.

SQL> select * from v $ version where rownum <3;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

The data table T contains the object_id and object_name columns. Here, object_id is the primary key column.

SQL> create table t (object_id number, object_name varchar2 (100 ));

Table created

SQL> alter table t add constraint pk_t primary key (object_id );

Table altered

Enter a part of basic data as the original value.

SQL> insert into t select object_id, object_name from dba_objects where wner = 'Scott ';

20 rows inserted

SQL> commit;

Commit complete

In order to conduct the experiment, we create a procedure program segment and insert data from dba_objects to data table T. The program is defined as follows:

SQL> create or replace procedure P_ERRORSTACK_TEST

2 is

3 type t_objs_list is table of dba_objects % rowtype index by binary_integer;

4 t_obj_infos t_objs_list;

5 begin

6 select *

7 bulk collect into t_obj_infos

8 from dba_objects;

9

10 if (t_obj_infos.count <> 0) then

11 for I in t_obj_infos.first... t_obj_infos.last loop

12 insert into t

13 values (t_obj_infos (I). object_id, t_obj_infos (I). object_name );

14 end loop;

15 end if;

16 end P_ERRORSTACK_TEST;

17/

Procedure created

Note three details: First, we insert data one by one, rather than insert into select. Second, we insert data into the for loop, with many cycles. In fact, we cannot check and judge one by one. Third, we insert all the data in dba_objects. A part of the inserted data must conflict with the primary key in the original data table.

An error is reported during actual execution.

SQL> exec P_ERRORSTACK_TEST;

Begin P_ERRORSTACK_TEST; end;

ORA-00001: violation of unique constraints (SCOTT. PK_T)

ORA-06512: In "SCOTT. P_ERRORSTACK_TEST", line 12

ORA-06512: In line 1

Those who have experience in debugging and testing are very familiar with such scenarios. During mass data testing, stored procedure jobs include thousands of identical or different SQL statements to execute massive amounts of data. An error is reported after half a day of execution. Similar errors include exceeding the limit on the length (number string), inserting null values into non-empty constraint fields, and violating foreign key constraints.

There are only a few reasons for this. One is the Bug of the program. The developer did not take into account the business scenario, and generally appeared in the null value calculation result null. The second is the design problem. The business personnel and designers did not consider the current abnormal scenarios. The last problem is that there is a problem with the source of the test data and additional cleanup is required.

No matter what the cause is, there are two things that must be done: Which SQL statement reports an error and what is the data parameter value of the error.

Of course, we can consider adding debugging code to input the execution process to achieve positioning. We are more convenient to use the error stack diagnostic event Method for locating.

Related reading:

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

For more details, please continue to read the highlights of page 2nd: