sql定位过程报错_Oracle查询sql错误信息的控制和定位

在sqlplus中执行的sql出错之后应该如何处理和对应，多行sql语句或者存储过程的信息如何进行错误定位，这篇文章将结合实例进行简单地说明。

环境准备

使用Oracle的精简版创建docker方式的demo环境，详细可参看：

如何进行错误定位

场景：

假如有3行insert的sql语句，中间一行出错之后，后续继续执行的情况下，如何定位到第二行？

dbms_utility.format_error_backtrace

通过使用dbms_utility.format_error_backtrace可以得到ERROR at line xxx:的信息，这对我们较为有用，我们接下来进行确认

oracle@e871d42341c0:~$ sqlplus system/abcd1234@XE <

> SET SERVEROUTPUT ON

> 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);

> EOF

SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 13:06:07 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> SQL> Name Null? Type

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

STUID NOT NULL NUMBER(4)

STUNAME VARCHAR2(50)

SQL>

2 rows deleted.

SQL>

no rows selected

SQL>

1 row created.

SQL> insert into student values (1001, 'liumiao')

*

ERROR at line 1:

ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated

SQL>

1 row created.

SQL>

STUID STUNAME

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

1001 liumiaocn

1003 michael

SQL>

Commit complete.