2021 – Add Entry To Child Table

This post explains how a child record is added for a parent record. These terms (parent and child record) are part of the Relational Database Management System (RDBMS) nomenclature. The fully expanded description would be: Create a new record in a child table that is related to the parent table via a Foreign Key (FK) … Read more

2021 – Ensuring Uniqueness Part 2

Unique Array Values The previous post in this series demonstrated maintaining unique scalar values in JSON data and Relational Data. In both cases uniqueness can be maintained with unique indexes. That sameness does not hold for array as this post will demonstrate. This all comes about due to a new requirement for our patient tracking … Read more

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