2021 – Ensuring Uniqueness Part 1

The next feature for the API’s is maintaining unique patient names. This is best done in the database because it is the lowest level of interaction (and defense) for the application. In our case two unique indexes are required – one for the JSON data and one for the relational data: The patient_name column in … Read more

2021 – JSON Path Expressions And Dynamic SQL

You say passing, I say using…let’s call the whole thing off I use colons, you use dollar signs, let’s call the whole thing off The previous post presented some pretty complex code including: JSON Path Expressions (dollar signs, ampersands, etc) Dynamic SQL (colons and the USING clause) JSON_EXISTS (PASSING clause) I promised an explanation of … Read more

2021- Parameter Data Type Checks

This post a quick eplanation of how Oracle validates datatype when compiling and executing code. Parameter Datatypes Parameter datatype are very important because they dictates the behavior of the PL/SQL compiler. For example the get_pname function has 2 parameters: p_os – the number of the operating system – the datatype is number so it will … Read more

2021 – JSON API

Here is the PL/SQL API for the patient data in the JSON table. It contains the following: One function to retrieve the patient name associated with a given OS and MRN One procedure to insert a new patient, OS and MRN And at this point no errors need be raised. Ever. Here is the package … Read more

2021 – RDBMS Data API

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

2021 – Compare JSON and RDBMS Data 2

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