Because the world needs more PL/SQL blogs
To get things up and running the original code for Project 2020 processed string JSON values only. But that is not much use if one has numeric values in their JSON. So I have updated the Polymorphic Table Function (PTF) to handle numbers. The changes required were very arduous and involved 17 million hours of … Read more
So far the 2020 project code only handles string values such as {“KEY1″:”VALUE1”} which becomes a column named KEY1 with a value of VALUE1. That’s fine for introductory purposes but what about JSON objects such as {“OBJECT5”:{“KEY5″:”VALUE5”}}? Thankfully the combination of JSON_DATA_GUIDE, JSON_VALUE and Polymorphic Table Functions (PTF’s) handle’s it easily. First up is the … Read more
This post finally puts queried data into columns in Polymorphic Table Function (PTF) result set. The algorithm is fairly straightforward: Get a set of rows (aka ROWSET) using DBMS_TF.GET_ROW_SET Find the column in the ROWSET that contains JSON For each row in the ROWSET Grab the JSON data For each KEY we care about Use … Read more
It’s finally time to get data to populate the structures defined in previous posts. The code for this is pretty straightforward. Fetch a set of rows Navigate through the row set column by column row by row extracting data First up – some variables… v_row_set DBMS_TF.ROW_SET_T; — fetched rows go here v_clob CLOB; — JSON … Read more
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