Oracle 無原生 Array 型別，但提供 Collection（集合）類型：VARRAY（定長陣列）、Nested Table（巢狀表）、Associative Array（關聯陣列，僅 PL/SQL 可用）。其中 Nested Table 可儲存於資料庫、作為 SQL/存儲過程參數，是模擬 Array 傳遞的標準方案。本文示範：建立 Nested Table 型別 → 存儲過程接收並遍歷 → Java JDBC 使用 oracle.sql.ARRAY / ArrayDescriptor 傳遞 List 資料。
CREATE OR REPLACE TYPE varchar_array IS TABLE OF VARCHAR2(128);
/
IS TABLE OF 宣告為 Nested Table（無長度上限，稀疏儲存）。CREATE TABLE tb (col1 VARCHAR2(128));
CREATE OR REPLACE PROCEDURE test1 (arr IN varchar_array) IS
BEGIN
FOR i IN arr.FIRST .. arr.LAST LOOP
-- Nested Table 可能稀疏，建議用 FIRST/LAST 配合 EXISTS 判斷
IF arr.EXISTS(i) THEN
INSERT INTO tb VALUES (arr(i));
END IF;
END LOOP;
-- 視業務決定是否在此 COMMIT，通常由呼叫端控制交易邊界
END test1;
/
ojdbc8.jar (JDK 8+) 或 ojdbc11.jar (JDK 11+)，需與資料庫版本相容。com.oracle.database.jdbc:ojdbc8:21.9.0.0 (從 Oracle Maven Repo 下載)。
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class ArrayProcDemo {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@192.168.1.199:1521:orcl";
String user = "scott";
String password = "tiger";
Connection conn = null;
CallableStatement cstmt = null;
try {
// 1. 取得連線
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false); // 交易由應用控制
// 2. 準備資料
List list = new ArrayList<>();
list.add("A");
list.add("B");
list.add("C");
// 3. 取得 ArrayDescriptor (型別名稱區分大小寫，需與 DB 端一致)
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("VARCHAR_ARRAY", conn);
// 4. 將 List 轉為 oracle.sql.ARRAY
ARRAY oracleArray = new ARRAY(descriptor, conn, list.toArray());
// 5. 呼叫存儲過程
cstmt = conn.prepareCall("{ CALL test1(?) }");
cstmt.setArray(1, oracleArray);
cstmt.execute();
// 6. 提交交易
conn.commit();
System.out.println("批次插入成功，筆數: " + list.size());
} catch (SQLException e) {
e.printStackTrace();
try { if (conn != null) conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
} finally {
// 7. 資源關閉 (JDBC 4.0+ try-with-resources 更佳)
closeQuietly(cstmt);
closeQuietly(conn);
}
}
private static void closeQuietly(AutoCloseable ac) {
if (ac != null) try { ac.close(); } catch (Exception ignore) {}
}
}
| 環節 | 關鍵類/方法 | 說明 |
|---|---|---|
| 型別描述符 | ArrayDescriptor.createDescriptor(typeName, conn) | 快取建議：型別描述符可重用，建議應用啟動時建立一次緩存 |
| ARRAY 建構 | new ARRAY(descriptor, conn, javaArray) | javaArray 為 Object[]，元素型別需對應 SQL 型別 |
| 綁定參數 | cstmt.setArray(idx, oracleArray) | 對應 CALL 句法中 ? 位置 |
| PL/SQL 遍歷 | FOR i IN arr.FIRST..arr.LAST + arr.EXISTS(i) | Nested Table 稀疏特性，避免 NO_DATA_FOUND |
ArrayDescriptor.createDescriptor("VARCHAR_ARRAY", conn) 中的型別名稱必須與資料庫端 CREATE TYPE 定義的大小寫完全一致。Oracle 內部儲存為大寫，但建議統一用大寫避免困擾。
conn.setAutoCommit(false) + 成功後 commit() / 失敗 rollback()。PRAGMA AUTONOMOUS_TRANSACTION)，否則破壞外層交易一致性。FORALL i IN INDICES OF arr INSERT INTO tb VALUES (arr(i))，單次 SQL 引擎呼叫，效能指數級提升。PreparedStatement.addBatch() + executeBatch()。INSERT /*+ APPEND */、或外部表/ SQL*Loader。CREATE OR REPLACE TYPE number_array IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE test_num (arr IN number_array) IS
BEGIN
FORALL i IN INDICES OF arr
INSERT INTO num_tbl VALUES (arr(i));
END;
/
List nums = Arrays.asList(new BigDecimal("1.1"), new BigDecimal("2.2"));
ArrayDescriptor numDesc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", conn);
ARRAY numArray = new ARRAY(numDesc, conn, nums.toArray());
// 後續 setArray/execute 同字串範例
| 錯誤現象 | 原因 | 解決 |
|---|---|---|
| PLS-00306: wrong number or types of arguments | 型別名稱拼錯、大小寫不符、或未建立型別 | 檢查 SELECT type_name FROM user_types 確認 |
| ORA-21700: object does not exist or is marked for delete | ArrayDescriptor 建立失敗，型別不存在 | 同左 |
| ClassCastException: cannot cast to oracle.sql.ARRAY | 使用非 Oracle 專用 Connection (如連線池包裝後) | conn.unwrap(OracleConnection.class) 取得原生連線 |
| NullPointerException 在 ARRAY 建構 | List 含 null 元素，但 SQL 型別 NOT NULL | 預先過濾 null 或調整表欄位允許 NULL |
Oracle Nested Table + JDBC oracle.sql.ARRAY 是實現「集合/陣列參數化」的標準路徑。核心步驟：DB 端建型別 → 存儲過程遍歷/批次綁定 → Java 端 ArrayDescriptor + ARRAY + setArray。生產環境務必加上交易控制、異常處理、資源關閉、型別描述符快取、FORALL 批次優化，才能穩定高效運行。
