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. There … 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
These are the scripts I used for my JSON Data Guide presentation at the ODTUG KSCOPE 2019 conference. Setup and Introduction User Data Guide Views Search Index with Dataguide Preferred Names Rename Columns Search Index Never Forgets DBMS_JSON.CREATE_VIEW DBMS_JSON.CREATE_VIEW_ON_PATH String + Number = String Be sure to run the Setup and Introduction scripts first. Thanks … Read more
The Oracle PLSQL JSON_OBJECT function assembles JSON KEY:VALUE pairs. It uses column values from a table or view as the VALUE. It can use hardcoded strings or column values as the KEY. Here is an example creating 3 records in a table and querying them using the JSON_OBJECT function. — record 1 NOT JSON INSERT … Read more
JSON Path Expression Item Method’s (PEIM) were added in Oracle 18c. They provide extensions to interpret JSON data. But the interpretations are stricter than they appear. Take the string() PEIM for instance – the Oracle documentation says it returns an “SQL VARCHAR2(4000) interpretation of the targeted scalar JSON value”. That seems straightforward but… — Create … Read more
Dataguide provides three attributes for JSON data – the path, the datatype and the length. Ever since Oracle 12.2 it has done that dutifully – even for arrays of scalar (non key-value pair) values. But initially it only denoted the fact there was an array, it did not denote the datatype of the entries in … Read more