sql - 使用外部表的问题

Question

我有一个使用外部表的 PLSQL ABC（示例名称）应用程序。该应用程序的简要概述如下。

该应用程序在 unix 服务器上使用一个日志池，一个文件（包含一些记录）由另一个应用程序每天在某个随机时间放置在日志池中，该文件由 ABC 应用程序由一个 oracle JOB 使用（每 10 次发生一次） min) 并重命名为 Progress.txt（示例名称）。有一些外部表从重命名的文件（Progress.txt）中加载数据并从文件中执行读取操作，根据我们的评估相应地生成一些文件，如Progress.log，Progress.bad（for bad records）对文件执行我们将记录放入不同目录的文件，例如将好记录放入名为“好”目录的文件中，将坏记录或错误记录放入坏目录中。评估完成后，重命名的文件将重新命名为原始名称。

如何在不重命名原始文件的情况下实现此功能？

    --------------------------------------------------------
--  DDL for Procedure PR_IMPORT_AUDIT_LOG
--------------------------------------------------------
set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "ABC"."PR_IMPORT_AUDIT_LOG" 
AS

   --CURSOR CUR_FILE_LIST,  is loaded with correct file names from the external table
   --EXT_TAB_IMPORT_FILE_LIST (This external table searches the logpool dir for any files present)

   --CURSOR CUR_WRONG_FILES_LIST,  is loaded with incorrect file names from the external table
   

   V_INSERT_TIMESTAMP   TIMESTAMP := SYSTIMESTAMP;
BEGIN
    

    --CHECK IF EXISTS NOT ALLOWED FILENAMES
    FOR REC_FILE_LIST IN CUR_WRONG_FILES_LIST
    LOOP
      --SEND ERROR MAIL
      DECLARE
         V_SUBJECT   VARCHAR2(255)   := 'ABC: Import of "'||REC_FILE_LIST.FNAME||'" is raising an error';
         V_BODY      VARCHAR2(10000) := 'Hi,' || chr(10) || chr(10) || 'during the import of the file "'||REC_FILE_LIST.FNAME||'" the following errors have occurred:';
      BEGIN

         V_BODY := V_BODY || chr(10) ||'- Error 020 = Wrong file name';  
         V_BODY := V_BODY || chr(10) || chr(10) || 'Please contact the respective IT system for correction. ';   

         SEND_MAIL('AL_IMPORT_ERROR_HANDLING',V_SUBJECT,V_BODY);
      END;     
      
      --MOVE FILE TO ERROR FOLDER
      UTL_FILE.FRENAME('ABC_AUDITLOG', REC_FILE_LIST.FNAME, 'ABC_IMPORTBAD', REC_FILE_LIST.FNAME, TRUE); 
      
    END LOOP;
    
    FOR REC_FILE_LIST IN CUR_FILE_LIST -- This cursor contains all valid file name. How can I make the filename in this iteration avaialble to the external table to so that they know current file name under process.
    LOOP
      --RENAME FILE IN inProgress.txt
      UTL_FILE.FRENAME('ABC_AUDITLOG', REC_FILE_LIST.FNAME, 'ABC_AUDITLOG', 'inProgress.txt', TRUE);  
      
      --Perform some business logic
      
      --remove inProgress.bad
      BEGIN
         UTL_FILE.FREMOVE('ABC_AUDITLOG','inProgress.bad');
      EXCEPTION
         WHEN OTHERS
         THEN
            IF SQLCODE = -29283 -- suppresses no file found exception
            THEN
               NULL;
            ELSE
               RAISE;
            END IF;
      END;         
      --remove inProgress.log      
      BEGIN
         UTL_FILE.FREMOVE('ABC_AUDITLOG','inProgress.log');
      EXCEPTION
         WHEN OTHERS
         THEN
            IF SQLCODE = -29283 -- suppresses no file found exception
            THEN
               NULL;
            ELSE
               RAISE;
            END IF;
      END;   
      
      COMMIT;
      
    END LOOP;

EXCEPTION
   WHEN OTHERS
   --send some error email      
END;

/

游标 CUR_FILE_LIST 包含所有有效的文件名。如何使此迭代中的文件名可用于外部表，以便他们知道正在处理的当前文件名而不重命名原始文件。

使用重命名文件的外部表示例（inProgress.txt）

--------------------------------------------------------
--  DDL for Table EXT_TAB_AUDIT_LOG
--------------------------------------------------------

  CREATE TABLE ABC.EXT_TAB_AUDIT_LOG 
   (    --column definitions
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ABC_AUDITLOG
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
  
  STRING SIZES ARE IN CHARACTERS       
            LOGFILE 'inProgress.log'
            BADFILE 'inProgress.bad'
            DISCARDFILE 'inProgress.dis'
            FIELDS 
               (    
               --field names from the file
               )               
               
                         )
      LOCATION
       ( 'inProgress.txt'
       )
    )
   REJECT LIMIT UNLIMITED ;

问题在于： • 当 AL 导入失败时，没有一条记录被导入。•错误增量文件为空•原来的AL文件消失了！它没有移动到子文件夹（好的或坏的文件夹）。在另一个 10 分钟程序开始后，无法访问原始文件名（已重命名为 inProgress.txt）。

重命名是一个问题，因为在失败的情况下会丢失原始文件名，并且无法检查丢失了哪个文件。

Procedure PR_INSERT_AUDIT_LOG_FROM_FILE returned the following error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04039: unexpected error while trying to find file inProgress.txt in /opt/app/logpool/

标签： sqlplsqloracle19cexternal-tables