2021 – Virtual Column Invisibility, Errors and Indexes

This post covers continues with the Virtual Column discussion including making them invisible, handling errors in the underlying function and creating indexes on them. Invisibility First up is invisibility. Virtual columns are treated as just another column in Data Manipulation Language (DML) statements. For example and INSERT statement like the one below assumes that all … Read more

2020 – Handling JSON Objects

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

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