Oracle DBMS_JSON
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose The new 12.2 DBMS_JSON built-in package 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 12.2 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
Dataguide Formatting
FORMAT_FLAT NUMBER 2
FORMAT_HIERARCHICAL NUMBER 1
PRETTY NUMBER 1
Schema Types
TYPE_ARRAY NUMBER(2) 6
TYPE_BOOLEAN NUMBER(2) 2
TYPE_OBJECT NUMBER(2) 5
TYPE_NULL NUMBER(2) 1
TYPE_NUMBER NUMBER(2) 3
TYPE_STRING NUMBER(2) 4
Dependencies
ALL_JSON_DATAGUIDES DBMS_XDB JSON_OBJECT_T
DBA_JSON_DATAGUIDES DG$GETDGQUOTENAME PLITBLM
DBMS_ASSERT INT$DBA_JSON_DATAGUIDES USER_JSON_COLUMNS
DBMS_JSON0 JSON_ARRAY_T USER_JSON_DATAGUIDES
DBMS_JSON_INT JSON_ELEMENT_T USER_TAB_COLS
DBMS_LOB JSON_KEY_LIST  
Documented Yes
Exceptions
Error Code Reason
   
First Available 12.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/
Subprograms
 
ADD_VIRTUAL_COLUMNS (new 12.2)
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);
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);
 
CREATE_VIEW (new 12.2)
Create a view with relational columns and scalar JSON fields as specified in a dataguide dbms_json.create_view(
viewname  IN VARCHAR2,
tablename IN VARCHAR2,
jcolname  IN VARCHAR2,
dataguide IN CLOB);
exec dbms_json.create_view('VIEW1', 'J_PURCHASEORDER', 'PO_DOCUMENT',
dbms_json.get_index_dataguide('J_PURCHASEORDER', 'PO_DOCUMENT',
dbms_json.format_hierarchical));
 
CREATE_VIEW_ON_PATH (new 12.2)
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);
exec dbms_json.create_view_on_path('VIEW2', 'J_PURCHASEORDER', 'PO_DOCUMENT', '$');
 
DROP_VIRTUAL_COLUMNS (new 12.2)
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');
 
GET_INDEX_DATAGUIDE (new 12.2)
Get JSON dataguide from a dataguide enabled JSON search index. When running on the shard catalog server returns NULL. dbms_json.get_index_dataguide(
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)
FROM DUAL;
 
RENAME_COLUMN (new 12.2)
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’);

Related Topics
DBMS_JSON0
JSON Functionality
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved