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?
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
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
}
The SQLException has additional fields that hold the SQL error number (as in ORA-12899) and a message something like what you see as value too large for column "schmnm"."tbl"."data" (actual: 5, maximum: 3)
You have to parse the string if you want to truely pinpoint the error in the SQL statement.
See: http://docs.oracle.com/javase/6/docs/api/java/sql/SQLException.html
Read about the errorCode and SQLState properties within the exception.
