'PLSQL : ORA-00904 invalid identifier in Cursor Select [duplicate]

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source