SQL_LOADER小結

關鍵點： 使用DOS命令，定時批次載入文字檔案入庫

[@more@]

---- 目錄結構 ----
--loader3
|
--bad
--discard
--log
--txt
--txt_bak
doit.bat
ins_log.sql
khxx.bat
khxx.ctl

待處理的檔案上傳到txt目錄下，已處理的檔案備份到txt_bak目錄下；

---- doit.bat檔案內容 ----

dir txt /W /b >todo.txt
for /f %%a in (todo.txt) do @if exist txt\%%a copy /Y txt\%%a txt_bak\%%a
for /f %%a in (todo.txt) do @if exist txt\%%a khxx %%a

---- khxx.bat檔案內容 ----

sqlldr control=KHXX.ctl log=log\%~n1.log bad=bad\%~n1.bad discard=discard\%~n1.dis errors=500 data=txt\%1
sqlplus @ins_log "FWZ_KHXX_CTAIS2" "%~f1" "%1" "%~n1.log" "%~n1.bad"
del txt\%1

---- ins_log.sql檔案內容 ----
set verify off
set echo off
insert into FWZ_load_log
(tab_name, file_directory, file_name, log_file, bad_file)
values
('&1','&2','&3','&4','&5');

commit;
exit;

---- KHXX.ctl檔案內容 ----
LOAD DATA
append
INTO TABLE SJDR.FWZ_KHXX_CTAIS2
FIELDS TERMINATED BY X'09'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col1 ,
col2 ,
col3 ,
col4 ,
col5 ,
col6 ,
col7 ,
col8 ,
col9 ,
col10 ,
col11 ,
col12 ,
col13 char(1024),
col14 ,
col15 ,
col16 ,
col17 ,
col18 ,
col19 ,
col20 ,
col21 ,
col22 ,
col23 ,
col24 ,
col25 ,
col26 ,
col27 ,
col28 ,
col29 ,
col30 ,
col31 ,
col32 ,
col33 ,
col34 ,
col35 ,
col36 ,
col37 ,
col38 ,
col39 ,
col40 ,
col41 ,
col42 ,
col43 ,
col44 ,
col45 ,
col46 ,
col47 ,
col48 ,
col49 ,
col50 ,
col51 ,
col52 ,
col53 ,
col54 ,
col55 ,
col56 ,
col57 ,
col58 ,
col59 ,
col60 ,
col61 ,
col62 ,
col63 ,
col64
)

-- 待完善的地方有：將日誌轉存到資料庫
-- 新增重複處理bad檔案