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?
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: