Because the world needs more PL/SQL blogs
This post introduces the PLSQL API for the RDBMS version of the simple patient tracking system. First up, here is a reminder of the data. INSERT INTO patient ( patient_id, patient_name ) VALUES(1,’Patient 1′); INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(1,1,’MRN123′); INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(2,1,’MRN456′); INSERT INTO patient … Read more
The last post showed the pitfalls of treating JSON as simple text when comparing it to relational data. This post demonstrates a better way to perform the comparison. Use built in SQL and PLQL functions to translate the relational data to JSON Perform a “JSON aware” comparison to the JSON data Relational To JSON Step … Read more
I really need to compare my data sets (one RDBMS and one JSON) to ensure they match. And the only programming languages I trust are SQL and PLSQL. If they say the data is the same then I trust it is the same. The Data — RDBMS SELECT patient_name, ordering_system, patient_mrn FROM patient p, patient_mrn … Read more
Project 2021 requires 2 different types of database structures. Relational (RDBMS) Tables with rows of columns and integrity checks JSON All data stored in a single column with embedded KEY:VALUE pairs RDBMS Structure And Data Here is the SQL to create and populate the RDBMS structure. CREATE TABLE patient ( patient_id NUMBER NOT NULL PRIMARY … Read more
Welcome to 2021 – the year of adaptability! That’s the concept for this years project. Specifically the adaptability of two database design models – relational and JSON – and two programming languages – SQL and PLSQL. This project will work through the life cycle of a database system as it adapts to new requirements. Table … Read more
Welcome to 2021 – the year of adaptability! That’s the concept for this years project. Specifically the adaptability of two database design models – relational and JSON – and two programming languages – SQL and PLSQL. This project will work through the life cycle of a database system as it adapts to new requirements. There … Read more
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