Transaction Backout Does Not Do JSON

I was testing transaction backout with non-scalar datatypes. And since I’m doing JSON research I decided to try JSON columns. I discovered that transaction backout does not work with JSON. Not sure just why yet. Here is the test code I used.

SQL> CREATE TABLE JSON
 2   ( always_json CLOB
 3     CONSTRAINT keep_it_real
 4*      CHECK ( always_json IS JSON ) )
Table created.

SQL> INSERT INTO json
 2   VALUES(
 3   '
 4   {
 5      "plch_id" : "1",
 6      "plch_desc" : "One"
 7   }'
 8   );
1 row created.

SQL> CREATE OR REPLACE PACKAGE hold_stuff AS
 2     v_scn NUMBER;
 3   END;
 4   /
Package created.

SQL> BEGIN
 2     hold_stuff.v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 3   END;
 4   /
PL/SQL procedure successfully completed.

SQL> SET TRANSACTION NAME 'DELETE';
Transaction set.

SQL> DELETE json;
1 row deleted.

SQL> COMMIT;
Commit complete.

SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('DELETE');
 3   BEGIN
 4     DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 5                                         names   => v_names,
 6                                         scnhint => hold_stuff.v_scn );
 7   END;
 8   /
DECLARE
*
ERROR at line 1:
ORA-55511: Flashback Transaction experienced error in executing undo SQL
ORA-02290: check constraint (ORA-02290: check constraint (DRH.KEEP_IT_REAL)
violated
.) violated
ORA-06512: at "SYS.DBMS_FLASHBACK", line 51
ORA-06512: at "SYS.DBMS_FLASHBACK", line 86
ORA-06512: at line 4

I'm unable to see what Oracle used for the undo operation (an INSERT) but it will be interesting once I find it.

Thanks for reading!

When Views Are Forced To Care

In my last post regarding Edition Based Redefinition (EBR) I explained that Oracle views do not automatically take on changes to the structures underneath them. This makes them a very handy feature. But they do have limitations – such as when an new NOT NULL column is added. Here is an example:

-- create a table with just one lonely column
SQL> CREATE TABLE demo
 2   ( col1 NUMBER );
Table created.

-- create a view on top of the table
SQL> CREATE VIEW demo_view AS
 2   SELECT *
 3     FROM demo;
View created.

-- add a NOT NULL column to the table
SQL> ALTER TABLE demo
 2   ADD ( col2 NUMBER NOT NULL );
Table altered.

-- try to insert a record into the view
SQL> INSERT INTO demo_view
 2   VALUES(2);
INSERT INTO demo_view
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DRH"."DEMO"."COL2")

The INSERT does not succeed because a value is not provided for COL2 in the DEMO table.

In the next few post’s I’ll explain how this failure might be worked around using a methodology that requires downtime.

Then I’ll segue into how EBR handles it with minimal downtime.

Thanks for reading!

Views Don’t Care Unless They Have To

Oracle’s Edition Based Redefinition (EBR) feature allows modifying database objects such as PLSQL code or database tables without interfering with a functioning application. In this series of articles I’ll introduce some of the underlying concepts of EBR and show some examples of its use. This first post describes a feature of Oracle views that helps enable EBR – the fact that views do not automatically change when their underlying tables do. Here is an example:

-- create demo table
SQL> CREATE TABLE demo
 2 ( col1 NUMBER );
Table created.

-- create a view on top of the table
SQL> CREATE VIEW demo_view AS
 2 SELECT *
 3 FROM demo;
View created.

-- insert a record into the view and only provdide value for col1
SQL> INSERT INTO demo_view
 2 VALUES(1);
1 row created.

-- add a second column to the table
SQL> ALTER TABLE demo
 2 ADD ( col2 NUMBER );
Table altered.

-- Does the view see the new column right away? No!
SQL> INSERT INTO demo_view
 2 VALUES(2);
1 row created.

-- The view remains blissfully unaware
SQL> SELECT *
 2 FROM demo_view;
COL1
----------
 1
 2

-- the table sure knows about the column though
SQL> INSERT INTO demo
 2 VALUES(3);
INSERT INTO demo
 *
ERROR at line 1:
ORA-00947: not enough values

Inserts directly into the table fail because of the new column. But not the view! The view does not see the columns until it is recreated.

SQL> CREATE OR REPLACE VIEW demo_view AS
 2   SELECT *
 3     FROM demo;
View created.

SQL> INSERT INTO demo_view
 2   VALUES(3);
INSERT INTO demo_view
 *
ERROR at line 1:
ORA-00947: not enough values

In future articles I’ll explain how this relates to EBR.

Thanks for reading!