implestrat.com

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(
  b_content => utl_raw.cast_to_raw('{"KEY1" : "VALUE1"}'));
  t := v.insert_one(d);
END;

What objects get created to support this collection?  A quick query of the USER_OBJECTS view reveals the following:

SELECT object_name,
       object_type
  FROM user_objects;

OBJECT_NAME               OBJECT_TYPE
------------------------- -----------
CLCT                      TABLE
SYS_C007444               INDEX
SYS_IL0000073558C00005$$  INDEX
SYS_LOB0000073558C00005$$ LOB

The CLCT table stores data for the CLCT collection. It has an index as well as a LOB and LOB index to store JSON data in a Binary Large Object (BLOB) field.

Here is what the CLCT table looks like.

SQL> DESC clct
Name            Null?    Type
--------------- -------- -------------
ID              NOT NULL VARCHAR2(255)
CREATED_ON      NOT NULL TIMESTAMP(6)
LAST_MODIFIED   NOT NULL TIMESTAMP(6)
VERSION         NOT NULL VARCHAR2(255)
JSON_DOCUMENT            BLOB

The JSON_DOCUMENT field contains the JSON document. This means it can be queried using Oracle’s SQL JSON functions such as JSON_VALUE shown here.

SELECT JSON_VALUE(json_document,'$.KEY1')
  FROM clct;

JSON_VALUE(JSON_DOCUMENT,'$.KEY1')
----------------------------------
VALUE1

To investigate what JSON keys exist in the JSON document we access it’s Data Guide like this:

SELECT JSON_DATAGUIDE(json_document)
  FROM clct;

JSON_DATAGUIDE(JSON_DOCUMENT)
--------------------------------------------------
[{"o:path":"$.KEY1","type":"string","o:length":8}]

And since we are working in the meta-data crazy world of JSON there must be some meta-data for the collection as well! Here’s how we access that.

Note the use of the JSON_QUERY SQL function to show the results.

DECLARE
  collection SODA_COLLECTION_T;
BEGIN
  collection := DBMS_SODA.open_collection('CLCT');
  DBMS_OUTPUT.PUT_LINE(
        JSON_QUERY(collection.get_metadata,'$' PRETTY));
END;

And here is the meta-data for the collection.

{
"schemaName" : "D",
"tableName" : "CLCT",
"keyColumn" :
{
"name" : "ID",
"sqlType" : "VARCHAR2",
"maxLength" : 255,
"assignmentMethod" : "UUID"
},
"contentColumn" :
{
"name" : "JSON_DOCUMENT",
"sqlType" : "BLOB",
"compress" : "NONE",
"cache" : true,
"encrypt" : "NONE",
"validation" : "STANDARD"
},
"lastModifiedColumn" :
{
"name" : "LAST_MODIFIED"
},
"versionColumn" :
{
"name" : "VERSION",
"method" : "SHA256"
},
"creationTimeColumn" :
{
"name" : "CREATED_ON"
},
"readOnly" : false
}

I’ll discuss details of the meta-data in a later post. For now just notice that the values for the name keys match the keys in the CLCT table.

For more posts about Oracle SQL and JSON click here or for a comprehensive set of training videos click here

Thanks for reading!

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 code below shows an implicit commit. First it creates a table and inserts three rows into it. At this point the the insert of three rows can be rolled back. Then another table is created which performs an implicit commit. This means the three inserts can no longer be rolled back.

CREATE TABLE abc
( col1 NUMBER );
INSERT INTO abc VALUES(1);
INSERT INTO abc VALUES(2);
INSERT INTO abc VALUES(3);

Then create another table.

CREATE TABLE def
( col1 NUMBER );

Check the records in the first table.

SELECT *
  FROM abc;

COL1
----
   1
   2
   3

Attempt to roll back.

ROLLBACK;

But the rows are still there…

SELECT *
  FROM abc;

COL1
----
   1
   2
   3

And now with SODA…

Create a table and insert 3 rows.

CREATE TABLE abc
( col1 NUMBER );
INSERT INTO abc VALUES(1);
INSERT INTO abc VALUES(2);
INSERT INTO abc VALUES(3);

Create a SODA collection.

DECLARE

  v soda_collection_t;
  d soda_document_t;
  t NUMBER;

BEGIN

  v := DBMS_SODA.CREATE_COLLECTION ('ABC_COLLECT');
  d := SODA_DOCUMENT_T(
  b_content => utl_raw.cast_to_raw('{"KEY1" : "VALUE1"}'));
  t := v.insert_one(d);

END;

Check for the rows in the first table.

SELECT *
  FROM abc;

COL1
----
   1
   2
   3

Check for the row in the SODA table created for the collection.

SELECT json_document
  FROM abc_collect;

JSON_DOCUMENT
--------------------------------------
7B224B45593122203A202256414C554531227D

Try to rollback.

ROLLBACK;

The rollback worked! On the ABC table and the SODA table.

SELECT *
  FROM abc;
no rows selected

SELECT json_document
  FROM abc_collect;
no rows selected

So the SODA table was created without an implicit commit.

Conversely an explicit commit may be required when dropping a SODA collection (including its underlying table). If there are any outstanding transactions when trying to drop a collection Oracle will raise ORA-40626 as shown in this example.

BEGIN
DBMS_OUTPUT.PUT_LINE(
     DBMS_SODA.DROP_COLLECTION('ABC_COLLECT'));
END;
*
ERROR at line 1:
ORA-40626: The table or view underlying the collection
           cannot be dropped.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SODA_UTIL", line 162
ORA-06512: at "XDB.DBMS_SODA_ADMIN", line 1857
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SODA", line 24
ORA-06512: at line 1

-- commit the transactions first
COMMIT;

Commit complete.

-- then drop the collection
BEGIN
  DBMS_OUTPUT.PUT_LINE(
    DBMS_SODA.DROP_COLLECTION('ABC_COLLECT')); 
END;

1

In the next few posts I’ll look further into the “table or view underlying the collection”.

Thanks for reading!

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:

1. Its fun to learn new things
2. I get to use the iconic Go For Soda song from Canadian musician Kim Mitchell in a post!

And a 1…and a 2…

DECLARE

  v_doc SODA_DOCUMENT_T;
  v_chorus VARCHAR2(1000);

BEGIN

  v_chorus :=
    '{"LINE1":"Might as well go for a soda",
      "LINE2":"Nobody hurts and nobody cries",
      "LINE3":"Might as well go for a soda",
      "LINE4":"Nobody drowns and nobody dies"}';

  v_doc := SODA_DOCUMENT_T(
       b_content => UTL_RAW.CAST_TO_RAW(v_chorus));

  DBMS_OUTPUT.PUT_LINE(
      UTL_RAW.CAST_TO_VARCHAR2(v_doc.get_blob));

END;

And here is the output…

{"LINE1":"Might as well go for a soda",
"LINE2":"Nobody hurts and nobody cries",
"LINE3":"Might as well go for a soda",
"LINE4":"Nobody drowns and nobody dies"}

I’m going to delve further into the SODA offerings in Oracle 18c in the next few posts.

Thanks for reading!