SQL & PL/SQL

I was playing around with polymorphic table functions and decided to write dynamic row and column count PTF:

CREATE OR REPLACE
  PACKAGE PTF_PKG
    AS
      FUNCTION GET_DATA(
                        P_TBL      TABLE,
                        P_ROW_CNT  NATURALN,
                        P_COL_CNT  NATURALN
                       )
        RETURN TABLE
        PIPELINED
        ROW POLYMORPHIC USING PTF_PKG;
      FUNCTION DESCRIBE(
                        P_TBL      IN OUT DBMS_TF.TABLE_T,
                        P_ROW_CNT  NATURALN,
                        P_COL_CNT  NATURALN
                       )
        RETURN DBMS_TF.DESCRIBE_T;
      PROCEDURE FETCH_ROWS(
                           P_ROW_CNT NATURALN,
                           P_COL_CNT  NATURALN
                          );
END PTF_PKG;
/
CREATE OR REPLACE
  PACKAGE BODY PTF_PKG
    AS
      FUNCTION DESCRIBE(
                        P_TBL      IN OUT DBMS_TF.TABLE_T,
                        P_ROW_CNT         NATURALN,
                        P_COL_CNT  NATURALN
                       )
        RETURN DBMS_TF.DESCRIBE_T
        AS
            V_NEW_COL  DBMS_TF.COLUMN_METADATA_T;
            V_NEW_COLS DBMS_TF.COLUMNS_NEW_T;
        BEGIN
            FOR V_COL IN 1..P_TBL.COLUMN.COUNT LOOP
              P_TBL.COLUMN(V_COL).PASS_THROUGH := FALSE;
            END LOOP;
            FOR V_COL IN 1..P_COL_CNT LOOP
              V_NEW_COL := DBMS_TF.COLUMN_METADATA_T(
                                                     NAME    => 'COL' || V_COL,
                                                     TYPE    => DBMS_TF.TYPE_VARCHAR2,
                                                     MAX_LEN => 20
                                                    );
              V_NEW_COLS(V_COL) := V_NEW_COL;
            END LOOP;
            RETURN DBMS_TF.DESCRIBE_T(
                                      NEW_COLUMNS     => V_NEW_COLS,
                                      ROW_REPLICATION => TRUE
                                     );
      END;
      PROCEDURE FETCH_ROWS(
                           P_ROW_CNT NATURALN,
                           P_COL_CNT  NATURALN
                          )
       AS
           TYPE COL_SET_TBL_TYPE
             IS
               TABLE OF DBMS_TF.TAB_VARCHAR2_T
               INDEX BY PLS_INTEGER;
           V_NEW_COL_SET   COL_SET_TBL_TYPE;
           V_ROW_SET       DBMS_TF.ROW_SET_T;
           V_ROW_COUNT     PLS_INTEGER;
           V_PUT_COL_COUNT PLS_INTEGER := DBMS_TF.GET_ENV().PUT_COLUMNS.COUNT;
       BEGIN
           DBMS_TF.GET_ROW_SET(
                               ROWSET    => V_ROW_SET,
                               ROW_COUNT => V_ROW_COUNT
                              );
           DBMS_TF.ROW_REPLICATION(
                                   REPLICATION_FACTOR => P_ROW_CNT
                                  );
           FOR V_ROW IN 1..P_ROW_CNT LOOP
             FOR V_COL IN 1..V_PUT_COL_COUNT LOOP
               V_NEW_COL_SET(V_COL)(V_ROW) := 'ROW' || V_ROW || ' - COL' || V_COL;
             END LOOP;
           END LOOP;
           FOR V_COL IN 1..V_PUT_COL_COUNT LOOP
             DBMS_TF.PUT_COL(
                             COLUMNID => V_COL,
                             COLLECTION => V_NEW_COL_SET(V_COL)
                            );
           END LOOP;
  END;
END PTF_PKG;
/

And execution:

SQL> SELECT  ROWNUM,
  2          T.*
  3    FROM  PTF_PKG.GET_DATA(DUAL,3,5) T
  4  /

    ROWNUM COL1                 COL2                 COL3                 COL4                 COL5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         1 ROW1 - COL1          ROW1 - COL2          ROW1 - COL3          ROW1 - COL4          ROW1 - COL5
         2 ROW2 - COL1          ROW2 - COL2          ROW2 - COL3          ROW2 - COL4          ROW2 - COL5
         3 ROW3 - COL1          ROW3 - COL2          ROW3 - COL3          ROW3 - COL4          ROW3 - COL5

SQL> SELECT  ROWNUM,
  2          T.*
  3    FROM  PTF_PKG.GET_DATA(DUAL,5,3) T
  4  /

    ROWNUM COL1                 COL2                 COL3
---------- -------------------- -------------------- --------------------
         1 ROW1 - COL1          ROW1 - COL2          ROW1 - COL3
         2 ROW2 - COL1          ROW2 - COL2          ROW2 - COL3
         3 ROW3 - COL1          ROW3 - COL2          ROW3 - COL3
         4 ROW4 - COL1          ROW4 - COL2          ROW4 - COL3
         5 ROW5 - COL1          ROW5 - COL2          ROW5 - COL3

SQL>

First question is - I don't need a table parameter since all I need is DUAL which I could set in DESCRIBE however table parameter is required. So if someone passed multi-row, e.g. N row table my code will return N times P_ROW_CNT rows:

SQL> SELECT  ROWNUM,
  2          T.*
  3    FROM  PTF_PKG.GET_DATA(DEPT,2,3) T
  4  /

    ROWNUM COL1                 COL2                 COL3
---------- -------------------- -------------------- --------------------
         1 ROW1 - COL1          ROW1 - COL2          ROW1 - COL3
         2 ROW2 - COL1          ROW2 - COL2          ROW2 - COL3
         3
         4
         5
         6
         7
         8

8 rows selected.

SQL>

Yes, this is minor - code can check table name in DESCRIBE and burp if not DUAL. But where I got stuck is how to replicate parts of resultset. For example, this will give us how many rows (say 10) are in resultset:

           DBMS_TF.GET_ROW_SET(
                               ROWSET    => V_ROW_SET,
                               ROW_COUNT => V_ROW_COUNT
                              );

Now I want to return just first 7 rows. I tried

V_ROW_SET.DELETE(8,10);

It didn't work…

SY.