SQLLoader実行バッチ作成(制御ファイルもね)

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
・CSVデータ出力バッチ作成
→SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
・テーブル生成コマンド取得
・ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序（シーケンス）生成コマンド取得
・データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
・サブクエリーのアクセス順指定
・グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

突然必要となった大量データ、大量テーブルのcsv取り込み、なかなか面倒です。 ネットに転がっているvbsも動かず、SQLDeveloperで１テーブルずつせこせこ作成するのもつらい・・・。 というわけで、Oracle使いならやっぱCUIでしょ！

準備するもの

まず、以下のスクリプトをコピーして、"csvimport.sql"というファイルを作成します。 このとき、以下の部分をメンテしてください。

メンテ箇所
user/password@servicename ユーザー名/パスワード@Oracle接続子(ネット・サービス名)
in ('TABLE1','TABLE2'..) 取り込み対象テーブル
TRUNCATE 実行すると、テーブルがリセットされますので、追加にしたい場合はAPPEND等に変更して下さい。
SKIP=1 1行目を読み飛ばします。タイトルが無い場合(1行目からデータの場合)は、省略して下さい。
^`r CSVの項目値としての改行コードを指定します(\r\n等)。CSVレコードの終端コードではありません。 別ページにあるCSVデータ出力バッチ作成を実行すると、 改行コードが"^`r"や"^`n"に変換されて出力されるのでそれを改行コードに戻してインポートする場合、左記の様に指定します。
^`n

ちなみに、Windowsが対象です。UNIX系(Solaris,Linux等)の場合はそのまま使えません。いい感じにカスタマイズして下さい。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
------------------------------------------------------------------
-- Copyright(c) 2015-2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.08.22 作成
-- 2015.08.27 date,timestamp型を明確化,バッチ実行順を変更
-- 2015.09.01 画面出力メッセージを修正
-- 2015.09.08 処理手順を簡潔化、コメントに対応
-- 2015.09.14 先頭行をスキップ化
-- 2015.09.16 フォルダ構成をCSV_DATA,SQLLDR_CTL,SQLLDR_LOGに
-- 2015.09.17 ^`r^`nをCRLFに置換して取り込み
-- 2016.02.26 画面表示テーブル名を固定長化、TRAILING NULLCOLSを追加
--
-- [実行方法]
-- 1)このテキストを、"csvimport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
--   @echo off
--   sqlplus -s user/password@servicename @csvimport.sql
--   csvimport.bat
-- 3)ログファイルを調査し、エラーが発生していないか確認します。
--
-- [注意事項]
-- LOB系、LONG型には対応していません。
-- "^`r"⇒CR、"^`n"⇒LFに置換して取り込みます。置換を使用せず、改行コードをそのまま値としてダイレクトに取り込みたい場合は、レコード終端(EOR)判定制御を組み込んで下さい。
------------------------------------------------------------------
set escape    ?
set linesize  300
set pagesize  0
set term      off
set head      off
set feed      off
set trim      on
set trimspool on
set verify    off
$mkdir CSV_DATA   >> __csvimport1.log 2>> __csvimport2.log
$mkdir SQLLDR_CTL >> __csvimport1.log 2>> __csvimport2.log
$mkdir SQLLDR_LOG >> __csvimport1.log 2>> __csvimport2.log
------------------------------------------------------------------
-- __csvimport1.sql
------------------------------------------------------------------
spool  __csvimport1.sql
prompt spool  SQLLDR_CTL/?&?&1..ctl
prompt prompt OPTIONS(DIRECT=Y,MULTITHREADING=Y)
prompt prompt LOAD DATA INFILE 'CSV_DATA/?&?&1..csv'
prompt prompt TRUNCATE INTO TABLE ?&?&1
prompt prompt FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS(
prompt select case when col.column_id = 1 then '     ' else '   , ' end
prompt     || rpad(col.column_name,31)
prompt     || rpad(case when col.data_type    = 'DATE'         then rpad(col.data_type,15) || ' "YYYY/MM/DD HH24:MI:SS"'
prompt                  when col.data_type like 'TIMESTAMP(%)' then rpad(col.data_type,15) || ' "YYYY/MM/DD HH24:MI:SS.FF' || col.data_scale || '"'
prompt                  when col.data_type    = 'CHAR'         then '"REPLACE(REPLACE(:' || rpad(col.column_name,30) || ',''^`r'', CHR(13)),''^`n'', CHR(10))"'
prompt                  when col.data_type    = 'VARCHAR2'     then '"REPLACE(REPLACE(:' || rpad(col.column_name,30) || ',''^`r'', CHR(13)),''^`n'', CHR(10))"'
prompt                  else ' ' end,100)
prompt     || nvl2(cmn.comments,'-- ' || cmn.comments,' ')
prompt   from user_tab_columns  col
prompt      , user_col_comments cmn
prompt  where col.table_name  = '?&?&1'
prompt    and col.table_name  = cmn.table_name(+)
prompt    and col.column_name = cmn.column_name(+)
prompt  order by col.column_id asc
prompt /
prompt prompt )
prompt spool  off
spool  off
------------------------------------------------------------------
-- __csvimport2.sql
------------------------------------------------------------------
spool  __csvimport2.sql
select '@__csvimport1.sql ' || tbl.table_name as cmdline
  from user_tables tbl
 where tbl.table_name in ('TABLE1','TABLE2'..)
 order by table_name asc
/
spool off
@__csvimport2.sql
------------------------------------------------------------------
-- csvimport.bat
------------------------------------------------------------------
spool  csvimport.bat
prompt @echo off
select 'SET  /p  X=' || rpad( tbl.table_name, 30) || ' <nul
SQLLDR user/password@servicename '
|| '    SKIP=1'
|| ' CONTROL=''SQLLDR_CTL/' || rpad( tbl.table_name || '.ctl''', 40)
|| '     LOG=''SQLLDR_LOG/' || to_char(sysdate,'YYYYMMDDHH24MISS') || '_' || tbl.table_name || '.log'''
|| ' | findstr -i ロードは完了しました。' as cmdline
  from user_tables tbl
 where tbl.table_name in ('TABLE1','TABLE2'..)
 order by table_name asc
/
prompt ECHO ロード処理がすべて終了しました。詳細はログファイルを参照して下さい。
spool off
$del __csvimport*.*
$echo スクリプトを生成しました。
exit

CSVデータの配置

スクリプトの存在するフォルダ上で以下のサブフォルダを作成、CSVデータを配置します。

事前に用意するサブフォルダ
CSV_DATA データベースに取り込むためのCSVデータ

実行

スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。

@echo off

sqlplus -s user/password@servicename @csvimport.sql

csvimport.bat

実行時に生成されるサブフォルダ
SQLLDR_CTL SQLローダー用の制御ファイルを生成
SQLLDR_LOG SQLローダーの実行結果ログ


Copyright(c) 2014-2022 pakkin. All Rights Reserved.