如何从派生自SYS_REFCURSOR的DBMS_SQL游标中获取值? [英] How do I fetch values from a DBMS_SQL cursor derived from a SYS_REFCURSOR?

问题描述

我正在尝试遍历DBMS_SQL.TO_CURSOR_NUMBER中的游标编号，并遇到问题–当我尝试将值拉入变量时，得到ORA-01007(变量不在选择列表中).

I'm trying to iterate over a cursor number from DBMS_SQL.TO_CURSOR_NUMBER, and running into problems – when I try to pull a value into a variable, I get ORA-01007 (variable not in select list).

下面是一个代码块，它重复了我的问题:

Here's a code block that replicates my problem:

DECLARE
  cur SYS_REFCURSOR;
  nm INTEGER;
  colDescs DBMS_SQL.DESC_TAB2;
  numCols INTEGER;
  val VARCHAR2(3);
BEGIN
  OPEN cur FOR
    SELECT 'x' AS foo, 2 AS bar
    FROM dual;
  nm := DBMS_SQL.TO_CURSOR_NUMBER(cur);
  DBMS_SQL.DESCRIBE_COLUMNS2(nm, numCols, colDescs);

  DBMS_OUTPUT.PUT_LINE(numCols);

  DBMS_OUTPUT.PUT_LINE(DBMS_SQL.FETCH_ROWS(nm));

  DBMS_SQL.column_value(nm, 1, val);
  DBMS_OUTPUT.PUT_LINE(val);

  DBMS_SQL.CLOSE_CURSOR(nm);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(nm);
    DBMS_OUTPUT.PUT_LINE('borked '||SQLCODE);
END;
/

预期输出:

2
1
x

实际输出:

2
1
borked -1007

推荐答案

您尚未完成在获取之前:

  DBMS_SQL.DEFINE_COLUMN(nm, 1, val, 3);

这可行:

DECLARE
  cur SYS_REFCURSOR;
  nm INTEGER;
  colDescs DBMS_SQL.DESC_TAB2;
  numCols INTEGER;
  val VARCHAR2(3);
BEGIN
  OPEN cur FOR
    SELECT 'x' AS foo, 2 AS bar
    FROM dual;
  nm := DBMS_SQL.TO_CURSOR_NUMBER(cur);
  DBMS_SQL.DESCRIBE_COLUMNS2(nm, numCols, colDescs);

  DBMS_OUTPUT.PUT_LINE(numCols);

  DBMS_SQL.DEFINE_COLUMN(nm, 1, val, 3);

  DBMS_OUTPUT.PUT_LINE(DBMS_SQL.FETCH_ROWS(nm));

  DBMS_SQL.column_value(nm, 1, val);
  DBMS_OUTPUT.PUT_LINE(val);

  DBMS_SQL.CLOSE_CURSOR(nm);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(nm);
    DBMS_OUTPUT.PUT_LINE('borked '||SQLERRM);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

anonymous block completed
2
1
x

这篇关于如何从派生自SYS_REFCURSOR的DBMS_SQL游标中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！