SQLLDR - Conditional load (end of line)-CodePudding

This is the control file that I am trying to load using SQL Loader. However, I am able to only load 1 record and cannot load TRL (the last line of data file) into LTD column. I need to be able to load "TRL 02 0001 56778 34 999 111" value into LTD column. Appreciate your help on this.

Sample Data:

  HDR
  12|45|3|SUE|US
  TRL 02 0001 56778 34 999 111

Control File:

OPTIONS (SKIP=1)
LOAD DATA 
INFILE '*.TXT'
BADFILE 'A.bad'
  INTO TABLE A
  REPLACE
  WHEN (1:3) != 'TRL'
  FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
  (
    ID   FILLER ,
    LTD , 
    CAGE , 
    SUPP   FILLER ,
    CODE ,
    NAME , 
    DBA_NAME   FILLER , 
    CNTRY_CODE ,
    STATUS CONSTANT "U",
    RECORD_ID "S.nextval"
    )
 INTO TABLE A
 REPLACE
 WHEN (1:3) = 'TRL'
 (
    LTD    CHAR(300),
    STATUS CONSTANT "U",
    RECORD_ID "S.nextval"
  );

CodePudding user response：

When you're inserting into multiple tables, you have to use position (otherwise it won't work). Also, the 2nd table lacks in trailing nullcols.

Therefore, for a sample target table and a sequence:

SQL> CREATE TABLE a
  2  (
  3     id           NUMBER,
  4     ltd          VARCHAR2 (30),
  5     cage         VARCHAR2 (5),
  6     supp         NUMBER,
  7     code         VARCHAR2 (5),
  8     name         VARCHAR2 (5),
  9     dba_name     NUMBER,
 10     cntry_code   VARCHAR2 (5),
 11     status       VARCHAR2 (1),
 12     record_id    NUMBER
 13  );

Table created.

SQL> CREATE SEQUENCE s;

Sequence created.

SQL>

control file looks like this:

OPTIONS (SKIP=1)
LOAD DATA 
INFILE *                          --> modified this (as I have sample data in the control file)
BADFILE 'A.bad'
  INTO TABLE A
  REPLACE
  WHEN (1:3) != 'TRL'
  FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
  (
    ID   FILLER ,
    LTD , 
    CAGE , 
    SUPP   FILLER ,
    CODE ,
    NAME , 
    DBA_NAME   FILLER , 
    CNTRY_CODE ,
    STATUS CONSTANT "U",
    RECORD_ID "S.nextval"
    )
 INTO TABLE A
 REPLACE
 WHEN (1:3) = 'TRL'
 TRAILING NULLCOLS                --> added this
 (
    LTD    POSITION(1) CHAR(300), --> added POSITION
    STATUS CONSTANT "U",
    RECORD_ID "S.nextval"
  )
  
begindata
HDR
12|45|3|SUE|US
TRL 02 0001 56778 34 999 111

Testing:

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

SQL*Loader: Release 18.0.0.0.0 - Production on Pet Lip 10 08:37:05 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
Commit point reached - logical record count 2

Table A:
  1 Row successfully loaded.

Table A:
  1 Row successfully loaded.

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

SQL> select * from a;

        ID LTD                            CAGE        SUPP CODE  NAME    DBA_NAME CNTRY S  RECORD_ID
---------- ------------------------------ ----- ---------- ----- ----- ---------- ----- - ----------
           45                             3                US                           U          1
           TRL 02 0001 56778 34 999 111                                                 U          2

SQL>

Looks OK to me.