dcsimg
 

Oracle's Native JSON Changes May Present a Surprise

Thursday Jun 13th 2019 by David Fitzjarrell
Oracle's Native JSON Changes May Present a Surprise

Recently Oracle has updated its JSON functionality in the database and that may present unexpected issues.  Read on to see what changed and how it could affect results.

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.

# # #

See all articles by David Fitzjarrell

Home
Mobile Site | Full Site