Oracle | DBMS

Oracle DBMS_JSON Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Provides an interface for dataguide operations for those working with Java Script Object Notation inside the Oracle database. Be sure to also learn about the new function JSON_DATAGUIDE that takes as its input a table column of JSON data and, for each JSON document in the column, returns a flat data guide as a CLOB.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Field Types
TYPE_ARRAY	NUMBER(2)	6
TYPE_BOOLEAN	NUMBER(2)	2
TYPE_GEOJSON	NUMBER(2)	7
TYPE_OBJECT	NUMBER(2)	5
TYPE_NULL	NUMBER(2)	1
TYPE_NUMBER	NUMBER(2)	3
TYPE_STRING	NUMBER(2)	4
Extended Types
TYPE_FLOAT	NUMBER(2)	11
TYPE_DOUBLE	NUMBER(2)	12
TYPE_DATE	NUMBER(2)	13
TYPE_TIMESTAMP	NUMBER(2)	14
TYPE_YMINTERVAL	NUMBER(2)	15
TYPE_DSINTERVAL	NUMBER(2)	16
TYPE_BINARY	NUMBER(2)	17
Materialized View Options
MV_REFRESH_ON_STATEMENT	NUMBER(2)	1
MV_REFRESH_ON_COMMIT	NUMBER(2)	2
MV_REFRESH_ON_DEMAND	NUMBER(2)	3

Dependencies

ALL_JSON_DATAGUIDES	DBMS_OUTPUT	JSON_OBJECT_T
ALL_JSON_DATAGUIDE_FIELDS	DBMS_SODA_ADMIN	PLITBLM
CDB_JSON_DATAGUIDES	DG$GETDGQUOTENAME	SODA_COLLECTION_T
DBA_JSON_DATAGUIDES	INT$DBA_JSON_DATAGUIDES	SYS_DGAGG
DBA_JSON_DATAGUIDE_FIELDS	INT$DBA_JSON_DG_COLS	USER_JSON_COLUMNS
DBMS_ASSERT	JSON_ARRAY_T	USER_JSON_DATAGUIDES
DBMS_JSON0	JSON_DATAGUIDE	USER_JSON_DATAGUIDE_FIELDS
DBMS_JSON_INT	JSON_ELEMENT_T	USER_TAB_COLS
DBMS_LOB	JSON_KEY_LIST

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-40582	Cannot find a data guide-enabled context index
ORA-61780	`Cannot find a multi-value index with given name "<table_name>" and schema name "<schema_name>"`

First Available 12.2

Security Model Owned by XDB with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsjson.sql

Subprograms

ADD_VIRTUAL_COLUMNS

Add virtual columns based on dataguide information. On a shard catalog server no virtual column is added.

Overload 1 dbms_json.add_virtual_columns( tablename IN VARCHAR2, jcolname IN VARCHAR2, dataguide IN CLOB, resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE);

exec dbms_json.add_virtual_columns('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.get_index_dataguide('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.format_hierarchical));

Overload 2 dbms_json.add_virtual_columns( tablename IN VARCHAR2, jcolname IN VARCHAR2, frequency IN NUMBER DEFAULT 0, hidden IN BOOLEAN DEFAULT FALSE);

exec dbms_json.add_virtual_columns('J_PURCHASEORDER', 'PO_DOCUMENT', 50);

BLOB_TO_JSON

Receives a table, and column and, if the column is of data type BLOB with an IS JSON FORMAT OSON constraint, converts the column to JSON type

Check the corresponding BLOB2JSON procedure in DBMS_JSON_INT and it is clear that this procedure's parameters are wrong and this will not work until Oracle patches it correcting the missing owner parameter. dbms_json.blob_to_json( tablename IN VARCHAR2, columnname IN VARCHAR2, throwError IN BOOLEAN DEFAULT TRUE);

CREATE TABLE B2JTEST (

      pkcol   INTEGER,

      testcol BLOB

      CONSTRAINT ensure CHECK (testcol IS JSON));

      

      desc b2jtest

      Name        Null?    Type

      ----------- -------- -----------

      PKCOL                NUMBER(38)

      TESTCOL              BLOB;

      

      exec xdb.dbms_json.blob_to_json('B2JTEST', 'ENSURE', FALSE);

      

      PL/SQL procedure successfully completed.

      

      exec xdb.dbms_json.blob_to_json('B2JTEST', 'TESTCOL', TRUE);

           *

      ORA-00942: table or view does not exist

CREATE_VIEW (new 23ai overload)

Create a view with relational columns and scalar JSON fields as specified in a dataguide

Overload 1

It is highly recommended that the second overload be easily create an error if two tables have the same name but are in different schemas. dbms_json.create_view( viewname IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, dataguide IN CLOB resourcePath IN VARCHAR2 DEFAULT NULL, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT, path IN VARCHAR2 DEFAULT '$', resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE, detectBoolean IN BOOLEAN);

exec dbms_json.create_view('VIEW1', 'J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.get_index_dataguide('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.format_hierarchical));

Overload 2 dbms_json.create_view( viewname IN VARCHAR2, tableowner IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, dataguide IN CLOB resourcePath IN VARCHAR2 DEFAULT NULL, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT, path IN VARCHAR2 DEFAULT '$', resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE, detectBoolean IN BOOLEAN);

exec dbms_json.create_view('VIEW1', 
      'C##UWCLASS', 'J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.get_index_dataguide('J_PURCHASEORDER', 
      'PO_DOCUMENT', dbms_json.format_hierarchical));

CREATE_VIEW_ON_PATH

Create a view based on dataguide information, with relational columns, top level scalar types, and fully expanded subtree under a given path. When running on the shard catalog server this raises an error stating that the dataguide is empty. dbms_json.create_view_on_path( viewname IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, path IN VARCHAR2, frequency IN NUMBER DEFAULT 0 resourcePath IN VARCHAR2 DEFAULT NULL, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT);

exec dbms_json.create_view_on_path('VIEW2', 'J_PURCHASEORDER', 'PO_DOCUMENT', '$');

      

      call dbms_json.createViewOnPath('MOVIE_TICKETS_VIEW', 'MOVIE_TICKETS', 'BOOKING_DETAILS', '$');

DETECT_DATETIME (new 23ai)

Data Guide Date Time formatting dbms_json.detect_datetime RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.detect_datetime;

DETECT_DATETIME --------------- 8

DROP_VIRTUAL_COLUMNS

Drop virtual columns created by procedure add_virtual_columns. This has no effect when running on the shard catalog server. dbms_json.drop_virtual_columns( tablename IN VARCHAR2, jcolname IN VARCHAR2);

exec dbms_json.drop_virtual_columns(''J_PURCHASEORDER', 'PO_DOCUMENT');

FORMAT_FLAT

Data Guide Date Time formatting dbns_json.format_flat RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.format_flat;

      

      FORMAT_FLAT

      -----------

                2

FORMAT_HIERARCHICAL

Data Guide Hierarchical formatting dbns_json.format_hierarchical RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.format_hierarchical;

      

      FORMAT_HIERARCHICAL

      -------------------

                        1

FORMAT_SCHEMA (new 23ai)

Data Guide Schema formatting dbms_json.format_schema RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.format_schema;

      

      FORMAT_SCHEMA

      -------------

                  3

GATHER_STATS

Undocumented but used for Data Guide formatting dbms_json.gather_stats RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.gather_stats;

      

      GATHER_STATS

      ------------

                 4

GEOJSON

JSON Data Guide formatting dbms_json.geoJSON RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.geoJSON;

GET_INDEX_DATAGUIDE

Get JSON dataguide from a dataguide enabled JSON search index. When running on the shard catalog server returns NULL.

Overload 1 dbms_json.get_index_dataguide( owner IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, format IN NUMBER, pretty IN NUMBER DEFAULT 0) RETURN IN CLOB;

SELECT dbms_json.get_index_dataguide('UWJSON', 'PO', dbms_json.format_hierarchical, dbms_json.pretty);

Overload 2 dbms_json.get_index_dataguide( tablename IN VARCHAR2, jcolName IN VARCHAR2, format IN NUMBER, pretty IN NUMBER DEFAULT 0) RETURN CLOB;

SELECT dbms_json.get_index_dataguide('UWJSON', 'PO', dbms_json.format_hierarchical, dbms_json.pretty);

GET_MVI_SPEC (new 23ai)

Generates the multi-value index specs on a given index in JSON format

Overload 1 dbms_json.get_mvi_spec( owner IN VARCHAR2, indexName IN VARCHAR2) RETURN CLOB;

SELECT dbms_json.get_mvi_spec(USER, 'I_TAB1');

      ERROR:
      ORA-61780: cannot find a multi-value index with given name "I_TAB1" and schema name "SYS"

Overload 2 dbms_json.get_mvi_spec(indexName IN VARCHAR2) RETURN CLOB;

SELECT dbms_json.get_mvi_spec('I_TAB1');

      ERROR:
      ORA-61780: cannot find a multi-value index with given name "I_TAB1" and schema name "SYS"

GET_MVI_SPECS (new 23ai)

Generates all multi-value index specs on a given table in JSON format

Overload 1 dbms_json.get_mvi_specs( owner IN VARCHAR2, tablename IN VARCHAR2) RETURN CLOB;

SELECT dbms_json.get_mvi_specs(USER, 'TAB$);

      ERROR:
      ORA-61780: there is no multi-value index on table "TAB$" with schema name "SYS".

Overload 2 dbms_json.get_mvi_specs(tablename IN VARCHAR2) RETURN CLOB;

SELECT dbms_json.get_mvi_specs(USER, 'TAB$);

      ERROR:
      ORA-61780: there is no multi-value index on table "TAB$" with schema name "SYS".

GET_VIEW_SQL

Similar to create_view, but returns the creating view DDL but does not execute the DDL and create the view dbms_json.get_view_sql( viewName IN VARCHAR2, tableName IN VARCHAR2, jcolName IN VARCHAR2, dataguide IN CLOB, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT, path IN VARCHAR2 DEFAULT '$', resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE) RETURN CLOB;

exec dbms_json.create_view_on_path('VIEW3, 'J_PURCHASEORDER', 'PO_DOCUMENT', '$');

JSON_TYPE_CONVERTIBLE_CHECK (new 23ai)

Pre-upgrade check for the JSON type dbms_json.json_type_convertible_check( owner IN VARCHAR2, tablename IN VARCHAR2, columnname IN VARCHAR2, statustablename IN VARCHAR2, fastcheck IN BOOLEAN DEFAULT FALSE, appendstatus IN BOOLEAN DEFAULT FALSE);

TBD

PREPALLJCOLINM

For tables containing json columns created prior to 12.2 upgrades all JSON columns to to take advantage of in-memory JSON processing in 12.2 and above dbms_json.prepAllJColInM;

exec dbms_json.prepAllJColInM;

PREPJCOLINM

For JSON columns created prior to 12.2 upgrades the column to prepare to take advantage of in-memory JSON processing dbms_json.prepJColInM( tabName IN VARCHAR2, jcolName IN VARCHAR2);

TBD

PREPTABJCOLINM

For tables containing JSON columns created prior to 12.2 upgrades all JSON columns to to take advantage of in-memory JSON processing dbms_json.prepTabJColInM(tabName IN VARCHAR2);

exec dbms_json.prepTabJColInM('PO');

PRETTY

JSON Data guide formatting dbns_json.pretty RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.pretty;

      

      FORMAT_FLAT

      -----------

                1

RENAME_COLUMN

Set the preferred name for a view column or a virtual column creating using a dataguide. This has no effect when running on the shard catalog server. dbms_json.rename_column( tablename IN VARCHAR2, jcolname IN VARCHAR2, path IN VARCHAR2, type IN NUMBER, preferred_name IN VARCHAR2);

exec dbms_json.rename_column('UWJSON', 'PO', '$.purchaseOrder.items.name', dbms_json.type_string, 'item_name');