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