ORA-00904: string: invalid identifier

The Problem

You have written a SQL statement and are running it from the SQL Plus command prompt and you receive the error message ORA-00904: string: invalid identifier. You may have been running an Oracle procedure, package or anonymous block of PL\SQL code and received this error.

The Solution

To resolve your ORA-00904 error message the following criteria must be met:

  1. Column name must begin with a letter
  2. The column name must be a maximum of 30 characters in length
  3. The name has to be alphanumeric and may contain the characters $, _ or #.  Other characters can be used if the column is enclosed in double quotation marks
  4. The column cannot be a reserved work, such as SELECT, INSERT, UPDATE, etc

Also, you should check that you are referencing the correct alias. If you have a long list of tables that you are selecting from in your SQL statement double check that the alias you are using to reference a table is the correct one. More often than not it will be a very simple reason why you are receiving this error and it will just be a typo or you’re using the wrong reference to a table.

Another point that I’d like to make is you should always make sure the use table alias’ when writing SQL queries. For example, the following SQL shows a very basic alias usage:

                  SELECT t1.col1, t1.col2, t2.col1, t2.col2
                  from tab1 t1, tab2 t2
                  where t1.col1 = t2.col1;

This may not help you avoid the ORA-00904 error message but it will help prevent other Oracle error messages and is better practice to use this method so as to avoid ambiguously defined columns.

Hopefully this has helped you fix a problem you were having. If it has I would love to hear from you in the comments box below

Like it, share it...

Category: PL/SQL

Related Posts

Leave a Reply

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