Ora-00904: Invalid Identifier

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'

Why do I have ORA-00904 even when the column is present?

ORA-00904-invalid identifier errors are frequently caused by case-sensitivity issues. Normally, Oracle tables and columns are not case sensitive and cannot contain punctuation marks and spaces. But if you use double quotes to create a quoted identifier, that identifier must always be referenced with double quotes and with the correct case. For example:

create table bad_design("goodLuckSelectingThisColumn  " number);

ORA-00904 Invalid Identifier: But Column Name is Valid

Strings in Oracle are to be enclosed into single quotes, not double ones.

Therefore, almost the whole WHERE clause is wrong, should've been

WHERE
(((PRODDTA.F4211.SDLNTY != 'T'))) AND
(((PRODDTA.F4211.SDADDJ IS NOT NULL))) AND
(((PRODDTA.F4211.SDLTTR < '980'))) AND
(((PRODDTA.F4211.SDLITM != 'CFO247'))) AND
(((PRODDTA.F4211.SDSHAN != '70789701'))) AND
(((PRODDTA.F4211.SDSHAN != '70789702'))) AND
(((PRODDTA.F4211.SDSHAN != '70626701')))

Error you got is kind of misleading; as you enclosed 70626701 into double quotes, Oracle presumed it was a column name, while it was not.

How to solve ORA-00904: : invalid identifier while creating table?

Use Oracle's datatypes. Define number columns with 0 precision to make them integers. Use varchar2 rather than varchar (although Oracle will change it for you).

create table eas_citizen_text_info (
    UID number(12,0) not null Primary key,
    Gender varchar2(15),
    First_name varchar2(30),
    Middle_name varchar2(30),
    Last_name varchar2(30),
    DOB date Not null,
    Age number(3,0),
    Caste varchar2(30),
    Martial_status varchar2(15),
    Address varchar2(255),
    House_No_Bldg_Apt varchar2(75),
    Street_Road_Lane varchar2(75),
    Landmark varchar2(75),
    Area_locality_sector varchar2(75),
    Village_Town_City varchar2(75),
    Post_office varchar2(75),
    District varchar2(25),
    Sub_district varchar2(25),
    Pincode number(15,0),
    State varchar2(30),
    Father_name varchar2(200),
    Mother_name varchar2(200),
    Guardian_name varchar2(200)
);

However we still get the error:

  UID number(12,0) not null Primary key,
    *
ERROR at line 2:
ORA-00904: : invalid identifier


SQL>

But Oracle has been generous enough to point out which column is the cause of the error. If we check the documentation we find UID in the list of SQL reserved keywords. Find that here.

The solution is simple: change UID to something safe such as CITIZEN_UID and the table can be created:

SQL> create table eas_citizen_text_info (
  2      citizen_UID number(12,0) not null Primary key,
  3      Gender varchar2(15),
  4      First_name varchar2(30),
  5      Middle_name varchar2(30),
  6      Last_name varchar2(30),
  7      DOB date Not null,
  8      Age number(3,0),
  9      Caste varchar2(30),
 10      Martial_status varchar2(15),
 11      Address varchar2(255),
 12      House_No_Bldg_Apt varchar2(75),
 13      Street_Road_Lane varchar2(75),
 14      Landmark varchar2(75),
 15      Area_locality_sector varchar2(75),
 16      Village_Town_City varchar2(75),
 17      Post_office varchar2(75),
 18      District varchar2(25),
 19      Sub_district varchar2(25),
 20      Pincode number(15,0),
 21      State varchar2(30),
 22      Father_name varchar2(200),
 23      Mother_name varchar2(200),
 24      Guardian_name varchar2(200)
 25  );

Table created.

SQL>

ORA-00904: \d+$: invalid identifier

Single quotes are used for string literals; double quotes are used for identifiers.

Start with a static SQL statement that works:

select regexp_substr(table_name, '\d+$') result
from user_tables
where regexp_like(table_name, '\d+$')

Then to make it dynamic you have to make the whole statement a string; you can either escape the existing quotes by doubling them up - note that is two single-quote characters, not one double-quote character:

EXECUTE IMMEDIATE 'select regexp_substr(table_name, ''\d+$'') result
  from user_tables
  where regexp_like(table_name, ''\d+$'')'
into splitstring;

or use the alternative quoting mechanism:

EXECUTE IMMEDIATE q'^select regexp_substr(table_name, '\d+$') result
  from user_tables
  where regexp_like(table_name, '\d+$')^'
into splitstring;

You don't need dynamic SQL here though, you can just do:

select regexp_substr(table_name, '\d+$') result
into splitstring
from user_tables
where regexp_like(table_name, '\d+$');

db<>fiddle - with a single matching table; if there are none, or more than one, then your query into a scalar variable will fail. Depending on what you're doing with the numeric part of the table name(s), you might want to use a cursor and loop over all the extracted values. That's a separate issue though.