Upgrading to 12.2 ? Make sure you won't break JSON


Will upgrade from 12.1 to 12.2 break your applications? It may if your developers are using JSON.
In both release 12.1 and 12.2, there are these keywords:

orclx> select * from v$reserved_words where keyword like 'JSON%' order by 1;

KEYWORD                            LENGTH R R R R D     CON_ID
------------------------------ ---------- - - - - - ----------
JSON                                    4 N N N N N          0
JSONGET                                 7 N N N N N          0
JSONPARSE                               9 N N N N N          0
JSON_ARRAY                             10 N N N N N          0
JSON_ARRAYAGG                          13 N N N N N          0
JSON_EQUAL                             10 N N N N N          0
JSON_EXISTS                            11 N N N N N          0
JSON_EXISTS2                           12 N N N N N          0
JSON_OBJECT                            11 N N N N N          0
JSON_OBJECTAGG                         14 N N N N N          0
JSON_QUERY                             10 N N N N N          0
JSON_SERIALIZE                         14 N N N N N          0
JSON_TABLE                             10 N N N N N          0
JSON_TEXTCONTAINS                      17 N N N N N          0
JSON_TEXTCONTAINS2                     18 N N N N N          0
JSON_VALUE                             10 N N N N N          0

16 rows selected.


The SQL functions are the also same in both releases:
orclx> select distinct name from v$sqlfn_metadata where name like 'JSON%' order by 1;


11 rows selected.

The problem comes with PL/SQL. According to the 12.2 docs:
SQL/JSON functions json_value, json_query, json_object, and json_array, as well as SQL/JSON condition json_exists, have been added to the PL/SQL language as built-in functions (json_exists is a Boolean function in PL/SQL).
This means that if, within your PL/SQL code, you created a function called (for example) JSON_VALUE, it will compile and run in releases up to 12.1, but in 12.2 it will throw errors. This is what our client had done: they had written PL/SQL equivalents of the SQL functions.
That was a nasty problem to detect, and the only solution is to re-write the functions to have different names and adjust all the code that uses them.
Lesson learnt - never use a keyword as an identifier.
John Watson
Oracle Certified Master DBA


I really appreciate this post. I've been looking all over for this!
link spam removed
link spam removed