My Oracle Support Banner

All Records Containing Date Columns Are Rejected In The bad File When Using LKM File to External Table Or LKM File to Oracle (SQLLDR) (Doc ID 560902.1)

Last updated on JULY 22, 2022

Applies to:

Oracle Data Integrator - Version 10.1.3.4.0 to 10.1.3.4.8 [Release 10gR3]
Information in this document applies to any platform.

Symptoms

When using either :

to load data from flat files to Oracle Datastores, then the date columns with a date format are not interpreted and all records end up in the .bad file.

How to reproduce

  1. Create a file (i.e.: file.txt) with contents:

    my_column;my_date_column;
    1;19-1-2008;
    2;19-2-2008;
     
  2. Create a target table with the same structure (i.e.: connected in sqlplus with the work repository user (ODIW)):

  3. Create a Datastore for the file with following characteristics:
    records separated by a line break and fields by a semicolon. Use the reverse button to get the appropriate columns and add a date format for my_date_column: dd-MM-yyyy. 
  4. Create a Datastore for the table:
    on the Model and DataStore created, just reverse it from the schema where you created it. 
  5. Import "LKM File to Oracle (EXTERNAL TABLE)" and remove the NOLOGGING statement from the create table template (<Bug:6774336>) to avoid runtime errors. 
  6. Create a mapping from the file to the table (for example, using the auto mapping), and select the imported LKM. 
  7. Run the mapping. While it should work without any error being returned, no rows make it into the target table.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.