2021 – RDBMS Data API

This post introduces the PLSQL API for the RDBMS version of the simple patient tracking system. First up, here is a reminder of the data. INSERT INTO patient ( patient_id, patient_name ) VALUES(1,’Patient 1′); INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(1,1,’MRN123′); INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(2,1,’MRN456′); INSERT INTO patient … Read more

2021 – Compare JSON and RDBMS Data 2

The last post showed the pitfalls of treating JSON as simple text when comparing it to relational data. This post demonstrates a better way to perform the comparison. Use built in SQL and PLQL functions to translate the relational data to JSON Perform a “JSON aware” comparison to the JSON data Relational To JSON Step … Read more

2021 – First Data Structures

Project 2021 requires 2 different types of database structures. Relational (RDBMS) Tables with rows of columns and integrity checks JSON All data stored in a single column with embedded KEY:VALUE pairs RDBMS Structure And Data Here is the SQL to create and populate the RDBMS structure. CREATE TABLE patient ( patient_id NUMBER NOT NULL PRIMARY … Read more

2021 – JSON/Relational and SQL/PLSQL

Welcome to 2021 – the year of adaptability! That’s the concept for this years project. Specifically the adaptability of two database design models – relational and JSON – and two programming languages – SQL and PLSQL. This project will work through the life cycle of a database system as it adapts to new requirements. There … Read more

KSCOPE 2019 JSON Data Guide

These are the scripts I used for my JSON Data Guide presentation at the ODTUG KSCOPE 2019 conference. Setup and Introduction User Data Guide Views Search Index with Dataguide Preferred Names Rename Columns Search Index Never Forgets DBMS_JSON.CREATE_VIEW DBMS_JSON.CREATE_VIEW_ON_PATH String + Number = String Be sure to run the Setup and Introduction scripts first. Thanks … Read more


The Oracle PLSQL JSON_OBJECT function assembles JSON KEY:VALUE pairs. It uses column values from a table or view as the VALUE. It can use hardcoded strings or column values as the KEY.  Here is an example creating 3 records in a table and querying them using the JSON_OBJECT function. — record 1 NOT JSON INSERT … Read more

PEIM’s Are Strict

JSON Path Expression Item Method’s (PEIM) were added in Oracle 18c. They provide extensions to interpret JSON data. But the interpretations are stricter than they appear. Take the string() PEIM for instance – the Oracle documentation says it returns an “SQL VARCHAR2(4000) interpretation of the targeted scalar JSON value”. That seems straightforward but… — Create … Read more

JSON Data Guide Nuance 3

Dataguide provides three attributes for JSON data – the path, the datatype and the length. Ever since Oracle 12.2 it has done that dutifully – even for arrays of scalar (non key-value pair) values. But initially it only denoted the fact there was an array, it did not denote the datatype of the entries in … Read more