调用DBMS_SQL.describe_columns触发ORA-29471权限错误，如何解决？

错误根源 #

调用dbms_sql.to_refcursor(lv_cursorid)后，原DBMS_SQL游标ID的控制权会被完全移交到REF CURSOR对象，此时该游标ID已失效，无法再用于后续的DBMS_SQL.describe_columns等操作，这就是触发ORA-29471错误的直接原因。

修正方案 #

调整代码执行顺序，在转换为REF CURSOR之前完成所有DBMS_SQL相关操作，包括列描述和列定义：

1. 先执行DBMS_SQL.describe_columns获取列信息
2. 循环定义各列类型
3. 最后调用dbms_sql.to_refcursor转换游标

修正后的存储过程代码 #

procedure ExecuteQuery (
      pio_report        IN OUT reports_dictionary_tab%ROWTYPE
      , pi_rpt_params   in out Report_Params2_obj
      , pi_vars         vchar100_tab_ty
      , pi_binds        vchar100_tab_ty
      , po_cursor       out gv_rc
      , po_columnDesc   out DBMS_SQL.desc_tab
      , pi_debug boolean default gv_debug
) is
    lv_rows integer;--Ignore return, not valid for SELECT or DDL, only DML.
    lv_columnCount integer; --Not sure if this is needed outside of this
    lv_cursorid integer;
begin

    lv_cursorid := DBMS_SQL.open_cursor;
    
    DBMS_SQL.parse( c => lv_cursorid, statement => pio_report.query, language_flag => dbms_sql.native);

    
    --set the bind variables...
    begin
        for b in 1..pi_vars.count loop
            DBMS_SQL.bind_variable ( c => lv_cursorid, name => pi_binds (b), value => pi_vars (b));
        end loop;

    exception
        when others then
            DebugOut( pi_table_name => 'Binds', pi_table => pi_binds , pi_debug => pi_debug);
            DebugOut( pi_table_name => 'Values', pi_table => pi_vars , pi_debug => pi_debug);
    end;

    --Ignore the row count coming back, it is undefined
    --for SELECT statements
    lv_rows := DBMS_SQL.EXECUTE (lv_cursorid);

    -- 调整顺序：先执行列描述和定义，再转换为REF CURSOR
    DBMS_SQL.describe_columns( c=> lv_cursorid, col_cnt => lv_columnCount , desc_t => po_columnDesc );

    for c in 1..po_columnDesc.count loop

        case po_columnDesc(c).col_type

            when gv_type_varchar then dbms_sql.define_column( c=> lv_cursorid, position => c, column => gv_datatype_varchar, column_size => gv_vchar2_col_size );
            when gv_type_char then dbms_sql.define_column( c=> lv_cursorid, position => c, column => gv_datatype_varchar, column_size => gv_vchar2_col_size );
            when gv_type_number then dbms_sql.define_column( c=> lv_cursorid, position => c, column => gv_datatype_number );
            when gv_type_date then dbms_sql.define_column( c=> lv_cursorid, position => c, column => gv_datatype_date );
            when gv_type_tstamp_tz then dbms_sql.define_column( c=> lv_cursorid, position => c, column => gv_datatype_tstamp_tz );
            when gv_type_clob then dbms_sql.define_column( c=> lv_cursorid, position => c, column => gv_datatype_clob );
            else DebugOut ( pi_text => 'Unknown Column type '||po_columnDesc(c).col_type||' for "'||po_columnDesc(c).col_name||'"' , pi_debug => pi_debug);
        end case;

    end loop; --End Column Definition Loop

    -- 最后转换为REF CURSOR
    po_cursor := dbms_sql.to_refcursor(lv_cursorid);

end;

额外提示 #

• 转换为REF CURSOR后，无需手动关闭原DBMS_SQL游标，Oracle会自动处理游标资源释放。
• 若后续仍需使用DBMS_SQL游标操作，请勿执行to_refcursor转换，保持DBMS_SQL游标生命周期独立。

内容的提问来源于stack exchange，提问作者Michael S. Miller