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?

A reminder regarding cheating and the PL/SQL Challenge

We are coming up on the end of the first quarter of playing the PL/SQL Challenge. In early July, we will hold the first championship playoff, with a first prize of 1000 USD. We feel, therefore, that it is a good time to remind all players about the rules regarding cheating.

To read all the PL/SQL Challenge rules, click here.

The PL/SQL Challenge offers cash prizes and will become a means by which programmers can establish or improve their reputations as PL/SQL developers. It is serious stuff, in other words. And that means that it is possible that  someone will try to cheat or "game the system" to improve their chances of winning a prize and elevated status. We have given lots of thought to the ways that people could cheat. We have taken several measures to minimize the chance of successful cheating. And we have decided on the following consequences.

If you violate any of the rules of the PL/SQL Challenge or cheat in the process of submitting your answer, then you will lose all your points for the current month (any previously submitted and any others submitted for the rest of the month). If this occurs a second time, your account will be closed and all accumulated points lost.

Here are what we consider to be examples of cheating (this list is by no means exhaustive; if you find another way to cheat and it is not on this list, you will still be penalized when you are discovered):

• Creating multiple accounts: you may only create one account for yourself in the Challenge.
• You take the quiz in one account, taking your time to look up the answer, study the code, etc. Once you are sure of the answer, you log into your "real" account and submit your answer quickly, to game the timing algorithm.
• You receive the quiz from someone else, study and determine the correct answer, and submit your answer quickly, to game the timing algorithm.
• You initiate or participate in any sort of discussion forum/thread/SMS on the quiz during the day in which the quiz is active. Feel free to discuss the question and answers after once that quiz is "history." While people can still answer it, however, please refrain from publishing information or discussion about it.
• You use any kind of a "bot" to submit your answer or answers.
• You start the quiz, study and research until you are ready to answer the question, then you refresh the webpage in hopes of restarting the clock. It won't help you, but it's still cheating.
  

14 June Quiz: Why does VALUE_ERROR exception go unhandled?(366)

In this quiz, we present a block of code that includes this line:

value_error EXCEPTION;

In other words, we declare an exception with the same name as an exception defined in the STANDARD package of Oracle (and thus can be referenced without a package qualifier name, as in STANDARD.VALUE_ERROR). By doing so, if Oracle raises its VALUE_ERROR exception, a WHEN clause like

WHEN VALUE_ERROR

will not trap the exception. Tricky, yes....and at least one player had some comments on it, so take a look...

Use UTL_FILE.IS_OPEN to see if file exists?

We received this note from Miguel:

"In the first place i want to congratulate you on this wonderful game, but the real reason that i'm writing is because i don't agree with the answer to the 11 June quiz in which you asked: Which of the following choices correctly names a schema-level program or packaged subprogram provided by Oracle that you can use to determine if a file exists?

"In my opinion you can use UTL_FILE.IS_OPEN to determine if the file exists or not (i know that you can not use it by itself but in fact in one situation that you have already tried to open a file you can use it to check the existence).

If you use it in conjunction with utl_file.fopen you can find out if the file exists or not...

DECLARE
  vInHandle  utl_file.file_type;
BEGIN
  vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
 
  IF utl_file.is_open(vInHandle) THEN
    dbms_output.put_line('The File exists');
  Else
    dbms_output.put_line('The File dosen't exists');
  END IF;
END fopen;

Miguel, we salute your creativity! All too often, developers stick to the documented and "intended" purpose of a program and don't explore other ways that it can be applied to solve a problem.

So, by all means, please keep exploring and pushing boundaries.

In this case, however, we must disagree. The IS_OPEN subprogram of UTL_FILE will only tell you if a file is open or not (and even then, so far as we know, this function only checks the value of a field in the UTL_FILE.FILE_TYPE record; it doesn't actually check file status). If the file doesn't exist, it will not be opened and so you never even get to call IS_OPEN.