PLSQL : ORA-00904 invalid identifier in Cursor Select

Question

I have EXTERNAL TABLE created as below.

CREATE TABLE EXTERNAL_TABLE
( "col1"           DATE
, "col2"           DATE
) ORGANIZATION external
  ( TYPE oracle_loader
    DEFAULT DIRECTORY  INPUT_DIRECTORY
    ACCESS  PARAMETERS
    ( RECORDS DELIMITED  BY NEWLINE
      LOGFILE 'Some.log'
      BADFILE 'Some.bad'
      FIELDS  TERMINATED BY "|" LDRTRIM
      ( "col1" DATE
      , "col2" DATE MASK "MM/DD/YYYY"
      )
    )
    LOCATION ('SomeDat.dat')
  )
  PARALLEL 1
  REJECT LIMIT UNLIMITED
/

I am populating the data loaded into external table into a transaction table for further processing.

PROCEDURE upload
IS
lv_post_dt DATE;
TYPE dataTblType IS TABLE OF My_Transaction_Table%ROWTYPE;
lv_dataTbl    dataTblType;
    
CURSOR externalCur IS
      SELECT lv_post_dt col1,
             col2  --ORA-00904 invalid identifier is encountered here
      FROM  EXTERNAL_TABLE;
      
BEGIN
lv_post_dt := SYSTIMESTAMP;

OPEN externalCur;
    LOOP
      FETCH externalCur BULK COLLECT
        INTO lv_dataTbl LIMIT 25000;
    
      EXIT WHEN lv_dataTbl.COUNT = 0;
    
      FOR i IN 1 .. lv_dataTbl.COUNT LOOP
        --Insert recordsinto My_Transaction_Table
      END LOOP;
CLOSE externalCur;

END upload;

I tried below as well.

CURSOR externalCur IS
      SELECT lv_post_dt col1,
             ext.col2  --ORA-00904 invalid identifier is encountered here
      FROM  EXTERNAL_TABLE ext;
  
CURSOR externalCur IS
      SELECT lv_post_dt col1,
             EXTERNAL_TABLE.col2  --ORA-00904 invalid identifier is encountered here
      FROM  EXTERNAL_TABLE; 


  

Even though col2 belongs to EXTERNAL_TABLE, why I am getting invalid identifier error and how to fix this?

When I write lv_post_dt col1 then why it works?

Answer 1

That's because of unfortunate double quotes. Why did you use them? They bring nothing but problems.

This is what you did (disregard the fact that this isn't an external table):

SQL> CREATE TABLE EXTERNAL_TABLE
  2  ( "col1"           DATE
  3  , "col2"           DATE
  4  );

Table created.

Can you select everything from it? Sure:

SQL> select * from external_table;

no rows selected

Can you select only col2? Not really; here's your ORA-00904: invalid identifier:

SQL> select col2 from external_table;
select col2 from external_table
       *
ERROR at line 1:
ORA-00904: "COL2": invalid identifier

Why is that so? Because of double quotes. Once you use them and have mixed (or lower) letter case while naming objects, you have to use double quotes and match letter case every time:

SQL> select "col2" from external_table;

no rows selected

SQL>

---

What should you do? In my opinion, drop the table and recreate it, but this time omit double quotes, everywhere.