0

I'm using an External Table in Oracle 11g. I have a date field in the last column of the file. I parse that in into a TIMESTAMP column in my table definition:

CREATE TABLE e_tbl_one
(
FOPV_KEY            INTEGER,
FACILITY            VARCHAR2(50),
QTY_6HR             NUMBER,
SECURITY_CODE       NUMBER,
LOAD_DATE          TIMESTAMP(0) WITH LOCAL TIME ZONE
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY MYDB_EXTERNAL_TABLE_DATA
     ACCESS PARAMETERS 
       ( records delimited by newline SKIP 1
         NOBADFILE
         NOLOGFILE
         fields terminated by '|' 
         missing field values are null 
         (
          FOPV_KEY,
          FACILITY,
          QTY_6HR,
          SECURITY_CODE,
          LOAD_DATE date 'mm/dd/yyyy hh:mi:ss am'
         )
  )
     LOCATION (MYDB_EXTERNAL_TABLE_DATA:'e_tbl_one.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

This will parse the date 11/10/2015 1:59:37 PM but not 11/10/2015 01:59:37 PM or 11/10/2015 12:45:12 PM

The log file shows:

KUP-04021: field formatting error for field LOAD_DATE
KUP-04026: field too long for datatype
KUP-04101: record 2 rejected in file /dbfs_direct/FS1/MYDB_EXTERNAL_TABLE/e_tbl_one.txt

The data for LOAD_DATE at this time was: 11/11/2015 07:28:36 am. The record in the text file is:

70581692|WS3|308|2048|11/11/2015 07:41:00 am[CRLF]

Why can't this be parsed as LOAD_DATE date 'mm/dd/yyyy hh:mi:ss am'?

Paul
  • 3,725
  • 12
  • 50
  • 86
  • What error do you get? And what patch level are you at? Works fine for me in 11.2.0.4... – Alex Poole Nov 11 '15 at 00:06
  • I'm also on 11.2.0.4.0 (64bit Production). I don't get an error, just no data in the table because all rows fail. I think it something has to do with the fact that it's the last column in the file. – Paul Nov 11 '15 at 14:55
  • 1
    Maybe taking out the `NOLOGFILE` and seeing what it's actually reporting would help. – Alex Poole Nov 11 '15 at 15:00
  • 1
    Possibly a line ending or CRLF issue, but I'd have thought it would error the other way. Can you add exactly what is in your .txt file - at least the first three lines? If you can check the file format (DOS/Unix, character set) that would be useful too. – Alex Poole Nov 11 '15 at 15:43
  • `fields terminated by '|' RTRIM` seems to do the trick – Paul Nov 11 '15 at 15:53
  • I still can't duplicate it, but glad you got it sorted. Maybe it's a platform difference, or patch level, or something. Strange. – Alex Poole Nov 11 '15 at 16:07

0 Answers0