How to fix PL/SQL: ORA-00918: column ambiguously defined in Oracle

Question

I'm creating a package in Oracle, and when I've compiled the body of the package, i am getting the PL/SQL: ORA-00918: column ambiguously defined error.

I've gone through the code, and double checked the aliases, so am a bit stumped as to why I am receiving this error.

The error in question is on Line 10. The PERSON_CODE, FUND_YEAR and UIO_ID in the WHERE clause are the arguments on the function that I am creating in the package.

    SELECT CASE 
            WHEN LH.PROP_NOT_TAUGHT > 50 AND LA.DELIVERY_PROVIDER IS NOT NULL THEN TO_NUMBER(OU.UKPRN)
            ELSE LA.UK_PROV_NO 
           END AS UKPRN_T
    FROM FES.LEARNER_AIMS LA
     JOIN FES.LEARNER_HE LH
      ON LH.PERSON_CODE = LA.PERSON_CODE
       AND LH.FUNDING_YEAR = LA.FUNDING_YEAR
     LEFT JOIN FES.ORGANISATION_UNITS OU
      ON OU.ORGANISATION_CODE = LA.DELIVERY_PROVIDER
    WHERE LA.PERSON_CODE = PERSON_CODE
     AND LA.FUNDING_YEAR = FUND_YEAR
     AND LA.UIO_ID = UIO_ID;

Answer 1

Your function parameter name and the name of the field are clashing, creating a shadowing effect. You can prefix the name of the parameter with the function name to remove the ambiguity

 AND LA.UIO_ID = MyfunctionName.UIO_ID;

Alternatively, rename the parameter to avoid such occurrences.

Answer 2

Its always good practice to use table alais with columns Names.

SELECT CASE 
        WHEN LH.PROP_NOT_TAUGHT > 50 AND LA.DELIVERY_PROVIDER IS NOT NULL THEN TO_NUMBER(OU.UKPRN)
        ELSE LA.UK_PROV_NO 
       END AS UKPRN_T
FROM FES.LEARNER_AIMS LA
 JOIN FES.LEARNER_HE LH
  ON LH.PERSON_CODE = LA.PERSON_CODE
   AND LH.FUNDING_YEAR = LA.FUNDING_YEAR
 LEFT JOIN FES.ORGANISATION_UNITS OU
  ON OU.ORGANISATION_CODE = LA.DELIVERY_PROVIDER
WHERE LA.PERSON_CODE = <tableAlaisForPersonCode>PERSON_CODE
 AND LA.FUNDING_YEAR = <tableAlaisForFUND_YEAR>FUND_YEAR
 AND LA.UIO_ID = <tableAlaisForUIO_ID>UIO_ID;

Answer 3

. The PERSON_CODE, FUND_YEAR and UIO_ID are the arguments on the function.

It is bad practice to use PL/SQL parameter names which are the same as column names. The compiler applies the nearest namespace check, which means in this case it tries to map PERSON_CODE to a table column. Aliasing is optional so it doesn't realise that you're trying to reference PL/SQL parameters.

Because you have more than one table with a column called PERSON_CODE you get the ORA-00918 error. Otherwise you would just have a query which returned all rows.

The better practice is to name parameters differently; the convention is to prefix them with p_:

WHERE LA.PERSON_CODE = P_PERSON_CODE
 AND LA.FUNDING_YEAR = P_FUND_YEAR
 AND LA.UIO_ID = P_UIO_ID;

Answer 4

Alias is missing for the column UIO_ID, just provide OU.UIO_ID

SELECT CASE 
        WHEN LH.PROP_NOT_TAUGHT > 50 AND LA.DELIVERY_PROVIDER IS NOT NULL THEN TO_NUMBER(OU.UKPRN)
        ELSE LA.UK_PROV_NO 
       END AS UKPRN_T
FROM FES.LEARNER_AIMS LA
 JOIN FES.LEARNER_HE LH
  ON LH.PERSON_CODE = LA.PERSON_CODE
   AND LH.FUNDING_YEAR = LA.FUNDING_YEAR
 LEFT JOIN FES.ORGANISATION_UNITS OU
  ON OU.ORGANISATION_CODE = LA.DELIVERY_PROVIDER
WHERE LA.PERSON_CODE = PERSON_CODE
 AND LA.FUNDING_YEAR = FUND_YEAR
 AND LA.UIO_ID = OU.UIO_ID;