DEV Community: Loïc

🔥Comparing Binary JSON formats 🎯

Loïc — Mon, 25 Mar 2024 09:57:23 +0000

TL;DR - Binary JSON formats are critical for databases. But are all these binary formats the same? What if the answer is: definitely NOT? What if they influence programming practices? Want to know more, you're at the right place 🍿👇

In this Comparing "Binary JSON formats" video, I deep dive into these 4 Binary JSON formats:

BSON used in MongoDB
JSON (or "BJSON" as I call it - which stands as "Binary JSON" 😅) used in MySQL
JSONB used in PostgreSQL
OSON used in Oracle

After re-positioning the JSON standard and what it means, I present these formats one by one using a tool I've developed to quickly identify the evolving structure of JSON documents modified in live.

One clear difference will be shown for the JSON field (scalar) values representation, you'll be surprised!

Hopefully, I came up with comparative tables that one can use as quick notes. I personally defined application development best practices when dealing with each of these binary formats.

Then I continue looking at document compression and I finish with partial document updates and what are the impacts on the underlying database.

Enjoy!

🤩 Free Oracle Database: Download, Install, Tutorial… My Getting Started Guide 🚀🦭🐳

Loïc — Mon, 25 Mar 2024 08:45:44 +0000

TL;DR — In this post, I share the tricks I’ve learned for getting started fast with the Oracle Database for free.

Download, Install: Free and Fast

Here are the 2 paths I use when speed is needed:

Free Oracle Database 23c Free container image (more in this post)
Free Oracle Autonomous Database 19c/21c (_requires an Oracle Cloud Infrastructure Always Free account and an Internet connection)

Free Oracle Database 23c Free container image

Given you have a running Podman or Docker setup for x86_64 architecture (I work on both Linux and Windows), you can start an Oracle database 23c in less than 2 minutes (depending upon your Internet bandwidth) using this command:

podman run --rm --name oracle -p 1521:1521 \
       -e ORACLE_PASSWORD=free \
       gvenzl/oracle-free:23-slim

Gerald Venzl maintains images with different characteristics (full, regular, slim; normal or faststart; for 21c and 23c versions).

When the container is up and running, you get a Pluggable Database (aka PDB) named freepdb1 which is ready to accept your commands.

Oracle SQL Developer Extension for VSCode or SQLcl

The very next step is to get this SQL Developer extension:

This is the next generation of SQL Developer. Over time all features will be there with additional ones.

If you are more in the CLI team, you can get the latest version of SQLcl in one click. This requires a JDK 11 or 17. In case you didn’t know, SQLcl is the next generation of sqlplus, developed by the same team as SQL Developer; bringing console history, and powerful commands (Liquibase integration for changes management, DDL generation, formats conversion, color display, auto-completion, repeat command, status bar, vi mode, etc.). There is also a possibility to integrate JavaScript based plugins.

Jeff Smith has a lot to share about SQLcl and the SQL Developer extension.

Create a User and connect to your Database

With the SQL Developer extension or SQLcl, you can now access your running Pluggable Database to create the user account you’ll use to develop your application.

This step requires some kind of root access (as for Linux). The root user of an Oracle database is named SYS and can use the highest privileges SYSDBA. Hence let’s register a new Connection in SQL Developer extension (remember password is free):

Now you can connect to the database and open an SQL Worksheet:

Copy and paste the SQL commands below:

create user developer identified by "free";

-- no quota on USERS tablespace
alter user developer quota unlimited on users;

-- quick setup for developers
grant DB_DEVELOPER_ROLE to developer;

-- To drop: drop user developer cascade;

Running the script with F5 will create a user named developer with free as its password. It grants unlimited quota on the USERS tablespace (so you can insert data) and the new 23c DB_DEVELOPER_ROLE role is granted; it provides just enough privileges to let you develop and use numerous capabilities.

DONE

You now just need to register a new connection for this newly created developer user and you’re done, you can now connect to your Pluggable Database and create tables, views, sequences, JSON collections, etc.

Following are some links to continue your journey:

Happy dev!

Use Chance.js and Fony.js in your SQL queries😎

Loïc — Wed, 03 May 2023 07:01:43 +0000

TL;DR

This post explains how to use the Chance.js library and how to provide JSON template feature inspired from the Fony.js library so that you can generate random JSON data using SQL:

select json_serialize( 
           chance.template( json {'raceId': 'natural', 
                                  'name': 'name', 
                                  'laps': 'natural', 
                                  'date': 'date', 
                                  'podium': {} } 
                          ) pretty ) as new_races;

NEW_RACES
__________________________________________
{
  "date" : "2056-11-14T13:26:53.751000Z",
  "laps" : 35,
  "name" : "Scott Wilkins",
  "podium" :
  {
  },
  "raceId" : 64315
}

The Oracle Database has the MultiLingual Engine (MLE) for JavaScript built directly into the kernel since the 21c release. MLE allows running JavaScript code inside the Oracle database. It is powered by the GraalVM.

JavaScript support was first introduced in Oracle 21c specifically targeting Application Express developers, allowing them to use an additional programming languge. In Oracle 23c the feature set has been greatly expanded to include the creation of JavaScript ES Modules (ESM for ECMAScript Modules).

Setup

Remark: The following scripts are run on an Oracle database 23c FREE - Developer Release using sqlplus.

First things first, let's start a 23c FREE Developer Release Docker image using Podman:

# create a folder where we'll download our ES Modules
mkdir mle

# start the container
podman run -d --name oracle -e ORACLE_PASSWORD=free -p 1521:1521 \
  --mount=type=bind,src=./mle,dst=/home/oracle/mle,relabel=shared gvenzl/oracle-free:23.2.0-full
# wait a couple of seconds-minutes depending on whether it's the first time being started

The command will configure the SYS and SYSTEM passwords as free (in lower case), it will allow connecting to the FREEPDB1 pluggable database using local port 1521 and it will mount our local folder mle into the container as /home/oracle/mle.

Next, let's create a new user named developer and grant the right privileges for getting started:

-- Running these commands as SYS or SYSTEM
-- sqlplus sys/free@localhost:1521/freepdb1 as sysdba

-- create the user and grant privileges
create user developer identified by "free";
alter user developer quota unlimited on users; -- no quota issue on USERS tablespace
grant DB_DEVELOPER_ROLE to developer; -- new role to ease quick setup for developers
grant execute on javascript to developer; -- allows running JavaScript

-- create the directory to access filesystem where we'll download our ES Modules
create directory mle_dir as '/home/oracle/mle';
grant read on directory mle_dir to developer;

Installing the Chance.js ES Module

For the rest of this post, we'll focus on the Chance.js module from Victor Quinn under MIT License. This module is really interesting when working on data as it provides numerous ways to "generate random numbers, characters, strings, names, addresses, dice, and pretty much anything else"; in brief 100+ new data generators.

The latest version available at the time of writing is 1.1.11, so let's download it into our mle folder so that it can be used by the database:

curl -Lo ./mle/chance.js https://cdn.jsdelivr.net/npm/chance@1.1.11/+esm

Now it's time to connect as our developer user and do the real things, connect with sqlplus (from inside the container or using other tools such as SQLcl or SQL Developer from outside the container):

# Run sqlplus from inside the container:
podman exec -it oracle /bin/bash

sqlplus developer/free@localhost:1521/freepdb1

And now connected as developer, run:

-- prevents variable substitution because of &
SET VERIFY OFF
-- prevents && interpretation by sqlplus
SET DEFINE OFF

-- creates the MLE module named chance_module
create or replace mle module chance_module language javascript version '1.1.11' using bfile( mle_dir, 'chance.js' );
/

-- creates the corresponding MLE environment that will help for managing depencies at runtime
create or replace mle env chance_module_env imports ('chance' module chance_module);

-- creates a new 'extended' module that will export properly the functions for future usage in PL/SQL
create or replace mle module chance_extended language javascript version '1.1.11' as
import Chance from 'chance';
const chance = new Chance();
const chanceTypes = Object.keys(Object.getPrototypeOf(chance));

// inspired from Fony
function valid(type) {
  return chanceTypes.indexOf(type) !== -1;
}

function getArrayValue(definition) {
  if (definition.length !== 2) {
    return null;
}
  const type = definition[0];
  const count = definition[1];
  if (!valid(type) || typeof count !== "number" || count === 0) {
    return null;
}
  return new Array(count).fill(null).map(function() {
    return getValue(type);
  });
}

function getValue(type) {
  if (Array.isArray(type)) {
    return getArrayValue(type);
  }
  if (typeof type === "object" && !Array.isArray(type) && type != null) {
    var result = template(type);
    return result._$_chance ? result._$_chance : result;
  }
  try {
    return chance[type]();
  } catch (exception) {
    return null;
  }
}

function template(json_template) {
  const output = {};
  Object.keys(json_template).map(function(key, index) {
    if (typeof key === "string" && key.charAt(0) === '$' && valid(key.substring(1))) {
      output['_$_chance'] = chance[key.substring(1)](json_template[key]);
    } else {
      output[key] = getValue(json_template[key]);
    }
  });
  return output;
}

chance.yearStr = chance.year;
chance.year = function (doc) { return parseInt(chance.yearStr(doc)); }

export {chance, template};

/

So far, we've installed the Chance.js module and an extended one that exports properly each of the function with a signature compatible with the Oracle database data types (including the native JSON and new 23c BOOLEAN).

We've also created an MLE environment that will help us for the next setup phase: integrating the SQL engine with the JavaScript MLE using PL/SQL functions, and more precisely, how JavaScript imports will be managed:

-- Installing a PL/SQL package to make Chance functions accessible in SQL
-- See how the chance_module_env MLE environment is being used so that dependencies resolution goes well
create or replace package chance as
 function address( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.address';
 function address return varchar2 as mle module chance_extended env chance_module_env signature 'chance.address';
 function age return number as mle module chance_extended env chance_module_env signature 'chance.age';
 function age( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.age';
 function altitude return number as mle module chance_extended env chance_module_env signature 'chance.altitude';
 function altitude( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.altitude';
 function ampm( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ampm';
 function ampm return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ampm';
 function android_id( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.android_id';
 function android_id return varchar2 as mle module chance_extended env chance_module_env signature 'chance.android_id';
 function animal( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.animal';
 function animal return varchar2 as mle module chance_extended env chance_module_env signature 'chance.animal';
 function apple_token( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.apple_token';
 function apple_token return varchar2 as mle module chance_extended env chance_module_env signature 'chance.apple_token';
 function areacode return varchar2 as mle module chance_extended env chance_module_env signature 'chance.areacode';
 function areacode( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.areacode';
 function avatar( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.avatar';
 function avatar return varchar2 as mle module chance_extended env chance_module_env signature 'chance.avatar';
 function bb_pin return varchar2 as mle module chance_extended env chance_module_env signature 'chance.bb_pin';
 function bb_pin( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.bb_pin';
 function birthday( p_doc in json ) return date as mle module chance_extended env chance_module_env signature 'chance.birthday';
 function birthday return date as mle module chance_extended env chance_module_env signature 'chance.birthday';
 function bool( p_doc in json ) return boolean as mle module chance_extended env chance_module_env signature 'chance.bool';
 function bool return boolean as mle module chance_extended env chance_module_env signature 'chance.bool';
 function cc return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cc';
 function cc( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cc';
 function cc_type return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cc_type';
 function cc_type( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cc_type';
 function cf( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cf';
 function cf return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cf';
 function character return varchar2 as mle module chance_extended env chance_module_env signature 'chance.character';
 function character( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.character';
 function city( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.city';
 function city return varchar2 as mle module chance_extended env chance_module_env signature 'chance.city';
 function coin( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.coin';
 function coin return varchar2 as mle module chance_extended env chance_module_env signature 'chance.coin';
 function color( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.color';
 function color return varchar2 as mle module chance_extended env chance_module_env signature 'chance.color';
 function company return varchar2 as mle module chance_extended env chance_module_env signature 'chance.company';
 function company( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.company';
 function country return varchar2 as mle module chance_extended env chance_module_env signature 'chance.country';
 function country( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.country';
 function county return varchar2 as mle module chance_extended env chance_module_env signature 'chance.county';
 function county( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.county';
 function cpf return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cpf';
 function cpf( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.cpf';
 function currency return json as mle module chance_extended env chance_module_env signature 'chance.currency';
 function currency( p_doc in json ) return json as mle module chance_extended env chance_module_env signature 'chance.currency';
 function currency_pair( p_doc in json ) return json as mle module chance_extended env chance_module_env signature 'chance.currency_pair';
 function currency_pair return json as mle module chance_extended env chance_module_env signature 'chance.currency_pair';
 function d10( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d10';
 function d10 return number as mle module chance_extended env chance_module_env signature 'chance.d10';
 function d100 return number as mle module chance_extended env chance_module_env signature 'chance.d100';
 function d100( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d100';
 function d12 return number as mle module chance_extended env chance_module_env signature 'chance.d12';
 function d12( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d12';
 function d20( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d20';
 function d20 return number as mle module chance_extended env chance_module_env signature 'chance.d20';
 function d30( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d30';
 function d30 return number as mle module chance_extended env chance_module_env signature 'chance.d30';
 function d4( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d4';
 function d4 return number as mle module chance_extended env chance_module_env signature 'chance.d4';
 function d6 return number as mle module chance_extended env chance_module_env signature 'chance.d6';
 function d6( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d6';
 function d8 return number as mle module chance_extended env chance_module_env signature 'chance.d8';
 function d8( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.d8';
 function "date" return date as mle module chance_extended env chance_module_env signature 'chance.date';
 function "date"( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.date';
 function "date"( p_doc in json, p_will_be_date in boolean ) return date as mle module chance_extended env chance_module_env signature 'chance.date';
 function depth return number as mle module chance_extended env chance_module_env signature 'chance.depth';
 function depth( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.depth';
 function dollar return varchar2 as mle module chance_extended env chance_module_env signature 'chance.dollar';
 function dollar( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.dollar';
 function domain( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.domain';
 function domain return varchar2 as mle module chance_extended env chance_module_env signature 'chance.domain';
 function email return varchar2 as mle module chance_extended env chance_module_env signature 'chance.email';
 function email( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.email';
 function euro return varchar2 as mle module chance_extended env chance_module_env signature 'chance.euro';
 function euro( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.euro';
 function exp return varchar2 as mle module chance_extended env chance_module_env signature 'chance.exp';
 function exp( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.exp';
 function exp_month( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.exp_month';
 function exp_month return varchar2 as mle module chance_extended env chance_module_env signature 'chance.exp_month';
 function exp_year( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.exp_year';
 function exp_year return varchar2 as mle module chance_extended env chance_module_env signature 'chance.exp_year';
 function falsy return varchar2 as mle module chance_extended env chance_module_env signature 'chance.falsy';
 function falsy( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.falsy';
 function fbid return varchar2 as mle module chance_extended env chance_module_env signature 'chance.fbid';
 function fbid( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.fbid';
 function first( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.first';
 function first return varchar2 as mle module chance_extended env chance_module_env signature 'chance.first';
 function floating( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.floating';
 function floating return number as mle module chance_extended env chance_module_env signature 'chance.floating';
 function gender return varchar2 as mle module chance_extended env chance_module_env signature 'chance.gender';
 function gender( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.gender';
 function geohash return varchar2 as mle module chance_extended env chance_module_env signature 'chance.geohash';
 function geohash( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.geohash';
 function geojson return json as mle module chance_extended env chance_module_env signature 'chance.geojson';
 function geojson( p_doc in json ) return json as mle module chance_extended env chance_module_env signature 'chance.geojson';
 function google_analytics( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.google_analytics';
 function google_analytics return varchar2 as mle module chance_extended env chance_module_env signature 'chance.google_analytics';
 function guid( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.guid';
 function guid return varchar2 as mle module chance_extended env chance_module_env signature 'chance.guid';
 function hammertime return number as mle module chance_extended env chance_module_env signature 'chance.hammertime';
 function hammertime( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.hammertime';
 function hash return varchar2 as mle module chance_extended env chance_module_env signature 'chance.hash';
 function hash( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.hash';
 function hashtag( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.hashtag';
 function hashtag return varchar2 as mle module chance_extended env chance_module_env signature 'chance.hashtag';
 function hour( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.hour';
 function hour return number as mle module chance_extended env chance_module_env signature 'chance.hour';
 function iban return varchar2 as mle module chance_extended env chance_module_env signature 'chance.iban';
 function iban( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.iban';
 function "integer" return number as mle module chance_extended env chance_module_env signature 'chance.integer';
 function "integer"( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.integer';
 function ip( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ip';
 function ip return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ip';
 function ipv6( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ipv6';
 function ipv6 return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ipv6';
 function klout( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.klout';
 function klout return number as mle module chance_extended env chance_module_env signature 'chance.klout';
 function last return varchar2 as mle module chance_extended env chance_module_env signature 'chance.last';
 function last( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.last';
 function latitude return number as mle module chance_extended env chance_module_env signature 'chance.latitude';
 function latitude( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.latitude';
 function letter( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.letter';
 function letter return varchar2 as mle module chance_extended env chance_module_env signature 'chance.letter';
 function locale return varchar2 as mle module chance_extended env chance_module_env signature 'chance.locale';
 function locale( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.locale';
 function longitude( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.longitude';
 function longitude return number as mle module chance_extended env chance_module_env signature 'chance.longitude';
 function millisecond return number as mle module chance_extended env chance_module_env signature 'chance.millisecond';
 function millisecond( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.millisecond';
 function minute( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.minute';
 function minute return number as mle module chance_extended env chance_module_env signature 'chance.minute';
 function month( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.month';
 function month return varchar2 as mle module chance_extended env chance_module_env signature 'chance.month';
 function name return varchar2 as mle module chance_extended env chance_module_env signature 'chance.name';
 function name( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.name';
 function natural( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.natural';
 function natural return number as mle module chance_extended env chance_module_env signature 'chance.natural';
 function normal( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.normal';
 function normal return number as mle module chance_extended env chance_module_env signature 'chance.normal';
 function paragraph( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.paragraph';
 function paragraph return varchar2 as mle module chance_extended env chance_module_env signature 'chance.paragraph';
 function phone return varchar2 as mle module chance_extended env chance_module_env signature 'chance.phone';
 function phone( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.phone';
 function postal return varchar2 as mle module chance_extended env chance_module_env signature 'chance.postal';
 function postal( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.postal';
 function postcode return varchar2 as mle module chance_extended env chance_module_env signature 'chance.postcode';
 function postcode( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.postcode';
 function prefix( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.prefix';
 function prefix return varchar2 as mle module chance_extended env chance_module_env signature 'chance.prefix';
 function prime( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.prime';
 function prime return number as mle module chance_extended env chance_module_env signature 'chance.prime';
 function profession return varchar2 as mle module chance_extended env chance_module_env signature 'chance.profession';
 function profession( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.profession';
 function province( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.province';
 function province return varchar2 as mle module chance_extended env chance_module_env signature 'chance.province';
 function radio( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.radio';
 function radio return varchar2 as mle module chance_extended env chance_module_env signature 'chance.radio';
 function rpg( p_doc in varchar2 ) return json as mle module chance_extended env chance_module_env signature 'chance.rpg';
 function second( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.second';
 function second return number as mle module chance_extended env chance_module_env signature 'chance.second';
 function sentence return varchar2 as mle module chance_extended env chance_module_env signature 'chance.sentence';
 function sentence( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.sentence';
 function ssn return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ssn';
 function ssn( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.ssn';
 function state return varchar2 as mle module chance_extended env chance_module_env signature 'chance.state';
 function state( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.state';
 function street( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.street';
 function street return varchar2 as mle module chance_extended env chance_module_env signature 'chance.street';
 function string( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.string';
 function string return varchar2 as mle module chance_extended env chance_module_env signature 'chance.string';
 function suffix return varchar2 as mle module chance_extended env chance_module_env signature 'chance.suffix';
 function suffix( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.suffix';
 function syllable( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.syllable';
 function syllable return varchar2 as mle module chance_extended env chance_module_env signature 'chance.syllable';
 function timestamp return number as mle module chance_extended env chance_module_env signature 'chance.timestamp';
 function timestamp( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.timestamp';
 function timezone return json as mle module chance_extended env chance_module_env signature 'chance.timezone';
 function timezone( p_doc in json ) return json as mle module chance_extended env chance_module_env signature 'chance.timezone';
 function tld return varchar2 as mle module chance_extended env chance_module_env signature 'chance.tld';
 function tld( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.tld';
 function tv( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.tv';
 function tv return varchar2 as mle module chance_extended env chance_module_env signature 'chance.tv';
 function twitter( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.twitter';
 function twitter return varchar2 as mle module chance_extended env chance_module_env signature 'chance.twitter';
 function url( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.url';
 function url return varchar2 as mle module chance_extended env chance_module_env signature 'chance.url';
 function word return varchar2 as mle module chance_extended env chance_module_env signature 'chance.word';
 function word( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.word';
 function wp7_anid( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.wp7_anid';
 function wp7_anid return varchar2 as mle module chance_extended env chance_module_env signature 'chance.wp7_anid';
 function wp8_anid2( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.wp8_anid2';
 function wp8_anid2 return varchar2 as mle module chance_extended env chance_module_env signature 'chance.wp8_anid2';
 function year( p_doc in json ) return number as mle module chance_extended env chance_module_env signature 'chance.year';
 function year return number as mle module chance_extended env chance_module_env signature 'chance.year';
 function zip( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.zip';
 function zip return varchar2 as mle module chance_extended env chance_module_env signature 'chance.zip';
 function zodiac( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.zodiac';
 function zodiac return varchar2 as mle module chance_extended env chance_module_env signature 'chance.zodiac';
 function template( p_doc in json ) return json as mle module chance_extended env chance_module_env signature 'template';
end;
/

That's all!

You can now use all the new functions to generate random data. See the following examples.

Using Chance.js functions in SQL

Our first example will focus on very basic data generation:

-- Generate a random string
select chance.string;

STRING
______________
^2nZ*5n*Xhv

-- Generate a random string with 5 chars
select chance.string( json { 'length': 5 } );

CHANCE.STRING(JSON{'LENGTH':5})    
__________________________________
@AgM3

If you want more than 1 row, you can leverage this trick:

-- Generate 7 random strings with 10 chars
select chance.string( json { 'length': 10 } ) connect by level <= 7;

CHANCE.STRING(JSON{'LENGTH':10})
___________________________________
#^lgZ*6r!i
N7alrlEznY
g(2@6A3c4o
NB9M4WXsMk
^uI*jJG1oM
T6bBsYKfFd
bMv]69L]WQ

-- Generate 5 prime numbers (between 0 to 10000)
select chance.prime connect by level <= 5;

   PRIME
________
    5591
    2113
    9239
    9479
     587

-- Generate a random credit card number (Mastercard)
select chance.cc( json { 'type': 'Mastercard' } );

CHANCE.CC(JSON{'TYPE':'MASTERCARD'})
_______________________________________
5199094555584914

-- Generate 2 random dates (which includes time for Oracle database)
-- first one is a DATE, second one is a formatted string (or varchar2)
select chance."date", to_char( chance."date", 'yyyy-mm-dd hh24:mi:ss' );

date        TO_CHAR(CHANCE."DATE",'YYYY-MM-DDHH24:MI:SS')
___________ ________________________________________________
15/09/49    2039-01-29 09:33:45

Remark that DATE is an Oracle datatype and thus you need to use lowercase call to distinguish it from the function, that's why the double-quotes are mandatory and the lower case must be used here (as for "integer"). In the previous example, the first date is 15th of September 2049 because of my localized session (France).

-- Generate a random date as a string (or varchar2)
-- here, the format is the one from Chance: american (28th of January 2024, MM/DD/YYYY)
select chance."date"( json{ 'string': true } );

CHANCE."DATE"(JSON{'STRING':TRUE})
_____________________________________
1/28/2024

-- Generate a random date as a string (or varchar2), but this time with format DD/MM/YYYY
select chance."date"( json{ 'string': true, 'american': false } );

CHANCE."DATE"(JSON{'STRING':TRUE,'AMERICAN':FALSE})
______________________________________________________
15/2/2103

-- Generate a random date with a fixed year
select chance."date"( json{ 'year': 2023 } );

ORA-04156: Unable to convert MLE value to database CHAR value. Reason:
unsupported type for value:2023-03-08T02:45:14.246Z.

Here you can see some limitations or flexibility (depending on your point of view) of interoperability between JavaScript and PL/SQL. When passing a JSON parameter, we can ask for an extended generation mode for a given data generator. But the PL/SQL function signatures have to be compatible because PL/SQL is strongly typed. Hence the trick here is to have a third signature for that case:

function "date" return date as mle module chance_extended env chance_module_env signature 'chance.date';
 function "date"( p_doc in json ) return varchar2 as mle module chance_extended env chance_module_env signature 'chance.date';
 -- This one will help:
 function "date"( p_doc in json, p_will_be_date in boolean ) return date as mle module chance_extended env chance_module_env signature 'chance.date';

If we add a second parameter then we allow the resulting type to be an Oracle database DATE instead of a VARCHAR2:

-- Generate a random date with a fixed year
-- Passing true as the second argument (p_will_be_date) so that 
-- the returned JavaScript date is properly casted into a DATE
select chance."date"( json{ 'year': 2023 }, true );

CHANCE."DATE"(JSON{'YEAR':2023},TRUE)
________________________________________
21/02/23

-- Generate a random address
select chance.address;

ADDRESS
_________________
66 Bomcu Drive

-- Generate a random timezone
select chance.timezone;

TIMEZONE
__________________________________________________________________________________________________________________________
{"name":"Samoa Standard Time","abbr":"SST","offset":13,"isdst":false,"text":"(UTC+13:00) Samoa","utc":["Pacific/Apia"]}

You can see how it is now easy to generate random data, it's up to you... but one last word!

Using extended Chance.js functions in SQL

If you've taken a look at the extended MLE module above, you may have seen some additional code. In fact, while looking for JavaScript data generators, I found out that Fony.js (MIT License) from Safia Abdalla provided an interesting concept of JSON template. So I've decided to integrate it and improve it to benefit from the additional possibilities offered by Chance.js in terms of generator customization using a JSON document as a parameter. The result is that the template() function can now be used to generate random JSON documents based on JSON templates!

Let's see some examples:

-- Generate a JSON document containing tags as an array of 3 words
select chance.template( json {'tags': ['word', 3]} );

CHANCE.TEMPLATE(JSON{'TAGS':['WORD',3]})    
___________________________________________
{"tags":["ehugu","luf","fiksojuh"]}

-- Generate a JSON document containing random information for a person
select chance.template( json { 'name': 'name', 'age': 'age', 'address': 'address' } );

CHANCE.TEMPLATE(JSON{'NAME':'NAME','AGE':'AGE','ADDRESS':'ADDRESS'})
_______________________________________________________________________
{"address":"1904 Luwe Ridge","age":38,"name":"Cory Hopkins"}

You'll note that the ordering of the JSON fields is not respected. This is expected since the template() function leverages the Object.keys() function which in the latest ES standard doesn't guarantee the ordering anymore. But that's not really a problem, right?

-- Generate 2 random JSON documents for fictious races
-- We display the JSON document formatted for better readability
select json_serialize( chance.template( json {'raceId': { '$natural': {'min': 1, 'max': 99999} }, 
                                              'name': 'name', 
                                              'laps': { '$natural': {'min': 5, 'max': 42} }, 
                                              'date': 'date', 
                                              'podium': {} } ) pretty ) as new_races 
  connect by level <= 2;

NEW_RACES
__________________________________________
{
  "date" : "2056-11-14T13:26:53.751000Z",
  "laps" : 35,
  "name" : "Scott Wilkins",
  "podium" :
  {
  },
  "raceId" : 64315
}
{
  "date" : "2060-10-11T13:18:00.826000Z",
  "laps" : 5,
  "name" : "Wesley Ramsey",
  "podium" :
  {
  },
  "raceId" : 84434
}

This would bring some interesting ability to populate the JSON Relational Duality Views examples from the 23c FREE documentation.

Conclusion

We've seen how to import an ES Module inside the Oracle database 23c FREE Developer Release thanks to the JavaScript MultiLingual Engine. We've also seen how dependencies resolution work using an MLE environment.

As you can see, the possibilities are infinite and benefiting from such a data generator for both simple values but also JSON documents is incredibly useful. This could be used for Oracle Application Express (APEX) applications, demos, random data for unit testing, etc.

And now, it's up to you!

Resources to continue learning

As Jeff Smith said in his blog post on MLE JS Modules & Snippets, we highly recommend reading the 23c JavaScript Developer Guide (Docs.).

You may want also to review Martin Bach's blog post on Using JavaScript community modules in Oracle Database 23c Free - Developer Release which I've stolen some of the commands used here.

Don't forget to provide us your feedback on the 23c FREE community forum that we monitor and where you can provide feedback.

Thanks

I would never have discovered the possibilities highlighted here without the help of Martin Bach and Lucas Victor Braun-Lohrer, respectively Product Manager for JavaScript MLE and Program Manager at Oracle Labs; so thanks a lot!!!

Oracle Database 23c FREE - Developer release

Loïc — Fri, 14 Apr 2023 10:06:33 +0000

Oracle Database 23c Free – Developer Release is a new, free offering of the coming 23c Oracle database (the next Long Term Support release coming in the next 12 months). It can be downloaded straight from the Internet with 1 click, enabling simple and fast access or from Docker Hub (more on that later).

As a converged database (since the 2000s), it's not anymore just a relational database! You can throw at it JSON, XML, Spatial, Property Graph, RDF, Machine Learning models, etc. and you'll be able to store these data and of course process them, including mixing them using SQL of course as well as NoSQL-oriented APIs: REST, MongoDB API, SODA, etc.

Oracle Database 23c Free – Developer Release is built-in upward compatibility with Oracle Database Enterprise Edition and Oracle Database cloud services ensures that users can move to any other Oracle Database offering anytime.

Available as Container Image, VirtualBox VM, and Linux RPM installation file, Oracle Database 23c Free – Developer Release includes groundbreaking new features such as:

JSON Relational Duality: you can build applications in either relational or JSON paradigms with a single source of truth and benefit from the strengths of both, relational and document models. Data is held once but can be accessed, written, and modified with either approach. You benefit from the best of both JSON and relational models, including ACID-compliant transactions and concurrency controls, which means you no longer have to make tradeoffs between complex object-relational mappings or data inconsistency issues.
JavaScript Stored Procedures (powered by GraalVM): JavaScript code can now be executed closer to data than ever before by writing JavaScript stored procedures or loading existing JavaScript libraries into the Oracle database. Support for JavaScript code improves your productivity by allowing reuse of existing business logic straight inside the data tier and reuse of your JavaScript skills. JavaScript code invocation can be intermixed with SQL and PL/SQL, providing polyglot programming language support.
JSON Schema: you can now ensure and validate JSON document structures via industry-standard JSON Schemas, enabling the confident and reliable use of the JSON data format.
Operational Property Graphs: you can now build both transactional and analytical property graph applications with Oracle database, using its industry-leading, new ISO SQL:2023 standard property graph queries support, including running graph analytics on top of relational and JSON data.
Oracle Kafka APIs: Kafka applications can now run against Oracle database Transactional Event Queues with minimal code changes enabling much more robust microservices built using transactional events that perform event operations and database changes in a single atomic transaction.
Application Usage Domains: The new domain construct can act as lightweight type modifiers that centrally document intended data usage, extending and drastically improving SQL standard domains. This allows you to better understand how the data is used and improves overall data quality, without the complexity and incompatibility of usage-specific data types or user-defined types.
Application Usage Annotations: Database metadata can now be stored directly alongside the data with the new Annotation mechanism inside the Oracle database. You can annotate common data model attributes for tables, columns, views, indexes, etc., providing a central, lightweight, declarative facility to register and exchange usage properties across applications. Storing the metadata along with the data guarantees consistency and universal accessibility to any user or application that uses the data.

There is of course much more to discover such as all the SQL enhancements: SELECT without DUAL table, GROUP BY alias or position, Table Value Constructor, IF [NOT] EXISTS clause, etc. inside the New Features Guide.

Finally, if you need help, there is a community forum to support you.

NoSQL & SQL together: Spring Boot example🤯

Loïc — Fri, 29 Apr 2022 07:21:45 +0000

👉Oracle Database API for MongoDB: use your autonomous database as if it was a MongoDB database🥰

Recover your Dropped Table from the Recyclebin ⛅😎

Loïc — Thu, 21 Apr 2022 09:44:17 +0000

👉Recyclebin: helpful for developers in case a DROP command occured mistakenly; tables can be recovered as if nothing happened 💪

📖 Documentation
💻 Gist

Machine Learning for SQL: train, deploy, score your models using SQL...😍

Loïc — Thu, 14 Apr 2022 21:36:19 +0000

👉MachineLearning for SQL: train, deploy and score models right inside the database, no need to move data over the network, stronger security, scalable platform, python and R APIs as well✅

CREATE TABLE customers (
    id                NUMBER  PRIMARY KEY,
    children          NUMBER,
    age               NUMBER,
    gender            CHAR(1),
    salary            NUMBER,
    married           CHAR(1),
    will_buy_an_house NUMBER
);

-- Inserting a very small data set to train 
-- the machine learning model...
INSERT INTO customers VALUES (1,0,20,'F',1200,'N',0);
INSERT INTO customers VALUES (2,3,48,'M',2800,'N',0);
INSERT INTO customers VALUES (3,1,33,'F',2500,'Y',1);
INSERT INTO customers VALUES (4,2,37,'F',1800,'Y',1);
INSERT INTO customers VALUES (5,0,52,'M',2000,'Y',0);
INSERT INTO customers VALUES (6,1,61,'M',1700,'Y',0);
COMMIT;

SELECT * FROM customers;

 ID  CHILDREN  AGE  GENDER  SALARY  MARRIED  WILL_BUY_AN_HOUSE
--- --------- ---- ------- ------- -------- ------------------
  1         0   20       F    1200        N                  0
  2         3   48       M    2800        N                  0
  3         1   33       F    2500        Y                  1
  4         2   37       F    1800        Y                  1
  5         0   52       M    2000        Y                  0
  6         1   61       M    1700        Y                  0

-- Configuration...
CREATE TABLE config (
    setting_name  VARCHAR2(30),
    setting_value VARCHAR2(4000)
);

-- Neural Network algorithm for classification
INSERT INTO config VALUES ('ALGO_NAME', 'ALGO_NEURAL_NETWORK');
-- Automatic data preparation: enabled!
INSERT INTO config VALUES ('PREP_AUTO','ON');
COMMIT;

-- Now training...
BEGIN
    DBMS_DATA_MINING.CREATE_MODEL(
        model_name          => 'NN_Classification',
        mining_function     => DBMS_DATA_MINING.classification,
        data_table_name     => 'customers',
        case_id_column_name => 'id',
        target_column_name  => 'will_buy_an_house',
        settings_table_name => 'config' 
    );
END;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.742

-- Likely to buy an house?
SELECT 100 * 
       PREDICTION_PROBABILITY(NN_Classification, 
              1 USING 
             43 AS age, 
              2 AS children, 
            'M' AS gender, 
           2300 AS salary, 
            'Y' AS married) AS probability_to_buy_an_house
  FROM dual;


              PROBABILITY_TO_BUY_AN_HOUSE
             ----------------------------
                        79.05203574218477

📖 Documentation
💻 Gist with code above to reproduce
😺OML GitHub repository
🤯Oracle LiveLabs
😎Oracle Machine Learning blog

Stop fighting naming things in SQL: Long identifiers✔

Loïc — Wed, 13 Apr 2022 08:19:29 +0000

👉Long identifiers: since 2017 (12.2 version of Oracle database), tables, columns, views, sequences, indexes... can have names with up to 1️⃣2️⃣8️⃣ bytes!😉

To learn more:

Reusable SQL templates: learn about SQL Macros🤯

Loïc — Tue, 12 Apr 2022 10:32:55 +0000

👉SQL Table Macro: create reusable SQL templates (FROM clause) you can pass tables, and other parameters to at runtime.

Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/sql-macros-sqm.html

SQL Table Macro is a 21c feature backported to the 19.7 release update.

CREATE TABLE planets (
    json_document BLOB,
    CONSTRAINT json_document_is_json CHECK (json_document IS JSON)
);

INSERT INTO planets (json_document) 
VALUES ( '[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, 
            {"name":"Mars"}, {"name":"Jupiter"}, {"name":"Saturn"}, 
            {"name":"Uranus"}, {"name":"Neptune"} ]' );
COMMIT;

-- One row retrieved containing a JSON array
SELECT JSON_SERIALIZE(json_document) AS array FROM planets;

                                                       ARRAY
-------------------------------------------------------------
[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, … ]


-- Second version where the column table is also a parameter
CREATE OR REPLACE FUNCTION unwind(t DBMS_TF.TABLE_T, c DBMS_TF.COLUMNS_T) 
       RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN := q'{
      SELECT d.array_item AS JSON_DOCUMENT, d.order_id
        FROM unwind.t nested }'|| unwind.c(1) ||q'{ COLUMNS (
                 NESTED PATH '$[*]' COLUMNS (
                     array_item CLOB FORMAT JSON PATH '$',
                     order_id FOR ORDINALITY
                 )
             ) d}';
END;
/
-- Using a table and one of its columns as parameters of SQL Macro
SELECT * FROM unwind( planets, COLUMNS( json_document ) );         

     JSON_DOCUMENT   ORDER_ID
------------------ ----------
{"name":"Mercury"}          1
  {"name":"Venus"}          2
  {"name":"Earth"}          3
   {"name":"Mars"}          4
{"name":"Jupiter"}          5
 {"name":"Saturn"}          6
 {"name":"Uranus"}          7
{"name":"Neptune"}          8

The example above uses the second version of my SQL Macro (compared to the animated GIF above) where I can also pass a column as a parameter!

Remark for Pluto: according to NASA, this is no more considered as the 9th planet 🙂

Kudos to Chris Saxon for helping a lot finding the summary for such a great feature!

Smart list of values in SQL with LISTAGG

Loïc — Fri, 08 Apr 2022 11:17:57 +0000

👉LISTAGG(): aggregates the values of a column by concatenating them into one string. Adding overflow management for a very long list of values.😀😀😁😁😁😂😂😅😅🤣🤣🤣🤣😱💥💨

Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466

Gist: https://gist.github.com/loiclefevre/1ae2e9f496e0156277feeff1b6e03242

PL/SQL functions defined in SQL queries🤯

Loïc — Thu, 07 Apr 2022 14:39:33 +0000

👉PL/SQL functions in the SQL WITH clause: Your PL/SQL function defined in the WITH clause of a subquery! Results in better performance as compared with schema-level functions.🥳

📖Documentation: https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT146

💻Gist to reproduce: https://gist.github.com/loiclefevre/ddab41639f92f36cf7b09570d3174812

👮‍♀️IMMUTABLE Tables👮‍♂️

Loïc — Wed, 06 Apr 2022 10:04:49 +0000

👉 IMMUTABLE tables: Insert-only tables in which existing data cannot be modified. Deleting rows is either prohibited or restricted based on the insertion time of the rows.

☝ an example of such an IMMUTABLE table used to store orders as JSON data. You may (for legal reasons) be able to delete data but only after waiting for 30 days. Also, you can drop the table but only after 16 days without any activity on it. Setting the database parameter blockchain_table_max_no_drop to 0 before creating such tables can help for dev/test! Finally, you can use DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS to delete expired rows.

⚠ feature backported to 19.11 version.

Learn more: