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

Question

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

Alex Poole · Accepted Answer · 2014-10-07 18:36:34Z

3

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

answered Oct 7, 2014 at 18:36

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Colin P. Hill Over a year ago

What a misleading flowchart! I didn't think it would be necessary - the EXECUTE step is skipped for converted cursors, so I assumed everything before it was also skipped.

Alex Poole Over a year ago

@Malnormalulo - I'm not sure if you can always do the execute before the define; but even if you can, they might have been shown in that order to avoid complications from the execute_and_fetch. But yes, bit confusing in this context.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related