18c Row_num Polymorphic Table Function Example

View All Scripts Login to Run Script

Script Name 18c Row_num Polymorphic Table Function Example
Description The row_num PTF example appends a sequence column to a table.
Area PL/SQL General
Referenced In Database PL/SQL Packages and Types Reference
Contributor Sarah Hirschfeld (Oracle)
Created Friday February 23, 2018

Statement 1

The parameters are : tab (the input table), ini (the initial value with a default value of 1), and inc (the amount to increment with a default value of 1)

Create the PTF implementation package row_num_p.

CREATE PACKAGE row_num_p IS 
  FUNCTION describe(tab IN OUT dbms_tf.table_t,  
                    ini NUMBER DEFAULT 1,  
                    inc NUMBER DEFAULT 1)  
           RETURN dbms_tf.describe_t; 
 
  PROCEDURE fetch_rows(ini NUMBER DEFAULT 1, inc NUMBER DEFAULT 1); 
END;

Package created.

Statement 2

This PTF accepts any input table and appends the sequence column ROW_ID to the table. The sequence values start with the specified value (ini) and each time it is incremented by the specified value (inc).

CREATE PACKAGE BODY row_num_p IS 
  FUNCTION describe(tab IN OUT dbms_tf.table_t,  
                    ini NUMBER DEFAULT 1,  
                    inc NUMBER DEFAULT 1)  
           RETURN dbms_tf.describe_t AS 
  BEGIN 
    RETURN dbms_tf.describe_t(new_columns => dbms_tf.columns_new_t(1 => 
               dbms_tf.column_metadata_t(name => 'ROW_ID',  
                                         TYPE => dbms_tf.type_number))); 
  END; 
 
  PROCEDURE fetch_rows(ini NUMBER DEFAULT 1, inc NUMBER DEFAULT 1) IS 
    row_cnt CONSTANT PLS_INTEGER := dbms_tf.get_env().row_count; 
    rid     NUMBER               := ini; 
    col     dbms_tf.tab_number_t; 
  BEGIN 
    dbms_tf.xstore_get('rid', rid); 
    FOR i IN 1 .. row_cnt LOOP col(i) := rid + inc*(i-1); END LOOP; 
    dbms_tf.put_col(1, col); 
    dbms_tf.xstore_set('rid', rid + inc*row_cnt); 
  END; 
 
END;

Package Body created.

Statement 3
Specify exactly one formal argument of type TABLE, specify the return type of the PTF as TABLE, specify a Table Semantics PTF type, and indicate the PTF implementation package to use is row_num_p.
Create a standalone polymorphic table function named row_num.
```
CREATE FUNCTION row_num(tab TABLE, 
                        ini NUMBER DEFAULT 1,  
                        inc NUMBER DEFAULT 1)  
           RETURN TABLE 
           PIPELINED TABLE POLYMORPHIC USING row_num_p; 
```
```
Function created.
```
Statement 4
The row_num PTF invocation reporting from the SCOTT.DEPT table produces a new column ROW_ID with value starting at 1 and incremented by 1 in the row set.
Invoke the PTF in a query.
```
SELECT * FROM row_num(scott.dept)
```
```
DEPTNO DNAME LOC ROW_ID
10 ACCOUNTING NEW YORK 1
20 RESEARCH DALLAS 2
30 SALES CHICAGO 3
40 OPERATIONS BOSTON 4

4 rows selected.
```
Statement 5
The row_num PTF invocation reporting from the SCOTT.DEPT table produces a new column ROW_ID with value starting at 100 and incremented by 1 in the row set.
```
SELECT * FROM row_num(scott.dept, 100)
```
```
DEPTNO DNAME LOC ROW_ID
10 ACCOUNTING NEW YORK 100
20 RESEARCH DALLAS 101
30 SALES CHICAGO 102
40 OPERATIONS BOSTON 103

4 rows selected.
```

DEPTNO	DNAME	LOC	ROW_ID
10	ACCOUNTING	NEW YORK	1
20	RESEARCH	DALLAS	2
30	SALES	CHICAGO	3
40	OPERATIONS	BOSTON	4

DEPTNO	DNAME	LOC	ROW_ID
10	ACCOUNTING	NEW YORK	100
20	RESEARCH	DALLAS	101
30	SALES	CHICAGO	102
40	OPERATIONS	BOSTON	103

Statement 6

The row_num PTF invocation reporting from the SCOTT.DEPT table produces a new column ROW_ID with value starting at 0 and decremented by 1 in the row set.

SELECT * FROM row_num(scott.dept, ini => 0, inc => -1)


DEPTNO DNAME LOC ROW_ID
10 ACCOUNTING NEW YORK 0
20 RESEARCH DALLAS -1
30 SALES CHICAGO -2
40 OPERATIONS BOSTON -3

4 rows selected.

DEPTNO	DNAME	LOC	ROW_ID
10	ACCOUNTING	NEW YORK	0
20	RESEARCH	DALLAS	-1
30	SALES	CHICAGO	-2
40	OPERATIONS	BOSTON	-3

Statement 7

The row_num PTF invocation reporting from the SCOTT.EMP table produces a new column ROW_ID with value starting at 0 and incremented by 0.25 in the row set which is partitioned by department number and ordered by employee name.

SELECT deptno, ename, job, sal, row_id 
FROM   row_num(scott.emp PARTITION BY deptno ORDER BY ename, ini => 0, inc => 0.25) 
WHERE  deptno IN (10, 30)


DEPTNO ENAME JOB SAL ROW_ID
10 CLARK MANAGER 2450 0
10 KING PRESIDENT 5000 .25
10 MILLER CLERK 1300 .5
30 ALLEN SALESMAN 1600 0
30 BLAKE MANAGER 2850 .25
30 JAMES CLERK 950 .5
30 MARTIN SALESMAN 1250 .75
30 TURNER SALESMAN 1500 1
30 WARD SALESMAN 1250 1.25

9 rows selected.

DEPTNO	ENAME	JOB	SAL	ROW_ID
10	CLARK	MANAGER	2450	0
10	KING	PRESIDENT	5000	.25
10	MILLER	CLERK	1300	.5
30	ALLEN	SALESMAN	1600	0
30	BLAKE	MANAGER	2850	.25
30	JAMES	CLERK	950	.5
30	MARTIN	SALESMAN	1250	.75
30	TURNER	SALESMAN	1500	1
30	WARD	SALESMAN	1250	1.25

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database