Passing non-numeric strings to number arguments (4507)

The 27 July quiz asked the following:

I create the following function in my schema:

CREATE OR REPLACE FUNCTION plch_num_diff (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
END;
/

Assuming that the value passed for number_in to this function is never NULL, which of the statements about this function are true?

A player wrote with the following objection:  "I think this question is ambiguous. It is not clear about the scenario where a user passes a non-number value into the function."

I don't think this is a valid objection for this quiz because if you pass a string that cannot be implicitly converted into a number for the argument to plch_num_diff, the function will not be run. Instead, Oracle will raise a VALUE_ERROR exception when it attempts the conversion (and before the function itself is executed).

This can be demonstrated as follows: I try to call plch_num_diff with an argument value of 'abc'. Then I trap any exception and display the error message and backtrace.

DECLARE
   l_value   NUMBER;
BEGIN
   l_value := plch_num_diff ('abc');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_backtrace);
END;
/

Here's the output I see:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 4

Notice that the backtrace does not show an error raised inside the function. Contrast that to the output from the following statements. I now raise an exception insde plch_num_diff and pass it a valid numeric value.

CREATE OR REPLACE FUNCTION plch_num_diff (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RAISE VALUE_ERROR;
   RETURN CEIL (number_in) - FLOOR (number_in);
END;
/

DECLARE
   l_value   NUMBER;
BEGIN
   l_value := plch_num_diff (1);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_backtrace);
END;
/

And now I see the following output, indicating that the error was raised within the function:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "HR.PLCH_NUM_DIFF", line 5
ORA-06512: at line 4

I therefore conclude that the behavior of PL/SQL in a block invoking plch_num_diff has nothing to do with the functionality of the function itself, which was the focus of the quiz.