One way to get a smaller CLOB from another CLOB is to follow the extract CLOB from JSON example: use DBMS_LOB INSTR/SUBSTR functions to obtain the CLOB fragment of interest. We could look at the same problem from another angle: delete the CLOB fragments *not* of interest, retaining only what we want, with DBMS_LOB.FRAGMENT_DELETE.

Continue Reading …


Extract very long string or CLOB from JSON

"Wheels within wheels", as Monty Bodkin would say. Extracting a very long string or CLOB from a JSON CLOB (very long => larger than max_string_size of 32767), in a pre-12.2 Oracle database, turned out to be more complex than it appeared at first sight.

This case study shows how to extract CLOB data from JSON CLOB, in an Oracle database with no/limited JSON parsing features.

Continue Reading …


Convert Relational Data to JSON

Convert Relational Data to JSON in Oracle: PL/JSON Method

How to convert relational data to JSON in the Oracle database, using PL/JSON.

Read the Full Article →


Implement true/false tests on JSON documents in Oracle database 12c using JSON conditional logic: JSON_EXISTS and JSON_TEXTCONTAINS.

Read the Full Article →
Querying JSON data in Oracle

Querying JSON Data in Oracle: SQL/JSON Query Functions, Dot Notation

Querying JSON data in Oracle 12c using various query approaches: simple SQL, SQL/JSON functions, dot-notation syntax.

Read the Full Article →
JSON in the database

Storing JSON Data in Oracle: Why and How

Storing JSON data in Oracle has been made easy in version 12c. A demo of how JSON can be stored in Oracle and validated using the IS JSON check.

Read the Full Article →
DBMS_REDEFINITION for online table redefinition

DBMS_REDEFINITION: Convert Non-Partitioned Table to Partitioned [pre 12.2]

Partitioning a non-partitioned table in an Oracle pre-12.2 database, using DBMS_REDEFINITION built-in methods. Scripts and demo.

Read the Full Article →
In Which Package Is This Procedure Placed

In Which Package Is This Procedure Placed?

Given a stored procedure name and the knowledge that it sits in *some* package in the database, how does one find out *which* package?

Read the Full Article →
Proxy authentication in Oracle

Proxy User Authentication in Oracle

Proxy user authentication in Oracle provides an effective way to manage both security and ease of setup, especially useful in a multi-user work environment.

Read the Full Article →
XMLTABLE to convert XML to relational data

XMLTABLE: Convert XML Data to Relational Form

Using the SQL/XML function XMLTABLE in Oracle database 12c, to map XML data into relational rows and columns.

Read the Full Article →