在sqlplus中执行的sql出错之后应该如何处理和对应,多行sql语句或者存储过程的信息如何进行错误定位,这篇文章将结合实例进行简单地说明。
环境准备
使用Oracle的精简版创建docker方式的demo环境,详细可参看:
https://www.jb51.net/article/153533.htm
如何进行错误定位
场景:
假如有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>
Com