Oracle version 12c 12.1.0.2.0
I have created a package with a function that tells me how many application we have had. It's a simple count returning a number using a couple of parameters in the select.
However when I use the function, I seem to get the same value which is the wrong value, even when changing one of the parameters, which is the year. But if I then run the select statement outside of the function I get the correct value, and the value does indeed change when I alter the year parameter.
Am I missing something?
Function within the package.
FUNCTION GET_HE_OFFERS ( uiocode IN VARCHAR2, entry_year IN NUMBER ) RETURN NUMBER IS
m_ret_val NUMBER := NULL;
BEGIN
SELECT COUNT(DISTINCT PERSON_CODE ) AS NO_OF_OFFERS INTO m_ret_val
FROM IPORTAL.IPV_UC_APPLICATIONS
WHERE EBS_COURSE_CODE = uiocode AND
ENTRY_YEAR = entry_year AND
DECISION IN ( 'C', 'U' );
RETURN m_ret_val;
END;
Using the function in a select returns 52, which is the same regardless of the year parameter
SELECT BROK03.APPLICATIONS_PKG.GET_HE_OFFERS ('SS2599A11', 2016) AS NO_OF_OFFERS FROM dual;
Running the select statement within the function returns the correct numbers and changes when the year is altered.
SELECT COUNT(DISTINCT PERSON_CODE ) AS NO_OF_OFFERS
FROM IPORTAL.IPV_UC_APPLICATIONS
WHERE EBS_COURSE_CODE = 'SS2599A11' AND
ENTRY_YEAR = 2016 AND
DECISION IN ( 'C', 'U' );
I have a feeling it is something related to the year parameter, but am not quite sure what would cause these results.