MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle 12c Offset & Rows

without comments

Oracle Database 12c provides a limit syntax in SQL for a query with the following clause:

[OFFSET n ROWS] FETCH FIRST m ROWS ONLY

Unfortunately, it can’t be used dynamically like this in a stored function or procedure:

  CURSOR dynamic_cursor
  ( cv_offset  NUMBER
  , cv_rows    NUMBER ) IS
    SELECT   i.item_title
    FROM     item i
    OFFSET cv_offset ROWS FETCH FIRST cv_rows ROWS ONLY;

If you attempt it, you would raise the following error:

CREATE OR REPLACE FUNCTION dynamic_range
*
ERROR at line 1:
ORA-03113: end-of-file ON communication channel
Process ID: 4516
SESSION ID: 78 Serial NUMBER: 4467

The easy solution is to simply write it as a function returning an ADT (Attribute Data Type) collection like:

1
2
3

CREATE OR REPLACE
  TYPE item_title_table AS TABLE OF VARCHAR2(60);
/

The following dynamic_range function returns a collection with a dynamic range bound to the limiting clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

CREATE OR REPLACE FUNCTION dynamic_range
( pv_offset  NUMBER
, pv_rows    NUMBER ) RETURN item_title_table IS
 
  /* Declare a collection type. */
  lv_item_title_table  ITEM_TITLE_TABLE := item_title_table();
 
  /* Local variable length string. */
  lv_item_title   VARCHAR2(60);
 
  /* Declare a local counter. */
  lv_counter  NUMBER := 1;
 
  /* Local NDS statement and cursor variables. */
  lv_stmt    VARCHAR2(2000);
  lv_cursor  SYS_REFCURSOR;
 
BEGIN
 
  /* Assigned a dynamic SQL statement to local variable. */
  lv_stmt := 'SELECT   i.item_title'||CHR(10)
          || 'FROM     item i'||CHR(10)
          || 'OFFSET :bv_offset ROWS FETCH FIRST :bv_rows ROWS ONLY';
 
  /* Open cursor for dynamic DNS statement. */
  OPEN lv_cursor FOR lv_stmt USING pv_offset, pv_rows;
  LOOP
    /* Fetch element from cursor and assign to local variable. */
    FETCH lv_cursor INTO lv_item_title;
 
    /* Exit when no more record found. */
    EXIT WHEN lv_cursor%NOTFOUND;
 
    /* Extend space, assign a value, and increment counter. */
    lv_item_title_table.EXTEND;
    lv_item_title_table(lv_counter) := lv_item_title;
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Close cursor. */
  CLOSE lv_cursor;
 
  /* Return collection. */
  RETURN lv_item_title_table;
END;
/

By using, the following query:

1
2

SELECT   COLUMN_VALUE AS item_title
FROM     TABLE(dynamic_range(2,5));

Hope this helps anybody who wants to make the limiting clause dynamic. You can find out how to embed it in PHP in Chapter 2 of the Oracle Database 12c PL/SQL Programming.

Written by maclochlainn

January 1st, 2014 at 3:09 pm

Posted in Oracle 12c,pl/sql,sql

Tagged with ,

«
»