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 header.

CREATE OR REPLACE PACKAGE jpatient_pkg AS

  FUNCTION get_pname ( p_os NUMBER,
                       p_mrn VARCHAR2 )
     RETURN VARCHAR2;

  PROCEDURE add_patient ( p_name VARCHAR2,
                          p_os NUMBER,
                          p_mrn VARCHAR2 );

END;

And the package body.

CREATE OR REPLACE PACKAGE BODY jpatient_pkg AS

/*---------------------------------------------------*/
FUNCTION get_pname ( p_os NUMBER,
p_mrn VARCHAR2 )
RETURN VARCHAR2 IS
/*---------------------------------------------------*/
v_sql VARCHAR2(1000);
v_ret_val VARCHAR2(1000);
BEGIN
--
-- Build this SQL using JSON Path Expressions
-- SELECT JSON_VALUE(patient_data,'$.pname')
-- FROM json_patient
-- WHERE JSON_EXISTS(patient_data,'$.mrns[*]?(@.OS == 1 && @.MRN == "MRN999")')
--
v_sql := 'SELECT JSON_VALUE(patient_data,''' ||
'$.pname' || ''') FROM json_patient WHERE ';
v_sql := v_sql || 'JSON_EXISTS(patient_data,''' ||
'$.mrns[*]?(@.OS == ' || p_os || ' && @.MRN == "' || p_mrn || '")' || '''' || ')';
EXECUTE IMMEDIATE v_sql INTO v_ret_val;
RETURN(v_ret_val);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN(NULL);
WHEN TOO_MANY_ROWS THEN
RETURN(NULL);
END;

/*---------------------------------------------------*/
PROCEDURE add_patient ( p_name VARCHAR2,
p_os NUMBER,
p_mrn VARCHAR2 ) IS
/*---------------------------------------------------*/
v VARCHAR2(100);
BEGIN

v := JSON_OBJECT( KEY 'OS' VALUE p_os,
KEY 'MRN' VALUE p_mrn );
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;

END;

Testing

First a reminder of the patient data.

SELECT *
  FROM json_patient;

PATIENT_DATA
------------------------------------------------------------------------------
{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}
{"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN99"}]}

-- Now query an MRN and OS that exists
BEGIN
DBMS_OUTPUT.PUT_LINE('Found ' || jpatient_pkg.get_pname(1,'MRN123'));
END;
And the result is
Found Patient 1

-- Query an MRN and OS that does not exist
BEGIN
DBMS_OUTPUT.PUT_LINE('Found ' || jpatient_pkg.get_pname(1,'NOT THERE'));
END;
Nothing was found
-- Now add a patient
BEGIN
jpatient_pkg.add_patient('Patient 3',1,'MRN88');
END;

-- query the patient we just added
SELECT *
FROM json_patient
WHERE JSON_VALUE(patient_data,'$.pname') = 'Patient 3'

PATIENT_DATA
--------------------------------------------------------------
{ "pname" : "Patient 3","mrns" : [{"OS" : 1,"MRN" : "MRN88"}]}

-- Add the patient again
BEGIN
jpatient_pkg.add_patient('Patient 3',1,'MRN88');
END;

-- And query it
PATIENT_DATA
--------------------------------------------------------------
{ "pname" : "Patient 3","mrns" : [{"OS" : 1,"MRN" : "MRN88"}]}
{ "pname" : "Patient 3","mrns" : [{"OS" : 1,"MRN" : "MRN88"}]}
-- will 2 of the same OS and MRN cause issues? Lets retrieve it using the package.
BEGIN
DBMS_OUTPUT.PUT_LINE('Found ' || jpatient_pkg.get_pname(1,'MRN88'));
END;
Nothing found and no error was returned because the TOO_MANY_ROWS exception was triggered and ignored in the package.

-- Insert a really long name
BEGIN
jpatient_pkg.add_patient('The longest name ever found, ever',1,'MRN654');
END;

-- and retrieve it
BEGIN
DBMS_OUTPUT.PUT_LINE('Found ' || jpatient_pkg.get_pname(1,'MRN654'));
END;
Found The longest name ever found, ever

The next post will start a more in-depth comparison of the two simplistic packages.

Thanks for reading!

Leave a Comment