28 July 2011

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.

3 comments:

  1. The question is clear enough in my opinion - the key phrase is "...which of the statements *about this function* are true?" which confines the scope of the question to the behaviour of this function, thus excluding the behaviour of the calling process.

    ReplyDelete
  2. I think the focus of this question is put on the functionality of the functions CEIL and FLOOR instead of the values of the variables.

    It's clear that these methods will crash if you pass them no-NUMBER values.
    In my opinion this is not the task of this function but of the function executing the plch_num_diff.

    Please correct me if I am on the wrong track.
    I'm a beginner and this is my first post in here.

    ReplyDelete
  3. Hello All,
    Finally a blog thread where everybody agrees :) :)

    Anyway, even if we admit a wrong/non-numeric input for the function, what we receive in that case is NOT a RETURN value of the function,
    so it still does not change the correctness of any of the choices, because all the choices
    make statements about the function's RETURN value, so that value is supposed to exist.

    Best Regards,
    Iudith

    ReplyDelete