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.

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.

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

Invoke the PTF stack in a query.

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