SQL*Loaderで、任意の列に固定の文字列を埋め込む方法(CONSTANT句)

目的

SQL*Loaderの動作確認や検証時にデータに固定値を設定する

マニュアル

Oracle® Database ユーティリティ 19c  
CONSTANTパラメータ  
列に定数値を設定するには、CONSTANTを指定して、その後に値を指定します。  

  CONSTANT  value  

CONSTANTデータは、SQL*Loaderでは、文字入力として認識されます。このデータは、必要に応じてデータベース列のデータ型に変換されます。  
値を引用符で囲むこともできます。特に、指定する値に空白や予約語が含まれている場合は、必ず引用符で囲んでください。  
また、ターゲット列に対して必ず有効な値を指定してください。指定した値が無効な場合は、すべてのレコードが拒否されてしまいます。   

注意点

CONSTANT句を使用して定数をロードするため、データファイルにはデータを含めない

事前準備

事前に下記の内容のSQLファイルを準備

SQL> DROP TABLE T1 PURGE;

Table dropped.

SQL> CREATE TABLE T1 (C1 NUMBER, C2 DATE, C3 VARCHAR2(100) , CONSTRAINT PK_T4 PRIMARY KEY (C1))
PARTITION BY RANGE(C2) SUBPARTITION BY HASH (C1) SUBPARTITIONS 4 (
    PARTITION PRT_202104 VALUES LESS THAN(TO_DATE('202105','YYYYMM')),
    PARTITION PRT_202105 VALUES LESS THAN(TO_DATE('202106','YYYYMM')),
    PARTITION PRT_202106 VALUES LESS THAN(TO_DATE('202107','YYYYMM')),
    PARTITION PRT_202107 VALUES LESS THAN(TO_DATE('202108','YYYYMM'))
);  2    3    4    5    6    7  

Table created.

SQL> CREATE INDEX T1IDX ON T1 (C2) LOCAL
(PARTITION I1
   (SUBPARTITION I1_H1,
    SUBPARTITION I1_H2,
    SUBPARTITION I1_H3,
    SUBPARTITION I1_H4),
 PARTITION I2
   (SUBPARTITION I2_H1,
    SUBPARTITION I2_H2,
    SUBPARTITION I2_H3,
    SUBPARTITION I2_H4),
 PARTITION I3
   (SUBPARTITION I3_H1,
    SUBPARTITION I3_H2,
    SUBPARTITION I3_H3,
    SUBPARTITION I3_H4),
 PARTITION I4
   (SUBPARTITION I4_H1,
    SUBPARTITION I4_H2,
    SUBPARTITION I4_H3,
    SUBPARTITION I4_H4));  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21  

Index created.

SQL> CREATE INDEX T1GIDX ON t1 (C3);

Index created.

SQL> drop sequence s1;

Sequence dropped.

SQL> create sequence s1;

Sequence created.

SQL> declare
  varDate date;
  varSeq NUMBER;
begin
  varDate := to_date('2021-04-01', 'YYYY-MM-DD');
  for j in 1..4 loop
    for i in 1..10000 loop
      select s1.nextval into varSeq from dual;
      insert into T1 values(varSeq, varDate, lpad(to_char(varSeq), 100, '0'));
    end loop;
    commit;
    varDate := add_months(varDate, 1);
  end loop;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15  

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'T1',DEGREE => 10,NO_INVALIDATE=>FALSE);

PL/SQL procedure successfully completed.

SQL> ALTER TABLE T1 TRUNCATE PARTITION PRT_202207;

Session altered.

実行例

SQL> desc T1
 Name    Null?    Type
 ------------------------------
 C1      NOT NULL NUMBER
 C2      NOT NULL DATE
 C3               VARCHAR2(105)

$ cat T1_07.csv
32049,"0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000032049",
32060,"0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000032060",
    :
  <中略>
    :
31867,"0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000031867",
31871,"0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000031871",

$ cat T1_07.ctl
OPTIONS(SKIP=0,ERRORS=0,DIRECT=true)
UNRECOVERABLE
LOAD DATA
BYTEORDERMARK NOCHECK
INFILE 'T1_07.csv'
INSERT
INTO TABLE T1 PARTITION(PRT_202207)
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
C1
,C2 constant '01-JUL-22'
,C3
)

C2(DATE)列に定数を指定する。データファイル(csv)にはC2列のデータを含めない。

$ sqlldr scott/tiger@pdb1 control=T1_07.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jul 29 20:33:35 2022
Version 19.12.0.0.0

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

Path used:      Direct

Load completed - logical record count 10000.

Table T1, partition PRT_202207:
  10000 Rows successfully loaded.

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