oracle tutorial webinars

ORA-00984: Column Not Allowed

The majority of Oracle mistakes are the result of simple mix-ups. Whether it is through errors deriving from copying and pasting across programs, mistaking program functions or just flat-out getting distracted during your work, programming an Oracle database can lead to user errors that are relatively easy to create. Often, these mistakes can be tough to spot at first, but, once the user is aware of them, they typically seem like a simple oversight. Thankfully, the user-friendly nature of Oracle means that problems like an ORA-00984 message are fairly easy to remedy.

The Problem

The ORA-00984 is an error resulting from a column not being allowed in a particular area of the program. It occurs when a user attempts to issue a SQL statement that includes a column name where it is not permitted. This can most often happen in reference to a false insertion in a VALUES clause of an INSERT statement.

For a refresher, let us go over the syntax that is most commonly used with INSERT statements. For a VALUES keyword, you will often work with this format:

INSERT INTO table

(column1, column2, … column_n )
VALUES
(expression1, expression2, … expression_n );

And for a SELECT statement, you will usually see the following:

INSERT INTO table
(column1, column2, … column_n )
SELECT expression1, expression2, … expression_n
FROM source_table
WHERE conditions;

So, as you can see from above, by accidentally inserting a column name under the VALUES section as opposed to the INSERT INTO table section in the first format, the ORA-00984 error could quite possibly be triggered.

Now that we know the source of this error, let us look at some strategies to amend the issue.

The Solution

To correct the error, the user can take a couple of approaches. First, the user could correct the INSERT statement by including a character value (instead of the column name). Additionally, if the user needs to include a column name, they could rewrite the INSERT statement with a sub-select.

A quick side note: On occasion, you will not be able to find information about the error in the alert.log file. This runs contrary to a lot of Oracle instinct where you can check this file for the source of the Oracle errors once you receive them. Instead, if there is no information about the error in this location, open the DML error logging. You can audit Oracle with additional AUDIT syntax by using the DDL audit and the LogMiner audits. DDL audit triggers allow an administrator to automatically track all changes to the database. This includes changes to tables, indices, and constraints, which can be quite useful in your search for the error source.

The Example

Let us now turn to an example to see how a solution can be successfully implemented. Suppose you tried to use the column named “clients” in an INSERT statement, much like the following:

INSERT INTO employers
(business_id, business_name)
VALUES
(7, client_name);

You would subsequently receive an error message, “ORA-00984: column not allowed here”. By taking the first approach, the following will change with the character value inclusion:

INSERT INTO employers
(business_id, business_name)
VALUES
(7, ‘Mohammad’);

Alternatively, if you took the approach of rewriting the INSERT statement with a sub-select, here is how it would look:

INSERT INTO employers
(business_id, business_name)
SELECT employer_no, client_name
FROM clients
WHERE city = ‘Syracuse’;

Both of these changes can correct the ORA-00984 error and allow your system to return to a fully-functioning state.

Looking Forward

Staying aware of how columns and values interact within your database is key to preventing errors like ORA-00984. It can be easy to get caught up in the tunnel vision that sometimes accompanies coding; keeping a clear eye can give you a cautious perspective that could save hours of frustration. Working with a dedicated Oracle consulting firm can also provide you with the proper mindset that is necessary to avoid these mistakes in your Oracle database.