Java调用Oracle SQL函数报错：参数数量或类型不匹配

1. 原Oracle SQL函数定义 #

CREATE OR REPLACE FUNCTION check_query(p_query_text VARCHAR2) RETURN SYS_REFCURSOR IS
    txt VARCHAR2(4000);
    from_clause VARCHAR2(4000);
    l_qry NVARCHAR2(4000);
    ret SYS_REFCURSOR; -- Define the SYS_REFCURSOR type for result set
    total_count NUMBER; -- Variable to store the total count

BEGIN
    IF p_query_text IS NOT NULL THEN
        txt := p_query_text;
        from_clause := 'toa_app_data WHERE';

        IF txt IS NOT NULL AND LENGTH(txt) > 0 THEN
            l_qry := 'SELECT * FROM toa_app_data WHERE app_id IN (SELECT toa_app_data.app_id FROM ' || from_clause ||
                     ' ' || txt || ') FETCH FIRST 5 ROWS ONLY'; -- Fetch top 5 rows
        ELSE
            l_qry := 'SELECT * FROM toa_app_data WHERE 1 = 0'; -- Return empty result set
        END IF;
            DBMS_OUTPUT.PUT_LINE('l_qry: ' || l_qry);
        OPEN ret FOR l_qry; -- Open the cursor for the top 5 rows or empty result set

        -- Get the total count for the query
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM toa_app_data WHERE app_id IN (SELECT toa_app_data.app_id FROM ' || from_clause || ' ' || txt || ')'
        INTO total_count;

    ELSE
        -- Open an empty cursor when p_query_text is NULL
        OPEN ret FOR SELECT * FROM toa_app_data WHERE 1 = 0;

        -- Set total_count as 0 when there is no input query
        total_count := 0;
    END IF;

    -- Print the total count (optional)
    DBMS_OUTPUT.PUT_LINE('Total count: ' || total_count);

    RETURN ret; -- Return the SYS_REFCURSOR with the top 5 rows or an empty cursor
END check_query;

2. 原Java调用代码 #

public List validateQuery(String queryString) throws SQLException{
    logger.debug("In validateQuery: query- ");
    //String retValue = null;
    Long totalCount = 0L;
    List list = new ArrayList();
    try{
        StoredProcedureQuery query = queryDao.getEntityManager().createStoredProcedureQuery(VALIDATE_QUERY_BEFORE_SAVE);
        query.registerStoredProcedureParameter(QUERY_STRING_TEXT, String.class, ParameterMode.IN);
        query.setParameter(QUERY_STRING_TEXT, queryString);
        query.registerStoredProcedureParameter("ret", Class.forName("java.sql.ResultSet"), ParameterMode.REF_CURSOR);
        query.registerStoredProcedureParameter("total_count", Long.class, ParameterMode.OUT);
        query.execute();
        list = query.getResultList();
        //retValue = (String) query.getOutputParameterValue("ret");
        totalCount = ((Number) query.getOutputParameterValue("total_count")).longValue();
    
        
    }catch (Exception ex) {
            logger.error(ex.getMessage(), ex);
        }
    
    return list;
}

3. 错误日志 #

Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHECK_QUERY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

错误原因分析 #

1. 参数数量不匹配：原Oracle函数仅接收1个IN参数p_query_text，返回值为SYS_REFCURSOR，内部计算的total_count并未对外暴露为输出参数。但Java代码中额外注册了ret（REF_CURSOR）和total_count（OUT）两个参数，导致调用时参数数量与函数定义不符。
2. 函数调用方式错误：原代码用createStoredProcedureQuery处理函数，但JPA中函数的返回值是直接返回的，而非通过OUT参数传递；同时用java.sql.ResultSet注册REF_CURSOR参数不符合Oracle的类型要求。
3. 需求与实现不匹配：原需求需要获取总记录数，但原函数仅在内部打印该值，未提供对外获取的途径。

解决方案 #

步骤1：修改Oracle逻辑为存储过程（更适配JPA调用） #

将原函数改为存储过程，明确暴露游标和总记录数两个输出参数：

CREATE OR REPLACE PROCEDURE check_query(p_query_text VARCHAR2, p_ret OUT SYS_REFCURSOR, p_total_count OUT NUMBER) IS
    txt VARCHAR2(4000);
    from_clause VARCHAR2(4000);
    l_qry NVARCHAR2(4000);
    total_count NUMBER;

BEGIN
    IF p_query_text IS NOT NULL THEN
        txt := p_query_text;
        from_clause := 'toa_app_data WHERE';

        IF txt IS NOT NULL AND LENGTH(txt) > 0 THEN
            l_qry := 'SELECT * FROM toa_app_data WHERE app_id IN (SELECT toa_app_data.app_id FROM ' || from_clause ||
                     ' ' || txt || ') FETCH FIRST 5 ROWS ONLY';
        ELSE
            l_qry := 'SELECT * FROM toa_app_data WHERE 1 = 0';
        END IF;
        DBMS_OUTPUT.PUT_LINE('l_qry: ' || l_qry);
        OPEN p_ret FOR l_qry;

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM toa_app_data WHERE app_id IN (SELECT toa_app_data.app_id FROM ' || from_clause || ' ' || txt || ')'
        INTO total_count;

    ELSE
        OPEN p_ret FOR SELECT * FROM toa_app_data WHERE 1 = 0;
        total_count := 0;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Total count: ' || total_count);
    p_total_count := total_count;
END check_query;

步骤2：调整Java调用代码 #

使用OracleTypes.CURSOR正确注册REF_CURSOR参数，匹配存储过程的参数定义：

import oracle.jdbc.OracleTypes;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;
import java.util.ArrayList;
import java.util.List;

public List validateQuery(String queryString) {
    logger.debug("In validateQuery: query- ");
    Long totalCount = 0L;
    List list = new ArrayList();
    try{
        StoredProcedureQuery query = queryDao.getEntityManager().createStoredProcedureQuery("check_query");
        
        // 注册IN参数：传入的WHERE子句
        query.registerStoredProcedureParameter("p_query_text", String.class, ParameterMode.IN);
        query.setParameter("p_query_text", queryString);
        
        // 注册REF_CURSOR输出参数：返回前5行数据
        query.registerStoredProcedureParameter("p_ret", OracleTypes.CURSOR, ParameterMode.REF_CURSOR);
        
        // 注册OUT参数：返回总记录数
        query.registerStoredProcedureParameter("p_total_count", Long.class, ParameterMode.OUT);
        
        query.execute();
        
        // 获取游标结果集
        list = query.getResultList();
        // 获取总记录数
        totalCount = ((Number) query.getOutputParameterValue("p_total_count")).longValue();
        
    }catch (Exception ex) {
        logger.error(ex.getMessage(), ex);
    }
    
    return list;
}

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