{"id":467,"date":"2018-08-19T23:28:24","date_gmt":"2018-08-19T21:28:24","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=467"},"modified":"2018-11-18T14:41:46","modified_gmt":"2018-11-18T12:41:46","slug":"polymorphic-table-functions-ptf-part-1-basics","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/polymorphic-table-functions-ptf-part-1-basics\/","title":{"rendered":"Polymorphic Table Functions (PTF) ,  Part 1 &#8211; Basics"},"content":{"rendered":"<p>I have already posted <a href=\"http:\/\/blog.sqlora.com\/en\/polymorphic-table-functions-example-transposing-columns-to-rows\/\" rel=\"noopener\" target=\"_blank\">some examples<\/a> on Polymorphic Table Functions in Oracle 18c in the last months. I quickly realized how difficult it is to explain completely new feature using advanced examples and wanted to write a series of posts starting from very basics. Now that the <a href=\"https:\/\/www.doag.org\/de\/home\/\" rel=\"noopener\" target=\"_blank\">Germany&#8217;s Oracle User Group (DOAG)<\/a> has accepted my presentation on PTF for their <a href=\"https:\/\/2018.doag.org\/de\/home\/\" rel=\"noopener\" target=\"_blank\">annual conference<\/a> is the time to do it. <!--more--><\/p>\n<p>Polymorphic Table Functions (abbreviated PTF) are the part of the SQL 2016 standard. Their definition can be summarized as follows:<\/p>\n<ul>\n<li>are user-defined table functions that can be called in FROM clause <\/li>\n<li>their row type is not declared when the function is created <\/li>\n<li>the row type of the result may depend on the function arguments and thus on precise invocation syntax<\/li>\n<li>PTF may have generic table parameters whose row type may not be declared at creation time (Caution! At least as of 18.0.1 Oracle <strong>requires exactly ONE<\/strong> table parameter)<\/li>\n<\/ul>\n<p>There is a very useful and interesting publicly available <a href=\"http:\/\/standards.iso.org\/ittf\/PubliclyAvailableStandards\/c069776_ISO_IEC_TR_19075-7_2017.zip\" rel=\"noopener\" target=\"_blank\">document on PTF (ISO\/IEC TR 19075-7:2017)<\/a>. Actually a &#8222;must read&#8220; one if you would like to have a deeper insight into the topic. But concerning Oracle&#8217;s implementation, be aware, not everything from the standard is implemented exactly in the described way or may not be implemented yet (as of 18.0.1). I&#8217;ll point out the differences as we go.<\/p>\n<p><strong>Motivation, Roles<\/strong><\/p>\n<p>With PTF it should be possible and simple to express sophisticated algorithms and encapsulate them within the DBMS making them generic and thus usable for various data sets just by invoking in a FROM clause of the SQL statement. <\/p>\n<p>You could think about a PTF as a kind of view but more procedural and more dynamic. With views, there are three roles: query author, view author and DBMS. The view author is publishing an interface to the underlying tables without exposing the inner logic. The query author uses the view (published interface) in a query and the DBMS manages the execution. Similarly you&#8217;ll find the roles with a PTF: <\/p>\n<ul>\n<li><strong>PTF author<\/strong> publishes an interface to a procedural mechanism that defines a result table. Responsible for the implementation of this mechanism<\/li>\n<li><strong>query author<\/strong> uses the published interface by invoking the PTF in a query <\/li>\n<li><strong>DBMS<\/strong> &#8211; is responsible for compilation, execution and state management<\/li>\n<\/ul>\n<p>The query author doesn&#8217;t have to know or understand any technical implementation details, he just invokes the function in a SQL statement like he does with a view. But does the PTF author have to deal with all technical details? Only as far as these concern the actual business case, the business logic of the PTF. The really technical stuff like fetching rows, cursor management, parallel execution, etc. is delegated to the database and is hidden behind the clear interface provided by the package DBMS_TF.<\/p>\n<p><a href=\"http:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2018\/08\/ptf1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2018\/08\/ptf1.png\" alt=\"\" width=\"863\" height=\"399\" class=\"alignnone size-full wp-image-813\" \/><\/a><\/p>\n<p><em>Figure 1: Basic PTF usage<\/em><\/p>\n<p><strong>PTF taxonomy <\/strong><\/p>\n<p>According to preview presentations from last year it should be possible to define a PTF without a table parameter at all. This should be called a <strong>leaf PTF<\/strong>. At the moment that kind of definition is neither possible nor mentioned in the documentation. Maybe it will be implemented in next releases. Now exactly one table parameter is required for PTF. <\/p>\n<p>So we consider the <strong>non-leaf<\/strong> PTF&#8217;s only. They are further classified based on the semantic of the input table parameter:<\/p>\n<ul>\n<li><strong>row semantic<\/strong> &#8211; the result of the function can be determined only by looking at the current row<\/li>\n<li><strong>table semantic<\/strong> &#8211; the result of the function can be determined by looking at the current row and some state &#8222;summarized&#8220; from previously processed rows<\/li>\n<\/ul>\n<p>Strictly speaking these properties are the properties of the input table parameters. As long as Oracle allows only for one input table, they can also be used to classify the functions themselves, but if this restriction will eventually be lifted, this won&#8217;t be possible anymore. SQL standard allows for multiple generic input table parameters having their own semantic.<\/p>\n<p>The <strong>row semantic<\/strong> PTF&#8217;s are sufficient for example if you need:<\/p>\n<ul>\n<li>to add some derived columns with <strong>really<\/strong> complicated calculations behind them (otherwise you&#8217;d be probably better off with a view or virtual column) <\/li>\n<li>to reformat the row: splitting it, renaming, hiding, manipulating columns, etc. <\/li>\n<li>to replicate the rows<\/li>\n<li>to output rows in defined format, e.g. JSON, XML<\/li>\n<li>to pivot the columns<\/li>\n<li>etc.<\/li>\n<\/ul>\n<p>The <strong>table semantics<\/strong> PTF&#8217;s are useful to implement user-defined analytics like aggregation or window functions. They operate on an entire table or a logical partition of it.<\/p>\n<p><strong>Definition, Invocation<\/strong><\/p>\n<p>Let&#8217;s start with a basic example: refer to the Figure 1, we should implement a PTF which accepts any table, a list of columns to discard and produces a new column with the name and value specified by invocation. Please don&#8217;t ask me for the sense and don&#8217;t contradict, this could be achieved much simpler just with a view &#8211; it is for demonstration purpose only. \ud83d\ude09<\/p>\n<p>The table we will use is defined as follows <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE t (A NUMBER, B NUMBER, C NUMBER);\r\n\r\nINSERT INTO t VALUES (1,2,3);\r\nINSERT INTO t VALUES (4,5,6);\r\nINSERT INTO t VALUES (7,8,9);\r\n\r\nCOMMIT;     \r\n<\/pre>\n<p><em>Listing 1: Test table<\/em><\/p>\n<p>What do you need to define a PTF? First, you need an implementation package, where you have to define the functions for the key PTF interfaces. You can then define the PTF itself within the package or as a standalone function at schema level.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE OR REPLACE PACKAGE my_ptf_package \r\n AS  \r\nFUNCTION my_ptf (tab TABLE\r\n               , cols_to_discard COLUMNS DEFAULT NULL\r\n               , new_col_name VARCHAR2 DEFAULT NULL\r\n               , new_col_val VARCHAR2 DEFAULT NULL)\r\n    RETURN TABLE PIPELINED ROW POLYMORPHIC USING my_ptf_package;\r\n \r\nEND my_ptf_package;\r\n\/\r\n<\/pre>\n<p><em>Listing 2: PTF defined as a package function<\/em><\/p>\n<p>The first parameter tab of type TABLE is required for every PTF. Except for it we defined another three parameters: for the column list to discard, for the name of the new column and the value of it. I&#8217;ve made them optional to demonstrate the absolutely minimalistic version of our PTF (see later). Of course it&#8217;s just a matter of your business case &#8211; all parameters can be made required.<\/p>\n<p>We then specify the return type which is simply TABLE. Yes, we don&#8217;t have to declare and specify any user-defined types and this is one of the main goals and advantages of polymorphic table functions.  Well, our PTF is PIPELINED, but it&#8217;s nothing new to this clause. Then we specify the functions semantic. We can accomplish our simple task by looking at just one row, so the semantic will be ROW. The clause POLYMORPHIC tells the function is a PTF and finally we tell the database where to find the implementation methods &#8211; USING my_ptf_package.<\/p>\n<p>If we wanted to hide the column C and introduce the new column D with the value &#8222;Hello world!&#8220;, the invocation would look something like this:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n-- Package-qualified\r\nSELECT *\r\nFROM   my_ptf_package.my_ptf(t, COLUMNS(C), 'D', 'Hello world!');\r\n\r\n<\/pre>\n<p><em>Listing 3: Calling PTF in SQL<\/em><\/p>\n<p>Look at the second parameter &#8211; we are using the new pseudo-operator  COLUMNS here. It has been introduced in 18c to support Polymorphic Table Functions. See <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/lnpls\/plsql-optimization-and-tuning.html#GUID-A3123AB1-E06D-4E00-A856-6052527482CE\" rel=\"noopener\" target=\"_blank\">Variadic Pseudo-Operators<\/a> for more details. It is used to pass a list of columns as a parameter into a PTF. There can be multiple parameters of this type with the meaning you define as PTF author. <\/p>\n<p>All supporting types and routines you need to implement a PTF are in the package <strong>DBMS_TF<\/strong>. Besides the package documentation you can have a look into the package specification itself and find valuable comments. <\/p>\n<p>Next we have to define the interface methods\/functions. There are four of the PTF interface methods: <strong>DESCRIBE, OPEN, FETCH_ROWS and CLOSE<\/strong>. Only <strong>DESCRIBE<\/strong> is required. Why? <\/p>\n<p><strong>Compilation and execution states.<\/strong><\/p>\n<p>Look at the Figure 1. How does the database know about the column D? It looks like magic and behaves somehow like dynamic SQL, but it is not! Well, it&#8217;s no magic here. You (PTF author) tell it! The SQL statement which invokes a PTF will be parsed just like any other and at this moment your defined DESCRIBE method will be called. It is the function where you tell the database how the result rows of your function will look like. This state of the PTF is referred as compilation state. <\/p>\n<p>What parameters must be specified for the DECSRIBE function? Generally speaking, they are the same as for the PTF itself, with the difference that the parameters of type TABLE and COLUMNS are converted to according record resp. table PL\/SQL types defined in DBMS_TF. Scalar parameters, if any, remain the same. The DESCRIBE function requieres exactly one parameter of type <em><strong>dbms_tf.table_t<\/strong><\/em> which describes the input table. The DBMS is responsible for filling the record type and passing it to DESCRIBE during the statement parsing.  This parameter is declared as IN OUT because you not only get the information about the table columns, but it is also the way to tell the database what you intend to do with those columns &#8211; more on this later.  Also the the parameter of type COLUMNS will be converted to the PL\/SQL table type <em><strong>dbms_tf.columns_t<\/strong><\/em>. <\/p>\n<p>The return type of the function DESCRIBE is the record type <strong>DESCRIBE_T<\/strong>, which is basically used to inform the database about new columns via collection (<strong>COLUMNS_NEW_T<\/strong>) of records <strong>COLUMN_METADATA_T.<\/strong> Other usages &#8211; compilation state and method names &#8211; will be described in following posts.  <\/p>\n<p>Let&#8217;s code.  Specify all four parameters doing the described type substitution. In the package body we&#8217;ll first do nothing in the DESCRIBE function (return NULL).  <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE OR REPLACE PACKAGE my_ptf_package AS\r\n \r\nFUNCTION my_ptf (tab TABLE\r\n               , cols_to_discard COLUMNS DEFAULT NULL\r\n               , new_col_name VARCHAR2 DEFAULT NULL\r\n               , new_col_val VARCHAR2 DEFAULT NULL)\r\n    RETURN TABLE PIPELINED ROW POLYMORPHIC USING my_ptf_package;\r\n \r\nFUNCTION describe(  \r\n                tab   IN OUT dbms_tf.table_t, \r\n                cols_to_discard  IN dbms_tf.columns_t DEFAULT NULL,\r\n                new_col_name VARCHAR2 DEFAULT NULL, \r\n                new_col_val VARCHAR2 DEFAULT NULL)  \r\n           RETURN dbms_tf.describe_t; \r\nEND my_ptf_package;\r\n\/  \r\n \r\nCREATE OR REPLACE PACKAGE BODY my_ptf_package AS\r\n \r\nFUNCTION describe(\r\n                tab   IN OUT dbms_tf.table_t, \r\n                cols_to_discard  IN  dbms_tf.columns_t DEFAULT NULL,\r\n                new_col_name VARCHAR2 DEFAULT NULL, \r\n                new_col_val VARCHAR2 DEFAULT NULL)  \r\n           RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n  RETURN NULL;\r\nEND; \r\n \r\nEND my_ptf_package; \r\n\/\r\nset echo on\r\n\r\nSQL&gt; SELECT * FROM my_ptf_package.my_ptf(t);\r\n\r\n         A          B          C\r\n---------- ---------- ----------\r\n         1          2          3\r\n         4          5          6\r\n         7          8          9\r\n\r\n<\/pre>\n<p><em>Listing 4: Describe-only PTF<\/em><\/p>\n<p>At least, we could compile the package without an error. Note, that we don&#8217;t need to  provide any implementation of the PTF in the package body. But what happens, if we try to invoke the function in some SQL statement? It works! Well, it does nothing  meaningful, simply passing all data through &#8222;as is&#8220;. Except for the three optional parameters we introduced to support intended functionality, we just created the smallest and simplest PTF you can write.  <\/p>\n<p>How does it work? First, we told the database there are no new columns to expect by returning NULL as a result of DESCRIBE. The fact that existing columns where passed through is due to special column properties\/flags in the column descriptor: <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nTYPE column_t IS RECORD (\r\n    description            COLUMN_METADATA_T, \r\n    pass_through           BOOLEAN,      \r\n    for_read               BOOLEAN); \r\n<\/pre>\n<p><em>Listing 5: Column descriptor type<\/em><\/p>\n<p><strong>Pass-through columns<\/strong> are passed to the output without any modifications. <strong>Read columns<\/strong> (for_read is TRUE) are those that will be processed during the execution inside FETCH_ROWS. These flags are not mutually exclusive. To understand the interaction, look at Figure 2.<\/p>\n<p><a href=\"http:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2018\/08\/ptf2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2018\/08\/ptf2.png\" alt=\"\" width=\"834\" height=\"403\" class=\"alignnone size-full wp-image-856\" \/><\/a><\/p>\n<p><em>Figure 2: Pass-through and read columns<\/em><\/p>\n<p>Suppose the DESCRIBE function has marked the column B as &#8222;for read&#8220; (red) and the column E as pass-through (green). The column D is both for read and pass-through (yellow &#8211; R+G=Y \ud83d\ude09 ). During the execution columns D and E automatically become part of the result row. Additionally there are any new columns (F and G) which DESCRIBE method has declared. Columns B and D (for read) are passed to the function and are the only columns you will see and can evaluate during the execution. Important notice: you cannot change any existing column values! You can either pass through &#8222;as is&#8220; or define a new column and assign a modified value to it.<\/p>\n<p>Now, the documentation states, all columns in the Row Semantics PTF are marked as pass-through by default. That&#8217;s why in former example the function has worked without any implementation. But we actually wanted to discard some columns and introduce a new one. Let&#8217;s do it!<\/p>\n<p>Let&#8217;s first take care of discarding the columns. We only need to iterate through table columns and check whether the column is in discard list. If so, mark it as not pass-through. Still return NULL, no new columns yet.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE OR REPLACE PACKAGE BODY my_ptf_package AS\r\n \r\nFUNCTION describe(\r\n           tab   IN OUT dbms_tf.table_t, \r\n           cols_to_discard  IN  dbms_tf.columns_t DEFAULT NULL,\r\n           new_col_name VARCHAR2 DEFAULT NULL, \r\n           new_col_val VARCHAR2 DEFAULT NULL)  \r\n           RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n-- we need to find columns to discard\r\n-- all other will be passed through by default\r\n  FOR I IN 1 .. tab.COLUMN.COUNT LOOP  \r\n     IF tab.COLUMN(i).description.name MEMBER OF cols_to_discard THEN \r\n          tab.column(i).pass_through := false;\r\n     END IF;\r\n  END LOOP;\r\n  RETURN NULL;\r\nEND; \r\n \r\nEND my_ptf_package; \r\n\/\r\n\r\nSQL&gt; SELECT * FROM my_ptf_package.my_ptf(t, COLUMNS(C));\r\n\r\n         A          B\r\n---------- ----------\r\n         1          2\r\n         4          5\r\n         7          8\r\n\r\nSQL&gt; \r\nSQL&gt; SELECT * FROM my_ptf_package.my_ptf(t, COLUMNS(B,C));\r\n\r\n         A\r\n----------\r\n         1\r\n         4\r\n         7\r\n<\/pre>\n<p><em>Listing 6: The next version our PTF discarding specified columns<\/em><\/p>\n<p>Works as expected. Now let us introduce the new column. The name of the column is known (passed as a parameter) and the data type is actually hard coded as VARCHAR2. If you wonder about this syntax <strong>(1 => dbms_tf&#8230;<\/strong>  just read about <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/lnpls\/plsql-collections-and-records.html#GUID-4B379569-4068-4DB1-9D69-E706F4AC5758\" rel=\"noopener\" target=\"_blank\">qualified expressions in 18c<\/a><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE OR REPLACE PACKAGE BODY my_ptf_package AS\r\n \r\nFUNCTION describe(\r\n           tab   IN OUT dbms_tf.table_t, \r\n           cols_to_discard  IN  dbms_tf.columns_t DEFAULT NULL,\r\n           new_col_name VARCHAR2 DEFAULT NULL, \r\n           new_col_val VARCHAR2 DEFAULT NULL)  \r\n           RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n-- we need to find columns to discard\r\n-- all other will be passed through by default\r\n  FOR I IN 1 .. tab.COLUMN.COUNT LOOP  \r\n     IF tab.COLUMN(i).description.name MEMBER OF cols_to_discard THEN \r\n          tab.column(i).pass_through := false;\r\n     END IF;\r\n  END LOOP;\r\n  \r\n  RETURN dbms_tf.describe_t( \r\n           new_columns =&gt; \r\n               dbms_tf.columns_new_t( \r\n                  1 =&gt; dbms_tf.column_metadata_t(\r\n                           name =&gt; new_col_name,  \r\n                           TYPE =&gt; dbms_tf.type_varchar2)));\r\nEND; \r\n \r\nEND my_ptf_package; \r\n\/\r\n\r\nSQL&gt; SELECT * FROM my_ptf_package.my_ptf(t, COLUMNS(C), &#039;D&#039;);\r\n\r\nORA-62573: new column (D) is not allowed with describe only \r\n           polymorphic table function\r\n\r\n<\/pre>\n<p><em>Listing 7: Trying to introduce new columns without FETCH_ROWS implementation: ORA-62573<\/em><\/p>\n<p>What&#8217;s going on? Well, until now we only have defined a DESCRIBE function. Our PTF has worked but it was what is called &#8222;describe-only&#8220; PTF. No new columns are permitted for such PTF. Our code was executed only once during statement parsing (compilation state). In order to introduce new columns, we need to define what has to happen with them during the execution state. We need to define other methods, FETCH_ROWS in first place.<\/p>\n<p><strong>Execution State<\/strong><\/p>\n<p>What parameters does the procedure FETCH_ROWS need? Any scalar parameters defined for the PTF itself, i.e. not of type TABLE or COLUMNS, are also required for FETCH_ROWS. Otherwise you&#8217;ll get the same ORA-62573 error.  Why so? Because of the signature mismatch the database does not recognize the FETCH_ROWS as an implementation method for our PTF. It looks just like some other procedure in the package, nothing to do with a PTF. <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE OR REPLACE PACKAGE my_ptf_package AS\r\n \r\nFUNCTION my_ptf (tab TABLE\r\n               , cols_to_discard COLUMNS DEFAULT NULL\r\n               , new_col_name VARCHAR2 DEFAULT NULL\r\n               , new_col_val VARCHAR2 DEFAULT NULL)\r\n    RETURN TABLE PIPELINED ROW POLYMORPHIC USING my_ptf_package;\r\n \r\nFUNCTION describe(  \r\n                tab   IN OUT dbms_tf.table_t, \r\n                cols_to_discard  IN dbms_tf.columns_t DEFAULT NULL,\r\n                new_col_name VARCHAR2 DEFAULT NULL, \r\n                new_col_val VARCHAR2 DEFAULT NULL)  \r\n           RETURN dbms_tf.describe_t; \r\n           \r\nPROCEDURE fetch_rows (new_col_name VARCHAR2 DEFAULT NULL, \r\n                    new_col_val VARCHAR2 DEFAULT NULL);            \r\nEND my_ptf_package;\r\n\/  \r\n\r\nCREATE OR REPLACE PACKAGE BODY my_ptf_package AS\r\n \r\nFUNCTION describe(\r\n           tab   IN OUT dbms_tf.table_t, \r\n           cols_to_discard  IN  dbms_tf.columns_t DEFAULT NULL,\r\n           new_col_name VARCHAR2 DEFAULT NULL, \r\n           new_col_val VARCHAR2 DEFAULT NULL)  \r\n           RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n-- we need to find columns to discard\r\n-- all other will be passed through by default\r\n  FOR I IN 1 .. tab.COLUMN.COUNT LOOP  \r\n     IF tab.COLUMN(i).description.name MEMBER OF cols_to_discard THEN \r\n          tab.column(i).pass_through := false;\r\n     END IF;\r\n  END LOOP;\r\n  \r\n  RETURN dbms_tf.describe_t( \r\n           new_columns =&gt; \r\n               dbms_tf.columns_new_t( \r\n                  1 =&gt; dbms_tf.column_metadata_t(\r\n                           name =&gt; new_col_name,  \r\n                           TYPE =&gt; dbms_tf.type_varchar2)));\r\nEND; \r\n\r\n\r\nPROCEDURE fetch_rows (new_col_name VARCHAR2 DEFAULT NULL, \r\n                    new_col_val VARCHAR2 DEFAULT NULL) IS\r\nvalcol dbms_tf.tab_varchar2_t;       \r\nenv    dbms_tf.env_t := dbms_tf.get_env(); \r\nBEGIN\r\n  -- For all rows just assign a constant value\r\n  FOR i IN 1..env.row_count LOOP\r\n    valcol(nvl(valcol.last+1,1)) := new_col_val;\r\n  END LOOP;\r\n  -- Put the collection with values back\r\n  DBMS_TF.PUT_COL(1, valcol);\r\nEND;\r\n \r\nEND my_ptf_package; \r\n\/\r\n\r\nSQL&gt; SELECT * FROM my_ptf_package.my_ptf(t, COLUMNS(C), \r\n                                     &#039;D&#039;, &#039;Hello World!&#039;);\r\n\r\n         A          B D           \r\n---------- ---------- ------------\r\n         1          2 Hello World!\r\n         4          5 Hello World!\r\n         7          8 Hello World!\r\n\r\n<\/pre>\n<p><em>Listing 8: Works! Discard columns and introduce a new one<\/em><\/p>\n<p>Mission completed. The implementation of FETCH_ROWS looks not that complex. What happens there? We don&#8217;t need to evaluate any columns for our simple task, just assign a constant value to a new column. Sounds easy, but how? Only once or for each input row? If for each row, how many are there, anyway? Will FETCH_ROWS be executed only once seeing all rows or multiple times? Do I even know? Do I need to know?  The answer to the most questions is as always, it depends&#8230;  But again, the implementation of the polymorphic table functions has the goal that the PTF author does not have to worry about it. In general FETCH_ROWS can be called multiple times. You don&#8217;t know and don&#8217;t need to know how often it will be called. You operate only on the current (active) row set. How big is this row set? We can see this in ENV_T, the record type containing the information about current execution state, which is filled by calling <em><strong>dbms_tf.get_env().<\/strong><\/em> Another way could be an out parameter from fetching the rows with procedures <em><strong>dbms_tf.get_row_set<\/strong><\/em> or <em><strong>dbms_tf.get_column<\/strong><\/em>, but we didn&#8217;t use them since we dont&#8217;t need to fetch any read columns to fulfill our task. The examples for these procedures will follow in the next posts. <\/p>\n<p>How are the values for columns stored during the execution of FETCH_ROWS? First, DBMS_TF  supports fifteen scalar data types that can only be used for read columns. There is a table type <strong>ROW_SET_T<\/strong> which is a table of records of the type <strong>COLUMN_DATA_T<\/strong>. Which in turn has a DESCRIPTION (record type COLUMN_METADATA_T) and fifteen so called variant fields for all supported types. These variant fields are actually collections (associative arrays or index-by tables in terms of PL\/SQL). Only one variant field of the proper data type is active for one column. Refer to Figure 3 <\/p>\n<p><a href=\"http:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2018\/04\/PTF_ROWSET2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2018\/04\/PTF_ROWSET2.png\" alt=\"\" width=\"982\" height=\"578\" class=\"alignnone size-full wp-image-684\" \/><\/a><\/p>\n<p><em>Figure 3 Storing input and output values in the collections of DBMS_TF<\/em><\/p>\n<p>We use the overloaded procedure <em><strong>dbms_tf.put_col<\/strong><\/em> which expect the collection of one of the fifteen defined types. This collection is holding the values for each row. <\/p>\n<p><strong>What&#8217;s next?<\/strong><\/p>\n<p>We introduced the new column but didn&#8217;t really used any input data flowing into the function. In the next post we continue to look at the basics and show how we can do it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I have already posted some examples on Polymorphic Table Functions in Oracle 18c in the last months. I quickly realized how difficult it is to explain completely new feature using advanced examples and wanted to write a series of posts starting from very basics. Now that the Germany&#8217;s Oracle User Group (DOAG) has accepted my [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":468,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54,3,5,53,4,22],"tags":[57,74,55],"class_list":["post-467","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-18c","category-oracle","category-plsql","category-ptf","category-sql","category-trivadis","tag-18c","tag-polymorphic-table-function","tag-ptf"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/comments?post=467"}],"version-history":[{"count":5,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/467\/revisions"}],"predecessor-version":[{"id":476,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/467\/revisions\/476"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media\/468"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}