如何在Oracle中描述与动态SQL关联的引用游标？

Question

这是我的第一个(编辑)堆叠溢出问题，所以请容忍我。

在Oracle11g中，我需要描述/询问引用游标从dblink上的另一个数据库上的过程调用返回的底层列，其中实际的并不总是“显式”的，但有时是动态生成的。

例如：

declare
    v_ref_cur sys_refcursor;
    v_cur_num number;
    v_col_count number;
    v_col_table dbms_sql.desc_tab3;
begin
    myProc@myDblink(v_ref_cur, 'myvalue');
    v_cur_num := dbms_sql.to_cursor_number(v_ref_cur);
    dbms_sql.describe_columns3(v_cur_num, v_col_count, v_col_table);
    ...
end

如果另一个数据库上的myProc()有一个“显式”的SQL语句，如下所示：

open cursor for select * from foo where bar = myParam;

光标转换和描述(仍然)工作得很好--我可以确定过程返回的列名、类型、长度等。

但是，如果另一个数据库上的myProc()涉及动态SQL，如：

v_sql := 'select * from foo where bar = ''' || myParam || '''';
open cursor for v_sql;

当试图调用dbms_sql.to_cursor_number()时，我得到一个ORA-01001无效游标错误。

是否有一种方法可以转换/描述从动态SQL派生的引用游标(从远程过程调用)？如果是这样的话，是怎么做的？若否，原因为何？

感谢任何/所有的帮助！

Answer 1

跨数据库链接使用DBMS_SQL会引发许多不同的错误，其中至少有一些是Oracle。通过将所有逻辑放在远程服务器上编译的函数中，可以避免这些问题。然后远程调用该函数。

--Create and test a database link
create database link myself connect to <schema> identified by "<password>"
    using '<connect string or service name>';
select * from dual@myself;

--myProc
create procedure myProc(p_cursor in out sys_refcursor, p_value varchar2) is
begin
    --open p_cursor for select * from dual where dummy = p_value;
    open p_cursor for 'select * from dual where dummy = '''||p_value||'''';
end;
/

--Simple function that counts and displays the columns.  Expected value is 1.
create or replace function count_columns return number is
    v_ref_cur sys_refcursor;
    v_cur_num number;
    v_col_count number;
    v_col_table dbms_sql.desc_tab3;
begin
    --ORA-01001: invalid cursor
    --myProc@myself(v_ref_cur, 'myvalue');
    myProc(v_ref_cur, 'myvalue');
    --ORA-03113: end-of-file on communication channel
    --v_cur_num := dbms_sql.to_cursor_number@myself(v_ref_cur);
    v_cur_num := dbms_sql.to_cursor_number(v_ref_cur);
    --Compilation error: PLS-00306: 
    --    wrong number or types of arguments in call to 'DESCRIBE_COLUMNS3'
    --dbms_sql.describe_columns3@myself(v_cur_num, v_col_count, v_col_table);
    dbms_sql.describe_columns3(v_cur_num, v_col_count, v_col_table);
    return v_col_count;
end;
/

begin
    dbms_output.put_line('Number of columns: '||count_columns@myself());
end;
/

Number of columns: 1