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.
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.
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 –
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 Comments
Good Article, thanx! For teaching purposes we put we romanian an russian translation our resorces ok ?
Are you asking if you can use my posts to teach people, with romaniam and Russian translations? Yes.
I do ask you attribute the original source though, this site.
Well done. Sometimes ferreting out the details a developer will want is difficult, but whenever it is obvious and in hand it was previously a sore point that Oracle stopped short of being completely clear. Once into complicated relational models like EBIZ, finding those details could be tediously time consuming for no value. You have eliminated that sore spot for a long list of cases. Thanks.
Thanks Mark! I’m not sure we’re “done” on this project, but we’ve made great progress and are trying to go deeper into the error messages to continue this philosophy whenever possible.