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

articles: 

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.

orclx>

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;

NAME
------------------------------
JSON
JSON_ARRAY
JSON_ARRAYAGG
JSON_EQUAL
JSON_EXISTS
JSON_OBJECT
JSON_OBJECTAGG
JSON_QUERY
JSON_SERIALIZE
JSON_TEXTCONTAINS2
JSON_VALUE

11 rows selected.

orclx>
The problem comes with PL/SQL. According to the 12.2 docs:
Quote:
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
http://skillbuilders.com

Comments

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