Oracle PL/SQL自定义隐式游标查询dblink同结构远端表报错排查

报错根因 #

• 报错1（第9行ORA-00942）：PL/SQL静态SQL不会解析变量值作为表名，你直接将存储远端表名的变量nt写在FROM子句后，Oracle会将nt作为实际表名查找，自然找不到对应表。
• 报错2（第15行PLS-00364）：静态SQL不会解析变量值作为字段名，c1.nf写法会被Oracle识别为取游标行c1下名为nf的固定字段，该字段不存在就会触发游标索引使用无效的报错。

修复后代码 #

采用动态SQL+DBMS_SQL包实现动态表查询、动态字段取值的需求，可直接运行：

CREATE OR REPLACE PROCEDURE test AS
    nt varchar2(20);
    nf varchar2(10);
    js varchar2(400);
    v_cur SYS_REFCURSOR; -- 动态游标变量
    v_cur_id NUMBER; -- DBMS_SQL游标ID
    v_col_val VARCHAR2(4000); -- 存储单字段取值
    v_col_cnt NUMBER; -- 表字段总数
    v_col_desc DBMS_SQL.DESC_TAB; -- 表字段描述集合
BEGIN
    FOR n0 IN 1..2 LOOP
        nt := 't' || n0 || '@DBLINK';
        -- 动态SQL打开对应远端表的游标
        OPEN v_cur FOR 'SELECT * FROM ' || nt;
        -- 转换为DBMS_SQL游标ID用于动态取字段值
        v_cur_id := DBMS_SQL.TO_CURSOR_NUMBER(v_cur);
        DBMS_SQL.DESCRIBE_COLUMNS(v_cur_id, v_col_cnt, v_col_desc);
        -- 预定义所有字段的取值类型
        FOR i IN 1..v_col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN(v_cur_id, i, v_col_val, 4000);
        END LOOP;
        -- 逐行读取表数据
        WHILE DBMS_SQL.FETCH_ROWS(v_cur_id) > 0 LOOP
            js := NULL;
            -- 动态读取f1-f4字段拼接
            FOR n1 IN 1..4 LOOP
                nf := 'f' || n1;
                -- 匹配字段名取对应值
                FOR i IN 1..v_col_cnt LOOP
                    IF v_col_desc(i).col_name = UPPER(nf) THEN
                        DBMS_SQL.COLUMN_VALUE(v_cur_id, i, v_col_val);
                        js := js || v_col_val;
                        EXIT;
                    END IF;
                END LOOP;
            END LOOP;
            DBMS_OUTPUT.PUT_LINE(js);
        END LOOP;
        -- 关闭游标避免资源泄漏
        DBMS_SQL.CLOSE_CURSOR(v_cur_id);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cur_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cur_id);
        END IF;
        -- 若不需要抛出异常可替换为NULL
        RAISE;
END;
/

运行说明 #

执行存储过程前先开启输出：

SET SERVEROUTPUT ON;

调用存储过程即可查看拼接结果：

EXEC test;

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