Monday, August 26, 2013

Oracle 12c OFFSET...FETCH.. bug?

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;
  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

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.

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.

Staying quiet... For a good reason :-)

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!