determine last_error_position

From: Dietmar Brueckmann <Dietmar.Brueckmann_at_lycos.de>
Date: Wed, 26 Jan 2005 13:31:03 +0100
Message-ID: <35pgs7F4p8j6vU1@individual.net>

Hi,

somewhere I've found the following sored procedure for getting the error position for a given sql-statement

---

CREATE OR REPLACE PROCEDURE error_pos(I_SQL_TEXT in varchar2,

   O_ERROR_POS out NUMBER,O_ERROR_TEXT out varchar2)    AUTHID CURRENT_USER
   IS
   cur1 integer;
   BEGIN

     O_ERROR_POS := -1;
     O_ERROR_TEXT := '?';
     cur1:=dbms_sql.open_cursor;
     dbms_sql.parse(cur1, I_SQL_TEXT,dbms_sql.native);
     dbms_sql.close_cursor(cur1);
     EXCEPTION
      when others then
       O_ERROR_POS := dbms_sql.LAST_ERROR_POSITION;
       O_ERROR_TEXT := SQLERRM;
       if dbms_sql.is_open(cur1) then
          dbms_sql.close_cursor(cur1);
       end if;

   END;
/

---

for "SELECT what FROM dual" I've got the correct error_position = 7. but "SELECT substr('abc','x',1) what FROM dual" results in "ORA-01722: invalid number", but error_position = -1; SQL-Plus knows the wrong position, and a tool I'm using also. What's the secret I don't know.

Is there a better solution for determining error_position? Or is then "EXCEPTION when other .." to improve?

-- 
Best regards
Dietmar Brueckmann

Received on Wed Jan 26 2005 - 06:31:03 CST