2020 – Part 4 – Add Columns For Scalars In JSON

This post adds columns (finally!) to the result set of the Polymorphic Table Function (PTF). The columns structure (name, datatype and length) is based on the keys in the JSON Data Guide (colname, type and length). Here is the code. FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) RETURN DBMS_TF.DESCRIBE_T IS v_dummy NUMBER; v_new_cols DBMS_TF.COLUMNS_NEW_T; v_colname … Read more

2020 – Part 3 – Find Keys In Dataguide In Describe Function

The previous post in this series determined which JSON columns have a Dataguide enabled Search Index. This post takes the next step of interrogating the Search Index for the JSON ksys it contains. The interrogation is done via the USER_JSON_DATAGUIDE_FIELDS view. Here’s the code from the PTF’s DESCRIBE function. FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T … Read more

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