Because the world needs more PL/SQL blogs
My experience with JSON and SQL and PLSQL has left me wanting more flexibility. I naively though that the flexible nature of JSON data would be matched by equally flexible SQL and PLSQL options. Thus I grew frustrated having to retool code to handle data structure changes just like I do with Relational Databases. Where … Read more
The last modification for the JSON structure change is the PL/SQL API. First up lets make sure the query API works. BEGIN DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname( p_os => 1, p_mrn => ‘1’)); END; Patient 1 That is the correct result. Now for the add_patient procedure. BEGIN jpatient_pkg.add_patient( p_name => ‘Bob’, p_os => 1, p_mrn => 11 ); END; … Read more
The patient management system has been given some new requirements. OS/MRN combinations must have a start date The start date defaults to the current date OS/MRN combinations may have and end date In the RDBMS world this is done in 3 steps: Add New Columns Set New Column Values Set NULL In JSON its done … Read more
The patient management system has been given some new requirements. OS/MRN combinations must have a start date The start date defaults to the current date OS/MRN combinations may have and end date And there is no requirement for the database to be available while the changes are made! That makes things really simple… Step 1 … Read more
This post compares index creation and usage for RDBMS data and JSON data. I’ll focus on three specific aspects. Creating Indexes Including JSON Search Indexes Verifying Index Usage Oracle’s EXPLAIN PLAN utility shows if an index was used or not in a query Resolving Index Usage Oracle’s EXPLAIN PLAN utility shows the actual actual criteria … Read more
Iudith Mentzel noticed a problem in my OS_LIST_UNIQUE function. It relied on the Ordering System (OS) number being incremental. For example [3,2,1] would not be considered a unique list. The problem lay in the following steps: Load the passed JSON Array into a table 1 Loop through table 1 sequentially to load unique values into … Read more
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
Oracle offers like JSON_TABLE and JSON_VALUE to translate JSON keys and values into RDBMS rows and columns. But there is no fair way to compare the performance of those functions with home grown code because its not possible to see the inner workings and influences of the built in functions. Thus an apples-to-apples comparison is … Read more
The previous posts in this series here and here explain the details of adding a Virtual Column (VC) to a table containing JSON data. The VC parsed the JSON to assemble a unique list of values. This post changes that VC to determine if the list passed in is unique and then return 1 if … Read more
This post continues our quest to find Bad JSON data while reducing code complexity. In particular we hid many lines of complex PL/SQL behind a simple to understand and use Virtual Column (VC). The previous post explained the theory and syntax for VC’s – this post continues our implementation of them to find bad data. … Read more