We created following SAMPLE Stored Procedure to see behavior of GET_NUMBER function of JSON_OBJECT_T object. Values returned by GET_NUMBER function of JSON_OBJECT_T object were unexpected and needs explanation
CREATE OR REPLACE PROCEDURE SAMPLE
(
IN_JSON_STR IN VARCHAR2
) AS
V_JSON_OBJECT JSON_OBJECT_T;
BEGIN
IF IN_JSON_STR IS JSON THEN
DBMS_OUTPUT.PUT_LINE('IN_JSON_STR = ' || IN_JSON_STR);
V_JSON_OBJECT := JSON_OBJECT_T.PARSE(IN_JSON_STR);
DBMS_OUTPUT.PUT_LINE('FIELD1 = ' || V_JSON_OBJECT.GET_NUMBER('FIELD1'));
DBMS_OUTPUT.PUT_LINE('FIELD2 = ' || V_JSON_OBJECT.GET_NUMBER('FIELD2'));
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID JSON STRING');
END IF;
END SAMPLE;
| Scenario # | Value passed for IN_JSON_STR | Value return by GET_NUMBER('FIELD1') | Value return by GET_NUMBER('FIELD2') | Comments |
|---|
| 1 | {} | NULL | NULL | OK and expected result |
| 2 | {"FIELD1":""} | 0 | NULL | Unexpected value 0 for FIELD1 but acceptable. Should be NULL for FIELD1? |
| 3 | {"FIELD1":"", "FIELD2":""} | 0 | 0 | Unexpected value 0 for FIELD1 and FIELD2 but acceptable. Should be NULL for FIELD 1 AND FIELD2? |
| 4 | {"FIELD1":"123", "FIELD2":""} | 123 | 0 | Unexpected value 0 for FIELD2 but accepted. Should be NULL for FIELD2? |
| 5 | {"FIELD1":"", "FIELD2":"123"} | 23 | 123 | UNEXPECTED AND UNACCEPTABLE. How come value for FIELD1 is coming as 23 ??? |
Any rational explanation on above behaviors / scenarios of GET_NUMBER function of JSON_OBJECT_T please ??? Specially scenario # 5 above.
Thanks & Regards.
Anil Verani