Sqlldr Basic Example

SQL*LOADER基本のサンプル(共通)

TBL_LOAD_DATAに任意100項目以内のCSVデータdata.csvをロードするサンプルです。
カラム、データタイプと関係ないから、共通といえます。
また、必要に応じて項目数とオプションを調整することができます。

テーブル作成用SQL。あらかじめsqlplusで実行します。

create table TBL_LOAD_DATA (        
LOAD_ID    NUMBER(9)    ,
LINE_NO    NUMBER(9)    ,
DATA_TYPE    VARCHAR2(100)    ,
LOAD_TAG    VARCHAR(100)    ,
LOAD_DT    DATE DEFAULT SYSDATE    ,
D01    VARCHAR2(100)    ,
D02    VARCHAR2(100)    ,
D03    VARCHAR2(100)    ,
D04    VARCHAR2(100)    ,
D05    VARCHAR2(100)    ,
D06    VARCHAR2(100)    ,
D07    VARCHAR2(100)    ,
D08    VARCHAR2(100)    ,
D09    VARCHAR2(100)    ,
D10    VARCHAR2(100)    ,
D11    VARCHAR2(100)    ,
D12    VARCHAR2(100)    ,
D13    VARCHAR2(100)    ,
D14    VARCHAR2(100)    ,
D15    VARCHAR2(100)    ,
D16    VARCHAR2(100)    ,
D17    VARCHAR2(100)    ,
D18    VARCHAR2(100)    ,
D19    VARCHAR2(100)    ,
D20    VARCHAR2(100)    ,
D21    VARCHAR2(100)    ,
D22    VARCHAR2(100)    ,
D23    VARCHAR2(100)    ,
D24    VARCHAR2(100)    ,
D25    VARCHAR2(100)    ,
D26    VARCHAR2(100)    ,
D27    VARCHAR2(100)    ,
D28    VARCHAR2(100)    ,
D29    VARCHAR2(100)    ,
D30    VARCHAR2(100)    ,
D31    VARCHAR2(100)    ,
D32    VARCHAR2(100)    ,
D33    VARCHAR2(100)    ,
D34    VARCHAR2(100)    ,
D35    VARCHAR2(100)    ,
D36    VARCHAR2(100)    ,
D37    VARCHAR2(100)    ,
D38    VARCHAR2(100)    ,
D39    VARCHAR2(100)    ,
D40    VARCHAR2(100)    ,
D41    VARCHAR2(100)    ,
D42    VARCHAR2(100)    ,
D43    VARCHAR2(100)    ,
D44    VARCHAR2(100)    ,
D45    VARCHAR2(100)    ,
D46    VARCHAR2(100)    ,
D47    VARCHAR2(100)    ,
D48    VARCHAR2(100)    ,
D49    VARCHAR2(100)    ,
D50    VARCHAR2(100)    ,
D51    VARCHAR2(100)    ,
D52    VARCHAR2(100)    ,
D53    VARCHAR2(100)    ,
D54    VARCHAR2(100)    ,
D55    VARCHAR2(100)    ,
D56    VARCHAR2(100)    ,
D57    VARCHAR2(100)    ,
D58    VARCHAR2(100)    ,
D59    VARCHAR2(100)    ,
D60    VARCHAR2(100)    ,
D61    VARCHAR2(100)    ,
D62    VARCHAR2(100)    ,
D63    VARCHAR2(100)    ,
D64    VARCHAR2(100)    ,
D65    VARCHAR2(100)    ,
D66    VARCHAR2(100)    ,
D67    VARCHAR2(100)    ,
D68    VARCHAR2(100)    ,
D69    VARCHAR2(100)    ,
D70    VARCHAR2(100)    ,
D71    VARCHAR2(100)    ,
D72    VARCHAR2(100)    ,
D73    VARCHAR2(100)    ,
D74    VARCHAR2(100)    ,
D75    VARCHAR2(100)    ,
D76    VARCHAR2(100)    ,
D77    VARCHAR2(100)    ,
D78    VARCHAR2(100)    ,
D79    VARCHAR2(100)    ,
D80    VARCHAR2(100)    ,
D81    VARCHAR2(100)    ,
D82    VARCHAR2(100)    ,
D83    VARCHAR2(100)    ,
D84    VARCHAR2(100)    ,
D85    VARCHAR2(100)    ,
D86    VARCHAR2(100)    ,
D87    VARCHAR2(100)    ,
D88    VARCHAR2(100)    ,
D89    VARCHAR2(100)    ,
D90    VARCHAR2(100)    ,
D91    VARCHAR2(100)    ,
D92    VARCHAR2(100)    ,
D93    VARCHAR2(100)    ,
D94    VARCHAR2(100)    ,
D95    VARCHAR2(100)    ,
D96    VARCHAR2(100)    ,
D97    VARCHAR2(100)    ,
D98    VARCHAR2(100)    ,
D99    VARCHAR2(100)    )
-- TABLESPACE tbs_data_load        
;

ロードコントロールファイルdata.ctl。CSVファイルと同じフォルダに格納します。

OPTIONS(LOAD=-1,SKIP=0,ERRORS=-1,ROWS=1000)
LOAD DATA
CHARACTERSET JA16SJISTILDE
INFILE 'data.csv' "STR x'0d0a'"
BADFILE 'data.bad'
DISCARDFILE 'data.dis'
--APPEND, INSERT, REPLACE
TRUNCATE
INTO TABLE TBL_LOAD_DATA
--WHEN (1) = 'ABC'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
D01    CHAR    ,
D02    CHAR    ,
D03    CHAR    ,
D04    CHAR    ,
D05    CHAR    ,
D06    CHAR    ,
D07    CHAR    ,
D08    CHAR    ,
D09    CHAR    ,
D10    CHAR    ,
D11    CHAR    ,
D12    CHAR    ,
D13    CHAR    ,
D14    CHAR    ,
D15    CHAR    ,
D16    CHAR    ,
D17    CHAR    ,
D18    CHAR    ,
D19    CHAR    ,
D20    CHAR    ,
D21    CHAR    ,
D22    CHAR    ,
D23    CHAR    ,
D24    CHAR    ,
D25    CHAR    ,
D26    CHAR    ,
D27    CHAR    ,
D28    CHAR    ,
D29    CHAR    ,
D30    CHAR    ,
D31    CHAR    ,
D32    CHAR    ,
D33    CHAR    ,
D34    CHAR    ,
D35    CHAR    ,
D36    CHAR    ,
D37    CHAR    ,
D38    CHAR    ,
D39    CHAR    ,
D40    CHAR    ,
D41    CHAR    ,
D42    CHAR    ,
D43    CHAR    ,
D44    CHAR    ,
D45    CHAR    ,
D46    CHAR    ,
D47    CHAR    ,
D48    CHAR    ,
D49    CHAR    ,
D50    CHAR    ,
D51    CHAR    ,
D52    CHAR    ,
D53    CHAR    ,
D54    CHAR    ,
D55    CHAR    ,
D56    CHAR    ,
D57    CHAR    ,
D58    CHAR    ,
D59    CHAR    ,
D60    CHAR    ,
D61    CHAR    ,
D62    CHAR    ,
D63    CHAR    ,
D64    CHAR    ,
D65    CHAR    ,
D66    CHAR    ,
D67    CHAR    ,
D68    CHAR    ,
D69    CHAR    ,
D70    CHAR    ,
D71    CHAR    ,
D72    CHAR    ,
D73    CHAR    ,
D74    CHAR    ,
D75    CHAR    ,
D76    CHAR    ,
D77    CHAR    ,
D78    CHAR    ,
D79    CHAR    ,
D80    CHAR    ,
D81    CHAR    ,
D82    CHAR    ,
D83    CHAR    ,
D84    CHAR    ,
D85    CHAR    ,
D86    CHAR    ,
D87    CHAR    ,
D88    CHAR    ,
D89    CHAR    ,
D90    CHAR    ,
D91    CHAR    ,
D92    CHAR    ,
D93    CHAR    ,
D94    CHAR    ,
D95    CHAR    ,
D96    CHAR    ,
D97    CHAR    ,
D98    CHAR    ,
D99    CHAR    ,
--LINE_NO    DECIMAL EXTERNAL "SEQ_LOAD_DATA.NEXTVAL",
--LINE_NO    SEQUENCE (MAX, 1),
DATA_ID    RECNUM,
LOAD_ID    CONSTANT 1,
DATA_TAG    CONSTANT 'DATA TAG',
DATA_TYPE    CONSTANT 'TYPE'
)

コマンドラインloaddata.bat。CSVと同じフォルダに格納して、実行します。

sqlldr username/password@tnsname control=data.ctl

必須ではないが、自動的にロードコントロールファイル作成するdata_ctl_gen.bat(Perlが必要)。

@rem = '--*-Perl-*--
@echo off
perl -x -S %0 %*
if NOT "%COMSPEC%" == "%SystemRoot%\system32\cmd.exe" goto endofperl
if %errorlevel% == 9009 echo You do not have Perl in your PATH.
if errorlevel 1 goto script_failed_so_exit_with_non_zero_val 2>nul
goto endofperl
@rem ';
#!perl
#line 15
 
#use strict;
#use warnings;
 
if ($#ARGV < 1 or $ARGV[0] eq '-h' or $ARGV[0] eq '--help' or $ARGV[0] eq '/?') {
    print STDERR "Usage: loaddata <username/password@tnsname> [tag] [type]";
}
else {
 
print << "_EOF_";
OPTIONS(LOAD=-1,SKIP=0,ERRORS=-1,ROWS=1000)
LOAD DATA
CHARACTERSET JA16SJISTILDE
INFILE '$ARGV[1]' "STR x'0d0a'"
BADFILE 'data.bad'
DISCARDFILE 'data.dis'
--APPEND, INSERT, REPLACE
TRUNCATE
INTO TABLE TBL_LOAD_DATA
--WHEN (1) = 'ABC'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
D01    CHAR    ,
D02    CHAR    ,
D03    CHAR    ,
D04    CHAR    ,
D05    CHAR    ,
D06    CHAR    ,
D07    CHAR    ,
D08    CHAR    ,
D09    CHAR    ,
D10    CHAR    ,
D11    CHAR    ,
D12    CHAR    ,
D13    CHAR    ,
D14    CHAR    ,
D15    CHAR    ,
D16    CHAR    ,
D17    CHAR    ,
D18    CHAR    ,
D19    CHAR    ,
D20    CHAR    ,
D21    CHAR    ,
D22    CHAR    ,
D23    CHAR    ,
D24    CHAR    ,
D25    CHAR    ,
D26    CHAR    ,
D27    CHAR    ,
D28    CHAR    ,
D29    CHAR    ,
D30    CHAR    ,
D31    CHAR    ,
D32    CHAR    ,
D33    CHAR    ,
D34    CHAR    ,
D35    CHAR    ,
D36    CHAR    ,
D37    CHAR    ,
D38    CHAR    ,
D39    CHAR    ,
D40    CHAR    ,
D41    CHAR    ,
D42    CHAR    ,
D43    CHAR    ,
D44    CHAR    ,
D45    CHAR    ,
D46    CHAR    ,
D47    CHAR    ,
D48    CHAR    ,
D49    CHAR    ,
D50    CHAR    ,
D51    CHAR    ,
D52    CHAR    ,
D53    CHAR    ,
D54    CHAR    ,
D55    CHAR    ,
D56    CHAR    ,
D57    CHAR    ,
D58    CHAR    ,
D59    CHAR    ,
D60    CHAR    ,
D61    CHAR    ,
D62    CHAR    ,
D63    CHAR    ,
D64    CHAR    ,
D65    CHAR    ,
D66    CHAR    ,
D67    CHAR    ,
D68    CHAR    ,
D69    CHAR    ,
D70    CHAR    ,
D71    CHAR    ,
D72    CHAR    ,
D73    CHAR    ,
D74    CHAR    ,
D75    CHAR    ,
D76    CHAR    ,
D77    CHAR    ,
D78    CHAR    ,
D79    CHAR    ,
D80    CHAR    ,
D81    CHAR    ,
D82    CHAR    ,
D83    CHAR    ,
D84    CHAR    ,
D85    CHAR    ,
D86    CHAR    ,
D87    CHAR    ,
D88    CHAR    ,
D89    CHAR    ,
D90    CHAR    ,
D91    CHAR    ,
D92    CHAR    ,
D93    CHAR    ,
D94    CHAR    ,
D95    CHAR    ,
D96    CHAR    ,
D97    CHAR    ,
D98    CHAR    ,
D99    CHAR    ,
--LINE_NO    DECIMAL EXTERNAL "SEQ_LOAD_DATA.NEXTVAL",
--LINE_NO    SEQUENCE (MAX, 1),
DATA_ID    RECNUM,
_EOF_
 
$username_password_tnsname = $ARGV[0]
 
$load_id = `echo select SEQ_LOAD_DATA.NEXTVAL from dual; \| sqlplus -S $ARGV[0]`;
$load_id =~ s/\n//g;
print "LOAD_ID    CONSTANT " . $load_id . "," . "\n";
 
print << "_EOF_";
LOAD_TAG    CONSTANT '$ARGV[2]',
DATA_TYPE    CONSTANT '$ARGV[3]'
)
_EOF_
 
}
 
__END__
:endofperl

自動ロードコントロールファイル作成を利用する場合loaddata.batがこのように変わります。

data_ctl_gen.bat username/password@tnsname filename.csv tag... type... > data.ctl
sqlldr username/password@tnsname control=data.ctl