Polymorphic Table Functions (PTF) – Tinkering with Rowsets

Writing the second “basics” post on PTF I discovered, that there were much more details worth mentioning, than it would be acceptable for a “basics” post and would blow it up anyway 😉 So I decided to to separate the tests and finding in this (more deep-dive) post.

CAUTION: WORK IS STILL IN PROGRESS!

I’ll skip the setup for the tests. Please refer to Part II to see the table and PTF definitions.

My goal was to see, how the input and output row sets look like. How to populate the output row set, what can go wrong, etc.

Let’s add some tracing to see more. The package DBMS_TF provides several overloaded functions TRACE, the one that acts just like dbms_output.put_line, whereas others accept various structured types as parameter and present their content in the pretty manner. We’ll use the one to output the content of the both row sets (see Listing 5).

Note that I’m not listing the whole package anymore, but only the changed part:

...
    dbms_tf.get_row_set(inp_rs, rowcnt, colcnt);
    dbms_tf.trace(inp_rs);

-- For Loops here

   ...

   dbms_tf.trace(out_rs); 
   dbms_tf.put_row_set(out_rs); 
END;

...

SQL> SELECT * FROM my_ptf(t, COLUMNS(A));

         A AGG_COL             
---------- --------------------
         1 2;3;"ONE"           
         4 5;6;"TWO"           
         7 8;9;"THREE"  


.... [1] = {"B":2, "C":3, "V":"ONE"}
.... [2] = {"B":5, "C":6, "V":"TWO"}
.... [3] = {"B":8, "C":9, "V":"THREE"}
.... [1] = {:"2;3;"ONE""}
.... [2] = {:"5;6;"TWO""}
.... [3] = {:"8;9;"THREE""}

As expected, we see three rows with three columns in the input row set. The column A is not there, being not a FOR_READ-column. The output row set consists of only one unnamed column. Unnamed, because we’ve only assigned the values, but not the column name. Is it a problem? Obviously not, it works! The procedure put_row_set is doing positional reference in that case. But what if we had assigned the column name?


   out_rs(1).description.name := 'TESTCOL'; 
   out_rs(1).tab_varchar2(2) := 'Test' ;
   dbms_tf.trace(out_rs); 
   dbms_tf.put_row_set(out_rs); 
END;

SQL> SELECT * FROM my_ptf(t, COLUMNS(A));

         A AGG_COL             
---------- --------------------
         1 2;3;"ONE"           
         4 Test                
         7 8;9;"THREE" 

.... [1] = {TESTCOL:"2;3;"ONE""}
.... [2] = {TESTCOL:"Test"}
.... [3] = {TESTCOL:"8;9;"THREE""}

What we can see, is that the column has its name now, but it makes no difference to the output of the query. We have defined the name of the new column within the DESCRIBE and that takes precedence.
What if we now add another column to the output row set?


...
   out_rs(2).description.name := 'TESTCOL'; 
   out_rs(2).tab_varchar2(2) := 'Test' ;
   dbms_tf.trace(out_rs); 
   dbms_tf.put_row_set(out_rs); 
END;

SQL> SELECT * FROM my_ptf(t, COLUMNS(A));

         A AGG_COL             
---------- --------------------
         1                     
         4                     
         7                     

Nothing happens! Moreover, the function is not aggregating anymore and returns no values in AGG_COL. Something is wrong! First, we can’t just extend the rowset with additional columns that were not declared as new in DESCRIBE. Try to correct this and declare the second NEW-column:


...
  RETURN dbms_tf.describe_t( 
           new_columns => 
               dbms_tf.columns_new_t( 
                  1 => dbms_tf.column_metadata_t(
                           name => new_col_name,  
                           TYPE => dbms_tf.type_varchar2)
                 ,2 => dbms_tf.column_metadata_t(
                           name => 'TESTCOL',  
                           TYPE => dbms_tf.type_varchar2)
                           ));
END describe; 

-- FETCH_ROWS unchanged

SQL> SELECT * FROM my_ptf(t, COLUMNS(A));

         A AGG_COL              TESTCOL             
---------- -------------------- --------------------
         1                                          
         4                                          
         7            

This doesn’t really help, except that we can see the second empty column. Note, that tracing of the output row set doesn’t work either. I guess, the reason for both is the no_data_found exception raised in the trace-procedure and in dbms_tf.put_row_set which is not further propagated. We’ve assigned only one value for the second column TESTCOL. But those procedures have to read every element of the INDEX-BY table. And reading unassigned element of an associative array leads to an exception! Just put the assignment for the second column inside the loop and everything works fine again.


...

    FOR r IN 1..rowcnt LOOP
      out_rs(1).tab_varchar2(r) := '';
      out_rs(2).tab_varchar2(r) := 'Test';

      FOR c IN 1..colcnt LOOP
        out_rs(1).tab_varchar2(r) := 
                            out_rs(1).tab_varchar2(r)||
                            ';'||DBMS_TF.COL_TO_CHAR(inp_rs(c), r);
      END LOOP;
      out_rs(1).tab_varchar2(r) := 
                           ltrim (out_rs(1).tab_varchar2(r) ,';'); 
    END LOOP;
   out_rs(2).description.name := 'TESTCOL'; 
   dbms_tf.trace(out_rs); 
   dbms_tf.put_row_set(out_rs); 
END;

SQL> SELECT * FROM my_ptf(t, COLUMNS(A));

         A AGG_COL              TESTCOL             
---------- -------------------- --------------------
         1 2;3;"ONE"            Test                
         4 5;6;"TWO"            Test                
         7 8;9;"THREE"          Test                

.... [1] = {:"2;3;"ONE"", TESTCOL:"Test"}
.... [2] = {:"5;6;"TWO"", TESTCOL:"Test"}
.... [3] = {:"8;9;"THREE"", TESTCOL:"Test"}

One test more.. If we name both columns explicitly, but change the order of them, that will have no impact on the result, although the tracing of the rowset shows the exchanged column names. That confirms the referencing of output columns to be positional only. Column names don’t matter at all!

...
   out_rs(2).description.name := 'AGG_COL'; 
   out_rs(1).description.name := 'TESTCOL'; 
   dbms_tf.trace(out_rs); 
   dbms_tf.put_row_set(out_rs); 
END; 

SQL> SELECT * FROM my_ptf(t, COLUMNS(A));

         A AGG_COL              TESTCOL             
---------- -------------------- --------------------
         1 2;3;"ONE"            Test                
         4 5;6;"TWO"            Test                
         7 8;9;"THREE"          Test       

.... [1] = {TESTCOL:"2;3;"ONE"", AGG_COL:"Test"}
.... [2] = {TESTCOL:"5;6;"TWO"", AGG_COL:"Test"}
.... [3] = {TESTCOL:"8;9;"THREE"", AGG_COL:"Test"}


So my findings so far:

  • the output rowset holds only PUT-columns
  • the order of columns is as defined in DESCRIBE (new columns), column names are not taken into account
  • always populate row sets to be dense. They are not allowed to be sparse.
  • adding columns to rowset will have no impact, if not declared in DESCRIBE altogether

Leave a Reply

Your email address will not be published. Required fields are marked *