Learning is not a spectator sport

September 11, 2013

12c FETCH FIRST

Filed under: Uncategorized — connormcdonald @ 8:33 pm

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.
About these ads

2 Comments »

  1. […] sacco di link a fonti di informazione sul nuovo Oracle 12c. Scorrendo i link mi sono soffermato su questo, non tanto per l’argomento quanto per l’autore che mi era un nome noto. Arrivato al […]

    Pingback by Oracle 12c: top n query e default | Oracle and other — January 16, 2014 @ 7:13 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The WordPress Classic Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 71 other followers

%d bloggers like this: