The advent of JSON (JavaScript Object Notation) has changed application programming, and, because of this, database vendors are adapting their products to make JSON easier to use within the database. Oracle is no exception, and with releases from 12.2 onwards Oracle has made JSON easily accessible from the PL/SQL engine. Even though JSON functionality is available in version 12.1, the enhancements implemented from 12.2 onward make JSON even easier to use. And, sometimes, making things easier can also break existing implementations. In this article, you’ll learn about what changed and how those changes can cause problems.
In version 12.1.0.2 JSON_TABLE, JSON_QUERY and JSON_EXISTS are separate functions. The following code illustrates this to return multiple phone numbers from a JSON table in version 12.1.0.2:
SELECT json_value(po_document, '$.Requestor' RETURNING VARCHAR2(32)),
json_query(po_document, '$.ShippingInstructions.Phone'
RETURNING VARCHAR2(100))
FROM j_purchaseorder
WHERE json_exists(po_document, '$.ShippingInstructions.Address.zipCode')
AND json_value(po_document, '$.AllowPartialShipment' RETURNING NUMBER(1))
= 1;
The results of the query are shown below:
JSON_VALUE(PO_DOCUMENT,'$.REQUES JSON_QUERY(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE'RETURNINGVARCHAR2(100))
-------------------------------- ----------------------------------------------------------------------------------------------------
Alexis Bull [{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]
In version 12.2.0.1 and later, the JSON_QUERY and JSON_EXISTS functionalities have been ‘rolled into’ JSON_TABLE, making code like that shown below possible:
SELECT jt.requestor, jt.phones
FROM j_purchaseorder,
json_table(po_document, '$'
COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
phones VARCHAR2(100 CHAR) FORMAT JSON
PATH '$.ShippingInstructions.Phone',
partial NUMBER(1) PATH '$.AllowPartialShipment',
has_zip VARCHAR2(5 CHAR) EXISTS
PATH '$.ShippingInstructions.Address.zipCode')) jt
WHERE jt.partial = 1 AND has_zip = 'true';
The results returned from the above query match those of the JSON_EXISTS version:
JSON_VALUE(PO_DOCUMENT,'$.REQUES JSON_QUERY(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE'RETURNINGVARCHAR2(100))
-------------------------------- ----------------------------------------------------------------------------------------------------
Alexis Bull [{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]
This makes for fewer parse calls to the database but has resulted in a change in behavior with respect to essentially non-existent JSON rows. This is illustrated with the following example, run on both 12.1.0.2 and 12.2.0.1. [The code has been obfuscated.] First, version 12.1.0.2:
SQL> select x.id - 1 my_index,
2 x.a,
3 x.my_rowid,
4 cdi_a,
5 cdi_rowid,
6 dbi_a,
7 dbi_rowid,
8 appl,
9 to_date(substr(dtappl, 1, 10), 'yyyy-mm-dd') dtappl
10 from apdain adi,
11 json_table(val, '$.stuff[0].more_stuff[*]'
12 columns(id for ordinality,
13 a path '$.a',
14 my_rowid path '$.rowid',
15 cdi_a path '$.cdi_a',
16 cdi_rowid path '$.cdi_rowid',
17 dbi_a path '$.dbi_a',
18 dbi_rowid path '$.dbi_rowid',
19 appl number path '$.appl',
20 dtappl path '$.dtappl')) x
21 where adi.id = 99999;
no rows selected
Now here is what version 12.2.0.1 returns:
SQL> select x.id - 1 my_index,
2 x.a,
3 x.my_rowid,
4 cdi_a,
5 cdi_rowid,
6 dbi_a,
7 dbi_rowid,
8 appl,
9 to_date(substr(dtappl, 1, 10), 'yyyy-mm-dd') dtappl
10 from apdain adi,
11 json_table(val, '$.stuff[0].more_stuff[*]'
12 columns(id for ordinality,
13 a path '$.a',
14 my_rowid path '$.rowid',
15 cdi_a path '$.cdi_a',
16 cdi_rowid path '$.cdi_rowid',
17 dbi_a path '$.dbi_a',
18 dbi_rowid path '$.dbi_rowid',
19 appl number path '$.appl',
20 dtappl path '$.dtappl')) x
21 where adi.id = 99999;
0
This unexpected behavior results in:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
which is the end result of this JSON error:
ORA-06512: at line 113
ORA-40462: JSON_VALUE evaluated to no value
In 12.1.0.2 this was not generated in this example because no row was returned for index values 0 and 2, yet in 12.2.0.1 JSON_TABLE returns NULL results and because the NOTFOUND flag isn’t set PL/SQL attempts to process the returned ‘data’ and fails miserably in the process. The original PL/SQL code that throws the error in 12.2.0.1 is provided below:
loop
t_my := null;
fetch my_cur into v_my_rec;
exit when nvl(my_cur%notfound, TRUE);
t_my.source_seq_num := v_lkup(v_my_rec.cdi_a);
t_my.dest_seq_num := v_lkup(v_my_rec.dbi_a);
end loop;
The code can be modified in a couple of ways to address the error. The preferred method is to set up an internal program block with an exception handler to ‘ignore’ the error:
begin
t_my.source_seq_num := v_lkup(v_my_rec.cdi_a);
t_my.dest_seq_num := v_lkup(v_my_rec.dbi_a);
exception
when value_error then
null;
end;;
The error handler specifically addresses the ORA-06502 thrown for the null pointer and continues execution through the entire loop; each affected loop block would be modified in this manner. A second method involves checking to see that the array index is not NULL, requiring that the code be modified for each instance of an array pointer assignment:
…
If <pointer_val1> is not null then
[existing code here]
end if;
If <pointer_val2> is not null then
[existing code here]
end if;
If <pointer_val3> is not null then
[existing code here]
end if;
…
Such edits could be tedious and confusing, which is why the exception handler within each loop is the preferred method of resolution.
Sometimes Oracle errors aren’t what they seem to be, and code working in one release that fails in another might need to be ‘disassembled’ to determine the actual cause of the reported error. In this case the cursor queries were executed in SQL*Plus so the results returned could be examined. Yes, for large PL/SQL blocks this could take a bit of time, but it may be the only way to discover the true source of an error.
# # #
