使用DBMS_SQL执行动态SQL无法从游标提取返回值

问题根源 #

你在EXECUTE_PKG.execute_sql中调用了DBMS_SQL.FETCH_ROWS(l_cursor)，这个操作会将DBMS_SQL游标的内部指针向前移动一行（也就是读取了表中唯一的那一行数据）。当后续调用DBMS_SQL.TO_REFCURSOR(l_cursor)转换为SYS_REFCURSOR时，新的游标会从当前指针位置继续读取，此时已经没有剩余数据，所以调用端的fetch操作返回0行。

修复方案 #

步骤1：移除多余的FETCH_ROWS调用 #

修改EXECUTE_PKG.execute_sql过程，删除l_fetch_result := DBMS_SQL.FETCH_ROWS (l_cursor);这一行代码。不需要提前读取行，转换后的SYS_REFCURSOR会负责从起始位置读取数据。

步骤2：优化日期类型绑定（可选但推荐） #

原代码中日期类型绑定将DATE转成字符串绑定，会导致隐式转换，可能引发性能或数据匹配问题。直接绑定DATE类型即可：

ELSIF l_anydata.GetTypeName () = 'SYS.DATE' THEN
    l_date_value := l_anydata.accessDate ();
    DBMS_SQL.BIND_VARIABLE (l_cursor, l_name, l_date_value); -- 去掉TO_CHAR转换

修复后的包体代码 #

PACKAGE BODY "EXECUTE_PKG"
AS
    PROCEDURE execute_sql (p_sql       IN   VARCHAR2,
                           p_bind_vars IN   anydata_tab,
                           p_cursor    OUT  SYS_REFCURSOR)
    AS
        l_cursor           INTEGER;
        l_name             VARCHAR2 (30);
        l_anydata          SYS.ANYDATA;
        l_varchar2_value   VARCHAR2 (4000);
        l_number_value     NUMBER;
        l_date_value       DATE;
        l_execute_result   INTEGER;
BEGIN
        l_cursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE (l_cursor, p_sql, DBMS_SQL.NATIVE);

        FOR i IN 1 .. p_bind_vars.COUNT LOOP
            l_name := p_bind_vars (i).name;
            l_anydata := p_bind_vars (i).data;

            IF l_anydata.GetTypeName () = 'SYS.VARCHAR2' THEN
                l_varchar2_value := l_anydata.accessVarchar2 ();
                DBMS_SQL.BIND_VARIABLE (l_cursor, l_name, l_varchar2_value);
            ELSIF l_anydata.GetTypeName () = 'SYS.NUMBER' THEN
                l_number_value := l_anydata.accessNumber ();
                DBMS_SQL.BIND_VARIABLE (l_cursor, l_name, l_number_value);
            ELSIF l_anydata.GetTypeName () = 'SYS.DATE' THEN
                l_date_value := l_anydata.accessDate ();
                DBMS_SQL.BIND_VARIABLE (l_cursor, l_name, l_date_value);
            END IF;
        END LOOP;

        l_execute_result := DBMS_SQL.EXECUTE (l_cursor);
        -- 移除DBMS_SQL.FETCH_ROWS调用
        p_cursor := DBMS_SQL.TO_REFCURSOR (l_cursor);
    END execute_sql;
END execute_pkg;

验证结果 #

修复后重新执行execute_my_sql过程，l_fetch_result会返回1，并且能正确打印出Fetched value: 1，符合预期。

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