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

oracle, plsql, sql

Solution

Running the statement through dynamic PL/SQL will store the relevant 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 drawbacks to this method:

- It requires some extra, ugly code to catch the exception and re-try the SQL.

- The example only works for selects. You'll need to tweak it for insert, update, delelete, merge, dynamic PL/SQL, etc. Normally you should know what kind of SQL statement it is. If you're unlucky, you'll need to parse the statement, which can be very difficult.

- The column number is wrong if the entire PL/SQL statement is on one line.

Problem

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

Original source

Related problems