2 Replies Latest reply on Aug 7, 2019 3:39 AM by Gaz in Oz

    ORACLE_LOADER external table skip issue

    JeeebeZ

      This is my table:

      -- Create table
      create table aug.ext_aug_data_1
      (
        year             NUMBER,
        fund             VARCHAR2(8),
        area             VARCHAR2(8),
        code             VARCHAR2(8),
        metric           VARCHAR2(30),
        amount           NUMBER(14,2)
      )
      organization external
      (
        type ORACLE_LOADER
        default directory AUG_DIR
        access parameters 
        (
          records delimited by '\r\n'
          badfile EPT_DIR:'aug_data_1.bad'
          skip 1
          fields  terminated by ','
          optionally enclosed by '"'
          MISSING FIELD VALUES ARE NULL
          REJECT ROWS WITH ALL NULL FIELDS
        )
        location (AUG_DIR:'aug_data_1.csv')
      )
      reject limit UNLIMITED;
      

       

      With the table we are on 12.2.0.1.0

       

      When I pass in a CSV of

      YEAR,FUND,AREA,CODE,METRIC,AMOUNT
      2019,132,LEVEL1,830,Base,936.84
      2019,132,LEVEL2,830,Base,351.62
      

       

      The aug_data_1.bad file comes back with

      T
      

       

      and the log file says

      error processing column YEAR in row 2 for datafile aug_data_1.csv
      ORA-01722: invalid number
      

       

      The T is the last character of the first row. Is this a known bug? I cant find anything referencing the skip function causing issues.

      I changed my skip 1 to skip 2 I still got 2 lines back but the first line only contained the number 4 (last character of 2nd row)

       

      I changed to unix endings, and I got back NT (skip 1) and 84 (skip 2). So, it looks like the skip isn't skipping the last 3 characters of the row for some reason? Anyone know how to resolve this?