SQL

How to Resolve ORA-00904 Invalid Identifier

Oracle Database Object Name Recognition Examples

Database Object Naming Rules

Normally, Oracle treats most identifiers as case insensitive ones excepts password. But there’s some special usages should be taken care of in case of ORA-00904 invalid identifier generated.

Not only ORA-00904, but ORA-00903 and ORA-00911 are also related to invalid usage on object identifiers. More specifically, ORA-00903 alerts users for invalid table name, and ORA-00911 is raised for positioning invalid characters.

According to Oracle Database Object Names and Qualifiers, there’re two kinds of valid naming rules to create a database object. One is non-quoted identifiers, the other is quoted identifiers.

  1. Quoted Identifiers
  2. They begin and end with double quotation marks (“”). This kind of identifiers should be used as exactly as originally defined. So I usually call it Exact Form.

  3. Non-quoted Identifiers
  4. They are not surrounded by any punctuation or special treatment. We can use them case-insensitively and flexibly in SQL statements as long as they are conceptually equivalent string. So I usually call it Loose Form.

Let’s take a look at some error types of ORA-00904 on column names.

ORA-00904

In most cases, the column name is the main source of ORA-00904.

Loose Form

Normally, we create a table without using double quotes:

SQL> create table all_names_1 (First_Name varchar2(25), Last_Name varchar2(25));

Table created.

Then we insert some data.

SQL> insert into all_names_1 select distinct first_name, last_name from employees;

107 rows created.

You can query this table without any special treatments. That said, column names in either lower or upper case is valid and acceptable.

SQL> select first_name, last_name from all_names_1 where first_name = 'Ed';

FIRST_NAME                LAST_NAME
------------------------- -------------------------
Ed                        Chen

Using such normally created tables guarantees that it will be no longer ORA-00904 anymore? Let’s see two common types of errors.

ORA-00904 due to Non-existent Columns

The first case is to select a non-existent column.

SQL> select first_name, last_name, num from all_names_1 where first_name = 'Ed';
select first_name, last_name, none from all_names_1 where first_name = 'Ed'
                              *
ERROR at line 1:
ORA-00904: "NUM": invalid identifier

As we know, the column NUM does not exist. We’d better to check the definition by describing the table.

ORA-00904 due to Misspelled Columns

The second case is to select a misspelled column name, which is the most common pattern of ORA-00904.

SQL> select first name, last_name from all_names_1 where first_name = 'Ed';
select first name, last_name from all_names_1 where first_name = 'Ed'
       *
ERROR at line 1:
ORA-00904: "FIRST": invalid identifier

In the statement, I missed an underscore between FIRST and NAME in the column list on purpose. As a result, SQL engine parsed the statement as this:

To query a column FIRST aliased as NAME and a column LAST_NAME from table ALL_NAMES_1 with rest of conditions.
Of course, there’s no column named FIRST, not even First Name. Please check the spell once again.

Exact Form

To use exact form of identifiers, we have to use double quotes to notify the database to create the exact name that we provide.

SQL> create table all_names_2 ("First_Name" varchar2(25), "Last_Name" varchar2(25));

Table created.

Then we insert some data.

SQL> insert into all_names_2 select distinct first_name, last_name from employees;

107 rows created.

From now on, we can no longer use the table loosely like the old days.

ORA-00904 due to Missing Double Quotes

Without adding double quotes on columns, we got ORA-00904.

SQL> select First_Name, Last_Name from all_names_2 where First_Name = 'Ed';
select First_Name, Last_Name from all_names_2 where First_Name = 'Ed'
                                                    *
ERROR at line 1:
ORA-00904: "FIRST_NAME": invalid identifier

Even though the column names are literally the same as their definition, we still got ORA-00904 due to missing doubles quotes on columns. This is because non-quoted identifier First_Name is recognized as FIRST_NAME, SQL parser cannot find a matched identifier in the table. Eventually, it raised ORA-00904 to alert this issue.

The cure is simple, we should quote them exactly as we provided at the table creation, instead of non-quoted form.

SQL> select "First_Name", "Last_Name" from all_names_2 where "First_Name" = 'Ed';

First_Name                Last_Name
------------------------- -------------------------
Ed                        Chen

We go back to use exact form to save the statement.

Blanks in Column Name

It’s worth noting that the exact form allows us to create columns with blanks without ORA-00904 problem. Let’s see a normal table first.

SQL> create table all_names_3 (First_Name varchar2(25), Last_Name varchar2(25), Num int, Create_Date date default sysdate);

Table created.

SQL> insert into all_names_3 (first_name, last_name, num) select first_name, last_name, count(*) from employees group by first_name, last_name;

107 rows created.

SQL> select first_name, last_name, num, create_date from all_names_3 where first_name = 'Ed';

FIRST_NAME                LAST_NAME                        NUM CREATE_DA
------------------------- ------------------------- ---------- ---------
Ed                        Chen                               1 12-MAR-19

Next, let’s see an odd case with blanks in column names.

SQL> create table all_names_4 ("First_Name" varchar2(25), "Last_Name" varchar2(25), " " int, "  " date default sysdate);

Table created.

SQL> insert into all_names_4 ("First_Name", "Last_Name", " ") select first_name, last_name, count(*) from employees group by first_name, last_name;

107 rows created.

SQL> select "First_Name", "Last_Name", " ", "  " from all_names_4 where "First_Name" = 'Ed';

First_Name                Last_Name
------------------------- ------------------------- ---------- ---------
Ed                        Chen                               1 12-MAR-19

As we can see, I used one blank space for the third column and two blank spaces for the fourth column, they can work well without ORA-00904 as long as you follow the rule to query the table.

Any name collision? No, this is because one blank is different from two blanks within the namespace of the same table, especially we are using exact form to define the columns.

Even though we did not get any ORA-00904 because we used the table carefully and properly, the exact-styled naming is really confusing. Try to describe the odd table:

SQL> desc all_names_4;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 First_Name                                         VARCHAR2(25)
 Last_Name                                          VARCHAR2(25)
                                                    NUMBER(38)
                                                    DATE

Consequently, it displays little information on the third and fourth column. For a new member, who has no idea what’s going on here. Moreover, once ORA-00904 is thrown when querying such odd table, no one can troubleshoot it.

This remind me that I had ever tried to remove a file with empty or blank name in an Unix OS. It took me a long time to deal with.

Other Invalid Identifiers

Errors like ORA-00904 could happen in all kinds of object creation. Let’s see some wrong types of identifiers to clarify the naming rules.

  1. Must Not Use Reserved Words
  2. Must Start with Letter
  3. Special Characters Restriction
  4. Must be within 30 Bytes Long

1. Must Not Use Reserved Words

Reserved words are wild things, everything that involves reserved words would become unpredictable and nasty eventually. You should never use them to name your database objects.

SQL> create table audit (c1 int);
create table audit (c1 int)
             *
ERROR at line 1:
ORA-00903: invalid table name

We didn’t see ORA-00904, instead, we saw ORA-00903 which is dedicated for table name validation error.

If you insist to use them, please use double quotations, the exact form.

SQL> create table “audit” (c1 int);

Table created.

Chances are, your users might create such database objects under totally unconscious situations. This is because their tools take care of the rest.

2. Must Start with Letter

How about a table name which starts with a number?

SQL> create table t1 (12345678 int);
create table t1 (12345678 int)
                 *
ERROR at line 1:
ORA-00904: : invalid identifier

So I added a letter “c” before the identifier to correct the problem.

SQL> create table t1 (c12345678 int);

Table created.

For any invalid characters being used, ORA-00911 is raised to this problem like the following.

SQL> create table $t1 (c12345678 int);
create table $t1 (c12345678 int)
             *
ERROR at line 1:
ORA-00911: invalid character

In fact, the dollar sign “$” is valid for an identifier, just don’t put it in the beginning of the object name. For more about the restrictions on special characters, you should go for the next rule.

Although ORA-00911 is an invalid related error, it’s not as obvious and specific as ORA-00904.

3. Special Characters Restriction

Only $, _, and # are allowed.

For column names, ORA-00911 raised for invalid character of using an unacceptable special character “?”.

SQL> create table t1 (c12345678?production int);
create table t1 (c12345678?production int)
                          *
ERROR at line 1:
ORA-00911: invalid character

Instead of ORA-00904, we saw ORA-00911 that notified us that the question mark is invalid character. So I changed the “?” into “_” to fix the problem.

SQL> create table t1 (c12345678_production int);

Table created.

In practice, using a string for an identifier is pretty normal. Instead of spaces, underscores are often used and recommended for separating meaningful words in a string.

4. Must be within 30 Bytes Long

First, I use an identifier which contains 31 characters.

SQL> create table t1 (c123456789012345678901234567890 int);
create table t1 (c123456789012345678901234567890 int)
                 *
ERROR at line 1:
ORA-00972: identifier is too long

For complying with naming rules, SQL engine raised ORA-00972 which is similar to ORA-00904 to alert the length problem. So I use an identifier with 30 characters to correct the problem.

SQL> create table t1 (c12345678901234567890123456789 int);

Table created.

Please note that, while a normal database object name can be 30 bytes long, a database link can be 128 bytes long.

Leave a Reply

Your email address will not be published. Required fields are marked *