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

2021 – First Data Structures

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

2021 – JSON/Relational and SQL/PLSQL

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