Oracle 與 Access 異構資料同步：EXEC_SQL 透過 ODBC 實現雙向導入導出

前言：Forms/Reports 時代的異構整合經典方案

在 Oracle Forms/Reports (Developer 6i/9i/10g) 應用中，常需與本地 Access (MDB/ACCDB) 交換資料：離線數據採集、外部數據匯入、報表數據源切換。本文詳細解析利用 Oracle Open Client Adapter (OCA) for ODBC + EXEC_SQL 套件，在 PL/SQL 中實現 Oracle ↔ Access 雙向同步的完整架構、代碼模板、參數綁定陷阱與現代替代方案。

架構總覽

Oracle Forms/Reports (Client) 
    ↓ PL/SQL EXEC_SQL
Oracle Database Server
    ↓ Database Link / ODBC Gateway
Oracle Open Client Adapter (OCA) for ODBC
    ↓ ODBC Driver
Microsoft Access (MDB/ACCDB File)

關鍵元件：

• OCA for ODBC：Oracle 官方異構連接器，安裝於資料庫伺服器 (或中間層)，將 ODBC DSN 映射為 Database Link 可用的服務名。
• EXEC_SQL：PL/SQL 原生動態 SQL 套件，支援跨 Database Link 執行 DDL/DML、綁定變量、抓取結果集。
• ODBC DSN：系統/用戶 DSN，配置 Access 檔案路徑、驅動程式 (Microsoft Access Driver *.mdb / *.accdb)、認證資訊。

前置準備：三步驟完成環境就緒

1. 安裝 OCA：Oracle Developer 安裝媒體 → 自定義安裝 → 勾選 Oracle Open Client Adapter for ODBC。
2. 建立 ODBC DSN：控制台 → 管理工具 → 資料來源 (ODBC) → 系統 DSN → 新增 → Microsoft Access Driver → 資料源名 TEST_ACCESS → 選擇 C:\Data\source.mdb → 進階 → 輸入登入名/密碼 (Access 安全性)。
3. 驗證連通性：SQL*Plus 測試 SELECT * FROM dual@TEST_ACCESS (需 OCA 將 DSN 註冊為全域服務名)。

核心代碼：Oracle → Access 導出 (全量覆蓋模式)

PROCEDURE oracle_to_access IS
    v_conn       EXEC_SQL.CONNTYPE;
    v_cursor     EXEC_SQL.CURSTYPE;
    v_ignore     PLS_INTEGER;
    v_name       student.name%TYPE;
    v_age        student.age%TYPE;
    CURSOR c_src IS SELECT name, age FROM student;  -- Oracle 來源
BEGIN
    -- 1. 開啟異構連接 (語法: user/pwd@odbc:DSN_NAME)
    v_conn := EXEC_SQL.OPEN_CONNECTION('user/pwd@odbc:TEST_ACCESS');
    
    -- 2. 清空目標表
    v_cursor := EXEC_SQL.OPEN_CURSOR(v_conn);
    EXEC_SQL.PARSE(v_conn, v_cursor, 'DELETE FROM student');
    v_ignore := EXEC_SQL.EXECUTE(v_conn, v_cursor);
    EXEC_SQL.CLOSE_CURSOR(v_conn, v_cursor);
    
    -- 3. 批量插入準備
    OPEN c_src;
    v_cursor := EXEC_SQL.OPEN_CURSOR(v_conn);
    EXEC_SQL.PARSE(v_conn, v_cursor, 'INSERT INTO student(name, age) VALUES (:1, :2)');
    
    -- 4. 迴圈綁定執行
    LOOP
        FETCH c_src INTO v_name, v_age;
        EXIT WHEN c_src%NOTFOUND;
        EXEC_SQL.BIND_VARIABLE(v_conn, v_cursor, ':1', v_name);
        EXEC_SQL.BIND_VARIABLE(v_conn, v_cursor, ':2', v_age);
        v_ignore := EXEC_SQL.EXECUTE(v_conn, v_cursor);
    END LOOP;
    CLOSE c_src;
    
    -- 5. 提交並清理
    EXEC_SQL.PARSE(v_conn, v_cursor, 'COMMIT');
    v_ignore := EXEC_SQL.EXECUTE(v_conn, v_cursor);
    EXEC_SQL.CLOSE_CURSOR(v_conn, v_cursor);
    EXEC_SQL.CLOSE_CONNECTION(v_conn);
    
EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
        IF EXEC_SQL.LAST_ERROR_CODE(v_conn) != 0 THEN
            RAISE_APPLICATION_ERROR(-20001, '導出 Access 失敗: ' || 
                EXEC_SQL.LAST_ERROR_CODE(v_conn) || ' - ' || EXEC_SQL.LAST_ERROR_MESG(v_conn));
        END IF;
        -- 確保資源釋放
        IF EXEC_SQL.IS_CONNECTED(v_conn) THEN
            IF EXEC_SQL.IS_OPEN(v_conn, v_cursor) THEN
                EXEC_SQL.CLOSE_CURSOR(v_conn, v_cursor);
            END IF;
            EXEC_SQL.CLOSE_CONNECTION(v_conn);
        END IF;
END oracle_to_access;

核心代碼：Access → Oracle 導入 (游標抓取模式)

PROCEDURE access_to_oracle IS
    v_conn       EXEC_SQL.CONNTYPE;
    v_cursor     EXEC_SQL.CURSTYPE;
    v_ignore     PLS_INTEGER;
    v_name       student.name%TYPE;
    v_age        student.age%TYPE;
BEGIN
    v_conn := EXEC_SQL.OPEN_CONNECTION('user/pwd@odbc:TEST_ACCESS');
    v_cursor := EXEC_SQL.OPEN_CURSOR(v_conn);
    
    -- 1. 清空 Oracle 目標表 (本地操作)
    DELETE FROM student;
    
    -- 2. 解析遠端查詢
    EXEC_SQL.PARSE(v_conn, v_cursor, 'SELECT name, age FROM student');
    v_ignore := EXEC_SQL.EXECUTE(v_conn, v_cursor);
    
    -- 3. 定義抓取欄位 (對應 SELECT 列順序)
    EXEC_SQL.DEFINE_COLUMN(v_conn, v_cursor, 1, v_name, 10);  -- VARCHAR2 長度
    EXEC_SQL.DEFINE_COLUMN(v_conn, v_cursor, 2, v_age);        -- NUMBER 無長度
    
    -- 4. 迴圈抓取並寫入本地
    LOOP
        v_ignore := EXEC_SQL.FETCH_ROWS(v_conn, v_cursor);
        EXIT WHEN v_ignore = 0;
        EXEC_SQL.COLUMN_VALUE(v_conn, v_cursor, 1, v_name);
        EXEC_SQL.COLUMN_VALUE(v_conn, v_cursor, 2, v_age);
        INSERT INTO student (name, age) VALUES (v_name, v_age);
    END LOOP;
    
    COMMIT;
    EXEC_SQL.CLOSE_CURSOR(v_conn, v_cursor);
    EXEC_SQL.CLOSE_CONNECTION(v_conn);
    
EXCEPTION
    WHEN EXEC_SQL.PACKAGE_ERROR THEN
        -- 同前錯誤處理
        ...
END access_to_oracle;

關鍵技術細節與避坑指南

1. EXEC_SQL 綁定/定義變量型別限制

僅支援三種型別：**NUMBER、DATE、VARCHAR2**。

• Access 布爾型 (Yes/No) → Oracle 用 NUMBER(1) (1/0) 映射。
• Access 長文本 (Memo) → Oracle 用 VARCHAR2(4000) 或 CLOB (需分段寫入)。
• Access OLE 物件/附件 → 不支援直接同步，需應用層處理。

2. 識別字元處理：空格、保留字

Access 表名/欄位名含空格時，SQL 需雙引號包裹：

-- Access 表名 "Student Detail", 欄位 "Student Name", "Student Age"
'INSERT INTO "Student Detail" ("Student Name", "Student Age") VALUES (:1, :2)'

Oracle 端建議建立同名視圖/同義詞屏蔽雙引號複雜度。

3. 效能優化：批量綁定 vs 逐行

上述代碼為逐行 EXECUTE，網路往返 = 行數。優化方案：

• PL/SQL 端收集陣列，改用 FORALL 批量綁定 (需 OCA/ODBC 驅動支援陣列綁定，較少見)。
• 改用 INSERT /*+ APPEND */ SELECT ... FROM TABLE@DBLINK (若 OCA 註冊為 DB Link 且支援查詢)。
• 大量資料首選 SQL*Loader / Data Pump / 外部表 繞過 PL/SQL 引擎。

4. 事務一致性與錯誤恢復

• 跨資料庫異構操作 不支援分散式事務 (Two-Phase Commit)。
• 導出前 DELETE 目標表 → 若導入中途失敗，目標表資料全失。對策：導入暫存表 → 驗證無誤 → TRUNCATE TABLE target; INSERT INTO target SELECT * FROM staging;。
• 錯誤日誌表記錄每筆失敗行 (PK、錯誤碼、時間)，支援重跑。

現代替代方案：淘汰 OCA/EXEC_SQL 的最佳選擇

總結

EXEC_SQL + OCA/ODBC 是 Oracle Forms/Reports 時代異構同步的經典方案，核心在於 OPEN_CONNECTION('user/pwd@odbc:DSN') 建立異構會話，再以 PARSE/BIND/EXECUTE/FETCH/DEFINE 手工驅動動態 SQL。雖然功能完整，但開發複雜度高、效能受限於逐行網路往返、缺乏分散式事務保障、元件老舊 (OCA 12c+ 已不再增強)。新專案強烈建議採用 Database Gateway for ODBC + DB Link 或 應用層 ETL，將異構邏輯下沉至資料庫層或上提至應用層，實現標準化、可維護、高效能的異構整合。