oracle tutorial webinars

ORA-00918

A major source of error across Oracle database systems derives from the table aspect of the software. Even for those of you that may only have database experience confined to the use of basic Microsoft Excel tables, you will know just how frequently mistakes can occur when inserting values, equations, and editing rows and columns. In this sense, Oracle is no different in that these same components can easily lead to errors developing. The ORA-00918 message constitutes just one of these types of errors.

Cause

The source of an ORA-00918 error comes from a mistake when attempting to join two or more tables that share the same name in across columns. This is often referred to as an ambiguous reference. Fortunately it is because of the nature of the action that diagnosing this kind of error is fairly straightforward. Typically you will attempt to merge the tables and it is at this point that the error will initiate, so you should know exactly where the problem is coming from.

Solution

Luckily the solution to this Oracle error is just about as straightforward as finding what causes the problem. What is needed is to add the prefix to each column with the table name that it originally belonged too and then re-execute the SQL statement. For example, if you had the tables POS and PAY being joined, with each containing a column by the name of DAT, then any time DAT is used it needs to be prefixed by its table name, such as POS.DAT or PAY.DAT.

To further illustrate, suppose you had to execute the following SQL statement in order to join two tables:

SELECT date_start, pos

FROM employees, management

WHERE employees.date_start = management.date_start;

This would return an ORA-00918 error, that will simply state “column ambiguously defined”. So what do you do next? You add a prefix to the column with the table name. For the above example, it would look something like this:

SELECT employees.date_start, pos

FROM employees, management

WHERE employees.date_start = management.date_start;

This method will solve the error and also provide more detail when you are scanning data on the fly.

Looking forward

So the error is relatively easy to diagnose and solve, so where’s the hard part? Well, there really isn’t one. Once you solve this error once, odds are you will remember how to quickly solve it in the future. However, the unfortunate aspect of the ORA-00918 is that there aren’t many preventative measures that can be taken. To keep it from springing up, the only thing you can really do is remain aware of the names that you give columns and pre-attach prefixes to common column names. If you find that you’re still having trouble resolving this issue over time and aren’t sure as to what steps you should take next, perhaps contact a licensed Oracle consultant to inquire about further direction.