This post is more a reminder to myself on how to perform “partial” FETCH in SQL*Plus with no need to use PL/SQL. I’ll need this post in the future for sure since I seem to forget it over time.

By partial I mean FETCH N rows and then wait, leaving the cursor open. This is similar to PL/SQL when the LIMIT parameter is used in a BULK COLLECT or to what the Siebel Connector does when fetching row to fill up a page on the screen. This is also the behavior Oracle expects your application to have when you set FIRST_ROWS_n optimization mode (and this is the whole point of simulating something like this 🙂

Assuming you want to FETCH 10 rows at a time, in SQL*Plus

SQL> set arraysize 10 pagesize 13 pau on

In details:

• arraysize determines how many rows to fetch at a time
• pagesize determines the number of lines in each page
• pause instructs SQL*Plus to stop and wait for you to press ENTER for the next

The trick is to have each row from the rowset account for 1 row in the page otherwise such row will consume more “space” out of the pagination and FETCH will bring more rows than actually displayed (which is not a big deal but over time you will get a page with no need to execute any FETCH). The value 13 in this case accounts for 10 rows from the FETCH plus 1 row for the columns names, one for the line below the column name and one for the empty line above.

SQL> select object_id from dba_objects;


 OBJECT_ID
----------
        20
        46
        28
        15
        29
         3
        25
        41
        54
        40


 OBJECT_ID
----------
        26
        17
        13
         9
        43
        51
        38
         7
        56
        19


 OBJECT_ID
----------
        14
         6
        44
        21
        45
        35
         5
        23
        47
        24

and from the SQL trace

PARSING IN CURSOR #140058741813400 len=33 dep=0 uid=94 oct=3 lid=94 tim=501040793433 hv=1491819215 ad='83d99250' sqlid='ch9x9ppcfqqqg'
select object_id from dba_objects
...
FETCH #140058741813400:c=1036,e=1367,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,plh=2969894235,tim=501040794966
...
FETCH #140058741813400:c=0,e=72,p=0,cr=7,cu=0,mis=0,r=10,dep=0,og=1,plh=2969894235,tim=501040795575
...
FETCH #140058741813400:c=0,e=64,p=0,cr=1,cu=0,mis=0,r=10,dep=0,og=1,plh=2969894235,tim=501041831892
...
FETCH #140058741813400:c=0,e=68,p=0,cr=1,cu=0,mis=0,r=10,dep=0,og=1,plh=2969894235,tim=501045599796

Little disclaimer: because SQL*Plus fetches 1 row only on the first FETCH call we get 1 row “leftover” from one call that we carry to the next. Basically the first page needs 2 calls to fill up the screen, while from the second on we fetch 10 rows but fill the screen with the 1 from the previous call plus 9 from the current call. This is why there are 4 FETCH calls to fill up 3 pages.