No Implicit Commits

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!