Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve Oracle query sql error information control and positioning, I believe that most people do not know much, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Environmental preparation

Create a docker-style demo environment using a stripped-down version of Oracle

How to locate the error

Scene:

If you have 3 lines of insert's sql statement, how do you navigate to the second line after an error on the middle line and subsequent execution?

Dbms_utility.format_error_backtrace

We can get the information of ERROR at line xxx: by using dbms_utility.format_error_backtrace, which is useful to us. Let's confirm it next.

Oracle@e871d42341c0:~$ sqlplus system/abcd1234@XE desc student > delete from student; > select * from student; > insert into student values (1001, 'liumiaocn'); > insert into student values (1001,' liumiao'); > insert into student values (1003, 'michael'); > select * from student; > commit; > exec dbms_output.put_line (dbms_utility.format_error_backtrace); > EOFSQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 13:06:07 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > SQL > Name Null? Type-STUID NOT NULL NUMBER (4) STUNAME VARCHAR2 (50) SQL > 2 rows deleted.SQL > no rows selectedSQL > 1 row created.SQL > insert into student values (1001 'liumiao') * ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violatedSQL > 1 row created.SQL > STUID STUNAME- 1001 liumiaocn 1003 michaelSQL > Commit complete.SQL > PL/SQL procedure successfully completed.SQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

As you can see, the line number is prompted when you report an error, but the line number is 1, because this is written in a single line, which is naturally the case, and it will be clearer if it is a single, multi-line stored procedure.

ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated

So we modify this example, put the sql of three lines of insert into a file, and then use dbms_utility.format_error_backtrace to confirm

Oracle@e871d42341c0:~$ cat / tmp/sqltest1.sql desc studentdelete from student;select * from student;insert into student values (1001, 'liumiaocn'); insert into student values (1001,' liumiao'); insert into student values (1003, 'michael'); select * from student;commit;oracle@e871d42341c0:~$

Then try to see if you can confirm the line number, and you'll find that you still can't pinpoint it:

Oracle@e871d42341c0:~$ sqlplus system/abcd1234@XE @ / tmp/sqltest1.sql > exec dbms_output.put_line (dbms_utility.format_error_backtrace); > EOFSQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 13:08:27 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > SQL > Name Null? Type-STUID NOT NULL NUMBER (4) STUNAME VARCHAR2 (50) 2 rows deleted.no rows selected1 row created.insert into student values (1001) 'liumiao') * ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated1 row created. STUID STUNAME- 1001 liumiaocn 1003 michaelCommit complete.SQL > PL/SQL procedure successfully completed.SQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

Since most of the scenarios of dbms_utility.format_error_backtrace are about the error location of stored procedures, let's use a simple stored procedure example to locate the line number of confirmation errors. First, take a look at a normal stored procedure and modify the above slightly:

Oracle@e871d42341c0:~$ cat / tmp/addstudent.sql create or replace PROCEDURE addstudentsISstudent_count number;BEGINdelete from student;select count (*) into student_count from student;dbms_output.put ('sql set count before:'); dbms_output.put_line (student_count); insert into student values (1001, 'liumiaocn'); insert into student values (1002,' liumiao'); insert into student values (1003, 'michael'); select count (*) into student_count from student;dbms_output.put (' sql set count after:') Dbms_output.put_line (student_count); END;/exec addstudents (); oracle@e871d42341c0:~$

The result execution information is as follows

Oracle@e871d42341c0:~$ sqlplus system/liumiao123 Procedure created.sql set count before: 0sql set count after: 3PL/SQL procedure successfully completed.SQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

Next, let's modify the content so that the second line of the primary key is repeated.

Oracle@e871d42341c0:~$ cat / tmp/addstudent.sqlcreate or replace PROCEDURE addstudentsISstudent_count number;BEGINdelete from student;select count (*) into student_count from student;dbms_output.put ('sql set count before:'); dbms_output.put_line (student_count); insert into student values (1001, 'liumiaocn'); insert into student values (1001,' liumiao'); insert into student values (1003, 'michael'); select count (*) into student_count from student;dbms_output.put (' sql set count after:') Dbms_output.put_line (student_count); END;/exec addstudents (); oracle@e871d42341c0:~$

If you execute it again, you will naturally make an error, but you can see that the number of rows is correctly reported, which is the message prompted by the mechanism of procedure.

Oracle@e871d42341c0:~$ sqlplus system/liumiao123 Procedure created.sql set count before: 0BEGIN addstudents (); END;*ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violatedORA-06512: at "SYSTEM.ADDSTUDENTS", line 10ORA-06512: at line 1SQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

As you can see, the message of ORA-06512: at "SYSTEM.ADDSTUDENTS", line 10 is what we expect, indicating that there is a problem with the execution on line 10 of this stored procedure, but we can actually use dbms_utility.format_error_backtrace in combination with exception to give a clearer way, such as:

Oracle@e871d42341c0:~$ cat / tmp/addstudent.sql create or replace PROCEDURE addstudentsISstudent_count number;BEGINdelete from student;select count (*) into student_count from student;dbms_output.put ('sql set count before:'); dbms_output.put_line (student_count); insert into student values (1001, 'liumiaocn'); insert into student values (1001,' liumiao'); insert into student values (1003, 'michael'); select count (*) into student_count from student;dbms_output.put (' sql set count after:') Dbms_output.put_line (student_count); exceptionwhen others thendbms_output.put ('exception happend with line info:'); dbms_output.put_line (dbms_utility.format_error_backtrace); END;/exec addstudents (); oracle@e871d42341c0:~$

Confirmation of the execution result:

Oracle@e871d42341c0:~$ sqlplus system/liumiao123 Procedure created.sql set count before: 0exception happend with line info: ORA-06512: at "SYSTEM.ADDSTUDENTS", line 10PL/SQL procedure successfully completed.SQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

In this way, we can see that the wrong location can be carried out more clearly, but due to functional limitations, the actual use scenario is still limited, but the information of positioning stored procedures can be confirmed by dbms_utility.format_error_backtrace and so on.

The above is all the contents of this article "how to control and locate sql error messages in Oracle query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.