SQLLOADERで桁数エラーとなる、よくあるパターンを紹介します。
SQLLOADERで255バイトを超えるデータを扱う場合、CTLファイルに桁数を定義していないと、「データファイルのフィールドが最大長を超えています」というエラーが発生します。
例えばテーブルの桁数はVARCHAR2(1000)で定義しているのに、300文字のデータをSQLLOADERで取り込めなくて、「なんで！？」となるパターンがよくあります。
SQLLOADERで桁数エラーとなる例
CTLファイル（桁数の定義なし）
OPTIONS (
SKIP = 0 -- スキップ無し（全件取り込む）
, DIRECT = TRUE -- ダイレクト・パス・ロード（制約無視の高速取り込み）
, ROWS = 1000 -- 1000件単位で登録
)
LOAD DATA
INFILE '.\data\TB_SAMPLE.tsv'
BADFILE '.\data\TB_SAMPLE.bad'
DISCARDFILE '.\data\TB_SAMPLE.dsc'
TRUNCATE INTO TABLE TB_SAMPLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
ID
, NAME
)
SQLLOADERコマンド実行
コマンド実行後はデータが登録されず、「0 行は正常にロードされました。」となります。
sqlldr TEST/TEST@localhost/orclpdb CONTROL='./CTL/TB_SAMPLE.CTL' LOG='./LOG/TB_SAMPLE.log' SQL*Loader: Release 12.2.0.1.0 - Production on 木 3月 5 09:45:26 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. 使用パス: ダイレクト ロードが完了しました - 論理レコード件数1。 表TB_SAMPLE:0 行は正常にロードされました。確認するログ・ファイル: ./LOG/TB_SAMPLE.log ロードの詳細を参照してください。
ログファイル
ログファイルを見ると「データファイルのフィールドが最大長を超えています」と表示されています。
SQL*Loader: Release 12.2.0.1.0 - Production on 木 3月 5 09:45:26 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. 制御ファイル: ./CTL/TB_SAMPLE.CTL データファイル.\data\TB_SAMPLE.tsv 不良ファイル: .\data\TB_SAMPLE.bad 廃棄ファイル: .\data\TB_SAMPLE.dsc (すべて廃棄できます) ロード数: ALL スキップ数: 0 許容エラー数: 50 継続文字: 指定なし 使用パス: ダイレクト 表TB_SAMPLE、 ロード済 すべての論理レコードから この表に対する有効な挿入オプション: TRUNCATE TRAILING NULLCOLSオプションは有効です。 列名 位置 長さ 用語暗号化データ型 ------------------------------ ---------- ----- ---- ---- --------------------- ID FIRST * WHT CHARACTER NAME NEXT * WHT CHARACTER レコード1: 拒否されました。- 表TB_SAMPLE,列NAMEでエラーが発生しました。データファイルのフィールドが最大長を超えています表TB_SAMPLE: 0 行は正常にロードされました。 1 行はデータ・エラーのためロードされませんでした。 0 行は、すべてのWHEN句が失敗したためロードされませんでした。 0 行はすべてのフィールドがNULLであったためロードされませんでした。 バインド配列サイズはダイレクト・パスで使用されません。 列配列の行数: 1000 ストリーム・バッファのバイト数: 256000 読取りバッファのバイト数: 1048576 スキップされた論理レコードの合計: 0 読み込まれた論理レコードの合計: 1 拒否された論理レコードの合計: 1 廃棄された論理レコードの合計: 0 SQL*Loaderのメイン・スレッドによってロードされたストリーム・バッファの合計: 0 SQL*Loaderのロード・スレッドによってロードされたストリーム・バッファの合計: 0 実行開始木 3月 05 09:45:26 2020 実行終了木 3月 05 09:45:27 2020 実行時間: 00: 00: 00.28 CPU時間 : 00: 00: 00.17
CTLファイル（N桁数の定義あり）
下記の様にCTLファイルのカラムに桁数の定義をすることで、桁数エラーが解消します。
OPTIONS (
SKIP = 0 -- スキップ無し（全件取り込む）
, DIRECT = TRUE -- ダイレクト・パス・ロード（制約無視の高速取り込み）
, ROWS = 1000 -- 1000件単位で登録
)
LOAD DATA
INFILE '.\data\TB_SAMPLE.tsv'
BADFILE '.\data\TB_SAMPLE.bad'
DISCARDFILE '.\data\TB_SAMPLE.dsc'
TRUNCATE INTO TABLE TB_SAMPLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
ID
, NAME CHAR(1000)
)