8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

JSON Support Enhancements in Oracle Database 12c Release 2 (12.2)

Oracle 12c Release 2 (12.2) includes several enhancements to the JSON functionality in the database. This article details some of them, as well as linking to separate articles on others.

Related articles.

JSON Developers Guide

The documentation for Oracle Database 12c Release 2 (12.2) now includes a new manual, which brings together all the support for JSON inside the database.

The JSON conditions and functions from 12.1 are now supported in PL/SQL

Oracle Database 12c Release 1(12.1) introduced a number of JSON conditions and functions available from SQL. Oracle Database 12c Release 2 (12.2) now supports these conditions and functions in PL/SQL also.

The following example shows the IS JSON and JSON_EXISTS conditions and the JSON_VALUE and JSON_QUERY functions used in PL/SQL.

  PROCEDURE test_json (p_json IN CLOB) IS
    IF p_json IS JSON THEN
      DBMS_OUTPUT.put_line(p_json || ' - IS JSON');
      IF JSON_EXISTS(p_json, '$.employee_no' FALSE ON ERROR) THEN
        DBMS_OUTPUT.put_line('employee_no = ' || JSON_VALUE(p_json, '$.employee_no'));
        DBMS_OUTPUT.put_line('employee_no = ' || JSON_QUERY(p_json, '$.employee_no' WITH CONDITIONAL WRAPPER));
      END IF;
      DBMS_OUTPUT.put_line(p_json || ' - IS NOT JSON');
    END IF;
  test_json('{ "employee_no":9999 }');
banana - IS NOT JSON
{ "employee_no":9999 } - IS JSON
employee_no = 9999
employee_no = [9999]

PL/SQL procedure successfully completed.


The following article explains these conditions and functions in more depth.

SQL/JSON Functions

Oracle Database 12c Release 2 (12.2) includes new SQL/JSON functions to generate JSON data directly from SQL. You can read more about this in the following article.

PL/SQL Object Types for JSON

Oracle Database 12c Release 2 (12.2) includes new JSON object types to support the in-memory parsing, generation and update of JSON data directly from PL/SQL. You can read more about this in the following article.

Rewrites to JSON_TABLE

In Oracle 12.2 multiple calls to JSON_EXISTS, JSON_VALUE, and JSON_QUERY may be rewritten to fewer JSON_TABLE calls to improve performance.

Simplified Syntax for JSON Search Indexes

A new simplified JSON search index syntax has been introduced in Oracle Database 12.2. There are no differences in the way the resulting JSON search index performs.

JSON Data Guide

Simplify the interaction with JSON data stored in the database using the JSON Data Guide functionality introduced in Oracle Database 12c Release 2 (12.2).


The {USER|ALL|DBA}_JSON_COLUMNS views display the table columns containing JSON data, that is table columns with an IS JSON check constraint.

In 18c these views also list view columns that contain JSON data.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.