CODEWITHSUNDEEP
oracle-databaseoracle11g
Colin P. Hill
Colin P. Hill

Reputation: 422

How do I fetch values from a DBMS_SQL cursor derived from a SYS_REFCURSOR?

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;
/

Expected output:

2
1
x

Actual output:

2
1
borked -1007

Upvotes: 2

Views: 2626

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You haven't done the DEFINE_COLUMN step(s); before you fetch:

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

So this works:

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

Upvotes: 3

Related Questions