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:
SQL> declare
2 type table_tt is table of employees%rowtype;
3 v_tt table_tt;
4
5 v_limit_nr number:=10;
6 v_counter_nr number:=0;
7 begin
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
13 end;
14 /
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
SQL>
Doing the same with hard-coded value works fine:
...
12 fetch next 10 rows only; -- hard-code
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
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.
4 comments:
SQL> declare
2 type table_tt is table of all_objects%rowtype;
3 v_tt table_tt;
4
5 v_limit_nr number:=10;
6 v_counter_nr number:=0;
7 begin
8 select *
9 bulk collect into v_tt
10 from all_objects
11 offset v_counter_nr*v_limit_nr rows
12 fetch next v_limit_nr rows only; -- variable
13 end;
14 /
from all_objects
*
ERROR at line 10:
ORA-03113: end-of-file on communication channel
Process ID: 21616
Session ID: 357 Serial number: 7
[oracle]$ uname -rvms
Linux 2.6.39-100.7.1.el6uek.x86_64 #1 SMP Wed May 16 04:04:37 EDT 2012 x86_64
Ok, so it is not only me... Logged SR (#3-7727572911). Will keep everybody posted!
By the way, an interesting finding by my colleague Grigoriy Novikov. The following two cases would work just fine:
...
12 fetch next to_number(v_limit_nr) rows only;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
...
12 fetch next v_limit_nr+0 rows only;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
It seems to be that there is an issue with the datatypes.
Looks then some casting should fix things:
declare
type table_tt is table of all_objects%rowtype;
v_tt table_tt;
v_limit_nr number:=10;
v_counter_nr number:=0;
begin
select *
bulk collect into v_tt
from all_objects
offset cast(v_counter_nr*v_limit_nr as number) rows
fetch next cast(v_limit_nr as number) rows only; -- variable
end;
/
Post a Comment