ORA-00904: 无效标识符

小开

Are you sure you have a column DEPARTEMENT_CODE on your table PS_TBL_DEPARTMENT_DETAILS

ORA-00904: string: invalid identifier Cause: The column name entered is either missing or invalid. Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in d double quotation marks. It may not be a reserved word.

小开

DEPARTMENT_CODE is not a column that exists in the table Team. Check the DDL of the table to find the proper column name.

小开

最佳答案

Your problem is those pernicious double quotes.

SQL> CREATE TABLE "APC"."PS_TBL_DEPARTMENT_DETAILS"
2  (
3    "Company Code" VARCHAR2(255),
4    "Company Name" VARCHAR2(255),
5    "Sector_Code" VARCHAR2(255),
6    "Sector_Name" VARCHAR2(255),
7    "Business_Unit_Code" VARCHAR2(255),
8    "Business_Unit_Name" VARCHAR2(255),
9    "Department_Code" VARCHAR2(255),
10    "Department_Name" VARCHAR2(255),
11    "HR_ORG_ID" VARCHAR2(255),
12    "HR_ORG_Name" VARCHAR2(255),
13    "Cost_Center_Number" VARCHAR2(255),
14    " " VARCHAR2(255)
15  )
16  /


Table created.


SQL>

Oracle SQL allows us to ignore the case of database object names provided we either create them with names all in upper case, or without using double quotes. If we use mixed case or lower case in the script and wrapped the identifiers in double quotes we are condemned to using double quotes and the precise case whenever we refer to the object or its attributes:

SQL> select count(*) from PS_TBL_DEPARTMENT_DETAILS
2  where Department_Code = 'BAH'
3  /
where Department_Code = 'BAH'
*
ERROR at line 2:
ORA-00904: "DEPARTMENT_CODE": invalid identifier




SQL> select count(*) from PS_TBL_DEPARTMENT_DETAILS
2  where "Department_Code" = 'BAH'
3  /


COUNT(*)
----------
0


SQL>

tl;dr

don't use double quotes in DDL scripts

(I know most third party code generators do, but they are disciplined enough to put all their object names in UPPER CASE.)

The reverse is also true. If we create the table without using double-quotes …

create table PS_TBL_DEPARTMENT_DETAILS
( company_code VARCHAR2(255),
company_name VARCHAR2(255),
Cost_Center_Number VARCHAR2(255))
;

… we can reference it and its columns in whatever case takes our fancy:

select * from ps_tbl_department_details

… or

select * from PS_TBL_DEPARTMENT_DETAILS;

… or

select * from PS_Tbl_Department_Details
where COMAPNY_CODE = 'ORCL'
and cost_center_number = '0980'

小开

I had the same exception in JPA 2 using eclipse link. I had an @embedded class with one to one relationship with an entity. By mistake ,in the embedded class, i had also the annotation @Table("TRADER"). When the DB was created by the JPA from the entities it also created a table TRADER (which was a wrong as the Trader entity was embedded to the main entity) and the existence of that table was causing the above exception every time i was trying to persist my entity. After deleting the TRADER table the exception disappered.

小开

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.

小开

In my case, this error occurred, due to lack of existence of column name in the table.

When i executed "describe tablename" , i was not able to find the column specified in the mapping hbm file.

After altering the table, it worked fine.

小开

Also make sure the user issuing the query has been granted the necessary permissions.

For queries on tables you need to grant SELECT permission.
For queries on other object types (e.g. stored procedures) you need to grant EXECUTE permission.

小开

I was passing the values without the quotes. Once I passed the conditions inside the single quotes worked like a charm.

Select * from emp_table where emp_id=123;

instead of the above use this:

Select * from emp_table where emp_id='123';

小开

I had this error when trying to save an entity through JPA.

It was because I had a column with @JoinColumn annotation that didn't have @ManyToOne annotation.

Adding @ManyToOne fixed the issue.

小开

I had the same exception and the problem in my case was due the column was not present in the sub-query - despite I know the "missing" column/identifier belongs to the table with the alias I'm referring to.

Example - this query generates the ORA-00904 - invalid identifier at the column MC.SIT_TYPE:

SELECT MC.ID_CODE,
MC.EMP_NAME
FROM (SELECT MC.ID_CODE,
MC.EMP_NAME
FROM EMP_WORKER MC) MC
INNER JOIN TEM_SHEETS FR ON MC.SIT_TYPE = FR.ID_CODE -- This line has the ORA-00904 error.
LEFT JOIN WRK_TYPE RAS ON MC.ID_CODE = RAS.ID_CODE;

Now, after modify the sub-query - by adding the MC.SIT_TYPE column - , the ORA-00904 disapears.

Result:

SELECT MC.ID_CODE,
MC.EMP_NAME
FROM (SELECT MC.ID_CODE,
MC.EMP_NAME, 
 -- Here, the "missing invalid identifier, in this case (MC.SIT_TYPE)" is added:
MC.SIT_TYPE
FROM EMP_WORKER MC) MC
INNER JOIN TEM_SHEETS FR ON MC.SIT_TYPE = FR.ID_CODE
LEFT JOIN WRK_TYPE RAS ON MC.ID_CODE = RAS.ID_CODE;

Here are some tips:

Check very closely the tabulation/indent of your code - (depending of your settings), SQL Developer handles the indentation of your code different.
The code you're working on might not be tabulated/indented - making the debugging of these kind of errors harder and time-consuming.
Modify the aliases of your columns/sub-queries - code in general - for generate unique aliases - with this, I mean: don't use the same aliases for sub-queries and related tables.

小开

I got the error message because there was an table alias i missed and didn't use.

This throws the error because of the 'cl' alias:

SELECT * FROM MYSCHEMA.COMPANYLOCATION_CL cl JOIN MYSCHEMA.COMPANYTYPE_CT ON MYSCHEMA.COMPANYLOCATION_CL.CLNR = MYSCHEMA.COMPANYTYPE_CT.CT_CL_NR

Without 'cl' it works fine:

SELECT * FROM MYSCHEMA.COMPANYLOCATION_CL JOIN MYSCHEMA.COMPANYTYPE_CT ON MYSCHEMA.COMPANYLOCATION_CL.CLNR = MYSCHEMA.COMPANYTYPE_CT.CT_CL_NR

Please don't complain about the table names - the schema is 20 years old...

小开

In my case the error was caused by using the wrong case for a column name and it seems you're having the same issue.

Department_Code isn't the same as DEPARTMENT_CODE for Oracle.

小开

In my case I got this error when I tried to create a new table in Oracle like this with , in last line

create table products(
id_pro number primary key,
product_name varchar2(20),
);

By removing , the problem solved like this

create table products_test(
id_pro number primary key,
product_name varchar2(20)
);