Because the world needs more PL/SQL blogs
The previous post in this series satisfied the following requirement in RDBMS data. Create a new record in a child table that is related to the parent table via a Foreign Key (FK) to a Primary Key (PK) in the parent table. Its much simpler to describe in terms of JSON data: Add a new … Read more
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
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
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
Oracle’s JSON Path Expression (JPE ) uses specific characters such as dollar signs and dots to step through JSON structures. For example a JPE of $.NAME steps to the NAME key at the absolute top of a JSON structure. Whenever invalid syntax JPE syntax is encountered Oracle throws an error with a JZN prefix. Why … Read more
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
In this post I’ll explain how to make Dynamic SQL with JSON Path Expressions (JPE) safe from SQL Injection. Or would it be JPE injection? Let’s figure that out later. For now lets get into the code! Here is the function that queries the patient name for a given Ordering System (OS) and Medical Record … Read more
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
One of the requirements for the simple PLSQL API’s being created in these posts is that all errors must be ignored. Don’t laugh – I’ve often been given that requirement in the real world. That does not leave me with much to do at this point so I’ll use the time to take care of an … Read more
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