ORA-00918: column ambigously defined, using DB Link

Question

When I execute the query below I get the following error message :

ORA-00918: column ambigously defined

ORA-02063: preceding line from ABC

Query:

SELECT
    dos.*,
    cmd.*,
    cmd_r.*,
    adr_inc.*,
    adr_veh.*,
    loc.*,
    fou_d.*,
    fou_r.*,   --Works if I comment this line
    mot.*

FROM
    DOSSIERS@ABC               dos
    LEFT JOIN CMDS@ABC         cmd     ON cmd.DOS_CODE_ID = dos.dos_code_id
    LEFT JOIN CMDS_RECCSTR@ABC cmd_r   ON cmd_r.DOS_CODE_ID = dos.DOS_CODE_ID AND cmd_r.CMD_CODE_ID = cmd.CMD_CODE_ID AND cmd_r.CMD_DT_CREAT = cmd.CMD_DT_CREAT
    LEFT JOIN HISTO_ADR@ABC    adr_inc ON adr_inc.DOS_CODE_ID = dos.DOS_CODE_ID
    LEFT JOIN HISTO_ADR@ABC    adr_veh ON adr_veh.DOS_CODE_ID = dos.DOS_CODE_ID
    LEFT JOIN LOC@ABC          loc     ON dos.DOS_CODE_ID = loc.DOS_CODE_ID
    LEFT JOIN FOURNISS@ABC     fou_d   ON fou_d.PAY_CODE_ID = loc.PAY_CODE_ID_D AND fou_d.FOU_CODE_ID = loc.FOU_CODE_ID_D
    LEFT JOIN FOURNISS@ABC     fou_r   ON fou_r.PAY_CODE_ID = loc.PAY_CODE_ID_R AND fou_r.FOU_CODE_ID = loc.FOU_CODE_ID_R
    LEFT JOIN REF_MOT@ABC      mot     ON mot.RMR_CODE_ID = cmd_r.RMR_CODE_ID

WHERE
    dos.REF_EXT = 'XXXXXXX'

If I comment fou_r.* in SELECT it works.

The following queries don't work neither:

SELECT *
FROM ... ;

SELECT (SELECT count(xxx) FROM ...)
FROM ...;

I looked at similar issues on SO but they were all using complex queries or was using many SELECT inside WHERE. Mine is simple that is why I don't understand what could be wrong.

Current Database: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Target Database (refers to db link ABC target): Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

Client: Toad for Oracle 9.7.2.5

whoever downvoted the question, can you please give the reasons as I am not sure what's wrong with the question — Shankar
I see no reason for downvoting this question either. And I don't see any reason for the error. Getting data with a qualifier should always work (as long as you don't use USING, thus removing a column from the qualifyable columns). May be a bug. — Thorsten Kettner
I'm curious if the error only happens when using the db link. Are you in a position to run the same query directly on the database instance referred to by the database link ABC? — sstan
Is fou_r a view in the remote database? Does that view work? — Robert Dupuy
OK, then if your source DB is 11.2.0.2 it looks like bug 13589271; does FOURNISS have a column with a 30-character name? Can you add the definition of FOURNISS and HISTO_ADR to see if that suggests why one works and the other doesn't? — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2015-09-25T15:37:54

You seem to be hitting bug 13589271. I can't share details from MOS, but there isn't much to share anyway. It's related to the remote table having a column with a 30-character name though, as you have in your remote FOURNIUSS table.

Unfortunately simply aliasing the column in your query, like this:

fou_d.COLUMN_WITH_30_CHARACTERS_NAME alias_a,
fou_r.COLUMN_WITH_30_CHARACTERS_NAME alias_b,

... doesn't help and still gets the same error, as the alias is applied by the local database and the problem seems to be during the remote access. What does seem to work is using an in-line view to apply a column alias before the join:

...
LEFT JOIN LOC@ABC          loc     ON dos.DOS_CODE_ID = loc.DOS_CODE_ID
LEFT JOIN (
    SELECT PAY_CODE_ID, FOU_CODE_ID, COLUMN_WITH_30_CHARACTERS_NAME alias_a FROM FOURNISS@ABC
)                          fou_d   ON fou_d.PAY_CODE_ID = loc.PAY_CODE_ID_D AND fou_d.FOU_CODE_ID = loc.FOU_CODE_ID_D
LEFT JOIN (
    SELECT PAY_CODE_ID, FOU_CODE_ID, COLUMN_WITH_30_CHARACTERS_NAME alias_b FROM FOURNISS@ABC
)                          fou_r   ON fou_r.PAY_CODE_ID = loc.PAY_CODE_ID_R AND fou_r.FOU_CODE_ID = loc.FOU_CODE_ID_R
LEFT JOIN REF_MOT@ABC      mot     ON mot.RMR_CODE_ID = cmd_r.RMR_CODE_ID
...

This even works if you give the column the same alias in both inline views. The downside is that you have to explicitly list all of the columns from the table (or at least those you're interested in) in order to be able to apply the alias to the problematic one, but having done so you can still use fou_d.* and fou_r.* in the outer select list.

I don't have an 11.2.0.2 database but I've run this successfully in an 11.2.0.3 database which still showed the ORA-00918 error from your original code. It's possible something else in 11.2.0.2 will stop this workaround being effective, of course. I don't see the original problem in 11.2.0.4 at all, so upgrading to that terminal patch release might be a better long-term solution.

Using * is generally considered a bad practice anyway though, not least because you're going to get a lot of duplicated columns from the joins (lots of dos_code_id in each row, for example); but you're also likely to be getting other data you don't really want, and anything that consumes this result set will have to assume the column order is always the same in those tables - any variation, or later addition or removal of a column, will cause problems.

ORA-00918: column ambigously defined, using DB Link

1 Answers