Fix ORA-00904: invalid identifier Error in Oracle 11g database

Published by Jacob Martin on March 7, 2015

If you are an Oracle database user then you very well familiar with the ORA-00904: invalid identifier error. It doesn’t matter which version you are using10g, 11g or 12g, this is one of the most common error comes while doing CRUD (Create, Read, Update, and Delete) operations in Oracle. SELECT, INSERT, UPDATE and DELETE are used to perform CRUD operation in Oracle database. What will you do if you get this error while running in SQL script? In such situation firstly you have to know what this Oracle trying to say here. Invalid identifier means the column name entered is either missing or invalid; this one is the most common cause of this error but not the only one. . Some time it occurred if you use names, which happened to be reserved word in Oracle database. So let’s first know by what reason you got such error message and how to resolve it.

Some reasons of “ORA-00904: invalid identifier error”

Here are some reasons which may lead to ORA-00904 or “invalid identifier” in Oracle 11g database.

·         Due to extra comma at last column

If you are copying column definition from some other table’s DDL statement and if the selected  column is not the last one you will also copy comma, and if you put it as last column in your DDL statement you will see “ORA-00904: invalid identifier” because after comma Oracle expect another column declaration.

·         Due to Reserved keyword as Column name

If you will see the error Schema Creation Failed: ORA-00904: invalid identifier. The reason is that your schema creation failed because AUDIT is a reserved word in Oracle 11g R2.+

·         While Inserting data into Table

Apart from table creation, you will see error “ORA-00904: invalid identifier” if you use wrong column name in INSERT statement or use a non-existent column name. Most of the time it happens because of typo, but some other time it could be due to parallel update e.g. someone changed the schema of table and renamed or dropped the column you are referring in INSERT query.

How to remove Invalid Identifier Error in Oracle database

Invalid Identifier Error in Oracle database can be removed by following the valid column name in DDL like CREATE or ALTER statement. For DML statements like SELECT, UPDATE, INSERT and DELETE, ORA-00904 can be eliminated by using correct column name. If you are making SQL script to run on production database, try to test these queries on production copy of database before running it directly on live database. Just like the same if you are creating a table make sure you use a valid column name in your schema. A valid column name in Oracle database

• Must begin with a letter.
• Can exceed it more than 30 characters.
• Must consist of alphanumeric characters
• May contain special characters: $, _, and #.
• If the column name uses any other characters, it must be enclosed in double quotation marks.
• Can’t be a reserved word.

ORA-00904 may occur when you try to create or alter a table with invalid column name. It also may occur when you try to reference a non existing column in a select / insert / update / delete statement. If, you are not able to recover the Oracle 11G Database even after following above steps, then you can opt for appropriate recovery tool that is Oracle File Repair tool. With this tool you can easily recover all files and database objects from your Oracle database. It not only support Oracle 11G version but it also support all version of Oracle. It deeply scans the database and if there is any error or corruption it fix and then recover all possible data.

Steps to Fix ORA-00904: invalid identifier Error in Oracle 11g database error

Step 1: Search Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases in your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.

1539 Total Views 1 Views Today