Forum Stats

  • 3,781,305 Users
  • 2,254,499 Discussions
  • 7,879,637 Comments

Discussions

Different results from within function to running query outside of function

f3b9ff54-9454-4630-9911-432e2abaa731
edited Jul 9, 2019 9:07PM in SQL & PL/SQL

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_OFFERSFROM IPORTAL.IPV_UC_APPLICATIONSWHERE 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.

Tagged:
Jonathan LewisFrank Kulash

Answers

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,931 Gold Trophy
    edited Jul 9, 2019 1:15PM

    Don't use the same "entry_year" twice in your function for different things. In your function the column and the parameter have the same name

    So use a table alias, use scoping for your parameter ( use GET_HE_OFFERS.entry_year) or just rename the year parameter

    Jonathan LewisFrank Kulash
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,696 Silver Crown
    edited Jul 9, 2019 9:07PM

    The problem is exactly as ascheffer mentioned:

    Column Name Precedence

    If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

    Caution:

    When a variable or parameter name is interpreted as a column name, data can be deleted, changed, or inserted unintentionally.

    Source:

    https://docs.oracle.com/database/121/LNPLS/nameresolution.htm