29 September 2010

Missing RETURN in 28 September quiz - but no impact on correctness! (1442)

Several players noticed that the function I included in the 28 September quiz was missing a RETURN statement:
CREATE OR REPLACE FUNCTION partnum_from_name (
  partname_in IN plch_parts.partname%TYPE)
  RETURN plch_parts.partnum%TYPE
IS
  l_return   plch_parts.partnum%TYPE;
BEGIN
  SELECT p.partnum
    INTO l_return
    FROM plch_parts p
   WHERE p.partname = partnum_from_name.partname_in;
END partnum_from_name;
Yes, missing a RETURN statement, no doubt about it. This bothered several players, and with good reason. If and when this function was executed it would (if one row was found for this part name) raise the following error:
ORA-06503: PL/SQL: Function returned without value
So what's the problem? Here's what one player wrote: "The function defined in the quiz on 28-SEP-2010 is invalid because it does not have a RETURN clause." Well, actually, that is not so. The function is valid, compiles without error. So in the context of this quiz, the lack of a RETURN statement had no impact whatsoever on the correctness of the choices. The function was compiled, and since it contained a select from the plch_parts table, the dependency view would reveal that information - no matter the run-time problems that might be encountered when the function was executed. Still, it was not my intention to leave out the RETURN statement, so I will add it into the question text to avoid this distraction in the future. In addition, and somewhat to my surprise, several players thought that I had made a mistake when writing a SELECT statement as follows:
SELECT COUNT (*)
 FROM all_dependencies
WHERE     referenced_name = 'PLCH_PARTS'
      AND referenced_owner = USER
      AND referenced_type = 'TABLE' 
Specially, they felt that the word "USER" should have been within single quoates, as in:
SELECT COUNT (*)
 FROM all_dependencies
WHERE     referenced_name = 'PLCH_PARTS'
      AND referenced_owner = 'USER'
      AND referenced_type = 'TABLE' 
But that would only work if the name of the schema that owned PLCH_PARTS was "USER". USER, without single quotes, is a built-in function that returns the name of the currently connected schema. So all in all, nothing to change in the quiz scoring. Thanks for writing with your comments! Cheers, SF

8 comments:

  1. I have a question on your answers of choice "SELECT COUNT (*) FROM user_dependencies WHERE referenced_name = 'PLCH_PARTS' AND referenced_type = 'TABLE'" and you said "This query retrieves dependency information from the user_dependencies view, so it does not need to include a WHERE clause predicated for the owner of the object. This query will display "2"." I think that althrough you are selecting from user_dependencies view, the WHERE on referenced_owner is still needed if you referenced objects in other schema. This choice is correct just because you have only one reference to 'PLCH_PARTS' in one schema.

    ReplyDelete
  2. That's actually what shifted my mind :(. I haven't chosen a single answer because I thought it would not compile and only after I pressed 'Submit' button, I realized that it would be runtime error 'The function returned without value' but not a compilation error.
    Thigh...

    ReplyDelete
  3. In my opinion the missing predicate in the WHERE-clause for the referenced_owner isn't neccessary, because in a default installation of Oracle there is on table named "PLCH_PARTS".

    ReplyDelete
  4. siamnobita, please read (and understand) the assumptions.

    The assumption for the quiz is that the only DB objects that exist are those described in the quiz itself (or defined by Oracle with the installation of the database).

    ReplyDelete
  5. The thing in USER in the WHERE statement... Shouldn't we use USER_DEPENDENCIES instead of ALL_DEPENDENCIES+WHERE?

    ReplyDelete
  6. Filipe Silva, I have read the assumption and have no question on the scoring of that choice but I have question only on the word describe why the choice is correct.

    "This query retrieves dependency information from the user_dependencies view, so it does not need to include a WHERE clause predicated for the owner of the object."

    My point is column 'REFERENCED_OWNER' should not be eliminated just because you select from user's views. And IMHO, his word can imply that this column is not necessary if you select from this view.

    ReplyDelete
  7. This is a reaction on the blog. Like the new format. It is less compact then the previous one, on a big screen (1366 x 768 gives a lot of whitespace), but it is much nicer and look more like the pl/sql challange as it should be.

    ReplyDelete
  8. Siamnobita,

    Yes, I see your point. The answer "works" but only because of the assumptions, which means that there are no other objects. I will adjust that question text to address this.

    SF

    ReplyDelete