Using SQL*Loader with a static column

I have a table in Oracle that has 6 columns and a comma-delimited text file that has 5 "columns"

I need to put the year in the first column of each row. The year is not in the text file. It will be the same for each row that is appended to the table. This process will run one a year.

An example text file:

2541-1,36,00000,Some Words Here,00000000
1425-4,25,15245,Some Other Words,45786524
6548-8,12,30210,Different Words,885411246

How do I set the first column to the year using SQL*Loader?

The result I want:

Column A	Column B	Column C	Column D	Column E	Column F
2023	2541-1	36	00000	Some Words Here	00000000
2023	1425-4	25	15245	Some Other Words	45786524
2023	6548-8	12	30210	Different Words	885411246

Solution

One option is to "load" a constant.

Table is - initially - empty:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLA                                               NUMBER
 COLB                                               VARCHAR2(7)
 COLC                                               NUMBER
 COLD                                               VARCHAR2(5)
 COLE                                               VARCHAR2(25)
 COLF                                               VARCHAR2(10)
 COLG                                               NUMBER
 COLH                                               NUMBER
 COLI                                               NUMBER

SQL> select * From test;

no rows selected

Control file:

load data
infile *
replace
into table test
fields terminated by ','
trailing nullcols
( cola constant "2023",
  colb,
  colc,
  cold,
  cole,
  colf,
  colg,
  colh,
  coli
)

begindata
2541-1,36,00000,Some Words Here,00000000,42564,63514,78546
1425-4,25,15245,Some Other Words,45786524,452654,156324,185647
6548-8,12,30210,Different Words,885411246,251624,846102,152026

Loading session:

SQL> $sqlldr scott/tiger@pdb1 control=test16.ctl log=test16.log

SQL*Loader: Release 21.0.0.0.0 - Production on Mon Mar 18 19:34:15 2024
Version 21.3.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 3

Table TEST:
  3 Rows successfully loaded.

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

Result:

SQL> select * From test;

      COLA COLB          COLC COLD  COLE                      COLF             COLG       COLH       COLI
---------- ------- ---------- ----- ------------------------- ---------- ---------- ---------- ----------
      2023 2541-1          36 00000 Some Words Here           00000000        42564      63514      78546
      2023 1425-4          25 15245 Some Other Words          45786524       452654     156324     185647
      2023 6548-8          12 30210 Different Words           885411246      251624     846102     152026

SQL>

Another option is to set default value for `cola` at time of creating that table:

SQL> drop table test;

Table dropped.

SQL> create table test
  2  (cola number default extract(year from sysdate),
  3   colb varchar2(7),
  4   colc number,
  5   cold varchar2(5),
  6   cole varchar2(25),
  7   colf varchar2(10),
  8   colg number,
  9   colh number,
 10   coli number);

Table created.

Control file: the same as previously, just remove cola line entirely:

<snip>
trailing nullcols
( colb,
  colc,
<snip>

Loading session and result:

SQL> $sqlldr scott/tiger@pdb1 control=test16.ctl log=test16.log

SQL*Loader: Release 21.0.0.0.0 - Production on Mon Mar 18 19:38:09 2024
Version 21.3.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 3

Table TEST:
  3 Rows successfully loaded.

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

SQL> select * From test;

      COLA COLB          COLC COLD  COLE                      COLF             COLG       COLH       COLI
---------- ------- ---------- ----- ------------------------- ---------- ---------- ---------- ----------
      2024 2541-1          36 00000 Some Words Here           00000000        42564      63514      78546
      2024 1425-4          25 15245 Some Other Words          45786524       452654     156324     185647
      2024 6548-8          12 30210 Different Words           885411246      251624     846102     152026

SQL>

Yet another option is to insert "calculated" value.

In that case, that calculated column has to be last in control file.

SQL> drop table test;

Table dropped.

SQL> create table test
  2  (cola number,
  3   colb varchar2(7),
  4   colc number,
  5   cold varchar2(5),
  6   cole varchar2(25),
  7   colf varchar2(10),
  8   colg number,
  9   colh number,
 10   coli number);

Table created.