In the JSON series so far, we’ve talked about how to store JSON data in Oracle and apply JSON conditional checks, and how to query JSON data and convert it to relational form. What if you want the opposite i.e. to convert relational data to JSON form? That’s doable too – let’s see how.

Convert Relational Data to JSON

What you have: a relational table in Oracle.

What you want: data extracted from said relational table in JSON format.

How do you do it?

Continue Reading …

{ 0 comments }

In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.

JSON Conditional Logic JSON_EXISTS JSON_TEXTCONTAINS

JSON conditionals check for the existence of specified paths/values within JSON documents. They are typically applied as row filters in the SQL WHERE clause.

Continue Reading …

{ 0 comments }

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 →
When should we use dynamic SQL

When Should We Use Dynamic SQL?

We know that, if there exists a choice between static SQL vs dynamic SQL, static SQL is the way to go. In that case, why and when should we use dynamic SQL?

Read the Full Article →
Static SQL vs Dynamic SQL

Static SQL vs Dynamic SQL: Which to use?

Static SQL vs dynamic SQL: a comparison on metrics such as security, maintainability and performance, and a rule of thumb for choosing between the two.

Read the Full Article →