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"
);
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.