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!