Windows 批次檔操作 Oracle 資料庫：SQL*Plus 與 SQL*Loader 自動化實戰

核心架構

批次檔 (.bat) 透過呼叫 Oracle Client 提供的命令列工具，實現無人值守的資料庫操作：

1. SQL 腳本檔 (.sql)：包含 DDL/DML/查詢，結尾加 EXIT; 確保返回批次檔繼續執行。
2. SQL*Loader 控制檔 (.ctl)：定義資料檔格式、對應資料表、欄位對應、錯誤處理。
3. 批次主程式 (.bat)：串聯上述工具，處理返回碼、日誌、錯誤通知。

完整範例：清空暫存表並匯入文字檔

1. SQL 清理腳本 tableclean.sql

-- 清空暫存表並提交
DELETE FROM data_temp;
COMMIT;
EXIT;

2. SQL*Loader 控制檔 dataloading.ctl

LOAD DATA
INFILE 'data.txt'
BADFILE 'dataloading.bad'
DISCARDFILE 'dataloading.dsc'
INSERT INTO TABLE "DATA_TEMP"
FIELDS TERMINATED BY ','
(
  COL1 CHAR(100)
)

data.txt 內容範例 (單欄位，每行一筆)：

VALUE001
VALUE002
VALUE003

3. 批次主程式 dataloading.bat

@echo off
REM ==========================================
REM Oracle 資料自動化載入批次
REM 依賴：Oracle Client 已安裝並設定 PATH/TNS_ADMIN
REM ==========================================

SET ORACLE_SID=ORCL
SET USER=scott
SET PWD=tiger
SET TNS=ORCL
SET LOG_DIR=D:\Oracle\Logs
SET DATE_TAG=%DATE:~,4%%DATE:~5,2%%DATE:~8,2%

REM 1. 清空目標表
sqlplus -s %USER%/%PWD%@%TNS% @tableclean.sql >> %LOG_DIR%\clean_%DATE_TAG%.log 2>&1
IF %ERRORLEVEL% NEQ 0 (
  ECHO [ERROR] Table clean failed >> %LOG_DIR%\batch_%DATE_TAG%.log
  GOTO END_BATCH
)

REM 2. SQL*Loader 匯入
sqlldr %USER%/%PWD%@%TNS% control=dataloading.ctl log=%LOG_DIR%\load_%DATE_TAG%.log bad=%LOG_DIR%\load_%DATE_TAG%.bad discard=%LOG_DIR%\load_%DATE_TAG%.dsc errors=1000000
IF %ERRORLEVEL% NEQ 0 (
  ECHO [ERROR] SQL*Loader failed >> %LOG_DIR%\batch_%DATE_TAG%.log
  GOTO END_BATCH
)

REM 3. 簡易驗證
sqlplus -s %USER%/%PWD%@%TNS% @verify.sql >> %LOG_DIR%\verify_%DATE_TAG%.log 2>&1

:END_BATCH
EXIT /B %ERRORLEVEL%

4. 驗證腳本 verify.sql

SET HEADING OFF FEEDBACK OFF
SELECT 'Loaded rows: ' || COUNT(*) FROM data_temp;
EXIT;

關鍵技巧與最佳實務

• 靜默模式：sqlplus -s 抑制版權資訊，輸出乾淨，便於日誌分析。
• 錯誤碼檢查：%ERRORLEVEL% 捕捉工具返回碼 (0=成功)，失敗即中斷並記錄。
• 日誌分離：各步驟獨立日誌，並加入時間戳記，便於追蹤。
• TNS 設定：確保 tnsnames.ora 可解析 %TNS%，或直接使用 Easy Connect host:port/service。
• SQL*Loader 參數：
  • ERRORS=1000000 允許大量錯誤不中斷 (配合 BAD/DISCARD 檔)
  • DIRECT=TRUE 大量資料直接路徑載入 (需無觸發器/約束/索引或預先停用)
  • PARALLEL=TRUE 並行載入 (Enterprise Edition，需分割資料檔)
• 安全性：避免明文密碼寫在批次檔，改用 OS 認證 (sqlplus / as sysdba) 或 Oracle Wallet / 環境變數傳遞。
• 排程部署：Windows 工作排程器 / SQL Server Agent (若有) / 第三方排程工具 (Jenkins, Rundeck) 定時觸發。

進階：動態參數化

REM 接受命令列參數：批次檔 日期 檔案路徑
SET BIZ_DATE=%1
SET DATA_FILE=%2
IF "%BIZ_DATE%"=="" SET BIZ_DATE=%DATE:~,4%%DATE:~5,2%%DATE:~8,2%
IF "%DATA_FILE%"=="" SET DATA_FILE=D:\Data\import_%BIZ_DATE%.txt

REM 修改控制檔 INFILE 路徑 (使用 sed 或 powershell 替換)
powershell -Command "(gc dataloading.ctl) -replace 'INFILE .*', 'INFILE \"%DATA_FILE%\"' | sc dataloading.ctl"