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

KSCOPE 2019 JSON Data Guide

These are the scripts I used for my JSON Data Guide presentation at the ODTUG KSCOPE 2019 conference. Setup and Introduction User Data Guide Views Search Index with Dataguide Preferred Names Rename Columns Search Index Never Forgets DBMS_JSON.CREATE_VIEW DBMS_JSON.CREATE_VIEW_ON_PATH String + Number = String Be sure to run the Setup and Introduction scripts first. Thanks … 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