18 June 2010

Use REGEXP_SUBSTR to get line number on which error was raised?(408)

In the 17 June 2010 quiz, we asked: "What information about a PL/SQL exception can be obtained inside an exception section of a PL/SQL block by calling a built-in or packaged function provided by Oracle?" One of the choices was "The line number on which the exception was raised". This choice was correct, because you can get this information with a call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. One of our players, however, offered the following as an alternative: Hi, you'll most probably regard option 1 of today's quiz "the line number on which the exception was raised" as wrong [no, we didn't!]. What I thought when clicking this option as "correct", is described by following example: 
DECLARE
  i   INTEGER;
BEGIN
  EXECUTE IMMEDIATE ('BEGIN :i := i_dont_exist; END;') USING OUT i;
EXCEPTION
  WHEN OTHERS
  THEN
     DBMS_OUTPUT.
      put_line (
        '*****error found in ' || REGEXP_SUBSTR (SQLERRM, 'line [0-9]*'));
     DBMS_OUTPUT.put_line ('HERE COMES THE ERROR MESSAGE');
     DBMS_OUTPUT.put_line (SQLERRM);
END;
What do you think? Is this a good alternative to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE?
Posted by at

2 comments:

  1. Unknown19 June, 2010 11:27

    Hi Steven,

    I feel, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is more reliable in tacing the EXACT line number
    where error has occured.

    In the above example, the REGEXP_SUBSTR displays "error found in line 1",
    where as the actual error is at line number four.Since identifier I_DONT_EXIST is not declared.
    If we use the following in the EXCEPTION section,the exact line number can be get.

    DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

    Thanks,
    Soumyakant

    ReplyDelete
  2. Steven Feuerstein23 June, 2010 23:55

    The bottom line is that the REGEXP_SUBSTR technique will ONLY work with dynamically-executed statements, so it is not a general solution.

    ReplyDelete

Subscribe to: Post Comments (Atom)