Posted by Tom Kyte’s latest magazine article talks about the new FETCH FIRST syntax in 12c. You can read about that at:
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html
There’s a (quite valid) comment at the end of the article:
“Note that in real life, you would use bind variables instead of hard-coded literals, so instead of using the number 5 as I did, you would have bound in the number 5.”
But Michael Rosenblum’s blog first drew my attention to the problem you might encounter when you do start using bind variables (http://wonderingmisha.blogspot.com.au/2013/08/oracle-12c-offsetfetch-bug.html), which I’ll reproduce here, together with a workaround that will still let you use bind variables.
SQL> drop table T purge;
Table dropped.
SQL> create table T as select * from all_objects;
Table created.
SQL> select owner, object_name, object_id
2 from t
3 order by owner, object_name
4 FETCH FIRST 5 ROWS ONLY;
OWNER OBJECT_NAME OBJECT_ID
--------------- ---------------------------------------- ----------
APEX_040200 APEX 88901
APEX_040200 APEX$ARCHIVE_CONTENTS 89728
APEX_040200 APEX$ARCHIVE_CONTENTS_IDX1 89732
APEX_040200 APEX$ARCHIVE_HEADER 89726
APEX_040200 APEX$ARCHIVE_HISTORY 89733
SQL> variable x number
SQL> exec :x := 5
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_id
2 from t
3 order by owner, object_name
4 FETCH FIRST :x ROWS ONLY;
OWNER OBJECT_NAME OBJECT_ID
--------------- ---------------------------------------- ----------
APEX_040200 APEX 88901
APEX_040200 APEX$ARCHIVE_CONTENTS 89728
APEX_040200 APEX$ARCHIVE_CONTENTS_IDX1 89732
APEX_040200 APEX$ARCHIVE_HEADER 89726
APEX_040200 APEX$ARCHIVE_HISTORY 89733
SQL> declare
2 x number := 5;
3 begin
4 for i in (
5 select owner, object_name, object_id
6 from t
7 order by owner, object_name
8 FETCH FIRST x ROWS ONLY
9 )
10 loop
11 null;
12 end loop;
13 end;
14 /
FETCH FIRST x ROWS ONLY
*
ERROR at line 8:
ORA-03113: end-of-file on communication channel
Process ID: 22229
Session ID: 131 Serial number: 46305
SQL> declare
2 x number := 5;
3 begin
4 for i in (
5 select owner, object_name, object_id
6 from t
7 order by owner, object_name
8 FETCH FIRST cast(x as number) ROWS ONLY
9 )
10 loop
11 null;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
Learning is not a spectator sport 
2 comments