サイトアイコン こじりふぁ

SQLLOADER 最大長を超えています

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)
)