{"id":614,"date":"2018-02-26T00:35:10","date_gmt":"2018-02-25T22:35:10","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=614"},"modified":"2018-04-04T16:23:59","modified_gmt":"2018-04-04T14:23:59","slug":"polymorphic-table-functions-example-transposing-columns-to-rows","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/polymorphic-table-functions-example-transposing-columns-to-rows\/","title":{"rendered":"Polymorphic Table Functions Example – Transposing Columns To Rows"},"content":{"rendered":"

Hey, Oracle 18c is now available on the cloud and for engineered systems! For more than a week now. That also means you can play with it at LiveSQL<\/a>. And of course you can try polymorphic table functions (PTF)! At least I’ve done that this weekend \ud83d\ude09 And here is my first working example. <\/p>\n

I already mentioned in my first post on PTF<\/a>, it would be nice to try to make PIVOT\/UNPIVOT functions more dynamic. We’ll start with Unpivot or transposing columns to rows here. Let’s make a key-value store out of the SCOTT.EMP<\/strong><\/code> table. That means we’ll retain some identifying column(s), in our case this will be EMPNO<\/strong><\/code>. All other columns will be “packed” using KEY_NAME-KEY_VALUE pairs. Maybe it will be a good idea not to use strings for all values, but to distinguish some data types. For our example, we will separate values of NUMBER, VARCHAR2 and DATE data types. So our target data structure will look something like this:<\/p>\n

\r\nEMPNO           NUMBER\r\nKEY_NAME        VARCHAR2\r\nKEY_VAL_CHAR    VARCHAR2 \r\nKEY_VAL_NUM     NUMBER\r\nKEY_VAL_DATE    DATE\r\n<\/pre>\n
Let’s start coding. We’ll create a function TAB2KEYVAL<\/strong> which will accept the table and a list of ID-columns as parameter. The implementation of a polymorphic table function resides in a package. We must provide the function DESCRIBE which will be invoked during SQL cursor compilation and describes the shape of the returned result set. This function accepts the record type TABLE_T<\/strong>, a collection of columns COLUMNS_T<\/strong> and returns a recod type DESCRIBE_T<\/strong>. During the execution stage the rows are processed inside of the function FETCH_ROWS.<\/p>\n
\r\nCREATE OR REPLACE PACKAGE tab2keyval_pkg AS\r\n\r\nFUNCTION describe(tab  IN OUT DBMS_TF.TABLE_T,\r\n                  id_cols IN DBMS_TF.COLUMNS_T)\r\nRETURN DBMS_TF.DESCRIBE_T;\r\n\r\nPROCEDURE fetch_rows;\r\n\r\nEND tab2keyval_pkg;\r\n\/\r\n<\/pre>\n
That’s what we need for now and we can start the implementation of a package body. In the function DESCRIBE<\/strong> we are looping through all columns or the source table and mark them not to be passed through, unless this is the specified ID-column (lines 9 to 19).  We also specify that all columns have to be processed during fetch phase (for_read = true). Again, unless it is an ID-column. Next we specify four new columns to hold the key name along with three possible key values (lines 22 to 29). That’s it. We just need to return  a record DESCRIBE_T<\/strong> containing the information about new columns. Another point here: we are going to replicate the source rows, because we’ll get one row per transposed column  (line 32).<\/p>\n
Now we are ready to look at the processing itself – procedure FETCH_ROWS. Using procedure GET_ROW_SET<\/strong> (line 48), we get an input rowset with column values of the columns, which we had marked as FOR_READ. The returned datatype is a table or a record type COLUMN_DATA<\/strong>, which in turn has so called “variant fields” – a tables for every supported datatype, e.g. tab_varchar2, tab_number and so on. Only one variant field corresponding to the actual datatype is active for a column.<\/p>\n
Every row in a rowset has it’s own replication factor. We set it initially to 0 (lines 50 to 52). Then we loop through all rows and columns increasing the replication factor and filling the tables which hold the data for key_name and key_values with actual data. These tables – one for the key name (NAMECOL) and three for key values (VALCHARCOL, VALNUMCOL, VALDATECOL) – are defined at lines 43 to 46. The processing actually differs only slightly because of the different data types.<\/p>\n
Next we specify the replication and pass the prepared collection for new columns back – the calls of PUT_COL<\/strong> at lines 78 to 82.<\/p>\n
\r\nCREATE OR REPLACE PACKAGE BODY tab2keyval_pkg AS\r\nFUNCTION describe(tab  IN OUT DBMS_TF.TABLE_T,\r\n                  id_cols IN DBMS_TF.COLUMNS_T)\r\nRETURN DBMS_TF.DESCRIBE_T \r\nAS\r\n   new_cols DBMS_TF.COLUMNS_NEW_T;\r\n   col_id   PLS_INTEGER := 1;\r\nBEGIN\r\n  FOR I IN 1 .. tab.COLUMN.COUNT LOOP\r\n      tab.column(i).pass_through := FALSE;\r\n      TAB.COLUMN(i).for_read := TRUE;\r\n      -- Unless this is one of ID-columns\r\n      FOR J IN 1 .. id_cols.COUNT LOOP\r\n          IF ( tab.COLUMN(i).description.name = id_cols(j) ) THEN\r\n              tab.column(i).pass_through := TRUE;\r\n              TAB.COLUMN(i).for_read := FALSE;\r\n          END IF;\r\n      END LOOP;\r\n  END LOOP;\r\n\r\n  -- new columns: key_name, key_val_char, key_val_num, key_val_date\r\n  NEW_COLS(col_id) := dbms_tf.column_metadata_t(name=>'KEY_NAME', \r\n                                  TYPE => dbms_tf.type_varchar2);\r\n  NEW_COLS(col_id+1) := dbms_tf.column_metadata_t(name=>'KEY_VAL_CHAR', \r\n                                  TYPE => dbms_tf.type_varchar2);\r\n  NEW_COLS(col_id+2) := dbms_tf.column_metadata_t(name=>'KEY_VAL_NUMBER', \r\n                                  TYPE => dbms_tf.type_number);\r\n  NEW_COLS(col_id+3) := dbms_tf.column_metadata_t(name=>'KEY_VAL_DATE', \r\n                                  TYPE => dbms_tf.type_date);\r\n\r\n  RETURN DBMS_TF.DESCRIBE_T(new_columns => new_cols\r\n                          , row_replication=>true);\r\nEND;\r\n\r\nPROCEDURE fetch_rows IS\r\n    inp_rs DBMS_TF.row_set_t;\r\n\r\n    env    dbms_tf.env_t := dbms_tf.get_env();\r\n    \r\n    colcnt PLS_INTEGER;\r\n    rowcnt PLS_INTEGER;\r\n    repfac dbms_tf.tab_naturaln_t;\r\n    namecol dbms_tf.tab_varchar2_t;\r\n    valnumcol dbms_tf.tab_number_t; \r\n    valcharcol dbms_tf.tab_varchar2_t; \r\n    valdatecol dbms_tf.tab_date_t; \r\nBEGIN \r\n    DBMS_TF.get_row_set(inp_rs, rowcnt, colcnt);\r\n\r\n    FOR i IN 1 .. rowcnt LOOP \r\n      repfac(i) := 0; \r\n    END LOOP;\r\n\r\n    FOR r IN 1 .. rowcnt LOOP\r\n      FOR c IN 1 .. colcnt LOOP\r\n        IF env.get_columns(c).type = dbms_tf.type_number then \r\n          repfac(r)                    := repfac(r) + 1;\r\n          namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"');\r\n          valnumcol(NVL(valnumcol.last+1,1)) := inp_rs(c).tab_number(r);\r\n          valcharcol(NVL(valcharcol.last+1,1)) := NULL;\r\n          valdatecol(NVL(valdatecol.last+1,1)) := NULL;\r\n        ELSIF env.get_columns(c).type = dbms_tf.type_varchar2 then\r\n          repfac(r)                    := repfac(r) + 1;\r\n          namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"');\r\n          valcharcol(NVL(valcharcol.last+1,1)) := inp_rs(c).tab_varchar2(r);\r\n          valnumcol(NVL(valnumcol.last+1,1)) := NULL;\r\n          valdatecol(NVL(valdatecol.last+1,1)) := NULL;\r\n        ELSIF env.get_columns(c).type = dbms_tf.type_date then\r\n          repfac(r)                    := repfac(r) + 1;\r\n          namecol(nvl(namecol.last+1,1)) := replace(env.get_columns(c).name,'"');\r\n          valdatecol(NVL(valdatecol.last+1,1)) := inp_rs(c).tab_date(r);\r\n          valcharcol(NVL(valcharcol.last+1,1)) := NULL;\r\n          valnumcol(NVL(valnumcol.last+1,1)) := NULL;\r\n        END IF;\r\n      END LOOP;\r\n    END LOOP;\r\n\r\n    dbms_tf.row_replication(replication_factor => repfac);\r\n    dbms_tf.put_col(1, namecol);\r\n    dbms_tf.put_col(2, valcharcol);\r\n    dbms_tf.put_col(3, valnumcol);\r\n    dbms_tf.put_col(4, valdatecol);\r\n    \r\nEND;\r\n\r\nEND tab2keyval_pkg;\r\n\/\r\n\r\n<\/pre>\n
The package is there, so just define the function using it and it works!<\/p>\n
\r\n\r\nCREATE OR REPLACE FUNCTION tab2keyval(tab TABLE,  id_cols COLUMNS)\r\nRETURN TABLE PIPELINED ROW POLYMORPHIC USING tab2keyval_pkg;\r\n\r\n\r\nSELECT  * \r\nFROM    tab2keyval(scott.emp, COLUMNS(empno));\r\n\r\n<\/pre>\n
\"\"<\/a><\/p>\n
What if we wanted to also use ENAME as identifying column and not to pack it in our key-value store? Just add it to COLUMNS<\/strong> operator. By the way, this COLUMNS SQL-operator is new in 18c  and is only allowed in the parameter context of a polymorphic function. As we can see, the column ENAME is now a simple column in the output and not the part of key-value store any more (there is no rows with key_name = ‘ENAME’ any more) <\/p>\n
\r\n\r\nSELECT  * \r\nFROM    tab2keyval(scott.emp, COLUMNS(empno, ename));\r\n\r\n<\/pre>\n
\"\"<\/a><\/p>\n
That was quite simple!  Now that we have produced this output, I’ll try to compose a PTF doing opposite task: transpose rows to columns, i.e. back to the original EMP table.  In the next post. Maybe next weekend \ud83d\ude09<\/p>\n
Related Posts<\/h3>