Just how long is forever? In the case of the “one minute CSTORE cursor” from the previous post it’s as long as the cursor remains parsed. A full discussion of cursor parsing and retention is well beyond the scope of this article so we’ll just define it as forever.
As a matter of fact I kept trying the query for 6 straight days while the database was up! Well, that’s not true – I took an hour off for a delicious dinner. That’s not true either – the dinner was not delicious.
Here are the results 6 days later!
-- That cursor still has the same OPEN date -- so the error is still raised SELECT * FROM poly_func(table1) SELECT * * ERROR at line 1: ORA-20000: Cursor is too old ORA-06512: at "D.POLY_PKG", line 34 OPEN 20190203 15463 NOW 20190209 133517 -- A new WHERE clause creates a new cursor so that -- works...for one minute at least. SELECT * FROM poly_func(table1) WHERE col1 = 19; COL1 COL2 COL3 ---------- ---------- ---------- 19 99 9 19 98 8 DESCRIBE 09-FEB-19 OPEN 20190209 133553 NOW 20190209 133553 -- The original still fails SELECT * FROM poly_func(table1); SELECT * * ERROR at line 1: ORA-20000: Cursor is too old ORA-06512: at "D.POLY_PKG", line 34 OPEN 20190203 15463 NOW 20190209 133604
And for the sake of clarity I re-ran the XSTORE cursor on ten thousand rows. Here are the abbreviated results confirming the XSTORE values increasing with each rowset but always starting at 11 because the XSTORE values clean out after the PTF executes.
SELECT * FROM poly_func(table1); COL1 COL2 COL3 ---------- ---------- ---------- 1 1 1 2 2 2 3 3 3 ... 9999 9999 9999 10000 10000 10000 10000 rows selected. 11 12 13 14 15 16 17 18 19 20 SELECT * FROM poly_func(table1); COL1 COL2 COL3 ---------- ---------- ---------- 1 1 1 2 2 2 3 3 3 ... 9999 9999 9999 10000 10000 10000 10000 rows selected. 11 12 13 14 15 16 17 18 19 20
WITH x AS ( SELECT * FROM poly_func(table1) ) SELECT * FROM poly_func(x); COL1 COL2 COL3 ---------- ---------- ---------- 1 1 1 2 2 2 ... 9999 9999 9999 10000 10000 10000 10000 rows selected. 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20
Thanks for reading!