ORA-00904: invalid identifier

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'

Related Posts:

  1. What is it exactly a BLOB in a DBMS context
  2. How do I reset a sequence in Oracle?
  3. MySQL equivalent of DECODE function in Oracle
  4. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  5. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  6. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  7. Self Join to get employee manager name
  8. how to fix oracle ORA-01722 invalid number error
  9. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  10. ORA-00979 not a group by expression
  11. ORA-00907: missing right parenthesis
  12. Why do we need “Relationships” between tables at all?
  13. Case statement in MySQL
  14. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  15. ORA-01843 not a valid month- Comparing Dates
  16. ORA-00918: column ambiguously defined in SELECT *
  17. Oracle error : ORA-00905: Missing keyword
  18. How do I limit the number of rows returned by an Oracle query after ordering?
  19. Oracle SELECT TOP 10 records
  20. Can a foreign key be NULL and/or duplicate?
  21. How to Select Top 100 rows in Oracle?
  22. ORA-12560: TNS:protocol adaptor error
  23. What is the difference between JOIN and UNION?
  24. Error Code: 2013. Lost connection to MySQL server during query
  25. ORA-00972 identifier is too long alias column name
  26. Oracle Apex column link from LOV values
  27. PLS-00103: Encountered the symbol when expecting one of the following:
  28. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  29. ORA-00918: column ambiguously defined in SELECT *
  30. ORA-00918: column ambiguously defined in SELECT *
  31. how to drop partition without dropping data in MySQL?
  32. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  33. ORA-01861: literal does not match format string
  34. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  35. “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
  36. How do you create a temporary table in an Oracle database?
  37. Oracle “Partition By” Keyword
  38. How to insert date values into table
  39. Oracle SQL: Update a table with data from another table
  40. Oracle SQL query for Date format
  41. The backend version is not supported to design database diagrams or tables
  42. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  43. SQL selecting rows by most recent date with two unique columns
  44. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  45. Postgres Error: More than one row returned by a subquery used as an expression
  46. Comparing Dates in Oracle SQL
  47. Convert timestamp to date in Oracle SQL
  48. Update statement with inner join on Oracle
  49. what is the difference between triggers, assertions and checks (in database)
  50. Oracle: If Table Exists
  51. DATEDIFF function in Oracle
  52. How to create id with AUTO_INCREMENT on Oracle?
  53. LEFT function in Oracle
  54. Solutions for INSERT OR UPDATE on SQL Server
  55. What is the difference between Views and Materialized Views in Oracle?
  56. How do I view the Explain Plan in Oracle Sql developer?
  57. ORA-01779: cannot modify a column which maps to a non key-preserved table
  58. “select * into table” Will it work for inserting data into existing table
  59. ORA-01735: invalid ALTER TABLE option – Toad
  60. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  61. What is an MDF file? [closed]
  62. What is “Advanced” SQL?
  63. How do you trouble shoot a “Data type mismatch in criteria expression” error in MS Access 2010?
  64. Inserting data into a temporary table
  65. Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
  66. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  67. When to use “ON UPDATE CASCADE”
  68. String or binary data would be truncated. The statement has been terminated
  69. Error converting data type varchar to float
  70. How to concatenate text from multiple rows into a single text string in SQL Server
  71. “You tried to execute a query that does not include the specified aggregate function”
  72. MySQL error: key specification without a key length
  73. SQL: Two select statements in one query
  74. Check if MySQL table exists without using “select from” syntax?
  75. mysql update column with value from another table
  76. What does Include() do in LINQ?
  77. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  78. Get everything after and before certain character in SQL Server
  79. SQLite string contains other string query
  80. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  81. Where are image paths to product category images stored in WordPress database
  82. Moving an entire wordpress site from one domain to a new domain and database
  83. WordPress SQL LIKE request doesn’t work for fields with special symbols
  84. Uppercase to sentence case for post titles
  85. Export / import WordPress database
  86. Better way to migrate to server?
  87. Why does changing Database Table Prefixes result in loss of content?
  88. WP MultiSite: Can’t select database error after changing to new domain
  89. $wpdb->get_var – What value is returned when zero records are found?
  90. Help me SELECT thumbnail from SQL and use
  91. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  92. add role on WordPress in PHP on a second database
  93. Deleting empty meta key values
  94. What’s the easiest way to connect external database and display data on wordpress?
  95. can’t delete a row from post_meta table
  96. Creating a “forum” – showing last post or last commented post
  97. How to add data from wordpress tables to new database tables you have created
  98. Replace Unwanted Space in Post Content URL
  99. Retrieve data from external database and insert back in
  100. Change permalinks in posts via SQL

Leave a Comment