Working on the chapter that covers best ways of manipulating with object collection. Of course, if you talk about pagination of the rowset, it is very tempting to test Oracle 12c new feature - OFFSET…FETCH.
Unfortunately, immediately got an issue. It seems to me that passing a variable to FETCH is not supported:
2 type table_tt is table of employees%rowtype;
3 v_tt table_tt;
5 v_limit_nr number:=10;
6 v_counter_nr number:=0;
8 select *
9 bulk collect into v_tt
10 from employees
11 offset v_counter_nr*v_limit_nr rows
12 fetch next v_limit_nr rows only; -- variable
type table_tt is table of employees%rowtype;
ERROR at line 2:
ORA-03113: end-of-file on communication channel
Process ID: 3060
Session ID: 20 Serial number: 35307
Doing the same with hard-coded value works fine:
12 fetch next 10 rows only; -- hard-code
PL/SQL procedure successfully completed.
Couldn’t find any reference to this anywhere, so it looks like a bug. Before I log it, could anybody confirm/deny the issue for different environment?
I am running:
- the official release of Oracle 12c
- on MS Windows 2012 (64-bit)
- on the top of VMWare ESXi 5.1
- VMX-9 version of the file
Update: got that issue confirmed as a bug #17404511. Thanks a lot to everybody who provided their test-cases. Will keep you posted.
Monday, August 26, 2013
Please, don't worry - I didn't suddenly disappear from the surface of the world of databases. It just happened that after some years of staying outside of publishing business I've been recruited to make a new book.The tentative name/dates are: PL/SQL Performance Tuning Guide: Tips and Techniques (Oracle Press, ETA late spring 2014).
Good news: my publisher authorized me to use interesting findings as spoilers. So, prepare yourselves for new discoveries in the land of PL/SQL!