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.