Example 169-11 Stack Polymorphic Table Function Example

Create the PTF implementation package stack_p.

CREATE PACKAGE stack_p AS

  FUNCTION  describe(tab  IN OUT dbms_tf.table_t, 
                     col         dbms_tf.columns_t)
            RETURN dbms_tf.describe_t;

  PROCEDURE fetch_rows;

END stack_p; 

Create the PTF implementation package body stack_p.

This PTF produces two new columns, COLUMN_NAME and COLUMN_VALUE, where the former contains the name of the unpivoted column and the latter contains the numeric value of that column. Additionally, the unpivoted columns are removed from the PTF's output.

CREATE PACKAGE BODY stack_p AS

  FUNCTION  describe(tab  IN OUT dbms_tf.table_t, 
                     col         dbms_tf.columns_t)
            RETURN dbms_tf.describe_t  AS
  BEGIN
    FOR i IN 1 .. tab.column.count LOOP
      FOR j IN 1 .. col.count LOOP
        IF (tab.column(i).description.name = col(j) AND
            tab.column(i).description.TYPE = dbms_tf.type_number) THEN
          tab.column(i).pass_through := false;
          tab.column(i).for_read     := true;
        END IF;
      END LOOP;
    END LOOP;

    RETURN dbms_tf.describe_t(
             new_columns => dbms_tf.columns_new_t(
               1 => dbms_tf.column_metadata_t(name => 'COLUMN_NAME', 
                                              TYPE => dbms_tf.type_varchar2),
               2 => dbms_tf.column_metadata_t(name => 'COLUMN_VALUE',
                                              TYPE => dbms_tf.type_number)),
             row_replication => true);
  END;

  PROCEDURE fetch_rows  AS
    env    dbms_tf.env_t := dbms_tf.get_env();
    rowset dbms_tf.row_set_t;
    colcnt PLS_INTEGER;
    rowcnt PLS_INTEGER;
    repfac dbms_tf.tab_naturaln_t;
    namcol dbms_tf.tab_varchar2_t;
    valcol dbms_tf.tab_number_t; 
  BEGIN 
    dbms_tf.get_row_set(rowset, rowcnt, colcnt);

    FOR i IN 1 .. rowcnt LOOP 
      repfac(i) := 0; 
    END LOOP;

    FOR r IN 1 .. rowcnt LOOP
      FOR c IN 1 .. colcnt LOOP
        IF rowset(c).tab_number(r) IS NOT NULL THEN
          repfac(r)                    := repfac(r) + 1;
          namcol(nvl(namcol.last+1,1)) := 
            INITCAP(regexp_replace(env.get_columns(c).name, '^"|"$'));
          valcol(NVL(valcol.last+1,1)) := rowset(c).tab_number(r);
        END IF;
      END LOOP;
    END LOOP;

    dbms_tf.row_replication(replication_factor => repfac);
    dbms_tf.put_col(1, namcol);
    dbms_tf.put_col(2, valcol);

  END;

END stack_p; 

Create the standalone PTF named stack. Specify exactly one formal argument of type TABLE, specify the return type of the PTF as TABLE, specify a Row Semantics PTF type, and indicate the PTF implementation package to use is stack_p.

CREATE FUNCTION stack(tab TABLE, 
                      col columns)
                  RETURN TABLE  
PIPELINED ROW POLYMORPHIC USING stack_p;

For all employees in departments 10 and 30, report values of columns MGR, SAL, and COMM ordered by department number and employee name.

SELECT   deptno, ename, column_name, column_value
FROM     stack(scott.emp, COLUMNS(mgr, sal, comm)) 
WHERE    deptno IN (10, 30)
ORDER BY deptno, ename;

    DEPTNO ENAME      COLUMN_NAME     COLUMN_VALUE
---------- ---------- --------------- ------------
        10 CLARK      Mgr                     7839
        10 CLARK      Sal                     2450
        10 KING       Sal                     5000
        10 MILLER     Sal                     1300
        10 MILLER     Mgr                     7782
        30 ALLEN      Comm                     300
        30 ALLEN      Mgr                     7698
        30 ALLEN      Sal                     1600
        30 BLAKE      Mgr                     7839
        30 BLAKE      Sal                     2850
        30 JAMES      Sal                      950
        30 JAMES      Mgr                     7698
        30 MARTIN     Comm                    1400
        30 MARTIN     Mgr                     7698
        30 MARTIN     Sal                     1250
        30 TURNER     Comm                       0
        30 TURNER     Sal                     1500
        30 TURNER     Mgr                     7698
        30 WARD       Comm                     500
        30 WARD       Mgr                     7698
        30 WARD       Sal                     1250

Example 169-13 Rand_col Polymorphic Table Function Example

This rand_col PTF example appends col_count number of random-valued columns to the output. Optionally, the caller can restrict the random values to a numeric range by specifying [low, high]. The new columns are named "RAND_<n>”

Create the PTF implementation package rand_col_p.

The parameters are :

• tab : Input table

• col_count (optional) : Number of random-valued columns to generate [Default = 1]

• low (optional) : Lower bound for the random numbers [Default = Null]

• high (optional) : Upper bound for the random numbers [Default = Null]

CREATE PACKAGE rand_col_p AS

  FUNCTION describe(tab       IN OUT DBMS_TF.table_t, 
                    col_count        NATURALN DEFAULT 1,  
                    low              NUMBER   DEFAULT NULL, 
                    high             NUMBER   DEFAULT NULL) 
           RETURN DBMS_TF.describe_t;

  PROCEDURE fetch_rows(col_count NATURALN DEFAULT 1,  
                       low       NUMBER   DEFAULT NULL, 
                       high      NUMBER   DEFAULT NULL);

END rand_col_p; 

Create the PTF implementation package body rand_col_p.

The parameter col_count is a 'shape-determining' parameter and thus must be a constant (no binds, correlations, or expressions). By defining the type of col_count to be NATURALN, which has an implicit NOT NULL constraint, we guarantee that a cursor with non-constant value for this parameter will get a compilation error.

CREATE PACKAGE BODY rand_col_p AS
  col_name_prefix CONSTANT dbms_id := 'RAND_';

  FUNCTION describe(tab       IN OUT DBMS_TF.table_t, 
                    col_count        NATURALN DEFAULT 1,  
                    low              NUMBER   DEFAULT NULL, 
                    high             NUMBER   DEFAULT NULL) 
           RETURN DBMS_TF.describe_t
  AS 
    cols DBMS_TF.columns_new_t;
  BEGIN 
    FOR i IN 1 .. col_count LOOP
     cols(i):= DBMS_TF.column_metadata_t(name=>col_name_prefix||i, TYPE=>DBMS_TF.type_number);
    END LOOP;

    RETURN DBMS_TF.describe_t(new_columns => cols);
  END;

  PROCEDURE fetch_rows(col_count NATURALN DEFAULT 1,  
                       low       NUMBER   DEFAULT NULL, 
                       high      NUMBER   DEFAULT NULL) 
  AS
    row_count CONSTANT PLS_INTEGER := DBMS_TF.get_env().row_count;
    col       DBMS_TF.tab_number_t;
  BEGIN
    FOR c IN 1 .. col_count LOOP
      FOR i IN 1 .. row_count LOOP
        col(i) :=  CASE WHEN (low IS NULL OR high IS NULL)
                        THEN dbms_random.VALUE 
	                     ELSE dbms_random.VALUE(low, high) 	                
	                 END;
      END LOOP;
      DBMS_TF.put_col(c, col);
    END LOOP;
  END;

END rand_col_p; 

Create the standalone rand_col PTF. Specify exactly one formal argument of type TABLE, specify the return type of the PTF as TABLE, specify a Row Semantics PTF type, and indicate the PTF implementation package to use is rand_col_p.

CREATE FUNCTION rand_col(tab TABLE, 
                         col_count NATURALN DEFAULT 1,  
                         low       NUMBER   DEFAULT NULL, 
                         high      NUMBER   DEFAULT NULL)
                  RETURN TABLE 
                  PIPELINED ROW POLYMORPHIC USING rand_col_p;

Invoke the rand_col PTF to display all columns of table SCOTT.DEPT with one produced RAND_1 column.

  SELECT * 
    FROM rand_col(scott.dept);

    DEPTNO DNAME          LOC               RAND_1
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK      .738666262
        20 RESEARCH       DALLAS        .093256312
        30 SALES          CHICAGO       .992944835
        40 OPERATIONS     BOSTON        .397948124

Invoke the rand_col PTF to display all columns of table SCOTT.DEPT with two produced RAND_1 and RAND_2 columns.

  SELECT * 
    FROM rand_col(scott.dept, col_count => 2);

    DEPTNO DNAME          LOC               RAND_1     RAND_2
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK      .976521361 .209802028
        20 RESEARCH       DALLAS        .899577891  .10050334
        30 SALES          CHICAGO       .277238362 .110736583
        40 OPERATIONS     BOSTON        .989839995 .164822363

For all employees for which their job is not being a SALESMAN, display the employee name, job, and produce three RAND columns generating random values between —10 and 10.

  SELECT ename, job, rand_1, rand_2, rand_3
    FROM   rand_col(scott.emp, col_count => 3, low => -10, high => +10)
    WHERE  job != 'SALESMAN';

ENAME      JOB           RAND_1     RAND_2     RAND_3
---------- --------- ---------- ---------- ----------
SMITH      CLERK     8.91760464 6.67366638 -9.2789076
JONES      MANAGER   6.78612961 -1.8617958  6.5282227
BLAKE      MANAGER   7.59545803 5.22269017 -2.7966401
CLARK      MANAGER   -6.4747304 -7.3650276 3.28388872
SCOTT      ANALYST   6.80492435 -3.2271045 -.97099797
KING       PRESIDENT -9.3161177 6.27762154 -1.8184785
ADAMS      CLERK     -1.6618848 3.13119089 8.06363075
JAMES      CLERK     2.86918245 -3.5187936 -.72913809
FORD       ANALYST   6.67038328 -7.4989893 1.99072598
MILLER     CLERK     -2.1574578 -8.5082989 -.56046716

Example 169-15 Split Polymorphic Table Function Example

This PTF example splits each row of the input table into cnt pieces dividing the values of the split columns.

Create the PTF implementation package split_p.

CREATE PACKAGE split_p AS

  FUNCTION  describe(tab  IN OUT DBMS_TF.table_t, 
                     col         DBMS_TF.columns_t,
                     cnt         NATURALN) 
            RETURN DBMS_TF.describe_t;

  PROCEDURE fetch_rows(cnt NATURALN);

END split_p; 

Create the PTF implementation package body split_p. Each row of the input table is split into cnt pieces dividing the values of the split columns.

CREATE PACKAGE BODY split_p AS

  FUNCTION  describe(tab  IN OUT DBMS_TF.Table_t, 
                     col         DBMS_TF.Columns_t,
                     cnt         NATURALN) 
            RETURN DBMS_TF.describe_t 
  AS
    new_cols DBMS_TF.columns_new_t;
    col_id   PLS_INTEGER := 1;
  BEGIN
    FOR i IN 1 .. tab.column.count LOOP
      FOR j IN 1 .. col.count LOOP
        IF (tab.column(i).description.name = col(j) AND
            tab.column(i).description.TYPE = DBMS_TF.type_number) THEN
          tab.column(i).pass_through := FALSE;
          tab.column(i).for_read     := TRUE;
          new_cols(col_id) := tab.column(i).description;
          col_id := col_id + 1;
        END IF;
      END LOOP;
    END LOOP;

    RETURN DBMS_TF.describe_t(new_columns=>new_cols, row_replication=>true); 
  END;
  PROCEDURE fetch_rows(cnt NATURALN) 
  AS
    inp_rs DBMS_TF.row_set_t;
    out_rs DBMS_TF.row_set_t;
    rows   PLS_INTEGER;
  BEGIN 
    DBMS_TF.get_row_set(inp_rs, rows);

    FOR c IN 1 .. inp_rs.count() LOOP
      FOR r IN 1 .. rows LOOP
        FOR i IN 1 .. cnt LOOP
          out_rs(c).tab_number((r-1)*cnt+i) := inp_rs(c).tab_number(r)/cnt;
        END LOOP;
      END LOOP;
    END LOOP;

    DBMS_TF.put_row_set(out_rs, replication_factor => cnt);
  END;

END split_p; 

Create the standalone PTF named split. Specify exactly one formal argument of type TABLE, specify the return type of the PTF as TABLE, specify a Row Semantics PTF type, and indicate the PTF implementation package to use is split_p.

CREATE FUNCTION split(tab TABLE, col columns, cnt NATURALN)
                  RETURN TABLE 
PIPELINED ROW POLYMORPHIC USING split_p;

For all employees in department 30, display the ENAME, SAL, and COMM columns. Invoke the split PTF with the COLUMNS pseudo-operator to divide the value of SAL and COMM by 2 for each replicated row returned by the query. Each row is replicated twice.

SELECT ename, sal, comm 
FROM   split(scott.emp, COLUMNS(sal, comm), cnt => 2) 
WHERE  deptno=30;

ENAME             SAL                COMM
---------- ---------- -------------------
ALLEN             800                 150
ALLEN             800                 150
WARD              625                 250
WARD              625                 250
MARTIN            625                 700
MARTIN            625                 700
BLAKE            1425
BLAKE            1425
TURNER            750                   0
TURNER            750                   0
JAMES             475
JAMES             475

Example 169-19 Row_num Polymorphic Table Function Example

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;

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;

Create a standalone polymorphic table function named row_num. 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 FUNCTION row_num(tab TABLE,
                        ini NUMBER DEFAULT 1, 
                        inc NUMBER DEFAULT 1) 
                        RETURN TABLE
PIPELINED TABLE POLYMORPHIC USING row_num_p;

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.

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

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

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

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