ORACLE ERROR PLS-00302: component must be declared when using with external table

Question

I am new to PLSQL I want to create a procedure which inserts records into a table(AUDIT_LOG ) and simultaneously create a file with the records inserted into the table. The records are checked for some regular expression before being inserted into the file and database table.

The select statement matches records from the external table(EXT_TAB_AUDIT_LOG). The code to create the procedure is as follows:

CREATE OR REPLACE EDITIONABLE PROCEDURE "ABC"."PR_INSERT_AUDIT_LOG_FROM_FILE" (P_IMPORT_TIMESTAMP IN TIMESTAMP,
  IMPORT_FILE_NAME IN VARCHAR2)
AS

    CURSOR CUR_CORRECT_RECORDS_LIST IS 
        SELECT ID                   
        , TRIM(PRIMARY_NAME)
        , TRIM(PRIMARY_VALUE)
        , TERMINAL_DATE        
        , LAND_ID                        
        , P_IMPORT_TIMESTAMP
   FROM EXT_TAB_AUDIT_LOG AL
   LEFT OUTER JOIN VERFIY_NAME VWZ ON VWZ.VERFIY_TABLE_ID = TRIM(AL.VERFIY_TABLE_ID)
   WHERE REGEXP_LIKE (ID,'\d{2}-\d{2}-\d{2}')
   AND   PRIMARY_NAME IS NOT NULL
   AND   PRIMARY_VALUE IS NOT NULL
   AND   TERMINAL_DATE IS NOT NULL
   AND   LAND_ID IS NOT NULL;
   
   
   V_FILE   UTL_FILE.FILE_TYPE;
    V_LOGFILE_FILE_LINE        VARCHAR2(10000);
    I CUR_CORRECT_RECORDS_LIST%ROWTYPE;
   

BEGIN

   INSERT INTO AUDIT_LOG 
      ( ID                   
      , PRIMARY_NAME   
      , PRIMARY_VALUE  
      , TERMINAL_DATE
      , LAND_ID           
      )
   SELECT ID                   
        , TRIM(PRIMARY_NAME)
        , TRIM(PRIMARY_VALUE)
        , TERMINAL_DATE        
        , LAND_ID                        
        , P_IMPORT_TIMESTAMP
   FROM EXT_TAB_AUDIT_LOG AL
   LEFT OUTER JOIN VERFIY_NAME VWZ ON VWZ.VERFIY_TABLE_ID = TRIM(AL.VERFIY_TABLE_ID)
   WHERE REGEXP_LIKE (ID,'\d{2}-\d{2}-\d{2}')
   AND   PRIMARY_NAME IS NOT NULL
   AND   PRIMARY_VALUE IS NOT NULL
   AND   TERMINAL_DATE IS NOT NULL
   AND   LAND_ID IS NOT NULL;
   
   
   V_FILE := UTL_FILE.FOPEN('ABC_AL_IMPORTLOG', IMPORT_FILE_NAME, 'w');
   
   
   
   FOR I IN CUR_CORRECT_RECORDS_LIST
   LOOP


 V_LOGFILE_FILE_LINE := RPAD(NVL(I.ID,' '),8)
                  || RPAD(NVL(I.PRIMARY_NAME,' '),25)
                  || RPAD(NVL(I.PRIMARY_VALUE,' '),40)
                  || RPAD(NVL(to_char(I.TERMINAL_DATE,'yyyy-mm-dd-hh24.mi.ss.ff6'),' '),26)
                  || RPAD(NVL(I.LAND_ID,' '),3);
                  
      UTL_FILE.PUT_LINE(V_FILE,V_LOGFILE_FILE_LINE,true);
   END LOOP;
    
    UTL_FILE.FCLOSE(V_FILE);

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DECLARE
         V_PROCEDURE_NAME  VARCHAR2(30)  := $$PLSQL_UNIT;
         V_SUBJECT         VARCHAR2(255) := 'Error on DB procedure '||V_PROCEDURE_NAME||' on '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss');
         V_BODY            VARCHAR2(10000) := 'Hi all,'||chr(10)||chr(10)||'Procedure '||V_PROCEDURE_NAME||' returned the following error:'||chr(10)||SQLERRM;
      BEGIN
         SEND_MAIL ( 'GENERIC_DB_ERROR', V_SUBJECT,V_BODY);
         RAISE;
      END;  
END PR_INSERT_AUDIT_LOG_FROM_FILE;

/

when ever I try to compile the above procedure I get compile time error as:

PLS-00302: component 'PRIMARY_NAME' must be declared

The table has the column PRIMARY_NAME, but I dont know why I get this error. Can anyone suggest me why I am getting this error with a possible solution?

Answer 1

Cursor's column list should have either column names or their aliases; yours doesn't:

CURSOR CUR_CORRECT_RECORDS_LIST IS 
    SELECT ID                   
    , TRIM(PRIMARY_NAME)     --> should be primary_name
    , TRIM(PRIMARY_VALUE)    --> should be primary_value
    , TERMINAL_DATE