Reputation: 422
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
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