SQL

How to Resolve ORA-00904 Invalid Identifier

ORA-00904 Invalid Identifier - Oracle Database Object Name Recognition Examples

ORA-00904

Normally, Oracle treats most identifiers as case insensitive ones excepts password. But there’re 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 names, and ORA-00911 is raised for positioning illegal characters.

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

Later on, we will also talk more issues about ORA-00904 invalid identifier.

Abstracts on Every Section

Here are the abstracts on every section that we will talk about in this post.

  1. Non-quoted Identifiers (Loose Form)
  2. They are not surrounded by punctuation marks or any other special treatments. We can use them case-insensitively and flexibly in SQL statements as long as they are basically equivalent string. So I usually call it Loose Form in this post.

    The error patterns in this section have:

  3. Quoted Identifiers (Exact Form)
  4. They begin and end with double quotation marks (“”). You can put almost every character in the double quote, including white spaces and reserved words. This kind of identifiers should be used exactly as is originally defined. So I usually call it Exact Form in this post.

    The error patterns in this section have:

  5. Other Naming Rules
  6. Not only quoted and non-quoted form, there’re several important naming rules related to ORA-00904, such as reserved words, valid special characters and length of identifiers, we should also take care of.

    The error patterns in this section have:

  7. ORA-00904 due to Mistakenly Added Extra Comma
  8. Misspelling column names is an error pattern have talked about it in the above, you have to correct the identifier to make your statements work. In this section, we will discuss a very strange error pattern of ORA-00904, an extra comma mistakenly added in CREATE TABLE.

  9. ORA-00904 due to Incorrect Order of Variable Declaration in PL/SQL
  10. Programming units allow us to declare variables, control flow and output the result. Debugging a programming unit sometimes is a long and tedious job. In this section, you can see an error pattern of ORA-00904 in PL/SQL code blocks, which is related to the order of variable declaration.

Let’s take a look at some error types of ORA-00904 on column names. In most cases, misuse of column names is the main source of ORA-00904.

ORA-00904 in 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 quotation marks. That is to say, column names in either lower or upper case is valid and acceptable. This is because SQL parser will treat all identifiers as upper-cased ones.

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 no longer have ORA-00904 anymore? Let’s see two common types of invalid identifiers.

1. ORA-00904 due to Non-existent Columns

The first case is to select a table, one of columns does not exist.

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

ORA-00904 invalid identifier in Toad for Oracle
ORA-00904 invalid identifier in Toad for Oracle

As we know, the column NUM is an invalid column because it does not exist in the table. We’d better to check the definition by describing the table.

SQL> desc all_names_1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FIRST_NAME                                         VARCHAR2(25)
 LAST_NAME                                          VARCHAR2(25)

2. ORA-00904 due to Non-existent Functions

This pattern is rare though, we should talk about it. Let’s see how we reproduce it.

SQL> select first_name, month(hire_date) hire_month from employees where last_name = 'Chen';
select first_name, month(hire_date) hire_month from employees where last_name = 'Chen'
                   *
ERROR at line 1:
ORA-00904: "MONTH": invalid identifier

SQL parser first tried to match a function named MONTH, but nothing is found, so it turned to match columns, no matched either. This reminds me that MySQL does have MONTH function, but Oracle does not.

The solution to ORA-00904 is to use the correct Oracle function named EXTRACT to get month value for your column.

SQL> select first_name, extract(month from hire_date) hire_month from employees where last_name = 'Chen';

FIRST_NAME           HIRE_MONTH
-------------------- ----------
Ed                            9

Please note that, some date time functions that you are used to use in MySQL like YEAR, MONTH, NOW, DATEDIFF, SUBSTRING are not valid functions or identifiers in Oracle. You may check Oracle SQL Language Reference : Functions for sure.

3. ORA-00904 due to Misspelled Columns

The second case is to select a misspelled and false 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 deliberately invalidated the column name by missing an underscore between FIRST and NAME. As a result, SQL parser translated 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 valid column named FIRST, not even First Name. The valid and true identifier is FIRST_NAME. We should check the spelling once again, then correct the identifier.

To avoid typos on column names, you can use a GUI tool like SQL Developer, Toad for Oracle or PL/SQL Developer to facilitate you to autocomplete column names. For an example of SQL Developer:

Autocomplete Column Names in SQL Developer Editor so as to Avoid ORA-00904 invalid identifier
Autocomplete Column Names in SQL Developer Editor so as to Avoid ORA-00904 invalid identifier
As you can see, we provided some letters and the editor of SQL developer will take care the rest.

ORA-00904 in Exact Form

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

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. Otherwise, we have great chances to use invalid identifiers in statements and get ORA-00904 thereafter.

1. 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 loosely recognized as FIRST_NAME, an upper-cased one. Therefore, SQL parser cannot find a matched identifier in the table. Eventually, it raised ORA-00904 to alert the false column.

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 used the exact form to make the statement valid. As we can see, using exact form is very inconvenient, we should take care of every tiny detail on identifiers to prevent them from being invalidated.

2. Blanks in Column Name

It’s worth noting that the exact form allows us to create columns filled with blanks, which are valid without ORA-00904 invalidation 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 but valid 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 or invalidation? No, this is because one blank is different from two blanks within the namespace of the same table, especially when we are using exact form to define the columns.

Even though we did not get any ORA-00904 and invalidation problem as we used the table carefully and properly, the exact-styled naming is really confusing. Try to describe the odd but valid 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 absolutely no idea what’s going on here. Moreover, once ORA-00904 is thrown when querying such an odd table, no one is able to 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 fix it.

Other Naming Rules about ORA-00904

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. ORA-00904 due to Using Reserved Words

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

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

We saw ORA-00904 again, but this time SQL parser can’t tell the column name and left invalid string empty.

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

SQL> create table t1 ("audit" 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. ORA-00904 due to Starting with Number

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

ORA-00904 warned you that it is illegal here. So I added a letter “c” before the identifier to fix ORA-00904.

SQL> create table t1 (c12345678 int);

Table created.

For any invalid characters being used, ORA-00911 is raised to alert 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 illegitimate related error, it’s not as obvious and specific as ORA-00904.

3. ORA-00911 due to Misusing Special Characters

Only $, _, and # are allowed. For column names, ORA-00911 is 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 “$”, “#” or “_” to correct the problem.

SQL> create table t1 (c12345678$production int);

Table created.

SQL> create table t2 (c12345678#production int);

Table created.

SQL> create table t3 (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. ORA-00972 due to Identifier is over 30 Bytes

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 parser raised ORA-00972 which is similar to ORA-00904 to notify 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, database links and restore points can be 128 bytes long.

ORA-00904 due to Mistakenly Added Extra Comma

An extra comma mistakenly added in the statement makes SQL parser don’t know what to do, let’s take a look some examples:

ORA-00904 in CREATE TABLE

SQL> create table t1 (c1 number, c2 date,);
create table t1 (c1 number, c2 date,)
                                    *
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> create table t1 (c1 number,, c2 date);
create table t1 (c1 number,, c2 date)
                           *
ERROR at line 1:
ORA-00904: : invalid identifier

As you can see, we have an extra comma in the column list. SQL parser knew there’s nothing or empty after the extra comma, but it eventually left ORA-00904 for you to fix the invalid usage.

As we have talked, defining columns with pure blanks are acceptable and meaningful as long as you use the exact form to create table. But a nothing or empty column is another idea, which is totally nonsense at all. No wonder ORA-00904 was thrown.

To correct the problem, we should remove the extra comma to make the statement work.

SQL> create table t1 (c1 number, c2 date);

Table created.

ORA-00904 due to Incorrect Order of Variable Declaration in PL/SQL

Suppose we’d like to use a cursor which involves a variable like this:

SQL> declare
  2    cursor c1 is select employee_id from hr.employees where department_id = v_num;
  3    v_num number;
  4  begin
  5    v_num := 110;
  6    open c1;
  7  end;
  8  /
  cursor c1 is select employee_id from hr.employees where department_id = v_num;
                                                                          *
ERROR at line 2:
ORA-06550: line 2, column 75:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 2, column 75:
PL/SQL: ORA-00904: "V_NUM": invalid identifier

ORA-06550: line 2, column 16:
PL/SQL: SQL Statement ignored

We saw ORA-00904 thrown by PL/SQL engine. In which, ORA-06550 indicated that the identifier at line 2, column 75 was used illegally in the anonymous PL/SQL block.

This error is very obvious, we cannot use variables before we declare them. In other words, we have to declare variables first. So I switched line 2 and 3 in places as this:

SQL> declare
  2    v_num number;
  3    cursor c1 is select employee_id from hr.employees where department_id = v_num;
  4  begin
  5    v_num := 110;
  6    open c1;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Now the code block is good.

Leave a Reply

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