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;

score 1 · Accepted Answer · answered Feb 12 '19 at 16:11

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.

answered Feb 12 '19 at 16:11

Andrew

26,629
5
63
86

Thank you, this is the solution I needed. Bit of an odd one as I have quite a few functions that it seemed okay with within the package that didn't use the function name. – KCB226 Feb 12 '19 at 16:17

score 0 · Answer 2 · edited May 16 '20 at 23:12

0

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;

edited May 16 '20 at 23:12

Dharman

30,962
25
85
135

answered Feb 12 '19 at 16:06

Jim Macaulay

4,709
4
28
53

Sorry, I should have added that 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. – KCB226 Feb 12 '19 at 16:10

score 0 · Answer 3 · answered Feb 12 '19 at 16:08

0

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;

answered Feb 12 '19 at 16:08

Vijiy

1,187
6
21

Sorry, I should have added that the PERSON_CODE, FUND_YEAR and UIO_ID are the arguments on the function that I am creating in the package. – KCB226 Feb 12 '19 at 16:10
then remove the ambuguity, provide functionName to the columns as well – Vijiy Feb 12 '19 at 16:14

score 0 · Answer 4 · answered Feb 12 '19 at 16:14

. 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;

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

4 Answers4