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
PARSEcall, before any other
DBMS_SQLprocedures 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).
v_text varchar2(10) := 'a';
insert into a_test (val1, val2) values (1,v_text);
when others then
v_ret := DBMS_SQL.LAST_ERROR_POSITION;
dbms_output.put_line('Error at offset position '||v_ret);