SQLローダで、ローダを実行した日時を同時にロードしたいという要件がありました。
その時、ちょっと躓いたのでメモします。
ちょっとしたお試しテーブルと、お試しコントロールファイルでやってます。
■コントロールファイル(test.ctl)
OPTIONS (
ROWS=1000,
DIRECT=TRUE,
MULTITHREADING=FALSE
)
LOAD DATA
APPEND
PRESERVE BLANKS
INTO TABLE A
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
A,
B,
C CHAR "TO_CHAR(SYSDATE,'YYYYMMDD')",
D CHAR "TO_CHAR(SYSDATE,'HH24MISS')"
)
■コントロールファイル(test.dat)
"a","b"
■実行結果
[oracle@node1 ~]$ export ORACLE_SID=boo_1
[oracle@node1 ~]$ sqlplus system/system
SQL*Plus: Release 12.1.0.2.0 Production on 日 9月 6 11:17:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
に接続されました。
SQL> select name from v$database;
NAME
---------------------------
BOO
SQL> create table a
2 (a char(1),b char(1),c char(8),d char(6));
表が作成されました。
SQL> select * from a;
レコードが選択されませんでした。
SQL> exit
[oracle@node1 ~]$ sqlldr userid=system/system control=test.ctl log=test.log errors=1 rows=1
SQL*Loader: Release 12.1.0.2.0 - Production on 日 9月 6 11:31:18 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
使用パス: ダイレクト
セーブ・データ・ポイントに達しました。 - 論理レコード件数1
ロードは完了しました。 - 論理レコード件数1
表A:
0行のロードに成功しました。
確認するログ・ファイル:
test.log
ロードの詳細を参照してください。
[oracle@node1 ~]$ ls -ltr
合計 52
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 デスクトップ
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 公開
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 テンプレート
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 ダウンロード
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 音楽
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 画像
drwxr-xr-x. 2 oracle oinstall 4096 7月 26 11:09 2015 ビデオ
drwxr-xr-x. 2 oracle oinstall 4096 8月 10 16:01 2015 ドキュメント
drwxr-xr-x. 3 oracle oinstall 4096 8月 10 16:41 2015 tmp
-rw-r--r--. 1 oracle oinstall 8 9月 6 11:01 2015 test.dat
-rw-r--r--. 1 oracle oinstall 241 9月 6 11:26 2015 test.ctl
-rw-r--r--. 1 oracle oinstall 8 9月 6 11:31 2015 test.bad
-rw-r--r--. 1 oracle oinstall 2284 9月 6 11:31 2015 test.log
[oracle@node1 ~]$ cat test.log
SQL*Loader: Release 12.1.0.2.0 - Production on 日 9月 6 11:31:18 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
制御ファイル: test.ctl
データファイルtest.dat
不良ファイル: test.bad
廃棄ファイル: 指定なし
(すべて廃棄できます)
ロード数: ALL
スキップ数: 0
許容エラー数: 1
継続文字: 指定なし
使用パス: ダイレクト
表A、 ロード済 すべての論理レコードから
この表に対する有効な挿入オプション: APPEND
列名 位置 長さ 用語暗号化データ型
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST * , O(")CHARACTER
B NEXT * , O(")CHARACTER
C NEXT * , O(")CHARACTER
列のSQL文字列 : "TO_CHAR(SYSDATE,'YYYYMMDD')"
D NEXT * , O(")CHARACTER
列のSQL文字列 : "TO_CHAR(SYSDATE,'HH24MISS')"
レコード1: 拒否されました。- 表A,列Cでエラーが発生しました。
論理レコードが終了する前に列が見つかりませんでした。(TRAILING NULLCOLSを使用)
表A:
0行のロードに成功しました。
1行はデータ・エラーのためロードされませんでした。
0行はWHEN句のエラーのためロードされませんでした。
0行はすべてのフィールドがNULLのためロードされませんでした。
バインド配列サイズはダイレクト・パスで使用されません。
列配列の行数: 1
ストリーム・バッファのバイト数: 256000
読取りバッファのバイト数: 1048576
スキップされた論理レコードの合計: 0
読み込まれた論理レコードの合計: 1
拒否された論理レコードの合計: 1
廃棄された論理レコードの合計: 0
ダイレクト・パスのマルチスレッド最適化が使用禁止です
実行開始日 9月 06 11:31:18 2015
実行終了日 9月 06 11:31:41 2015
実行時間: 00: 00: 23.41
CPU時間 : 00: 00: 00.03
失敗した。。。
■考察
テーブル列と、datファイルの項目数が合っていないと、制御ファイル
にTO_CHAR(SYSDATE,'YYYYMMDD')としていしても、エラーになります。
どうやら、TRAILING NULLCOLSが無いと、C、D列に日、時間をロードすることが
出来ないようです。
■コントロールファイル(test.ctl)修正後
OPTIONS (
ROWS=1000,
DIRECT=TRUE,
MULTITHREADING=FALSE
)
LOAD DATA
APPEND
PRESERVE BLANKS
INTO TABLE A
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
A,
B,
C CHAR "TO_CHAR(SYSDATE,'YYYYMMDD')",
D CHAR "TO_CHAR(SYSDATE,'HH24MISS')"
)
■実行結果
[oracle@node1 ~]$ sqlldr userid=system/system control=test.ctl log=test.log errors=1 rows=1
SQL*Loader: Release 12.1.0.2.0 - Production on 日 9月 6 11:34:49 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
使用パス: ダイレクト
セーブ・データ・ポイントに達しました。 - 論理レコード件数1
ロードは完了しました。 - 論理レコード件数1
表A:
1行のロードに成功しました。
確認するログ・ファイル:
test.log
ロードの詳細を参照してください。
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus system/system
SQL*Plus: Release 12.1.0.2.0 Production on 日 9月 6 11:35:07 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
最終正常ログイン時間: 日 9月 06 2015 11:34:49 +09:00
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
に接続されました。
SQL> select * from a;
A B C D
--- --- ------------------------ ------------------
a b 20150906 113453
SQL>
うまくいった！
■まとめ
TRAILING NULLCOLSを追記しただけでうまくいきました。
制御ファイルに、TO_CHAR(SYSDATE,'YYYYMMDD')に書いているのだから、
TRAILING NULLCOLSは、必要ないと思ったのですが、、、、
必要なようです。
ちなみに、CONSTANT 'XXXXX'の場合は、 TRAILING NULLCOLSは必要なかった
です。
