CREATE OR REPLACE PACKAGE tab2keyval_pkg AS 
FUNCTION describe(tab  IN OUT DBMS_TF.TABLE_T, 
                  id_cols IN DBMS_TF.COLUMNS_T) 
RETURN DBMS_TF.DESCRIBE_T; 
 
PROCEDURE fetch_rows; 
END tab2keyval_pkg; 

CREATE OR REPLACE PACKAGE BODY tab2keyval_pkg AS 
FUNCTION describe(tab  IN OUT DBMS_TF.TABLE_T, 
                  id_cols IN DBMS_TF.COLUMNS_T) 
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 
      tab.column(i).pass_through := FALSE; 
      TAB.COLUMN(i).for_read := TRUE; 
      -- Unless this is one of ID-columns 
      FOR J IN 1 .. id_cols.COUNT LOOP 
          IF ( tab.COLUMN(i).description.name = id_cols(j) ) THEN 
              tab.column(i).pass_through := TRUE; 
              TAB.COLUMN(i).for_read := FALSE; 
          END IF; 
      END LOOP; 
  END LOOP; 
 
  -- new columns: key_name, key_val_char, key_val_num, key_val_date 
  NEW_COLS(col_id) := dbms_tf.column_metadata_t(name => 'KEY_NAME',  
                                         TYPE => dbms_tf.type_varchar2); 
  NEW_COLS(col_id + 1) := dbms_tf.column_metadata_t(name => 'KEY_VAL_CHAR',  
                                         TYPE => dbms_tf.type_varchar2); 
  NEW_COLS(col_id + 2) := dbms_tf.column_metadata_t(name => 'KEY_VAL_NUMBER',  
                                         TYPE => dbms_tf.type_number); 
  NEW_COLS(col_id + 3) := dbms_tf.column_metadata_t(name => 'KEY_VAL_DATE',  
                                         TYPE => dbms_tf.type_date); 
 
  RETURN DBMS_TF.DESCRIBE_T(new_columns => new_cols, row_replication=>true); 
END; 
 
PROCEDURE fetch_rows IS 
    inp_rs DBMS_TF.row_set_t; 
 
    env    dbms_tf.env_t := dbms_tf.get_env(); 
     
    colcnt PLS_INTEGER; 
    rowcnt PLS_INTEGER; 
    repfac dbms_tf.tab_naturaln_t; 
    namecol dbms_tf.tab_varchar2_t; 
    valnumcol dbms_tf.tab_number_t;  
    valcharcol dbms_tf.tab_varchar2_t;  
    valdatecol dbms_tf.tab_date_t;  
BEGIN  
    DBMS_TF.get_row_set(inp_rs, 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 env.get_columns(c).type = dbms_tf.type_number then  
          repfac(r)                    := repfac(r) + 1; 
          namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"'); 
          valnumcol(NVL(valnumcol.last+1,1)) := inp_rs(c).tab_number(r); 
          valcharcol(NVL(valcharcol.last+1,1)) := NULL; 
          valdatecol(NVL(valdatecol.last+1,1)) := NULL; 
        ELSIF env.get_columns(c).type = dbms_tf.type_varchar2 then 
          repfac(r)                    := repfac(r) + 1; 
          namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"'); 
          valcharcol(NVL(valcharcol.last+1,1)) := inp_rs(c).tab_varchar2(r); 
          valnumcol(NVL(valnumcol.last+1,1)) := NULL; 
          valdatecol(NVL(valdatecol.last+1,1)) := NULL; 
        ELSIF env.get_columns(c).type = dbms_tf.type_date then 
          repfac(r)                    := repfac(r) + 1; 
          namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"'); 
          valdatecol(NVL(valdatecol.last+1,1)) := inp_rs(c).tab_date(r); 
          valcharcol(NVL(valcharcol.last+1,1)) := NULL; 
          valnumcol(NVL(valnumcol.last+1,1)) := NULL; 
        END IF; 
      END LOOP; 
    END LOOP; 
 
    dbms_tf.row_replication(replication_factor => repfac); 
    dbms_tf.put_col(1, namecol); 
    dbms_tf.put_col(2, valcharcol); 
    dbms_tf.put_col(3, valnumcol); 
    dbms_tf.put_col(4, valdatecol); 
     
END; 
 
END tab2keyval_pkg; 

CREATE OR REPLACE FUNCTION tab2keyval(tab TABLE,  id_cols COLUMNS) RETURN TABLE   
PIPELINED ROW POLYMORPHIC USING tab2keyval_pkg;

SELECT  *  
FROM    tab2keyval(scott.emp, COLUMNS(empno))