英文:
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);
移到该过程的末尾,它会运行。不过,我并不确定你究竟想要实现什么目标...
英文:
> 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...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论