A script-enabled browser is required for this page to function properly.

EXEC_SQL.LAST_ERROR_POSITION built-in function

This function returns the byte offset in the SQL statement where an error occurred. The first character in the statement is at position 0.

Syntax


FUNCTION EXEC_SQL.LAST_ERROR_POSITION
[connid IN CONNTYPE]
RETURN PLS_INTEGER;

Parameter

Description

connid

Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.DEFAULT_CONNECTION retrieves the primary connection handle from the cache.

Returns

An integer.

Usage notes

Use this function after EXEC_SQL.PARSE, and before another EXEC_SQL procedure or function. The byte offset at which an error occurred cannot be determined for OCA data sources.

Example


PROCEDURE eslasterrorpos(sqlstr VARCHAR2) is
  connection_id EXEC_SQL.CONNTYPE; 
  cursorID EXEC_SQL.CURSTYPE;
  nErrPos PLS_INTEGER := 0;
 errmesg VARCHAR2(256);

BEGIN
  connection_id := EXEC_SQL.OPEN_CONNECTION('');
  cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
BEGIN
--
-- parsing statement from caller
--
  EXEC_SQL.parse(connection_id, cursorID, sqlstr, exec_sql.V7);
-- 
-- check for error in statement; find out position where statement syntax is in error
--
 EXCEPTION
  WHEN EXEC_SQL.PACKAGE_ERROR THEN
  nErrPos := EXEC_SQL.LAST_ERROR_POSITION(connection_id);
   TEXT_IO.PUT_LINE(' position in text where error occured '|| nErrPos);
  errmesg := EXEC_SQL.LAST_ERROR_MESG(connection_id);
   TEXT_IO.PUT_LINE(' error message ' || errmesg);
 RETURN;
END;
--
-- here to execute statement
--

...

nRes := EXEC_SQL.EXECUTE(connection_id, cursorID);

...

EXEC_SQL.CLOSE_CURSOR(connection_id, cursorID);
EXEC_SQL.CLOSE_CONNECTION(connection_id);
END;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package