{"id":871,"date":"2018-10-29T17:48:54","date_gmt":"2018-10-29T15:48:54","guid":{"rendered":"http:\/\/blog.sqlora.com\/en\/?p=871"},"modified":"2018-11-05T16:40:15","modified_gmt":"2018-11-05T14:40:15","slug":"polymorphic-table-functions-ptf-part-2-more-basics-with-some-deep-dive","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/polymorphic-table-functions-ptf-part-2-more-basics-with-some-deep-dive\/","title":{"rendered":"Polymorphic Table Functions (PTF) , Part 2 \u2013 More Basics With Some Deep-Dive"},"content":{"rendered":"

In the first part<\/a> of PTF series we went through a very basic example removing some columns and adding a new column with a constant value. Starting from the same example we’ll do something more meaningful in the second part. How about concatenating the values of the removed columns as CSV in a new column? <\/p>\n

First, let’s extend the table we were using in Part 1 just to have some more columns to test with:<\/p>\n

\r\nDROP TABLE t;\r\n\r\nCREATE TABLE t (A NUMBER, B NUMBER, C NUMBER, V VARCHAR2(10));\r\n\r\nINSERT INTO t VALUES (1,2,3, 'ONE');\r\nINSERT INTO t VALUES (4,5,6, 'TWO');\r\nINSERT INTO t VALUES (7,8,9, 'THREE');\r\n\r\nCOMMIT;\r\n<\/pre>\n
Listing 1: Setup<\/em><\/p>\n
Back to the task. How should the PTF look like? What parameters do we need? Let’s make the function to accept a list of columns to stay. All remaining columns will be concatenated, so that we don’t need a separate list for them. Additionally we need a new column name. We can define a default column name and make this parameter optional though.  <\/p>\n
\r\nCREATE OR REPLACE PACKAGE my_ptf_package AS\r\n \r\nFUNCTION describe (tab IN OUT dbms_tf.table_t\r\n                 , cols2stay IN dbms_tf.columns_t \r\n                 , new_col_name IN DBMS_ID DEFAULT 'AGG_COL')\r\n         RETURN dbms_tf.describe_t; \r\n\r\nPROCEDURE fetch_rows (new_col_name IN DBMS_ID DEFAULT 'AGG_COL');\r\n           \r\nEND my_ptf_package;\r\n\/  \r\n<\/pre>\n
Listing 2: package specification<\/em><\/p>\n
The implementation of the DESCRIBE method looks very similar to that from Part 1<\/a>. We just changed the semantic of the columns parameter.<\/p>\n
\r\nCREATE OR REPLACE PACKAGE BODY my_ptf_package AS\r\n \r\nFUNCTION describe (tab IN OUT dbms_tf.table_t\r\n                 , cols2stay IN dbms_tf.columns_t\r\n                 , new_col_name IN DBMS_ID DEFAULT 'AGG_COL') \r\n         RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n  FOR I IN 1 .. tab.COLUMN.COUNT LOOP  \r\n     IF NOT tab.COLUMN(i).description.name MEMBER OF cols2stay THEN\r\n          tab.column(i).pass_through := false;\r\n          tab.column(i).for_read := true;\r\n     END IF;\r\n  END LOOP;\r\n   \r\n  RETURN dbms_tf.describe_t( \r\n           new_columns => \r\n               dbms_tf.columns_new_t( \r\n                  1 => dbms_tf.column_metadata_t(\r\n                           name => new_col_name,  \r\n                           TYPE => dbms_tf.type_varchar2)));\r\nEND; \r\n\r\n...\r\n\r\n<\/pre>\n
Listing 3: DESCRIBE method <\/em><\/p>\n
FETCH_ROWS<\/strong><\/p>\n
Now let’s look at the implementation of FETCH_ROWS.<\/strong> As we already know from Part 1<\/a>, FETCH_ROWS can be called multiple times, but we don’t need to concern about this. Just fetch, process and output. How to do this? What tools do we have? Well, there are two methods, both are somewhat complicated in their own way, but the combination of both methods looks better. Take a little popcorn: it will take some time. We’ll look at both pure methods first and then at their combination, looking at some other little things like tracing as well.<\/p>\n
Working with row sets <\/strong><\/em><\/p>\n
The implementation of the FETCH_ROWS is shown in Listing 4. We are working with the table type ROW_SET_T<\/strong> here, using variables of this type for fetching the data into (inp_rs<\/strong>) and as an output structure (out_rs<\/strong>) as well. For clarity, I’ll include the picture, we’ve seen in the first part again:<\/p>\n
\"\"<\/a><\/p>\n
To fetch the values of the columns of interest we use the procedure dbms_tf.get_row_set<\/em><\/strong> (line 7). It fetches only the columns marked as FOR_READ into the data structure of the shown type dbms_tf.row_set_t<\/em><\/strong> returning also the row and column count.<\/p>\n
Now, our task is to loop through rows (line 8) and columns (line 10) and produce the desired output. We are using dbms_tf.col_to_char<\/em><\/strong> for that (line 13). This function converts the column value to its string representation and expects column data collection – inp_rs(c)<\/strong><\/em> in our case – and the row number as parameters. <\/p>\n
We are assigning concatenated values directly to the elements of VARCHAR2 collection of the output row set out_rs.<\/strong> Because we have only one column to output, we can just use out_rs(1).<\/strong> I find this kind of referencing nested structured types and arrays to be rather complicated.<\/p>\n
After the loop we use dbms_tf.put_row_set<\/strong> to pass the output row set back to the database (to the main query).<\/p>\n
\r\nPROCEDURE fetch_rows(new_col_name IN DBMS_ID DEFAULT 'AGG_COL')  IS\r\n inp_rs dbms_tf.row_set_t; \r\n colcnt PLS_INTEGER;\r\n rowcnt PLS_INTEGER;\r\n out_rs dbms_tf.row_set_t; \r\nBEGIN \r\n    dbms_tf.get_row_set(inp_rs, rowcnt, colcnt);\r\n    FOR r IN 1..rowcnt LOOP\r\n      out_rs(1).tab_varchar2(r) := '';\r\n      FOR c IN 1..colcnt LOOP\r\n        out_rs(1).tab_varchar2(r) := \r\n                            out_rs(1).tab_varchar2(r)||\r\n                            ';'||DBMS_TF.COL_TO_CHAR(inp_rs(c), r);\r\n      END LOOP;\r\n      out_rs(1).tab_varchar2(r) := \r\n                            ltrim (out_rs(1).tab_varchar2(r) ,';'); \r\n    END LOOP;\r\n   dbms_tf.put_row_set(out_rs); \r\nEND;\r\n...\r\n\r\nSQL> SELECT * FROM my_ptf(t, COLUMNS(A));\r\n\r\n         A AGG_COL             \r\n---------- --------------------\r\n         1 2;3;"ONE"           \r\n         4 5;6;"TWO"           \r\n         7 8;9;"THREE"    \r\n\r\n\r\n<\/pre>\n
Listing 4: FETCH_ROWS procedure with get_row_set\/put_row_set<\/em><\/p>\n
We have two row sets here: input and output. Why two of them? What is the difference? Could we only use one? Basically, no, we can’t. The columns in the input row set are those, that we have defined as FOR_READ in the DESCRIBE method. They are called GET-columns in context of FETCH_ROWS. The columns in the output row set are in turn those that we have returned as NEW_COLUMNS from DESCRIBE method. They are called PUT-columns here.<\/p>\n
Having played with PTF’s for a while now, I have often made experience that after some change the PTF just stopped working, leaving the result (new columns) empty. Most of the time the reason for this was careless handling of row sets. I’ve made some tests to better understand the interrelationships and put them into a separate post<\/a> to avoid blowing up this post. <\/p>\n
The main findings: <\/p>\n