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. 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:
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
https://skillbuilders.com
Tag:Developer
