DBMS_SQL.describe_columns引发ORA-29471:拒绝对DBMS_SQL的访问

huangapple go评论133阅读模式
英文:

DBMS_SQL.describe_columns causes ORA-29471: DBMS_SQL access denied

问题

我对PL/SQL非常陌生。我正在尝试在工作中运行以下存储过程。对DBMS_SQL.describe_columns的调用导致"ORA-29471: DBMS_SQL访问被拒绝"错误。我该如何修复这个问题?我读到一些DBMS_SQL包中的函数和过程在给定无效游标ID时可能会引发此错误,但在失败的行之前,我使用了该游标ID来调用该包,所以我不知道游标ID怎么可能是无效的。

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;--忽略返回值,不适用于SELECT或DDL,仅适用于DML。
    lv_columnCount integer; --不确定这是否在此之外是必需的
    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);

    
    --设置绑定变量...
    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;

    --忽略返回的行数,对于SELECT语句来说是未定义的
    lv_rows := DBMS_SQL.EXECUTE (lv_cursorid);

    po_cursor := dbms_sql.to_refcursor(lv_cursorid);

    --此行失败
    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;
英文:

I am very new to PL/SQL. I am trying to get the following procedure to run at work. The call to DBMS_SQL.describe_columns is causing "ORA-29471: DBMS_SQL access denied" to be thrown. How do I fix this? I read that some functions and procedures within the DBMS_SQL package can throw this error when given an invalid cursor id, but I am using that cursor id in calls to that package prior to the line that fails. So, I don't know how the cursor id could be invalid.

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);
po_cursor := dbms_sql.to_refcursor(lv_cursorid);
-- this line fails
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
end;

答案1

得分: 1

文档中得知:

> 一旦 cursor_number 被转换为 REF CURSOR,cursor_number 将不再可以被任何 DBMS_SQL 操作访问。

如果你将以下这行代码:

po_cursor := dbms_sql.to_refcursor(lv_cursorid);

移到该过程的末尾,它会运行。不过,我并不确定你究竟想要实现什么目标...

英文:

From the documentation:

> Once the cursor_number is transformed into a REF CURSOR, the cursor_number is no longer accessible by any DBMS_SQL operations.

It will run if you move the line

    po_cursor := dbms_sql.to_refcursor(lv_cursorid);

to the end of the procedure. I'm not sure quite what you're trying to achieve though...

huangapple
  • 本文由 发表于 2023年2月24日 03:36:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75549537.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定