How can I get position of an error in Oracle SQL query?

Question

How can I get position of an error in the query?

I need to get position in a query string which causes an error, like sqlplus does it:

SQL> insert into tbl (data) values('12345')
  2  /
insert into tbl (data) values('12345')
                 *
ERROR at line 1:
ORA-12899: value too large for column "schmnm"."tbl"."data" (actual: 5,
maximum: 3)

How can I do that?

Answer 1

After some ramblings when I almost lost hope, I found (thanks to correct search string in Google) following link: https://forums.oracle.com/thread/1000551

SQL> DECLARE
  2     c   INTEGER := DBMS_SQL.open_cursor ();
  3  BEGIN
  4     DBMS_SQL.parse (c, 'select * form dual', DBMS_SQL.native);
  5
  6     DBMS_SQL.close_cursor (c);
  7  EXCEPTION
  8     WHEN OTHERS THEN
  9        DBMS_OUTPUT.put_line ('Last Error: ' || DBMS_SQL.LAST_ERROR_POSITION ());
 10        DBMS_SQL.close_cursor (c);
 11        RAISE;
 12  END;
 13  /
Last Error: 9
DECLARE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 11

Answer 2

To expand on accepted answer I'll provide JDBC code necessary to retrieve error position from SQL query.

PL/SQL:

This PL/SQL block accepts sql query text and returns error position:

DECLARE
  c        INTEGER := DBMS_SQL.open_cursor();
  errorpos integer := -1;
BEGIN
  BEGIN
    DBMS_SQL.parse(c, :sqltext, DBMS_SQL.native);
  EXCEPTION
    WHEN OTHERS THEN
      errorpos := DBMS_SQL.LAST_ERROR_POSITION();
  END;
  :errorpos := errorpos;
  DBMS_SQL.close_cursor(c);
END;

It accepts two parameters sqltext and errorpos.

Java:

On Java side we need to call PL/SQL code from above and retrieve error position. Here's a method for that:

private int retrieveErrorPosition(Connection connection, String query) {
    CallableStatement callStatement = null;
    try {
        callStatement = connection.prepareCall(LAST_ERROR_POSITION_QUERY);
        callStatement.setString(1, query);
        callStatement.registerOutParameter(2, OracleTypes.INTEGER);
        callStatement.execute();
        return callStatement.getInt(2);
    } catch (SQLException ex) {
        log.log(Level.SEVERE, "", ex);
    } finally {
        if (callStatement != null) {
            try {
                callStatement.close();
            } catch (SQLException sqle) {
            }
        }
    }
    return -1;
}

Usage:

Now, if query string executes with exception we can extract error position:

String query;
Connection connection;
try {
    //normal query execution
} catch (SQLException ex) {
    int sqlErrorPosition = retrieveErrorPosition(connection, query);
    //exception handling
}