KSCOPE Answer 2 of 2 – Part 3

This post concludes the series on imitating a CONNECT BY SQL query with a PTF. It calculates the CONNECT BY values (dates in this case) and outputs them as a TRACE column. First I add the trace column in the DESCRIBE function. v_new_col DBMS_TF.COLUMN_METADATA_T; v_new_cols DBMS_TF.COLUMNS_NEW_T; … v_new_col := DBMS_TF.COLUMN_METADATA_T( type => DBMS_TF.TYPE_VARCHAR2, name => … Read more

KSCOPE Answer 2 of 2 – Part 2

The next step to performing CONNECT BY queries in a Polymorphic Table Function is to add the explicit fetch. First add this to the DESCRIBE function to ensure all columns are fetched. FOR counter IN 1..p_tbl.column.count LOOP p_tbl.column(counter).for_read := TRUE; END LOOP; Then add a explicit FETCH to the FETCH_ROWS procedure. DBMS_TF.GET_ROW_SET( rowset => v_rowset, … Read more

KSCOPE Answer 1 of 2

At the recent ODTUG KSCOPE conference I used this script to demonstrate 3 concepts: Records fetched by a Polymorphic Table Function (PTF) can be removed from the output by specifying a replication factor of zero Values for added columns can be replicated across returned rows Great care is required to coordinate the replication of fetched … Read more

KSCOPE 2019 Polymorphic Table Functions

These are the scripts I used for my Polymorphic Table Function presentation at the ODTUG KSCOPE 2019 conference. Setup And Intro Row Replication Column Addition Row Replication And Column Addition GET_ROW_SET Row Set Details Get Value To Populate alues Columns Parameter CSTORE and XSTORE Table Polymorphic and Partitioning Partitioning Demo Be sure to run the … Read more

Table Polymorphism

All of the previous posts in this series relied on hand written code to group values together. Polymorphic Table Functions offer a way to do the grouping automatically. Its referred to as TABLE POLYMORPHISM and here is an introductory post. First up – some demo data. CREATE TABLE table1 ( col1 NUMBER ); BEGIN FOR … Read more