SQL & PL/SQL

I am curious as to why the process terminates when using the original function but works correctly in the new and improved version?

--ORIGINAL FUNCTION
--Spec
FUNCTION get_order_type(p_order_type IN ord_load.order_type%TYPE)
RETURN work.order_type%TYPE;
--Body
FUNCTION get_order_type(p_order_type IN ord_load.order_type%TYPE)
RETURN work.order_type%TYPE IS
/* Variables */
v_standardized_order_type work.order_type%TYPE;
BEGIN
/* Raise error if there is a incorrect order_type */
IF UPPER(p_order_type) NOT IN ('N','C','D','X') THEN
RAISE_APPLICATION_ERROR(-20001, 'Unrecognized order_type value: ' || p_order_type);
END IF;
/* Set the standardized order type. */
v_standardized_order_type := UPPER(p_order_type);
RETURN v_standardized_order_type;
END get_order_type;

**NOTE: Function get_order_type is in package1.
--When this function is called from an insert statement within package 1 it will RAISE_APPLICATION_ERROR's and use LOG_ERRORS functionality to log the unrecognized order_types to an err$ table and continue on with the next record to be processed.
--However, when package1.get_order_type is called from an insert statement within package2 it will RAISE_APPLICATION_ERROR's errors but NOT use LOG_ERRORS functionality so the process is terminated.







--NEW AND IMPROVED FUNCTION - Changes made to handle errors when calling from package2 were to move the setting of the return variable to before the RAISE_APPLICATION_ERROR call
FUNCTION get_order_type(p_order_type IN ord_load.order_type%TYPE)
RETURN work.order_type%TYPE IS
/* Variables */
v_standardized_order_type work.order_type%TYPE;
BEGIN
/* Set the standardized order type. */
v_standardized_order_type := UPPER(p_order_type);
/* Raise error if there is a incorrect order_type */
IF UPPER(p_order_type) NOT IN ('N','C','D','X') THEN
RAISE_APPLICATION_ERROR(-20001, 'Unrecognized order_type value: ' || p_order_type);
END IF;
RETURN v_standardized_order_type;
END get_order_type;

--RESULT - When this function is called from an insert statement within package 1 or package2 it will RAISE_APPLICATION_ERROR's and use LOG_ERRORS functionality to log the unrecognized order_types to an err$ table and continue on with the next record to be processed.

--Question: Why does the RETURN variable need to be set before RAISE_APPLICATION_ERROR is called in package1.get_order_type to use LOG_ERRORS functionality from package2?

Edited by: user10912651 on Jan 12, 2011 5:58 AM