Do you know about DBMS_SQL.LAST_ERROR_POSITION

I've been prompted to this posting by a Stackoverflow question on locating the column responsible for an ORA-01722 (invalid number) error.

Tucked away in the bowels of the under-used DBMS_SQL package is a LAST_ERROR_POSITION routine.

The documentation on it is a big vague with the suggestion that you "Call this function after a PARSE call, before any other DBMS_SQL procedures or functions are called." Rubbish, I respond.

You can call it any time you like in an error handling routine and it will tell you the character offset in the SQL where the error occurred. Which is good for those errors like 'Invalid Number' and value error, not a valid month and so on. You need to count the offset from the start of the SQL. If whitespace is making it tricky, a trace would tell you the SQL as it was parsed (as might a look in v$sql).

DECLARE
    v_ret NUMBER;
    v_text varchar2(10) := 'a';
BEGIN
    insert into a_test (val1, val2) values (1,v_text);
exception
    when others then
        v_ret := DBMS_SQL.LAST_ERROR_POSITION;
        dbms_output.put_line(dbms_utility.format_error_stack);
        dbms_output.put_line(dbms_utility.format_error_backtrace);
        dbms_output.put_line('Error at offset position '||v_ret);
        raise;
END;
.
/