PTF’s Know Their Columns

Continuing with Polymorphic Tabe Functions (PTF’s) this post delves further into the DESCRIBE function – specifically the table, package and function involved and the columns of the passed in object. I’ll let the code to the talking… CREATE OR REPLACE PACKAGE BODY poly_pkg AS FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) RETURN DBMS_TF.DESCRIBE_T AS BEGIN … Read more

Polymorphic Table Functions – Introduction

Polymorphic Table Functions (PTF’s) work with input that is not predefined and provide output that is not predefined. Thus they are very flexible. I encourage you to Google the phrase “Oracle Polymorphic Table Functions” to find information on this feature introduced in Oracle 18c. in this series of posts I’ll work through some examples of … Read more

SODA – More Than Just A Table

This post begins investigating what goes on behind the scenes to implement a SODA collection in Oracle. Lets dive right in and create a SODA collection named CLCT and put a JSON document in it using the DBMS_SODA PLSQL package. DECLARE v soda_collection_t; d soda_document_t; t NUMBER; BEGIN v := DBMS_SODA.CREATE_COLLECTION (‘CLCT’); d := SODA_DOCUMENT_T( … Read more

No Implicit Commits

Committing a transaction means the transaction cannot be rolled back. There are two types of commits: Explicit – when the COMMIT command is executed Implicit – when DDL (Data Definition Language) is executed DDL includes SQL such as CREATE TABLE. When DDL is executed all un-committed work in the current session is implicitly committed. The … Read more

Go For Soda

Simple Oracle Document Access (SODA) comes to PL/SQL! Being Canadian and being in a certain age group I have been patiently waiting for SODA in PLSQL. Two reasons: Its fun to learn new things I get to use the iconic Go For Soda song from Canadian musician Kim Mitchell in a post! And a 1…and … 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

JSON Data Guide Nuance 2

The previous post used numeric and string value to demonstrate how Oracle JSON Data Guide may change assigned datatypes to accommodate mixed data. This post demonstrates the same concept using JSON boolean values. SQL> INSERT INTO json_data 2 VALUES(1,'{“RAPTOR_LOST”:true}’); 1 row created. SQL> INSERT INTO json_data 2 VALUES(2,'{“RAPTOR_LOST”:”YES”}’); 1 row created. SQL> SELECT JSON_DATAGUIDE(json_col) boolean … Read more