Because the world needs more PL/SQL blogs
This post recaps all of the pieces we have to put our JSON data into colums in rows. We have the queried (aka GET) columns, the added (aka PUT) columns and the JSON paths. Here are the declared variables that store all of that information. They are are populated in the DESCRIBE function and then … Read more
We are now at the point where our DESCRIBE function parses JSON to add columns to the result set. For example {“KEY1″:”VAL1”} adds a column named KEY1 with a datatype of VARCHAR2. The next step in our PTF is actually retrieving the JSON values using JSON paths – for example the path of $.KEY1 will … Read more
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
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
The first posts in this series set up the data, the Polymorphic Table Function (PTF) and the Dataguide Search Index. This posts starts putting it all together by finding the JSON columns passed into the PTF. Its done in the DESCRIBE function with this code: — for every column passed in FOR counter IN 1..p_tbl.column.count … Read more
The next thing we need for Project 2020 is a simple and fast way to determine what keys are contained in our JSON data. My favourite way to get this is an Oracle Search Indexes because they are: Simple The SQL to create them is very straightforward and accessing the JSON keys simple via views … Read more
Way back in the previous decade I asked some PL/SQL developers how they might use Polymorphic Table Functions (PTF) and JSON Dataguides. Almost all of the replies involved translating JSON keys and values into columns and rows on the fly. So with that and a brand new decade in mind I’m embarking on a new … Read more
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