Error message ora 00904 - Исправление ошибок и поиск оптимальных решений проблем

FYI, in this case the cause was found to be mixed case column name in the DDL for table creation.

However, if you are mixing «old style» and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name. This happened to me, and google sent me to this stackoverflow page so I thought I’d share since I was here.

--NO PROBLEM: ANSI syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
INNER JOIN PS_NAME_PWD_VW B ON B.EMPLID = A.EMPLID
INNER JOIN PS_HCR_PERSON_NM_I C ON C.EMPLID = A.EMPLID
WHERE 
    LENGTH(A.EMPLID) = 9
    AND LENGTH(B.LAST_NAME) > 5
    AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/

--NO PROBLEM: OLD STYLE/deprecated/traditional oracle proprietary join syntax
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM PS_PERSON A
, PS_NAME_PWD_VW B 
, PS_HCR_PERSON_NM_I C 
WHERE 
    B.EMPLID = A.EMPLID
    and C.EMPLID = A.EMPLID
    and LENGTH(A.EMPLID) = 9
    AND LENGTH(B.LAST_NAME) > 5
    AND LENGTH(C.LAST_NAME) > 5
ORDER BY 1, 2, 3
/

The two SQL statements above are equivalent and produce no error.

When you try to mix them you can get lucky, or you can get an Oracle has a ORA-00904 error.

--LUCKY: mixed syntax (ANSI joins appear before OLD STYLE)
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM 
    PS_PERSON A
    inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
    , PS_NAME_PWD_VW B
WHERE 
    B.EMPLID = A.EMPLID
    and LENGTH(A.EMPLID) = 9
    AND LENGTH(B.FIRST_NAME) > 5
    AND LENGTH(C.LAST_NAME) > 5
/

--PROBLEM: mixed syntax (OLD STYLE joins appear before ANSI)
--http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/
SELECT A.EMPLID, B.FIRST_NAME, C.LAST_NAME
FROM 
    PS_PERSON A
    , PS_NAME_PWD_VW B
    inner join PS_HCR_PERSON_NM_I C on C.EMPLID = A.EMPLID
WHERE 
    B.EMPLID = A.EMPLID
    and LENGTH(A.EMPLID) = 9
    AND LENGTH(B.FIRST_NAME) > 5
    AND LENGTH(C.LAST_NAME) > 5
/

And the unhelpful error message that doesn’t really describe the problem at all:

>[Error] Script lines: 1-12 -------------------------
ORA-00904: "A"."EMPLID": invalid identifier  Script line 6, statement line 6,
column 51

I was able to find some research on this in the following blog post:

http://sqlfascination.com/2013/08/17/oracle-ansi-vs-old-style-joins/

In my case, I was attempting to manually convert from old style to ANSI style joins, and was doing so incrementally, one table at a time. This appears to have been a bad idea. Instead, it’s probably better to convert all tables at once, or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing.

Источник

Learn the cause and how to resolve the ORA-00904 error message in Oracle.

Description

When you encounter an ORA-00904 error, the following error message will appear:

ORA-00904: invalid identifier

Cause

You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite your SQL to include a valid column name. To be a valid column name the following criteria must be met:

The column name must begin with a letter.
The column name can not be longer than 30 characters.
The column name must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
The column name can not be a reserved word.

Let’s look at an example of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would receive an ORA-00904 error:

SQL> SELECT contact_id AS "c_id", last_name, first_name
  2  FROM contacts
  3  ORDER BY "cid";
ORDER BY "cid"
         *
ERROR at line 3:
ORA-00904: "cid": invalid identifier

This error was created by aliasing a column, but then mistyping the alias later. In this example, we created the alias called «c_id» for the contact_id, but then called it as «cid» in the ORDER BY clause.

To resolve this error, we can modify our SELECT statement to use the correct alias name in the ORDER BY clause as follows:

SQL> SELECT contact_id AS "c_id", last_name, first_name
  2  FROM contacts
  3  ORDER BY "c_id";

10 rows selected

Источник

ORA-00904 means that you used an invalid identifier to be a column name, which is most likely unmatched in name or incorrect syntax.

Since most user errors were caused by case-sensitive problem, so we have to explain the differences between loose form and exact form first.

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

Loose Form vs Exact Form

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, both are legal ways to name an object.

Non-quoted Identifiers (Loose Form)

This type of identifiers 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. For example:

Non-quoted Identifiers (Loose Form)

Given String	Recognized As
FIRST_NAME	FIRST_NAME
First_Name	FIRST_NAME
first_name	FIRST_NAME

So I call it Loose Form in this post. In fact, non-quoted identifiers are all regarded as upper-cased ones. This concept will help you to know ORA-00904 better.

Quoted Identifiers (Exact Form)

This type of identifiers 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. For example:

Quoted Identifiers (Exact Form)

Given String	Recognized As
«FIRST_NAME»	FIRST_NAME
«First_Name»	First_Name
«First Name»	First Name
«first_name»	first_name

So I call it Exact Form in this post. Misusing the two forms is the major source of ORA-00904 as far as I know.

Later on, we will also talk more unqualified issues about ORA-00904 to differentiate quoted from non-quoted identifiers.

Error Patterns of ORA-00904

In most cases, misuse of column names is the main source of ORA-00904, the rest is syntax error. In this post, we will talk about several error patterns of ORA-00904 in the following sections.

ORA-00904 in SELECT or INSERT
ORA-00904 in WHERE, ORDER BY or GROUP BY
ORA-00904 in CREATE TABLE
ORA-00904 in ALTER TABLE
ORA-00904 in PL/SQL
Toad Error ORA-00904: «REF»: invalid identifier

ORA-00904 in SELECT or INSERT Statements

Any columns listed in SELECT or INSERT all have chances to select invalid identifier.

In this section, ORA-00904 alerts users something is wrong, which may be caused by one of the following reasons:

Non-existent Columns
Non-existent Functions
Misspelled Columns
Case-Sensitive Column
Blanks in Column Names

ORA-00904 due to Non-existent Columns

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.

To avoid ORA-00904, you should 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, then qualify each of every column.

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 several common types of invalid identifiers.

The first case is to select a column which does not exist in the table.

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 SQL> insert into all_names_1 (first_name, last_name, num) values ('Ed', 'Chen', 100); insert into all_names_1 (first_name, last_name, num) values ('Ed', 'Chen', 100) * ERROR at line 1: ORA-00904: "NUM": invalid identifier

As we know, the column NUM is a wrong 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)

If you surprised that the column was missing from the table, it may be removed by someone else.

ORA-00904 due to Non-existent Functions

This pattern is rare though, we should talk about non-existent function calls. Let’s see how we reproduce ORA-00904.

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, SQL parser had no choice but to throw ORA-00904. This reminds me that MySQL does have MONTH function, but Oracle does not. Same reasons for non-existent function: ORA-00904: «DATEDIFF«: invalid identifier.

The solution to ORA-00904 is to call 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

Function DATEDIFF does not Work in Oracle

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

ORA-00904 due to Misspelled Columns

The third 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

As you can see, we provided some letters and the editor of SQL developer will take care the rest.

Case-Sensitive Columns

In some cases, SQL parser complained about the missing column by throwing ORA-00904, but column exists in the table. How could this happen? All you need to know is that how to use exact form to express columns in SQL statements.

To use exact form of identifiers, we have to use double quotes to wrap column names, which notify the database to create the exact name as we provided. How exactly? At least, we should treat them as case-sensitive identifiers.

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 incorrect identifiers in statements and get ORA-00904 thereafter.

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 SQL> insert into all_names_2 (First_Name, Last_Name) values ('Ed', 'Chen'); insert into all_names_2 (First_Name, Last_Name) values ('Ed', 'Chen') * ERROR at line 1: ORA-00904: "LAST_NAME": invalid identifier

Even though the column names are apparently 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.

That is to say, they are literally not the same.

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

Solution

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

SQL> insert into all_names_2 ("First_Name", "Last_Name") values ('Ed', 'Chen');





  


                       
  




  1 row created.

We used the exact form to make statements 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.

Blanks in Column Names

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.

ORA-00904 in WHERE, ORDER BY or GROUP BY Clauses

Mismatched column problems like non-existent columns, non-existent functions, misspelled columns and case sensitive columns that we have talked above could also happen in WHERE, ORDER BY or GROUP BY clause.

In this section, we specifically talk about how we can use column aliases in WHERE or GROUP BY clause.

Misusing Column Aliases

ORA-00904 due to Misusing Column Aliases

You can use column aliases in ORDER BY clauses like this.

SQL> column did format 9999; SQL> column eid format 9999; SQL> select department_id as did, employee_id as eid from employees order by did;


    DID   EID
 ----- -----
    10   200
    20   201
 ...

However, you can neither use column aliases in WHERE clauses directly:

SQL> select department_id as did, employee_id as eid from employees where did > 50; select department_id as did, employee_id as eid from employees where did > 50 * ERROR at line 1: ORA-00904: "DID": invalid identifier

Nor in GROUP BY clauses:

SQL> select department_id as did, count(employee_id) as cnt from employees group by did; select department_id as did, count(employee_id) as cnt from employees group by did * ERROR at line 1: ORA-00904: "DID": invalid identifier

We all know what column DID is, but SQL parser don’t. So it unqualified the alias name by throwing ORA-00904 to alert the problem.

Solutions

Of course, you can use real column names to suppress ORA-00904 anytime. The statements would be more stable in this way.

To be able to use column aliases in WHERE clause, you should wrap the query by an outer SELECT.

SQL> select * from (select department_id as did, employee_id as eid from employees) where did > 50;


    DID   EID
 ----- -----
    90   100
    90   101
 ...

For GROUP BY clauses that want to use column aliases, it’s a little tricky.

SQL> select did, count(eid) cnt from (select department_id as did, employee_id eid from employees) group by did;


    DID        CNT
 ----- ----------
   100          6
    30          6
 ...

That is to say, if you insist to use column aliases, an outer SELECT is your solution to ORA-00904 in this error pattern, which can regard all of your column aliases as real column names.

There’re more subtle usages on column alias c_alias in some SELECT subclauses, such as search_clause, cycle_clause and order_by_clause.

ORA-00904 in CREATE TABLE Statements

There’re several possible patterns of ORA-00904 in CREATE TABLE statement.

Misusing Reserved Words
Mistakenly Added Extra Comma
Starting with Number

ORA-00904 due to Misusing Reserved Words

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.

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 illegal 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.

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

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 illegal 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 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 illegal 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 usage of object naming, it’s not as obvious and specific as ORA-00904. Now let’s take a look at how to use special characters on object naming.

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 an illegal character. So I changed the «?» into «$», «#» or «_» to correct the problem.

Dollar sign

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





  


                       
  




  Table created.

Number sign

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


  Table created.

Underscore

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.

ORA-00904 in ALTER TABLE Statements

Problems like non-existent columns, non-existent functions, misspelled columns and case sensitive columns are also applied here. Therefore, I’d rather talk about some rare cases in this section.

There’s several patterns of ORA-00904 in ALTER TABLE statement.

ALTER TABLE ADD Column
ALTER TABLE MODIFY Column
ALTER TABLE ADD CONSTRAINT NOT NULL

ORA-00904 in ALTER TABLE ADD Column

A typical ORA-00904 in ALTER DATABASE ADD column statements is like this:

SQL> alter table t1 add column c2 date; alter table t1 add column c2 date * ERROR at line 1: ORA-00904: : invalid identifier

ORA-00904 specifically positioned at the reserved word COLUMN and told you that it is not a valid identifier. In fact, this is a syntax error. You don’t have to add the reserved word COLUMN in the statement. So we correct it by removing the keyword.

SQL> alter table t1 add c2 date;


  Table altered.

Furthermore, you should not use any reserved word in that position right after ADD.

ORA-00904 in ALTER TABLE MODIFY Column

Let’s see a case of ORA-00904 in ALTER DATABASE MODIFY column statements.

SQL> alter table all_names_1 modify (column fname varchar2(30)); alter table all_names_1 modify (column fname varchar2(30)) * ERROR at line 1: ORA-00904: : invalid identifier

Same reason here, you don’t have to add the reserved word COLUMN in the statement. Please remove it.

After removing the reserved word, we still got ORA-00904 because I used an invalid column name deliberately.

SQL> alter table all_names_1 modify (fname varchar2(30)); alter table all_names_1 modify (fname varchar2(30)) * ERROR at line 1: ORA-00904: "FNAME": invalid identifier

The column FNAME is not found in table. The valid column name should be FIRST_NAME.

SQL> alter table all_names_1 modify (first_name varchar2(30));


  Table altered.

ORA-00904 in ALTER TABLE ADD CONSTRAINT NOT NULL

SQL> create table t1 (c1 number);





  


                       
  




  Table created.

You can add an UNIQUE constraint on a column in ALTER TABLE ADD CONSTRAINT statements like this:

SQL> alter table t1 add constraint c1_unique UNIQUE (c1);


  Table altered.

But you cannot add a NOT NULL constraint on a column in the same way.

SQL> alter table t1 add constraint c1_nn NOT NULL (c1); alter table t1 add constraint c1_nn NOT NULL (c1) * ERROR at line 1: ORA-00904: : invalid identifier

As you can see, SQL parser threw ORA-00904 to alert the identifier name used in the statement is illegal. In fact, it’s a syntax error.

Why? NOT NULL is a constraint, isn’t it? Of course, NOT NULL is some kind of constraint, but it’d rather be a column attribute and we used it in the wrong way. Let’s treat it as a constraint first.

Solution 1: Regard it as a Constraint

Basically, there’re 4 types of subclauses for constraint clause in Oracle:

inline_constraint
out_of_line_constraint
inline_ref_constraint
out_of_line_ref_constraint

In the above statement that generated ORA-00904, it’s an out_of_line_constraint clause which does not allow NOT NULL to be used. Instead, you have to use inline_constraint for NOT NULL. That’s why you saw ORA-00904 when adding constraint NOT NULL.

SQL> alter table t1 modify (c1 constraint c1_nn NOT NULL);


  Table altered.

Let’s check the constraint name.

SQL> select constraint_name from user_constraints where table_name = 'T1';


  CONSTRAINT_NAME
 ------------------------------
 C1_UNIQUE
 C1_NN
  SQL> alter table t1 drop constraint C1_NN;
  Table altered.

Not like other constraints, NOT NULL cannot be a composite (multi-column) constraint, it only serves for its column.

Solution 2: Regard it as a Column Attribute

Strictly speaking, NOT NULL is a column attribute, so you don’t have to treat it like a constraint, even though it can be a constraint.

For example, we can modify a column as NOT NULL like this:

SQL> alter table t1 modify (c1 NOT NULL);


  Table altered.

As we can see, I treated it as a column attribute, not any constraint clause is involved. Let’s check the constraint name.

SQL> select constraint_name from user_constraints where table_name = 'T1';


  CONSTRAINT_NAME
 ------------------------------
 C1_UNIQUE
 SYS_C0011427

Consequently, the database provided a system-generated name for the constraint. Let’s go further to see how we drop the constraint.

SQL> alter table t1 modify (c1 NULL);


  Table altered.
  SQL> select constraint_name from user_constraints where table_name = 'T1';
  CONSTRAINT_NAME
 ------------------------------
 C1_UNIQUE

That is to say, we don’t have to know the constraint name in order to drop it, just revert the attribute. By the way, there’re more syntax that can add or drop NOT NULL constraints easily.

ORA-00904 in PL/SQL Stored Procedures

So far, we have two error patterns of ORA-00904 in PL/SQL.

Incorrect Order of Variable Declaration in PL/SQL
Unquoted String

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 to prevent from selecting invalid identifier. 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.

ORA-00904 due to Unquoted String

When you use EXECUTE IMMEDIATE in PL/SQL, you might want to know how to quote a string in a statement.

SQL> begin 2 execute immediate 'select nvl(first_name, NO_VALUE) from employees'; 3 end; 4 / begin * ERROR at line 1: ORA-00904: "NO_VALUE": invalid identifier ORA-06512: at line 2

In the above statement, we’d like to replace all NULL values with NO_VALUE strings by function NVL, but we hesitated to quote the string in the statement which is now also a string. As a result, we got ORA-00904. Now the problem is, how to quote a string in an outer string in order to make it right?

Solution

The answer is, just duplicate every single quotation mark as two single quotation marks to escape a single quotation mark in a string wherever you want to present a string in another string, especially in EXECUTE IMMEDIATE.

SQL> begin 2 execute immediate 'select nvl(first_name, ''NO_VALUE'') from employees'; 3 end; 4 /


  PL/SQL procedure successfully completed.

Now the code block is good.

Источник

ORA-00904: : invalid identifier error occurs when an invalid column name is used to refer a column in a table. The column name should follow the naming conventions. A valid column name must begin with a letter and can only contain alphanumeric characters as well as the special characters $, _, and #. The name of the column cannot be a reserved keyword. The name must be no more than 30 characters long.

If a column is referred to by an incorrect column name, the column cannot be found in the database table. This might be due to incorrect sql syntax or a failure to follow double quotation or naming standards. The column name could not be determined if an incorrect SQL Statement was used. The column could not be referred to if the double quote mark was missed from the column name. The column name should adhere to the naming conventions. If not, the error message ORA-00904: : invalid identifier will be displayed.

When the ORA-00904 error occurs

If an invalid column name is used to refer to a column in an Oracle database table in the SQL Statement, an error message will be displayed. The incorrect column name is the result of incorrect naming standards, missing double quotation marks, or incorrect SQL syntax. The issue will be resolved if a valid column name is followed by a valid SQL Statement.

CREATE TABLE EMP(
1id int, 
name VARCHAR2(100)
)

Error starting at line : 4 in command -
CREATE TABLE EMP(
1id number, 
name VARCHAR2(100)
)
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Root Cause

The incorrect column name is the result of not following naming conventions, missing double quotation marks, or improper SQL syntax. The database does not recognize the incorrect column name as a column name. Oracle was unable to find the column in the database table. As a result, Oracle displays an error message indicating that an incorrect column name was given and could not be found in the database.

Solution A

The table column name should follow the oracle naming conventions. The naming conventions are as follows

The column name should starts with an alphabet.
The column name should not be an oracle reserved keyword.
The column name should not be more than 30 characters
The column name can only contain alphanumeric characters as well as the special characters $, _, and #.

Problem1

-- STARTS WITH NUMBER
CREATE TABLE EMP(
1id int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution1

CREATE TABLE EMP(
"1id" int, 
name VARCHAR2(100)
)
Table EMP created.

Problem 2

-- CONTAINS WITH INVALID SPECIAL CHAR
CREATE TABLE EMP(
!id int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution2

CREATE TABLE EMP(
"!id" int, 
name VARCHAR2(100)
)
Table EMP created.

Problem 3

--  CONTAINS INVALID RESERVED KEYWORDS
CREATE TABLE EMP(
SIZE int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution3

CREATE TABLE EMP(
"SIZE" int, 
name VARCHAR2(100)
)
Table EMP created.

Problem 4

--  CONTAINS MORE THAN 30 CHARACTER
CREATE TABLE EMP(
EMPLOYEE_ID_WITH_MANAGER_ID_AND_SENIOR_MANAGER_ID int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution4

CREATE TABLE EMP(
id int, 
name VARCHAR2(100)
)
Table EMP created.

Solution B

If you add a comma in the last column, the error will be shown. The last column comma should be removed to resolve the error.

Problem

CREATE TABLE EMP(
id number, 
name VARCHAR2(100),
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution

CREATE TABLE EMP(
id number, 
name VARCHAR2(100)
)

Table EMP created.

Solution C

If the SQL Statement contains an incorrect syntax, oracle could not find the. column. Oracle will throw the error. The SQL Statement should use the correct and valid syntax.

Problem

insert into emp(id,name) values(1,'kim');

SQL Error: ORA-00904: "ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution

insert into emp("1id",name) values(1,'kim');

Solution D

If the column is created using number, special character or reserved keywords, the column could be referred using the double quotation enclosed. The error will be thrown if the double quotation is not used.

Problem

select 1id from emp;

select !id from emp;

select size from emp;

solution

select "1id" from emp;

select "!id" from emp;

select "size" from emp;

Источник

Содержание

Oracle / PLSQL: ORA-00904 Error Message
Description
Cause
Resolution
Option #1
How to Resolve ORA-00904 Invalid Identifier
ORA-00904
Loose Form vs Exact Form
Non-quoted Identifiers (Loose Form)
Quoted Identifiers (Exact Form)
Error Patterns of ORA-00904
ORA-00904 in SELECT or INSERT Statements
ORA-00904 due to Non-existent Columns
ORA-00904 due to Non-existent Functions
Function DATEDIFF does not Work in Oracle
ORA-00904 due to Misspelled Columns
Case-Sensitive Columns
Solution
Blanks in Column Names
ORA-00904 in WHERE, ORDER BY or GROUP BY Clauses
ORA-00904 due to Misusing Column Aliases
Solutions
ORA-00904 in CREATE TABLE Statements
ORA-00904 due to Misusing Reserved Words
ORA-00904 due to Mistakenly Added Extra Comma
ORA-00904 due to Starting with Number
Special Characters
ORA-00904 in ALTER TABLE Statements
ORA-00904 in ALTER TABLE ADD Column
ORA-00904 in ALTER TABLE MODIFY Column
ORA-00904 in ALTER TABLE ADD CONSTRAINT NOT NULL
Solution 1: Regard it as a Constraint
Solution 2: Regard it as a Column Attribute
ORA-00904 in PL/SQL Stored Procedures
ORA-00904 due to Incorrect Order of Variable Declaration in PL/SQL
ORA-00904 due to Unquoted String
Solution

Oracle / PLSQL: ORA-00904 Error Message

Learn the cause and how to resolve the ORA-00904 error message in Oracle.

Description

When you encounter an ORA-00904 error, the following error message will appear:

ORA-00904: invalid identifier

Cause

You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite your SQL to include a valid column name. To be a valid column name the following criteria must be met:

The column name must begin with a letter.
The column name can not be longer than 30 characters.
The column name must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
The column name can not be a reserved word.

Let’s look at an example of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would receive an ORA-00904 error:

To resolve this error, we can modify our SELECT statement to use the correct alias name in the ORDER BY clause as follows:

Источник

How to Resolve ORA-00904 Invalid Identifier

ORA-00904

ORA-00904 means that you used an invalid identifier to be a column name, which is most likely unmatched in name or incorrect syntax.

Since most user errors were caused by case-sensitive problem, so we have to explain the differences between loose form and exact form first.

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

Loose Form vs Exact Form

Non-quoted Identifiers (Loose Form)

Given String	Recognized As
FIRST_NAME	FIRST_NAME
First_Name	FIRST_NAME
first_name	FIRST_NAME

So I call it Loose Form in this post. In fact, non-quoted identifiers are all regarded as upper-cased ones. This concept will help you to know ORA-00904 better.

Quoted Identifiers (Exact Form)

Given String	Recognized As
«FIRST_NAME»	FIRST_NAME
«First_Name»	First_Name
«First Name»	First Name
«first_name»	first_name

So I call it Exact Form in this post. Misusing the two forms is the major source of ORA-00904 as far as I know.

Error Patterns of ORA-00904

In most cases, misuse of column names is the main source of ORA-00904, the rest is syntax error. In this post, we will talk about several error patterns of ORA-00904 in the following sections.

ORA-00904 in SELECT or INSERT Statements

Any columns listed in SELECT or INSERT all have chances to select invalid identifier.

In this section, ORA-00904 alerts users something is wrong, which may be caused by one of the following reasons:

ORA-00904 due to Non-existent Columns

Normally, we create a table without using double quotes:

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

Then we insert some data.

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

107 rows created.

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

Using such normally created tables guarantees that it will no longer have ORA-00904 anymore? Let’s see several common types of invalid identifiers.

The first case is to select a column which does not exist in the table.

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 SQL> insert into all_names_1 (first_name, last_name, num) values (‘Ed’, ‘Chen’, 100);
insert into all_names_1 (first_name, last_name, num) values (‘Ed’, ‘Chen’, 100)
*
ERROR at line 1:
ORA-00904: «NUM»: invalid identifier

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

If you surprised that the column was missing from the table, it may be removed by someone else.

ORA-00904 due to Non-existent Functions

This pattern is rare though, we should talk about non-existent function calls. Let’s see how we reproduce ORA-00904.

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, SQL parser had no choice but to throw ORA-00904. This reminds me that MySQL does have MONTH function, but Oracle does not. Same reasons for non-existent function: ORA-00904: » DATEDIFF «: invalid identifier.

The solution to ORA-00904 is to call 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’;

Function DATEDIFF does not Work in Oracle

ORA-00904 due to Misspelled Columns

The third 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.

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

Case-Sensitive Columns

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

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 incorrect identifiers in statements and get ORA-00904 thereafter.

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 SQL> insert into all_names_2 (First_Name, Last_Name) values (‘Ed’, ‘Chen’);
insert into all_names_2 (First_Name, Last_Name) values (‘Ed’, ‘Chen’)
*
ERROR at line 1:
ORA-00904: «LAST_NAME»: invalid identifier

Even though the column names are apparently 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.

That is to say, they are literally not the same.

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

Solution

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 SQL> insert into all_names_2 («First_Name», «Last_Name») values (‘Ed’, ‘Chen’);

We used the exact form to make statements 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.

Blanks in Column Names

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);

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’;

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);

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’;

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:

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.

ORA-00904 in WHERE, ORDER BY or GROUP BY Clauses

In this section, we specifically talk about how we can use column aliases in WHERE or GROUP BY clause.

ORA-00904 due to Misusing Column Aliases

You can use column aliases in ORDER BY clauses like this.

SQL> column did format 9999;
SQL> column eid format 9999;
SQL> select department_id as did, employee_id as eid from employees order by did ;

However, you can neither use column aliases in WHERE clauses directly:

SQL> select department_id as did, employee_id as eid from employees where did > 50 ;
select department_id as did, employee_id as eid from employees where did > 50
*
ERROR at line 1:
ORA-00904: «DID»: invalid identifier

Nor in GROUP BY clauses:

SQL> select department_id as did, count(employee_id) as cnt from employees group by did ;
select department_id as did, count(employee_id) as cnt from employees group by did
*
ERROR at line 1:
ORA-00904: «DID»: invalid identifier

We all know what column DID is, but SQL parser don’t. So it unqualified the alias name by throwing ORA-00904 to alert the problem.

Solutions

Of course, you can use real column names to suppress ORA-00904 anytime. The statements would be more stable in this way.

To be able to use column aliases in WHERE clause, you should wrap the query by an outer SELECT .

SQL> select * from (select department_id as did, employee_id as eid from employees) where did > 50;

For GROUP BY clauses that want to use column aliases, it’s a little tricky.

SQL> select did, count(eid) cnt from (select department_id as did, employee_id eid from employees) group by did ;

That is to say, if you insist to use column aliases, an outer SELECT is your solution to ORA-00904 in this error pattern, which can regard all of your column aliases as real column names.

There’re more subtle usages on column alias c_alias in some SELECT subclauses, such as search_clause , cycle_clause and order_by_clause .

ORA-00904 in CREATE TABLE Statements

There’re several possible patterns of ORA-00904 in CREATE TABLE statement.

ORA-00904 due to Misusing Reserved Words

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.

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 illegal string empty.

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

SQL> create table t1 («audit» int);

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

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

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 illegal usage.

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

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

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);

For any illegal 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

Although ORA-00911 is an illegitimate usage of object naming, it’s not as obvious and specific as ORA-00904. Now let’s take a look at how to use special characters on object naming.

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 an illegal character. So I changed the «?» into «$», «#» or «_» to correct the problem.

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

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

SQL> create table t3 (c12345678_production int);

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.

ORA-00904 in ALTER TABLE Statements

Problems like non-existent columns, non-existent functions, misspelled columns and case sensitive columns are also applied here. Therefore, I’d rather talk about some rare cases in this section.

There’s several patterns of ORA-00904 in ALTER TABLE statement.

ORA-00904 in ALTER TABLE ADD Column

A typical ORA-00904 in ALTER DATABASE ADD column statements is like this:

SQL> alter table t1 add column c2 date;
alter table t1 add column c2 date
*
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> alter table t1 add c2 date;

Furthermore, you should not use any reserved word in that position right after ADD .

ORA-00904 in ALTER TABLE MODIFY Column

Let’s see a case of ORA-00904 in ALTER DATABASE MODIFY column statements.

SQL> alter table all_names_1 modify (column fname varchar2(30));
alter table all_names_1 modify (column fname varchar2(30))
*
ERROR at line 1:
ORA-00904: : invalid identifier

Same reason here, you don’t have to add the reserved word COLUMN in the statement. Please remove it.

After removing the reserved word, we still got ORA-00904 because I used an invalid column name deliberately.

SQL> alter table all_names_1 modify (fname varchar2(30));
alter table all_names_1 modify (fname varchar2(30))
*
ERROR at line 1:
ORA-00904: «FNAME»: invalid identifier

The column FNAME is not found in table. The valid column name should be FIRST_NAME .

SQL> alter table all_names_1 modify (first_name varchar2(30));

ORA-00904 in ALTER TABLE ADD CONSTRAINT NOT NULL

SQL> create table t1 (c1 number);

You can add an UNIQUE constraint on a column in ALTER TABLE ADD CONSTRAINT statements like this:

SQL> alter table t1 add constraint c1_unique UNIQUE (c1);

But you cannot add a NOT NULL constraint on a column in the same way.

SQL> alter table t1 add constraint c1_nn NOT NULL (c1);
alter table t1 add constraint c1_nn NOT NULL (c1)
*
ERROR at line 1:
ORA-00904: : invalid identifier

As you can see, SQL parser threw ORA-00904 to alert the identifier name used in the statement is illegal. In fact, it’s a syntax error.

Solution 1: Regard it as a Constraint

inline_constraint
out_of_line_constraint
inline_ref_constraint
out_of_line_ref_constraint

In the above statement that generated ORA-00904, it’s an out_of_line_constraint clause which does not allow NOT NULL to be used. Instead, you have to use inline_constraint for NOT NULL . That’s why you saw ORA-00904 when adding constraint NOT NULL .

SQL> alter table t1 modify (c1 constraint c1_nn NOT NULL);

Let’s check the constraint name.

SQL> select constraint_name from user_constraints where table_name = ‘T1’;

SQL> alter table t1 drop constraint C1_NN;

Not like other constraints, NOT NULL cannot be a composite (multi-column) constraint, it only serves for its column.

Solution 2: Regard it as a Column Attribute

Strictly speaking, NOT NULL is a column attribute, so you don’t have to treat it like a constraint, even though it can be a constraint.

For example, we can modify a column as NOT NULL like this:

SQL> alter table t1 modify (c1 NOT NULL);

As we can see, I treated it as a column attribute, not any constraint clause is involved. Let’s check the constraint name.

SQL> select constraint_name from user_constraints where table_name = ‘T1’;

Consequently, the database provided a system-generated name for the constraint. Let’s go further to see how we drop the constraint.

SQL> alter table t1 modify (c1 NULL);

SQL> select constraint_name from user_constraints where table_name = ‘T1’;

That is to say, we don’t have to know the constraint name in order to drop it, just revert the attribute. By the way, there’re more syntax that can add or drop NOT NULL constraints easily.

ORA-00904 in PL/SQL Stored Procedures

So far, we have two error patterns of ORA-00904 in PL/SQL.

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.

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.

ORA-00904 due to Unquoted String

When you use EXECUTE IMMEDIATE in PL/SQL, you might want to know how to quote a string in a statement.

SQL> begin
2 execute immediate ‘select nvl(first_name, NO_VALUE) from employees’;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00904: «NO_VALUE»: invalid identifier
ORA-06512: at line 2

In the above statement, we’d like to replace all NULL values with NO_VALUE strings by function NVL , but we hesitated to quote the string in the statement which is now also a string. As a result, we got ORA-00904. Now the problem is, how to quote a string in an outer string in order to make it right?

Solution

SQL> begin
2 execute immediate ‘select nvl(first_name, »NO_VALUE») from employees’;
3 end;
4 /

Источник

Did you get an “ORA-00904: invalid identifier” error? Find out what caused the ORA-00904 error and how to resolve it in this article.

ORA-00904 Cause

So, you ran an SQL statement and you got this error:

ORA-00904: invalid identifier

What causes this error?

It’s caused by referring to a column that does not exist, or you are referring to an invalid alias in a SELECT statement.

It could also mean you’re using quotes when they aren’t needed. We can check this as part of the solution below.

To resolve the ORA-00904 error, check the following:

Check that your column name exists in the table you’re referring to
Check that you’re referring to the correct alias when referring to the column
Check that you’re using the correct table alias for the column (e.g. if the department table is aliased as dept, then it should be dept.department_id and not sup.department_id)
Check that you’re not referring to a column alias inside a WHERE clause
Check that the column name is 30 characters or less
Check that the column name contains only alphanumeric characters, $, _, and #, or it is contained in double quotes.
Check that it is not a reserved word. Many IDEs will highlight the word if it is reserved.

Let’s see an example.

This query will return an ORA-00904 error:

SELECT student_id AS stid, first_name, last_name
FROM student
WHERE st_id = 10;

ERROR at line 3:
ORA-00904: "ST_ID": invalid identifier

This error occurred on line 3, according to the output. The line numbers for errors can be helpful in working out the cause.

The error happened because I gave an alias to the student_id column, which is “stid”. But, in the WHERE clause, I called it “st_id”. There is no column or alias called st_id, so the query displayed an error.

To correct it, I should refer to the alias correctly. If it’s a WHERE clause, I need to use the column name. If it’s an ORDER BY, I can use the column name or the alias.

This is what happens when I try to use an alias inside a WHERE clause:

SELECT student_id AS stid, first_name, last_name
FROM student
WHERE stid = 10;

ERROR at line 3:
ORA-00904: "STID": invalid identifier

This is because the WHERE clause doesn’t know about column aliases. I need to refer to the foll column name.

SELECT student_id AS stid, first_name, last_name
FROM student
WHERE student_id = 10;

This query runs without issues.

ORA-00904 Invalid Identifier But Column Exists

Are you getting this error but you’re sure the column exists?

Take a look at the steps in the solution section above.

Make sure you’re not referring to the column alias in a WHERE clause, that you have double quotes if it contains special characters, that you’re referring to the right table, and that it’s not a reserved word.

ORA-00904 Invalid Identifier Insert Statement

Are you getting an ORA-00904 error when using an INSERT statement?

If so, there’s a reason why. It’s to do with double quotes in Oracle.

Let’s say I had a books table that I created like this.

CREATE TABLE books (
  book_id NUMBER(5),
  title VARCHAR2(100),
  "author" VARCHAR2(100)
);

Note the double quotes used when specifying “author”.

Now, when I insert into the table:

INSERT INTO books (book_id, title, author)
VALUES (1, 'The Adventure', 'John Smith');

SQL Error: ORA-00904: "AUTHOR": invalid identifier

Why is this causing an issue? There is clearly an author column in the table and in the statement, they are both spelt the same.

The error occurs because using double quotes in Oracle CREATE TABLE statements means the correct case, or double quotes, always need to be used.

Oracle has stored the column name in the table as “author”, all in lower case, because it is in quotes. When this happens, statements looking for this column are case-sensitive.

When I write the INSERT query, I specified author, without quotes. Oracle converts this to upper case, and because this match is case-sensitive, the column isn’t found. This is because AUTHOR is different to author.

To fix this, you can do one of two things:

Change your DDL when you create your table to remove the quotes.
If you can’t do this, then all of your SQL statements need to refer to this column using quotes. Update the INSERT statement to match.

A statement may look like this:

INSERT INTO books (book_id, title, "author")
VALUES (1, 'The Adventure', 'John Smith');

This row has been inserted successfully.

ORA-00904 Invalid Identifier Create Table

You can get this “invalid identifier” error when you create a table.

Consider this example:

CREATE TABLE books (
  CONSTRAINT book_id NUMBER(5) PRIMARY KEY,
  title VARCHAR2(100)
);

I’ve tried to create a table called books, with a book_id and a title. However, I get the follow error when I run this statement:

Error starting at line : 1 in command -
CREATE TABLE books
(
CONSTRAINT book_id NUMBER(5) PRIMARY KEY,
title VARCHAR2(100)
)
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

It doesn’t really tell me much, other than a “%s”, which doesn’t help me.

The reason this error has occurred is because a constraint refers to a column that does not exist.

In the first line inside the brackets, I specify CONSTRAINT, then the book_id column, data type and primary key.

However, this syntax means I am declaring a constraint. But, I have not yet declared the column yet.

One of the ways to correct this table definition is to remove the CONSTRAINT keyword.

CREATE TABLE books (
  book_id NUMBER(5) PRIMARY KEY,
  title VARCHAR2(100)
);

An even better way is to separate the column definition and the constraint creation. This is better because you can name the constraint, which makes it easier to manage later.

CREATE TABLE books (
  book_id NUMBER(5),
  title VARCHAR2(100),
  CONSTRAINT pk_books PRIMARY KEY (book_id)
);

For more information on creating tables, read my guide on the CREATE TABLE statement.

So, that’s how you can resolve the ORA-00904 error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Источник

This tutorial will explain what is causing the ORA-00904 error to occur and how to resolve the ORA-00904 invalid identifier error in Oracle.

ORA-00904 is a standard error in the oracle database, especially when trying to do CRUD operations in Oracle.

Hint: CRUD : is a kind of (SELECT, INSERT, UPDATE and DELETE) operations that happens in the any database not only Oracle

ORA-00904 Error Cause

This error always occurs when you try to select or reference an invalid or missing column. And as per Oracle Documented, this error causes an action in short sentences.

"ORA-00904: invalid identifier error"
Cause: Column name in error is either missing or invalid.
Action: Enter a valid column name.

ORA-00904 Solution

To resolve this error, firstly, we have to check whether this column name exists in the table or not by running this query:

SELECT TABLE_NAME
      ,COLUMN_NAME
      ,DATA_TYPE
      ,DATA_LENGTH
      ,NULLABLE
FROM   ALL_TAB_COLUMNS
WHERE  TABLE_NAME = 'EMPLOYEES'
AND    COLUMN_NAME = 'MANAGER_ID';

The output of the above query is:

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	NULLABLE
EMPLOYEES	MANAGER_ID	NUMBER	22	Y

In the above query, we checked the existence of our column “MANAGER_ID” from table “EMPLOYEES”. And as we can see from the output, this column exists; otherwise, this query will not return any data, which means no_data_found.

In case the column exists, we have to make sure that the column name meets the following criteria:

The column name cannot be a reserved word.
The column name should start with a letter not a number.
The column name length should not exceed 30 characters.
The column name could contains one of the following special characters “$” or “_” or “#”. And if we want to include other special characters in the column name we should enclosed the name by double quotation marks like this “COLUMN*1”

ORA-00904: Invalid Identifier Error occurs due to Using a Reserved Word in Column Name

If we use a reserved word as a column name, the ORA-00904 invalid identifier error will occur.

CREATE TABLE OSK_TEST
(
  "COLUMNlelandkrome119"   VARCHAR2 (150)
 ,"COLUMN*2"   VARCHAR2 (150)
 ,LEVEL        VARCHAR2(50)
);

Output:

Error report -
ORA-00904: : invalid identifier

In the above example, the ORA-00904: invalid identifier will occur because we use LEVEL as the third column name, a reserved word in the Oracle database; hence, we can’t use reserved words. Fortunately, we don’t have to remember all reserved words while naming any column in our database table.

Hint: You can refer to Oracle documentation of Oracle Database 10g to find the list of reserved words

ORA-00904: Invalid Identifier Error occur While Inserting Data into Table

This error will occur if we use the wrong column name inside the INSERT statement or use a non-existent column. It happens most of the time because of a typo; however, it could be because someone changed the table structure by changing the column name or deleting the column that we are referencing inside the INSERT statement.

INSERT INTO OSK_TEST (COLUMN1, COLUMN2, LEVEL3)
VALUES      ('FIRST VALUE', 'SECOND VALUE', 'THIRD VALUE');

Output:

Error report -
SQL Error: ORA-00904: "LEVEL3": invalid identifier

We used the non-existence column “LEVEL3” in the above example, so we got this SQL error. We can quickly fix this error by ensuring the column used in the INSERT statements.

ORA-00904: Invalid Identifier Error occur While Select Data from Table

This error will occur if we use the wrong column name inside the SELECT statement or use a non-existent co. Let’s take the below table as an example to execute a select statement against it.

CREATE TABLE OSK_TEST
(
  COLUMN_1   VARCHAR2 (150)
 ,COLUMN_2   VARCHAR2 (150)
);

Now, let’s perform the SELECT statement from this table.

SELECT COLUMN_3 FROM OSK_TEST;

Output:

ORA-00904: "COLUMN_3": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 20 Column: 28

We used the non-existence column “COLUMN_3” in the above example, so we got this SQL error.

ORA-00904: Invalid Identifier Error occur While Deleting Data from Table

This error will occur if we use the wrong column name inside the DELETE statement or use a non-existent column name.

Now, let’s perform the DELETE statement against the table name “OSK_TEST”.

DELETE FROM OSK_TEST
WHERE       COLUMN_4 IS NOT NULL;

Output:

Error report -
SQL Error: ORA-00904: "COLUMN_4": invalid identifier
00904. 00000 -  "%s: invalid identifier"

We used the non-existence column “COLUMN_4” in the above example, so we got this SQL error.

ORA-00904: Invalid Identifier Error occur While Updating Data in the Table

If we use the wrong column name inside the UPDATE statement or use a non-existent co, this error will occur.

Now, let’s perform the SELECT statement from this table.

UPDATE OSK_TEST
SET    COLUMN_5 = '123'
WHERE  COLUMN_2 = 'SECOND_VALUE';

Output:

Error report -
SQL Error: ORA-00904: "COLUMN_5": invalid identifier
00904. 00000 -  "%s: invalid identifier"

We used the non-existence column “COLUMN_5” in the above example, so we got this SQL error.

Conclusion

ORA-06512 is a kind of error that commonly occurs while doing CRUD operations in the database like INSERT, UPDATE, DELETE, and QUERY. It might also happen if we tried to use the wrong or non-existent column name in ALTER statement in the database. Here in this article, we have listed the most common causes that cause ORA-00904 errors to occur to help you in the troubleshooting process.

Источник

Ora-00904 Error Message “Invalid Identifier”

Error Ora-00904 means you are attempting to execute an SQL statement that is one of the following:

The SQL statement includes an invalid column name.
The SQL statement includes a column name which does not currently exist.

This error is most common when querying a SELECT statement.

To resolve this error, first check to make sure the column name being referenced exists. If it does not exist, you must create one before attempting to execute an SQL statement with the column. If the column name exists, be sure to check that the column name is in the proper syntax. To be valid, the column name must meet the following criteria:

The column name cannot be a reserved word.
The first letter of the column name must be a letter.
The column name must be less than or equal to 30 characters.
The column name must be made up of alphanumeric characters.
The column name can contain any of the following 3 characters: $, _, #. If the column name includes any other characters, it must be enclosed with double quotation marks.

The following is an example of an SQL statement containing Error Ora-00904:

SELECT column_name AS “column_id”

FROM table

ORDER BY column_id;

In this example, the name “column_id” is aliasing the column_name, which is later queried as column_id. Notice the missing double quotation marks in the second column_id. To resolve the error, follow the proper syntax by enclosing “column_id” in double quotation marks:

SELECT column_name AS “column_id”

FROM table

ORDER BY “column_id”;

To avoid seeing error Ora-00904 in the future, make sure the column name meets the criteria for a valid column name. Rewrite the query to follow this format. If you are attempting to query a column name which does not exist, you must create the column. Always double check spelling.

Источник