Transaction Backout Introduction

Oracle’s TRANSACTION_BACKOUT function does just what it’s name implies – it backs out committed transactions. Here is an example:

-- Create a simple demo table
SQL> CREATE TABLE demo
 2 ( demo_id NUMBER NOT NULL PRIMARY KEY,
 3   demo_name VARCHAR2(30) NOT NULL UNIQUE );

Table created.

-- Insert a record
SQL> BEGIN
 2     INSERT INTO demo
 3     VALUES(1,'Demo 1');
 4     COMMIT;
 5   END;
/

PL/SQL procedure successfully completed.

-- Create package to hold the SCN value later
SQL> CREATE OR REPLACE PACKAGE hold_stuff AS
 2     v_scn NUMBER;
 3   END;
 4 /

Package created.

-- Save the current SCN into the package value
SQL> BEGIN
 2     hold_stuff.v_scn := 
 3        DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 4 END;
 5 /

PL/SQL procedure successfully completed.

-- Set the name of the transaction for later backout
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

-- Update the record in the table and commit
SQL> UPDATE demo
 2   SET demo_name = 'Demo One';

1 row updated.

SQL> COMMIT;

Commit complete.

-- Verify the demo name has been changed
SQL> SELECT demo_name
 2     FROM demo;

DEMO_NAME
------------------------------
Demo One

-- Now backout the update transaction using the
-- transaction name and SN value
SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY :=
 3        SYS.TXNAME_ARRAY('UPDATE');
 4 BEGIN
 5   DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 6                     names => v_names,
 7                     scnhint => hold_stuff.v_scn );
 8 END;
 9 /

PL/SQL procedure successfully completed.

-- Verify the demo name was changed
SQL> SELECT demo_name
 2 FROM demo;

DEMO_NAME
------------------------------
Demo 1

This is just an introduction. In the next few blog entries I’ll explain the functionality in much more detail.

Thanks for reading.

Bringing JSON To The Table

As a long tine use of Relational Databases I live by the rule that data must be stored in and retrieved from table. And those tables must have columns. But JSON plays by it’s own rules by allowing random attribute-value pairs with no consistency enforced. But the latest version of my most used database, Oracle offers a way to store and retrieve this data in a JSON column in a table; and to retrieve it with a JSON_TABLE function. Here is an example:

-- create a table with a single JSON column
SQL> CREATE TABLE its_json_thanks
 2 ( json_only_please CLOB,
 3 CONSTRAINT json_only
 4 CHECK ( json_only_please IS JSON ) );

Table created.

-- Insert some valid JSON
SQL> INSERT INTO its_json_thanks
 2 VALUES(
 3 '
 4 {
 5 "primary_key" : "1",
 6 "unique_key" : "1"
 7 }');

1 row created.

-- Query the values out
SQL> SELECT pk,
 2 uk
 3 FROM its_json_thanks ijt,
 4 JSON_TABLE(json_only_please,'$'
 5 COLUMNS( pk VARCHAR2(10) PATH '$.primary_key',
 6 uk VARCHAr2(10) PATH '$.unique_key' ))
 7 WHERE ijt.json_only_please.primary_key = '1'
 8 /

PK UK 
---------- ---------- 
1 1

1 row selected

The JSON_TABLE function performs a self-join between the table and the JSON column it contains. It also aligns the attributes (primary key and unique key) with column names (PK and UK).

Oracle quite adept at serving non-tabular data (XML,etc) up in a columnar format for applications that need to see it that way while allowing other applications to interact in their native format.

I’d explain the JSON_TABLE example as a conversation between a maitre’d and a customer like this:

MD: Welcome, Primary JSON. How many in your party?

MRJ: Two please. Me and my wife, Unique.

MD: Ah yes, the famous PK and UK. We have a one dollar,
    two seater near the window for you.

Or maybe not.

Thanks for reading.