ORA-00918: column ambiguously specified, but what column?

Jeff Smith: My original title was, ‘…but what frigging column?!?’ but I had to change it for SEO purposes and of course some of you have small children.

This was one example of many where our Oracle Database error messages were, less than user or developer friendly.

Let’s look at an example:

EMPLOYEES and DEPARTMENTS. Both tables have a DEPARTMENT_ID field.

1.png

So if we were to run this query…

select *
from employees, departments
where department_id = department_id;

The database would say, well…I can’t do that so much, because for your predicate clause, I don’t know WHICH department_id column you want to work with.

But it wouldn’t tell us the problem was with DEPARTMENT_ID. It would say something like this –

ORA-00918: column ambiguously defined
00918. 00000 - "%s: column ambiguously specified - appears in %s and %s"

Now, this one is pretty easy to figure out, when there is only ONE predicate clause, but what if you had…30 predicate clauses? Which column reference is tripping the problem???

23ai brings smarter, clearer error messages

Wait, is ‘clearer’ a word?

Here’s what comes back running that exact same query on a 23ai database.

2.png

ORA-00918: DEPARTMENT_ID: column ambiguously specified - appears in DEPARTMENTS and EMPLOYEES
A column name used in a join was defined in more than one table and was referenced ambiguously. In a join, any column >name that occurs in more than one of the tables must be prefixed by its table name or table alias when referenced
Error at Line: 3 Column: 23

I think we can subjectively say that’s just, a better error message. And line 3 column 23, is the exact curpos of this identifier or column –

3.png

Official Docs, 19c vs 23ai

Here’s a handy reference site you can see what these ORA error messages look like ‘before’ and ‘after,’ and we’ve updated several hundred of them to be more helpful.

4.png

Sourse