12c FETCH FIRST

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.

2 comments

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.