ORA-918 raised after migrating from 10g to 11g

After migration from Oracle 10g to 11g, we found an invalid object, which was valid in 10g database:

SQL> alter procedure FXDM.SP_STAT_MAKER_P compile;

Warning: Procedure altered with compilation errors.

SQL> show errors
Errors for PROCEDURE FXDM.SP_STAT_MAKER_P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
479/4 PL/SQL: SQL Statement ignored
481/118 PL/SQL: ORA-00918: column ambiguously defined

According to Query Fails With ORA-00918 After Upgrade to 11g (835701.1), we found that in the following query, developer didn’t specify a qualifier for LAST_VRSN_F and the column exists in FX_MBR_MMKNG_ROLE_D、FX_MBR_AUTH_BY_MKT_D and INTGRT_TRDNG_MBR_D.

SELECT DISTINCT MBR.ORG_ACH_ID,TS_MKT.FX_TS_DL_MKT_D_ID,CCY.FX_CCY_PAIR_D_ID,AUTH.AUTH_ST,LAST_VRSN_F
FROM (SELECT * FROM DPA.INTGRT_ORG_D ) ORG
LEFT JOIN DPA.INTGRT_TRDNG_MBR_D MBR ON ORG.ORG_ACH_ID=MBR.ORG_ACH_ID
LEFT JOIN DPA.FX_MBR_AUTH_BY_MKT_D AUTH ON ORG.ORG_ACH_ID=AUTH.ORG_ACH_ID
LEFT JOIN DPA.FX_CCY_PAIR_D CCY ON AUTH.CCY_PAIR_D_ID=CCY.FX_CCY_PAIR_D_ID
LEFT JOIN DPA.FX_TRD_MD_D TRD_MD ON AUTH.TRD_MD_D_ID=TRD_MD.FX_TRD_MD_D_ID
LEFT JOIN DPA.FX_TS_DL_MKT_D TS_MKT ON AUTH.TS_DL_MKT_D_ID=TS_MKT.FX_TS_DL_MKT_D_ID
LEFT JOIN DPA.FX_MBR_MMKNG_ROLE_D MMKNG_ROLE ON MBR.ORG_ACH_ID=MMKNG_ROLE.ORG_ACH_ID AND CCY.FX_CCY_PAIR_D_ID=MMKNG_ROLE.CCY_PAIR_D_ID
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s