Because the world needs more PL/SQL blogs
Its time to deal with the inevitable complex changes to the database. I’ll start working through the JSON changes with this post that handles the following: Non-Unique Patient Names Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management … Read more
Its time to deal with the inevitable complex changes to the database. I’ll start working through the RDBMS changes with this post that handles the following: Non-Unique Patient Names Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management … Read more
Previous posts comparing JSON and RDBMS structure changes used a simple example adding RDBMS date columns or JSON keys. Now its time for something more challenging: Enterprise Master Patient Index (EMPI) All Patients must be given a new master identifier encompassing one or more patient records and their associated Medical Record Numbers (MRN’s) If the … Read more
JSON_TRANSFORM can ensure JSON structure when records are inserted into the database using an INSTEAD OF INSERT trigger on a view. CREATE OR REPLACE TRIGGER instead_patient INSTEAD OF INSERT ON migrated_patient BEGIN INSERT INTO json_patient VALUES ( JSON_TRANSFORM(:new.migrated, SET ‘$.unique_os’ = OS_LIST_UNIQUE( JSON_QUERY(:new.migrated,’$.mrns[*].OS’ WITH WRAPPER) ) REPLACE ON EXISTING, INSERT ‘$.mrns[*].sdate’ = SYSDATE IGNORE ON … Read more
The JSON_TRANSFORM function is perfect for evolving JSON structures to provide consistency. Take the patient tracking system being used for these posts for example. The first version did not have checks for unique OS values or start and end dates for OS/MRN pairs. Adding these keys is easily done as shown in this example. I can … Read more
My prior posts on JSON_TRANSFORM used simple text values and built-in Oracle functions like SYSDATE. This post demonstrates calling custom PL/SQL functions with JSON_TRANSFORM. Here are the two rows of demo data for this post. The pertinent keys are pname (patient name) and OS (Ordering System) in the elements of the mrns array. SELECT JSON_VALUE … Read more
The simple explanation of the JSON_TRANSFORM SET operation is that it sets values for specified JSON keys. For example “set $.KEY1 to 42′ will set the value of the KEY1 to 42. That’s pretty straightforward. The real power is the additional behaviours that can be defined such as: If $.KEY1 is missing then add it … Read more
A previous post in this series dealt with changes to the JSON data structures. Specifically the addition and population of two new key-value pairs. Two other posts here and here showed how object types can be used to make queries simpler as structures change. This is powerful but also quite limited. What we really need … Read more
Now that I know how flexible selecting JSON and object types are I am wondering if I can execute code in the object type body as well. I’ll try it now with this fully declared object type. CREATE OR REPLACE TYPE mrn_date_o AS OBJECT ( os NUMBER, mrn VARCHAR2(100), CONSTRUCTOR FUNCTION mrn_date_o ( SELF IN … Read more
The previous post introduced the ON MISMATCH for loading mismatched data into an Oracle Object. This post expands on that by explaining the three specific types of mismatches. They are: MISSING DATA The JSON does not have all of the keys to match the object type EXTRA DATA The JSON has more keys than the … Read more