JSON Check Constraints…or not

I’ve been researching the Oracle SQL JSON functions and found some weird interesting behaviors. My New Year’s resolution is to look for the goodness in things so I’ll present them as features and tie them loosely (very, very loosely) to existing Oracle features. Here’s the first entry. The following code imitates a check constraint by requiring an … Read more

JSON Unique Keys

When it comes to JSON unique key values there are two schools of thought – those that want keys within objects (squiggly brackets) to be unique and those that don’t care. This can present some interesting outcomes when parsing the data as shown in this post using the SQL JSON_QUERY function. Note that array pointers … Read more

Validate Parallel Pipelined Functions (Dont Assume PQ)

When writing code for parallel processing make sure you allow for running in a non parallel environment as well. For example here is the code from the previous post being run with PARALLEL QUERY disabled. SQL> ALTER SESSION DISABLE PARALLEL QUERY; Session altered. SQL> DECLARE 2 v_counter NUMBER := 0; 3 BEGIN 4 FOR x IN … Read more

Validate Parallel Pipelined Table Functions

This post shows how to verify a pipelined-parallel function is working as expected by adding timestamps and PQ names to the output. First let’s create a simple two column table and add some test data. CREATE TABLE demo ( col1 NUMBER,   col2 NUMBER ); BEGIN FOR counter1 IN 1..3 LOOP FOR counter2 IN 1..3 … Read more

Power Of Pipelining

Previous posts in this category introduced a way to perform DML in a SELECT using the following technology: Table Functions Autonomous Transactions A REF CURSOR as a parameter Row by row (aka slow by slow) processing of the REF CURSOR Parallel processing to overcome the slow by slow processing This post adds pipelining to the arsenal. Pipelining … Read more

Which PQ Server

This post shows how to determine which parallel query server’s (PQS) were used to execute a parallel table function. This is done using the v$px_process view which shows the relationship between Oracle sessions and PQ servers. First create a table with some demo data. CREATE TABLE demo ( col1 NUMBER ); BEGIN FOR counter IN 1..10 … Read more

Parallel By Range

Previous posts in this category introduced a way to perform DML in a SELECT using the following technology: Table Functions Autonomous Transactions A REF CURSOR as a parameter Row by row (aka slow by slow) processing of the REF CURSOR Parallel processing to try to overcome the slow by slow processing This post expands the parallel … Read more

Parallel By ANY

Previous posts in this category introduced a way to perform DML in a SELECT using the following technology: Table Functions Autonomous Transactions The following concepts were included: A REF CURSOR as a parameter Row by row (aka slow by slow) processing of the REF CURSOR Raw parallel query processing to try to overcome the slow by … Read more