+1 401 783 6172 (International callers)
gary@skillbuilders.com
Request Info
Register Login

Login/Register to Access all of Skillbuilders' Content

Connect with:

Login with Facebook Login with Google Login with LinkedIn

Login with your site account

Connect with:

Login with Facebook Login with Google Login with LinkedIn


Lost your password?

Not a member yet? Register now

SkillBuildersSkillBuilders
  • Services
    • Oracle APEX Hosting on AWS
    • Oracle Database Admin/Remote DBA
    • Oracle APEX App Development
    • APEX Administration
  • Products
    • Oracle Database Manager (free)
    • Deprecated APEX Plugins
  • Training
    • Course Calendar
    • Oracle DBA
    • Oracle Developer
    • Oracle APEX
    • Web Development
    • Free Webinars
    • Mentoring
    • Free Oracle Database Tutorials
      • Free Oracle Database Administration Tutorials
      • Free Oracle Developer Tutorials
      • Free Oracle APEX Tutorials
      • Free Solaris 11, Oracle VM and ZFS Tutorials
      • Free Oracle Database Retired Tutorials
      • Free Groovy and Grails Tutorials
  • Blog
  • About
    • About Us
    • Our Team
    • Events
    • Case Studies
      • Oracle Database Administration
      • Oracle APEX Development
      • Oracle APEX Administration
      • Oracle and AWS Cloud
    • Client Testimonials
    • Contact
    • Services
      • Oracle APEX Hosting on AWS
      • Oracle Database Admin/Remote DBA
      • Oracle APEX App Development
      • APEX Administration
    • Products
      • Oracle Database Manager (free)
      • Deprecated APEX Plugins
    • Training
      • Course Calendar
      • Oracle DBA
      • Oracle Developer
      • Oracle APEX
      • Web Development
      • Free Webinars
      • Mentoring
      • Free Oracle Database Tutorials
        • Free Oracle Database Administration Tutorials
        • Free Oracle Developer Tutorials
        • Free Oracle APEX Tutorials
        • Free Solaris 11, Oracle VM and ZFS Tutorials
        • Free Oracle Database Retired Tutorials
        • Free Groovy and Grails Tutorials
    • Blog
    • About
      • About Us
      • Our Team
      • Events
      • Case Studies
        • Oracle Database Administration
        • Oracle APEX Development
        • Oracle APEX Administration
        • Oracle and AWS Cloud
      • Client Testimonials
      • Contact

    Blog

    • Home
    • Blog
    • Upgrading to 12.2 ? Make sure you won’t break JSON

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

    • Posted by John Watson
    • Categories Blog
    • Date January 5, 2018

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

    Tag:Developer

    • Share:
    John Watson
    https://skillbuilders.com/our_team/john-watson/

    Previous post

    Oracle APEX Cloud Hosting Solutions
    January 5, 2018

    Next post

    12.2 upgrade - it can break all your outgoing https calls
    23 January, 2018

    Copyright © Skillbuilders.com

    Interested in joining our team?

    View details
    Managed APEX Hosting on AWS

    Sorry. This form is no longer accepting new submissions.