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