无法通过JDBC获取OUT参数中的二维关联数组问题排查

咱们一步步拆解你遇到的两个问题：

第一个错误：语法报错（ORA-06550 + PLS-00103） #

你JDBC里定义的调用字符串多了个不必要的分号：

String proc = "{call TXN_CONSOLIDATION_PKG.TXN_CONSOLIDATION_PROC (?,?,?,?,?,?,?);}";

PL/SQL的CALL语句不需要末尾的分号，把它去掉就能解决这个语法问题：

String proc = "{call TXN_CONSOLIDATION_PKG.TXN_CONSOLIDATION_PROC (?,?,?,?,?,?,?)}";

第二个错误：参数类型不匹配（ORA-06550 + PLS-00306 + PLS-00418） #

这是核心问题：JDBC的registerIndexTableOutParameter只能处理一维的PL/SQL索引表（关联数组），完全不支持嵌套的关联数组结构。你定义的final_txn_list_type是嵌套层级的（外层存储内层数组的索引表），JDBC无法识别这种嵌套类型，所以会报类型不匹配的错误。

这里给你两个可行的解决方案，优先推荐第一个，因为更简单易用：

方案1：改用REF CURSOR返回结果集（最推荐） #

把输出参数换成SYS_REFCURSOR，这是JDBC处理PL/SQL返回多行数据最常用的方式，兼容性最好。

步骤1：修改PL/SQL包

先定义一个记录类型对应你的行结构，然后用SYS_REFCURSOR输出结果：

CREATE OR REPLACE PACKAGE TXN_CONSOLIDATION_PKG AS
    -- 定义记录类型，根据你实际的列数和类型调整字段
    TYPE txn_info_rec_type IS RECORD (
        col1 VARCHAR2(100),
        col2 VARCHAR2(100),
        col3 VARCHAR2(100)
        -- 继续添加你需要的列
    );
    
    PROCEDURE TXN_CONSOLIDATION_PROC (
        tableName IN VARCHAR2, 
        tableType IN VARCHAR2, 
        startDate IN VARCHAR2, 
        endDate IN VARCHAR2, 
        cardNumber IN VARCHAR2, 
        rowCount IN INTEGER,
        final_txn_list OUT SYS_REFCURSOR
    );
END TXN_CONSOLIDATION_PKG;
/

CREATE OR REPLACE PACKAGE BODY TXN_CONSOLIDATION_PKG AS
    PROCEDURE TXN_CONSOLIDATION_PROC (
        tableName IN VARCHAR2, 
        tableType IN VARCHAR2, 
        startDate IN VARCHAR2, 
        endDate IN VARCHAR2, 
        cardNumber IN VARCHAR2, 
        rowCount IN INTEGER,
        final_txn_list OUT SYS_REFCURSOR
    ) IS
    BEGIN
        -- 替换成你原来的重复行合并逻辑，最后用OPEN...FOR返回结果集
        -- 示例（根据你的实际逻辑调整）：
        OPEN final_txn_list FOR
            SELECT col1, col2, col3
            FROM your_target_table
            WHERE card_number = cardNumber
              AND txn_date BETWEEN TO_DATE(startDate, 'MM/DD/YYYY HH24:MI:SS') 
                               AND TO_DATE(endDate, 'MM/DD/YYYY HH24:MI:SS')
            -- 这里加上你的合并逻辑，比如GROUP BY或者窗口函数
            FETCH FIRST rowCount ROWS ONLY;
    END TXN_CONSOLIDATION_PROC;
END TXN_CONSOLIDATION_PKG;
/

步骤2：修改JDBC代码处理REF CURSOR

Connection c = DriverManager.getConnection("url","username","pwd");
String proc = "{call TXN_CONSOLIDATION_PKG.TXN_CONSOLIDATION_PROC (?,?,?,?,?,?,?)}";
OracleCallableStatement s = (OracleCallableStatement)c.prepareCall(proc);

// 设置输入参数
s.setString(1, "F");
s.setString(2, "D");
s.setString(3, "01/01/2019 00:00:00");
s.setString(4, "11/01/2019 00:00:00");
s.setString(5, "Joe");
s.setInt(6, 100);

// 注册REF CURSOR类型的输出参数
s.registerOutParameter(7, OracleTypes.CURSOR);

s.execute();

// 获取并处理结果集
ResultSet rs = s.getCursor(7);
while (rs.next()) {
    // 根据列名或索引获取数据
    String col1 = rs.getString("col1");
    String col2 = rs.getString("col2");
    // 处理每一行的数据
    System.out.println("Col1: " + col1 + ", Col2: " + col2);
}

// 关闭资源（实际项目中建议用try-with-resources）
rs.close();
s.close();
c.close();

方案2：改用SQL层面的嵌套表类型（如果必须用数组） #

如果你坚持要使用数组输出，需要把PL/SQL的关联数组转换成SQL层面的嵌套表类型（JDBC支持这种顶层SQL类型）：

步骤1：创建SQL层面的类型

-- 创建内层的VARCHAR2数组类型（必须是SQL层面，不是PL/SQL）
CREATE OR REPLACE TYPE txn_info_array_sql_type IS TABLE OF VARCHAR2(100);
/

-- 创建外层的嵌套表类型
CREATE OR REPLACE TYPE final_txn_list_sql_type IS TABLE OF txn_info_array_sql_type;
/

步骤2：修改PL/SQL包使用这些SQL类型

CREATE OR REPLACE PACKAGE TXN_CONSOLIDATION_PKG AS
    PROCEDURE TXN_CONSOLIDATION_PROC (
        tableName IN VARCHAR2, 
        tableType IN VARCHAR2, 
        startDate IN VARCHAR2, 
        endDate IN VARCHAR2, 
        cardNumber IN VARCHAR2, 
        rowCount IN INTEGER,
        final_txn_list IN OUT final_txn_list_sql_type
    );
END TXN_CONSOLIDATION_PKG;
/

步骤3：修改JDBC代码处理SQL嵌套表

Connection c = DriverManager.getConnection("url","username","pwd");
String proc = "{call TXN_CONSOLIDATION_PKG.TXN_CONSOLIDATION_PROC (?,?,?,?,?,?,?)}";
OracleCallableStatement s = (OracleCallableStatement)c.prepareCall(proc);

// 设置输入参数
s.setString(1, "F");
s.setString(2, "D");
s.setString(3, "01/01/2019 00:00:00");
s.setString(4, "11/01/2019 00:00:00");
s.setString(5, "Joe");
s.setInt(6, 100);

// 注册输出参数，指定SQL类型的名称（注意是大写）
s.registerOutParameter(7, OracleTypes.ARRAY, "FINAL_TXN_LIST_SQL_TYPE");

s.execute();

// 获取嵌套数组并处理
Array outerArray = s.getArray(7);
Object[] outerElements = (Object[])outerArray.getArray();

for (Object innerObj : outerElements) {
    Array innerArray = (Array)innerObj;
    String[] innerElements = (String[])innerArray.getArray();
    // 处理每一行的列数据
    System.out.println("Row data: " + String.join(", ", innerElements));
}

// 关闭资源
s.close();
c.close();

这个方案需要注意用户需要有创建SQL类型的权限，而且处理逻辑比REF CURSOR复杂，所以优先推荐方案1。

内容的提问来源于stack exchange，提问作者user973179