0 Replies Latest reply on Mar 19, 2020 7:21 PM by epipko

    external table with preprocessor error

    epipko

      Oracle 19.3 on Win2019

       

      --as SYS

      CREATE DIRECTORY EXT_DIR AS 'd:\EXT_DIR';
      GRANT READ, WRITE ON DIRECTORY EXT_DIR TO prod_user;
      
      CREATE DIRECTORY BIN_DIR AS 'd:BIN_DIR';
      GRANT READ, WRITE ON DIRECTORY BIN_DIR TO prod_user;
      GRANT EXECUTE ON DIRECTORY BIN_DIR TO prod_user;
      
      CREATE OR REPLACE DIRECTORY WMS_INTERFACE AS  'd:\oraload\wms_interface';
      GRANT READ, WRITE ON DIRECTORY WMS_INTERFACE TO prod_user;
      
      

       

      -- as PROD_USER

      CREATE TABLE dir_list
        ( file_name VARCHAR2(255))
        ORGANIZATION EXTERNAL
        (
         TYPE ORACLE_LOADER
         DEFAULT DIRECTORY EXT_DIR
         ACCESS PARAMETERS
         (
           RECORDS DELIMITED BY NEWLINE
           PREPROCESSOR BIN_DIR: 'list_files.bat'
           FIELDS TERMINATED BY WHITESPACE
      )
      LOCATION ('sticky.txt')
      )
      REJECT LIMIT UNLIMITED;
      

       

      -- list_files.bat (in bin_dir)

      @echo off
      dir D:\ORALOAD\WMS_INTERFACE /b
      

       

      The files are generated on remote server and moved onto local db server. Bat file reads the directory for file names. That part works.

       

      I have an external table that needs to read files in wms_interface directory.

       

      CREATE TABLE prod_user.WM_TRANS_CART_DTL
      (
        INVC_NBR          NUMBER(9),
        CAR_NBR              VARCHAR2(20 BYTE),
        CAR_SEQ_NBR          NUMBER(5)
        )
      ORGANIZATION EXTERNAL
        (  TYPE ORACLE_LOADER
           DEFAULT DIRECTORY WMS_INTERFACE
           ACCESS PARAMETERS 
             ( 
               FIELDS LRTRIM
               MISSING FIELD VALUES ARE NULL
                (
                INVC_NBR            position (001:009) char(9),
          CAR_NBR             position (010:029) char(20),
          CAR_SEQ_NBR         position (030:034) char(5)
          )
             )
           LOCATION (WMS_INTERFACE:'OCD012039')
        )
      REJECT LIMIT UNLIMITED;
      

       

      When I run

      ALTER TABLE WM_TRANS_CART_DTL LOCATION ('OCD012039'); 
      select * FROM prod_user.WM_TRANS_CART_DTL;
      

       

      I am getting this error:

      ORA-29913: error in executing ODCIEXTTABLEOPEN callout

      ORA-29400: data cartridge error

      KUP-04005: error while accessing file d:\oraload\wms_interface\WM_TRANS_CART_DTL_10188_8052.log

       

      Here is the .log file that sits in EXT_DIR

       

      LOG file opened at 03/19/20 10:54:25
      Total Number of Files=1
      Data File: sticky.txt
      Log File: DIR_LIST_10188_2904.log
       LOG file opened at 03/19/20 10:54:25
      KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.
      Bad File: DIR_LIST_10188_2904.bad
      Field Definitions for table DIR_LIST
        Record format DELIMITED BY NEWLINE
        Data in file has same endianness as the platform
        Rows with all null fields are accepted
      
        Fields in Data Source: 
          FILE_NAME                       CHAR (255)
            Terminated by whitespace
            Trim whitespace same as SQL Loader
      

       

       

      What am I doing wrong?

      Thanks,