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 object type
- TYPE ERROR
- One or more of the data types between JSON keys and object type structures do not match. For example a string JSON value into a numeric object column
If none of the types are specified then all 3 are assumed.
Here are some demos. First is the test data.
SELECT *
FROM json_patient;
PATIENT_DATA
--------------------------------------------------------------------------------
{"pname" : "Patient 1","mrns" : [{"OS" : 1,"MRN" : "1"},{"OS" : 2,"MRN" : "2"}]}
{"pname" : "Patient 2","mrns" : [{"OS" : 1,"MRN" : "3"},{"OS" : 2,"MRN" : "4"}]}
And the mrn_date_o object definition.
CREATE OR REPLACE TYPE mrn_date_o AS OBJECT ( os NUMBER,
mrn VARCHAR2(100),
sdate DATE,
edate DATE);
Note – the objects in the mrns JSON array have 2 keys (OS and MRN) while the object has 4 fields (OS, MRN, SDATE, and EDATE).
Did you just use a note to foreshadow something? Yes I did! Thanks for noticing.
And code that specifies all 3 mismatch types.
BEGIN
FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
RETURNING mrn_date_o
ERROR ON MISMATCH( MISSING DATA,
EXTRA DATA,
TYPE ERROR ) ) mrn_date_o
FROM json_patient ) LOOP
DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
END LOOP;
END;
Raises ORA-40601: missing data for object type conversion ORA-06512: at line 2
This next example will not raise the error because MISSING DATA is not specified.
BEGIN
FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
RETURNING mrn_date_o
ERROR ON MISMATCH( EXTRA DATA,
TYPE ERROR ) ) mrn_date_o
FROM json_patient ) LOOP
DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
END LOOP;
END;
You can mix and match…or mix and mismatch…
I see what you did there. Very witty…
BEGIN
FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
RETURNING mrn_date_o
ERROR ON MISMATCH( EXTRA DATA,
TYPE ERROR )
IGNORE ON MISMATCH ( MISSING DATA ) ) mrn_date_o
FROM json_patient ) LOOP
DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
END LOOP;
END;
But only 1 per type.
BEGIN
FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
RETURNING mrn_date_o
ERROR ON MISMATCH( EXTRA DATA,
TYPE ERROR )
IGNORE ON MISMATCH ( TYPE ERROR ) ) mrn_date_o
FROM json_patient ) LOOP
DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
END LOOP;
END;
Raises -- PL/SQL: ORA-40450: invalid ON ERROR clause
The next post will delve deeper into what can (and cannot) be done with JSON and Object Types.
Thanks for reading!