Oracle SQL Loader Control File: Upload header in different table using WHEN

Question

I receive a file that has:

• an header, starting with the string header followed by some columns;
• a body: all the other lines after the first one (the header).

I know that only the header will begin with the string 'HEADER' so I thought I could check it using a WHEN condition in my control file, this is necessary because I have to insert the content of the header in a table and the body in another table, so I created the following control file:

OPTIONS (READSIZE=512000000,bindsize=512000000,rows=64000, ERRORS=100000)
LOAD DATA
characterset UTF8 length semantics byte
REPLACE
INTO TABLE <TABLE_1>
WHEN (1:6) != 'HEADER'
fields terminated by "|"
TRAILING NULLCOLS
(
  ANNO                 INTEGER EXTERNAL,
  COD_PRODOTTO         CHAR(12) "TRIM (:COD_PRODOTTO)",
  DESCRIZIONE_PRODOTTO CHAR(254) "TRIM (SUBSTRB(:DESCRIZIONE_PRODOTTO,1,254))",
  COD_PADRE            CHAR(12) "TRIM (:COD_PADRE)",
  FOGLIA               CHAR(1) "TRIM (:FOGLIA)",
  COD_STRUTTURA        CHAR(10)  "TRIM (:COD_STRUTTURA)",
  TIPO_STRUTTURA       CHAR(10) "TRIM (:TIPO_STRUTTURA)"
)
INTO TABLE <TABLE_2>
WHEN (1:6) = 'HEADER'
fields terminated by "|"
TRAILING NULLCOLS
(
  HEADER               FILLER,
  ANNO                 INTEGER EXTERNAL,
  COD_STRUTTURA        CHAR(12)  "TRIM (:COD_STRUTTURA)",
  TIPO_STRUTTURA       CHAR(12) "TRIM (:TIPO_STRUTTURA)",
  TIPO_INVIO           CHAR(5) "TRIM (:TIPO_INVIO)",
  DATA_RICEZIONE       "sysdate"
)

but I obtain that the body rows are correctly inserted but not the header's ones (it says that "ANNO" is an invalid number, but is not).

The "cool" part is that if I invert the wheres the header is inserted and not the body (invalid "ANNO" again).

Looking at the log it seems it read the structure of the two tables ignoring the wheres, like it is the content of a single table, because I have:

 Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ANNO                                FIRST     *   |       CHARACTER
COD_PRODOTTO                         NEXT    12   |       CHARACTER
    SQL string for column : "TRIM (:COD_PRODOTTO)"
DESCRIZIONE_PRODOTTO                 NEXT   254   |       CHARACTER
    SQL string for column : "TRIM (SUBSTRB(:DESCRIZIONE_PRODOTTO,1,254))"
COD_PADRE                            NEXT    12   |       CHARACTER
    SQL string for column : "TRIM (:COD_PADRE)"
FOGLIA                               NEXT     1   |       CHARACTER
    SQL string for column : "TRIM (:FOGLIA)"
COD_STRUTTURA                        NEXT    10   |       CHARACTER
    SQL string for column : "TRIM (:COD_STRUTTURA)"
TIPO_STRUTTURA                       NEXT    10   |       CHARACTER
    SQL string for column : "TRIM (:TIPO_STRUTTURA)"
Table <TABLE_2>, loaded when 1:6 = 0X484541444552(character 'HEADER')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
HEADER                               NEXT     *   |       CHARACTER
  (FILLER FIELD)
ANNO                                 NEXT     *   |       CHARACTER
COD_STRUTTURA                        NEXT    12   |       CHARACTER
    SQL string for column : "TRIM (:COD_STRUTTURA)"
TIPO_STRUTTURA                       NEXT    12   |       CHARACTER
    SQL string for column : "TRIM (:TIPO_STRUTTURA)"
TIPO_INVIO                           NEXT     5   |       CHARACTER
    SQL string for column : "TRIM (:TIPO_INVIO)"
DATA_RICEZIONE                       NEXT     *   |       CHARACTER
    SQL string for column : "sysdate"

So the second one starts with NEXT and not with FIRST, so I think all the columns are shifted.

I tried to add "POSITION(1) FILLER" for the second table but I obtain other errors about the types.

What is wrong in my control file?

Answer 1

Here's a simplified example of what you're doing. Why simplified? Because you didn't provide test case, and I didn't feel like typing that much.

Sample tables:

SQL> create table t1_header
  2    (header               varchar2(10),
  3     anno                 number,
  4     cod_struttura        varchar2(10)
  5    );

Table created.

SQL> create table t2_body
  2    (anno                 number,
  3     cod_prodotto         varchar2(12),
  4     descrizione_prodotto varchar2(10)
  5    );

Table created.

SQL>

Control file; it contains data to be loaded. I'm loading header table first, body next:

load data
infile *
replace
into table t1_header
when (1:6) = 'HEADER'
fields terminated by "|"
trailing nullcols
(header   filler,
 anno,
 cod_struttura
)

into table t2_body
when (1:6) <> 'HEADER'
fields terminated by "|"
trailing nullcols
(anno                  position(1),
 cod_prodotto,
 descrizione_prodotto
)

begindata
HEADER|2021|test 1
2019|test2|descr t2
2020|test3|descr t3

Loading session & the result:

SQL> $sqlldr scott/tiger control=test1.ctl log=test1.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Ruj 27 19:27:14 2021

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

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * From t1_header;

HEADER           ANNO COD_STRUTT
---------- ---------- ----------
                 2021 test 1

SQL> select * From t2_body;

      ANNO COD_PRODOTTO DESCRIZION
---------- ------------ ----------
      2019 test2        descr t2
      2020 test3        descr t3

SQL>

---

The way I see it, the key moment is to instruct SQL*Loader to reprocess the same file again. How? By setting the position:

(anno                  position(1),

As you can see, both tables were loaded, containing all sample rows.