2021 – JSON Code Changes With Database Unavailable

The last modification for the JSON structure change is the PL/SQL API. First up lets make sure the query API works.

BEGIN
  DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname( p_os => 1, p_mrn => '1'));
END;
Patient 1

That is the correct result. Now for the add_patient procedure.

BEGIN
  jpatient_pkg.add_patient( p_name => 'Bob',
                            p_os   => 1,
                            p_mrn  => 11 );
END;

The patient was indeed created…

SELECT *
  FROM json_patient bob
 WHERE JSON_VALUE(patient_data,'$.pname') = 'Bob';
PATIENT_DATA
----------------------------------------------------
{"pname" : "Bob","mrns" : [{"OS" : 1,"MRN" : "11"}]}

…but no start and end date values. Let’s add them to the add patient package with default values. Here is the code.

CREATE OR REPLACE PROCEDURE add_patient ( p_name VARCHAR2,
                                          p_os    NUMBER,
                                          p_mrn   VARCHAR2 ) AS
       v VARCHAR2(100);
BEGIN
  -- just add the SDATE and EDATE keys with hardcoded values
  v := JSON_OBJECT( KEY 'OS' VALUE p_os,
                    KEY 'MRN' VALUE p_mrn,
                    KEY 'SDATE' VALUE SYSDATE,
                    KEY 'EDATE' VALUE NULL );
  v := JSON_ARRAY(v FORMAT JSON);
  v := JSON_OBJECT( KEY 'pname' VALUE p_name,
                    KEY 'mrns' VALUE v FORMAT JSON);
  INSERT INTO json_patient(patient_data) VALUES(v);
END;

And run it now.

BEGIN
  jpatient_pjk.add_patient( p_name => 'Bob With Dates',
                            p_os   => 1,
                            p_mrn  => 111 );
END;

And check the records

SELECT *
  FROM json_patient bob_with_dates
 WHERE JSON_VALUE(patient_data,'$.pname') LIKE 'Bob%';
PATIENT_DATA
{"pname" : "Bob With Dates","mrns" : [{"OS" : 1,"MRN" : "111","SDATE" : "2021-08-10T02:27:50","EDATE" : null}]}
{"pname" : "Bob","mrns" : [{"OS" : 1,"MRN" : "11"}]}

Its just that simple!

Thanks for reading!