Oracle: find error position in dynamic SQL using SQL or PL / SQL

Question

How to find error position in Dynamic SQL statement in PL / SQL or SQL?

From SQL * Plus, I see the error location, for example, in the wrong SQL DML statement:

SYS@orcl > SELECT 2 X 3 FROM 4 TABLEX 5 / TABLEX * ERROR at line 4: ORA-00942: table or view does not exist 

SQL * Plus shows an error with a line number and prints and marks this line with an asterisk where an error is detected.

Converting to dynamic SQL, I can get an error code (SQLCODE) and an error message (SQLERRM):

 SYS@orcl > SET SERVEROUTPUT ON SYS@orcl > BEGIN 2 EXECUTE IMMEDIATE 'SELECT X FROM TABLEX'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE('SQLCODE:' || SQLCODE); 6 DBMS_OUTPUT.PUT_LINE('SQLERRM:' || SQLERRM); 7 END; 8 / SQLCODE:-942 SQLERRM:ORA-00942: table or view does not exist 

But how do I get the error position in a Dynamic SQL string?

I see that Oracle provides an SQL Binding Area (SQLCA) containing interesting error information. In particular:

• SQLCODE and SQLERRM fields (which can be a source of data obtained using the corresponding PL / SQL functions),
• the SQLERRD field, where is the SQLERRD (5) element that gives the "analysis error offset".

Is it possible to access SQLERRD from PL / SQL or SQL? If so, how? If not, what other method can give the location of the error from PL / SQL or SQL?

(Here http://docs.oracle.com/cd/B28359_01/appdev.111/b31231/chapter8.htm#BABIGBFF SQLCA is documented and accesses Pro * C.)

(The answer here how to declare SQLCA.SQLERRD? Seems to indicate that SQLERRD is not defined in PL / SQL and therefore not available.)

(Discussion here. Why doesn’t Oracle tell you that the table or view does not exist? Gives some suggestions to show bad SQL using trace files and show the location of errors in some development tools.)

Answer 1

you have a package for extracting error messages in dbms_utility

 begin .. generate error exception when others then dbms_output.put_line( dbms_utility.format_call_stack() || chr(10) || dbms_utility.format_error_backtrace() || chr(10) || dbms_utility.format_error_stack()) end; 

Answer 2

Running an instruction through dynamic PL / SQL will save the corresponding line number in the error stack.

For example, this statement has an error on line 4:

 declare v_count number; v_bad_sql varchar2(32767) := 'SELECT X FROM TABLEX'; begin execute immediate v_bad_sql into v_count; exception when others then begin execute immediate 'begin for i in ( '||v_bad_sql||') loop null; end loop; end;'; exception when others then dbms_output.put_line(sqlerrm); end; end; / ORA-06550: line 4, column 4: PL/SQL: ORA-00942: table or view does not exist ORA-00942: table or view does not exist ORA-06550: line 1, column 18: PL/SQL: SQL Statement ignored ORA-00942: table or view does not exist 

There are some disadvantages to this method:

• This requires additional, ugly code to catch the exception and retry SQL.
• The example only works for selection. You will need to configure it to insert, update, delete, merge, dynamic PL / SQL, etc. Usually you should know what kind of SQL statement it is. If you're out of luck, you need to parse the expression, which can be very difficult.
• The column number is invalid if the entire PL / SQL statement is on the same line.