implestrat.com

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

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

Another question I was asked at KSCOPE 2019 was if Polymorphic Table Functions (PTF) could mimic an SQL CONNECT BY query. The question borders between a real world usage and a home work assignment but I’ll give the benefit of the doubt and turn it into the next series of posts on performance. Here is … Read more

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

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

This post expands on TABLE POLYMORPHIC functionality by demonstrating how records are distributed across partitions. Two key points: The maximum number of rows per rowset is 1024 Within the FETCH_ROWS procedure you can process the whole fetched data set at one time if desired. First some demo data – 250 records each for COL1 values … Read more

The Oracle documentation states that an Execution ID is “…a unique execution id that can be used by the PTF to index any cursor execution specific runtime state.” The previous post in about Table Polymorphism demonstrated that each partition of data got its own OPEN, FETCH_ROWS and CLOSE procedure. Let’s see if each one get’s … Read more

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

This post progresses the PTF Totalling series with Cross PTF Retention (CPTFR). Note – I just made that up. It stores values in session memory for later retrieval. This post deals with the storing and a subsequent post will deal with the retrieving. I’ll start with a package to maintain the totals in a PL/SQL … Read more