Because the world needs more PL/SQL blogs
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
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