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 4Notice 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 4I 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.
