Oracle: Find the position of an error in dynamic SQL using SQL or PL/SQL

Question

How can I find the position of an error in a Dynamic SQL statement in PL/SQL or SQL?

From SQL*Plus I see the position of an error in, for example, an invalid 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 the error with the line number, and prints and marks that line with an asterisk where the error is found.

Converting to Dynamic SQL, I can get the error code (SQLCODE) and 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 position of the error in the Dynamic SQL string?

I see that Oracle provides a SQL Communications Area (SQLCA) that contains interesting information about an error. In particular:

• the SQLCODE and SQLERRM fields (that might be the source of the data retrieved with the respective PL/SQL functions),
• the SQLERRD field where the SQLERRD(5) element that gives the 'parse error offset'.

Is it possible to access SQLERRD from PL/SQL or SQL? If so, how? If not, what other technique 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 the SQLCA is documented and accessed with Pro*C.)

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

(The discussion here Why doesn't Oracle tell you WHICH table or view does not exist? gives some suggestions to show bad SQL using trace files and to show the location of errors in some development tools.)

Answer 1

you got 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;