sqlloader skips characters during data load into table-CodePudding

using the control file below to load data

LOAD DATA
  INFILE '/c/Transaction.txt'

  INTO TABLE tab1 APPEND WHEN (1:1) = 'D'
  (RUN_ID               "RUN_ID_SEQ.NEXTVAL"
  ,RUN_DATE_TIME        "SYSDATE"   
  )
  INTO TABLE tab2 TRUNCATE WHEN(1:1) <> 'D'
  FIELDS TERMINATED BY '|'  TRAILING NULLCOLS
  (

     DEALER_NUMBER
    ,TRAN_CODE
    ,TRAN_AMOUNT         "TO_NUMBER(:PL818_TRAN_AMOUNT,'999999.99')"
    ,TRAN_DATE           "TO_DATE(:PL818_TRAN_DATE,'DD-MM-YYYY')"
  )

This is the data set

DEALER_ID|TRAN_TYPE_CODE|TRAN_AMOUNT|TRAN_DATE

203113|34|1000.50|12-07-2022

No errors during load. Data loads correctly into the first table but first two characters are skipped when loading data into second table, table data looks like this. What could be causing this?

3113    34  1000.5  12-JUL-22

Skips "20"

CodePudding user response：

Sample tables:

SQL> desc tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RUN_ID                                             NUMBER
 RUN_DATE_TIME                                      DATE

SQL> desc tab2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEALER_NUMBER                                      NUMBER
 TRAN_CODE                                          NUMBER
 TRAN_AMOUNT                                        NUMBER
 TRAN_DATE                                          DATE

SQL>

Control file; note position(1) when loading into tab2. You must use it when loading data into different tables, using the same control file:

LOAD DATA
  INFILE *
  INTO TABLE tab1 APPEND WHEN (1:1) = 'D'
  (RUN_ID               "RUN_ID_SEQ.NEXTVAL"
  ,RUN_DATE_TIME        "SYSDATE"   
  )
  INTO TABLE tab2 TRUNCATE WHEN(1:1) <> 'D'
  FIELDS TERMINATED BY '|'  TRAILING NULLCOLS
  (

     DEALER_NUMBER       position(1)                          --> here
    ,TRAN_CODE
    ,TRAN_AMOUNT         "TO_NUMBER(:TRAN_AMOUNT,'999999.99')"
    ,TRAN_DATE           "TO_DATE(:TRAN_DATE,'DD-MM-YYYY')"
  )
  
BEGINDATA
203113|34|1000.50|12-07-2022

Loading session and the result:

SQL> $sqlldr scott/tiger@orcl control=test42.ctl log=test42.log

SQL*Loader: Release 18.0.0.0.0 - Production on Sri Srp 13 08:28:19 2022
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table TAB1:
  0 Rows successfully loaded.

Table TAB2:
  1 Row successfully loaded.

Check the log file:
  test42.log
for more information about the load.

SQL> select * from tab2;

DEALER_NUMBER  TRAN_CODE TRAN_AMOUNT TRAN_DATE
------------- ---------- ----------- ----------
       203113         34      1000,5 12-07-2022
       --
       here's your missing "20"
SQL>