Staging in Oracle: temporary tables and MERGE

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: two SQL*Plus or SQLcl
-- windows, A and B, both connected as LAB. Run each numbered step in the window it names, in order.
-- Two global temporary tables are created once, like any table, and each session sees only its own rows in them;
-- then a private temporary table exists in one session only. session_rows_model.py prints the same counts.
-- The last step drops what the script made.

-- 1. Window A: two global temporary tables, one per ON COMMIT setting.
DROP TABLE IF EXISTS feed_stage_txn PURGE;
DROP TABLE IF EXISTS feed_stage_ses PURGE;
CREATE GLOBAL TEMPORARY TABLE feed_stage_txn (
  line_no NUMBER(6)    CONSTRAINT feed_stage_txn_pk PRIMARY KEY,
  sku     VARCHAR2(12) NOT NULL,
  delta   NUMBER(6)    NOT NULL
) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE feed_stage_ses (
  line_no NUMBER(6)    CONSTRAINT feed_stage_ses_pk PRIMARY KEY,
  sku     VARCHAR2(12) NOT NULL,
  delta   NUMBER(6)    NOT NULL
) ON COMMIT PRESERVE ROWS;

-- 2. Window A: stage three feed lines in each table.
INSERT INTO feed_stage_txn VALUES (1, 'RICE-5K', 4);
INSERT INTO feed_stage_txn VALUES (2, 'TEA-250', -3);
INSERT INTO feed_stage_txn VALUES (3, 'OIL-1L', 6);
INSERT INTO feed_stage_ses SELECT * FROM feed_stage_txn;
SELECT (SELECT COUNT(*) FROM feed_stage_txn) AS txn_rows, (SELECT COUNT(*) FROM feed_stage_ses) AS ses_rows FROM dual;
-- Expected: 3, 3

-- 3. Window B: the same tables, none of A's rows. Line 1 again is no clash: each session's index is its own.
SELECT COUNT(*) AS txn_rows FROM feed_stage_txn;
-- Expected: 0
INSERT INTO feed_stage_txn VALUES (1, 'DAL-1K', 2);

-- 4. Window A: commit. DELETE ROWS empties A's rows of the first table; PRESERVE ROWS keeps the second.
COMMIT;
SELECT (SELECT COUNT(*) FROM feed_stage_txn) AS txn_rows, (SELECT COUNT(*) FROM feed_stage_ses) AS ses_rows FROM dual;
-- Expected: 0, 3

-- 5. Window B: A's commit did not touch B's row.
SELECT COUNT(*) AS txn_rows FROM feed_stage_txn;
-- Expected: 1
ROLLBACK;

-- 6. Window A: a private temporary table. DROP DEFINITION makes it last until the end of this transaction, and
--    creating it does not commit, so it can be created in the middle of a transaction.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_feed (line_no NUMBER(6), sku VARCHAR2(12), delta NUMBER(6))
  ON COMMIT DROP DEFINITION;
INSERT INTO ora$ptt_feed VALUES (1, 'RICE-5K', 4);
SELECT COUNT(*) AS ptt_rows FROM ora$ptt_feed;
-- Expected: 1

-- 7. Window B: the name means nothing in another session.
SELECT COUNT(*) FROM ora$ptt_feed;
-- Expected: an error, because no such table exists in session B.

-- 8. Window A: the commit drops the table with its rows.
COMMIT;
SELECT COUNT(*) FROM ora$ptt_feed;
-- Expected: an error, because the table is gone.

-- 9. Window A: two definitions that a private temporary table refuses.
CREATE PRIVATE TEMPORARY TABLE feed_ptt (line_no NUMBER(6)) ON COMMIT DROP DEFINITION;
-- Expected: an error: the name lacks the prefix ORA$PTT_ that PRIVATE_TEMP_TABLE_PREFIX sets.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_keyed (line_no NUMBER(6) PRIMARY KEY) ON COMMIT DROP DEFINITION;
-- Expected: an error: a private temporary table takes no PRIMARY KEY, UNIQUE, CHECK or NOT NULL constraint.

-- 10. Window A: clean up. A session stays bound to a PRESERVE ROWS table until it ends or truncates it, and DDL
--     waits for no session to be bound, so the table is truncated first.
TRUNCATE TABLE feed_stage_ses;
DROP TABLE feed_stage_txn PURGE;
DROP TABLE feed_stage_ses PURGE;

"""temp_tables.sql in the lab, in two SQL*Plus clients of LAB, A and B, step by step as the script numbers
them."""

from oracle_session import Lab

STEP = r"^-- \d+\. (?P<who>Window A|Window B|Both windows|Clean-up)\b"
WHO = {"Window A": ["A"], "Window B": ["B"], "Both windows": ["A", "B"], "Clean-up": ["A"]}

with Lab() as lab:
    lab.steps("stage/temp_tables.sql", STEP, WHO, users={"A": "lab", "B": "lab"}, errors=4)

A> 1. Window A: two global temporary tables, one per ON COMMIT setting.
Table dropped.


Table dropped.


Table created.


Table created.

A> 2. Window A: stage three feed lines in each table.
1 row created.


1 row created.


1 row created.


3 rows created.


  TXN_ROWS   SES_ROWS
---------- ----------
         3          3

B> 3. Window B: the same tables, none of A's rows. Line 1 again is no clash: each session's index is its own.
  TXN_ROWS
----------
         0


1 row created.

A> 4. Window A: commit. DELETE ROWS empties A's rows of the first table; PRESERVE ROWS keeps the second.
Commit complete.


  TXN_ROWS   SES_ROWS
---------- ----------
         0          3

B> 5. Window B: A's commit did not touch B's row.
  TXN_ROWS
----------
         1


Rollback complete.

A> 6. Window A: a private temporary table. DROP DEFINITION makes it last until the end of this transaction, and
A> creating it does not commit, so it can be created in the middle of a transaction.
Table created.


1 row created.


  PTT_ROWS
----------
         1

B> 7. Window B: the name means nothing in another session.
SELECT COUNT(*) FROM ora$ptt_feed
                     *
ERROR at line 1:
ORA-00942: table or view "LAB"."ORA$PTT_FEED" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

A> 8. Window A: the commit drops the table with its rows.
Commit complete.

SELECT COUNT(*) FROM ora$ptt_feed
                     *
ERROR at line 1:
ORA-00942: table or view "LAB"."ORA$PTT_FEED" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

A> 9. Window A: two definitions that a private temporary table refuses.
CREATE PRIVATE TEMPORARY TABLE feed_ptt (line_no NUMBER(6)) ON COMMIT DROP DEFINITION
*
ERROR at line 1:
ORA-00903: invalid table name
Help: https://docs.oracle.com/error-help/db/ora-00903/


CREATE PRIVATE TEMPORARY TABLE ora$ptt_keyed (line_no NUMBER(6) PRIMARY KEY) ON COMMIT DROP DEFINITION
                                                                           *
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
Help: https://docs.oracle.com/error-help/db/ora-14451/

A> 10. Window A: clean up. A session stays bound to a PRESERVE ROWS table until it ends or truncates it, and DDL
A> waits for no session to be bound, so the table is truncated first.
Table truncated.


Table dropped.


Table dropped.

"""What two sessions see in a staging table of each kind, modelled in Python.

A model, not Oracle: it applies the rules of Oracle's CREATE TABLE page and Administrator's Guide. A global temporary
table has one definition that every session uses, and each session sees only the rows it inserted: ON COMMIT DELETE
ROWS truncates a session's rows at each commit, ON COMMIT PRESERVE ROWS keeps them until the session ends. A private
temporary table belongs, definition and rows, to the session that created it: ON COMMIT DROP DEFINITION drops it at
the commit, ON COMMIT PRESERVE DEFINITION when the session ends.

Session A stages A_ROWS lines and session B stages B_ROWS lines in a table of the same name; then A commits. Press
Edit, set B_COMMITS = True (B commits as well), and run it again.
"""

A_ROWS = 3
B_ROWS = 1
B_COMMITS = False

SETTINGS = [
    ("global", "ON COMMIT DELETE ROWS"),
    ("global", "ON COMMIT PRESERVE ROWS"),
    ("private", "ON COMMIT DROP DEFINITION"),
    ("private", "ON COMMIT PRESERVE DEFINITION"),
]
MOMENTS = ["before A commits", "after A commits", "A in a new session"]


def after_commit(kind, setting, rows):
    """What a session holds right after its own commit: a row count, or no table at all."""
    if setting.endswith("DELETE ROWS"):
        return 0
    if setting.endswith("DROP DEFINITION"):
        return None
    return rows


def in_new_session(kind):
    """A new session of the same user: a global table's definition is still there, a private one is not."""
    return 0 if kind == "global" else None


def show(value):
    return "no table" if value is None else str(value)


print(f"Session A stages {A_ROWS} lines and session B stages {B_ROWS}; then A commits"
      + (", and B commits too." if B_COMMITS else "; B does not commit."))
for kind, setting in SETTINGS:
    b_after = after_commit(kind, setting, B_ROWS) if B_COMMITS else B_ROWS
    a_values = [A_ROWS, after_commit(kind, setting, A_ROWS), in_new_session(kind)]
    b_values = [B_ROWS, b_after, b_after]
    print()
    print(f"{setting} ({kind})")
    print(f"  {'':<20}{'A sees':>9}{'B sees':>9}")
    for moment, a, b in zip(MOMENTS, a_values, b_values):
        print(f"  {moment:<20}{show(a):>9}{show(b):>9}")
finished = True

Session A stages 3 lines and session B stages 1; then A commits; B does not commit.

ON COMMIT DELETE ROWS (global)
                         A sees   B sees
  before A commits            3        1
  after A commits             0        1
  A in a new session          0        1

ON COMMIT PRESERVE ROWS (global)
                         A sees   B sees
  before A commits            3        1
  after A commits             3        1
  A in a new session          0        1

ON COMMIT DROP DEFINITION (private)
                         A sees   B sees
  before A commits            3        1
  after A commits      no table        1
  A in a new session   no table        1

ON COMMIT PRESERVE DEFINITION (private)
                         A sees   B sees
  before A commits            3        1
  after A commits             3        1
  A in a new session   no table        1

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- A shop's shelf counts take a feed of changes: a positive delta for goods received, a negative one for goods
-- taken off the shelf. The feed is staged in a global temporary table, then one MERGE updates the SKUs that exist,
-- inserts the new ones, and deletes a row whose count falls to zero or below. merge_model.py prints the same result.
-- The script rolls its changes back and drops what it made.
SET SERVEROUTPUT ON

DROP TABLE IF EXISTS shelf_counts PURGE;
DROP TABLE IF EXISTS shelf_feed PURGE;

CREATE TABLE shelf_counts (
  sku       VARCHAR2(12) CONSTRAINT shelf_counts_pk PRIMARY KEY,
  item_name VARCHAR2(60) NOT NULL,
  on_shelf  NUMBER(6)    NOT NULL
);
INSERT INTO shelf_counts VALUES ('RICE-5K', 'Rice 5 kg', 10);
INSERT INTO shelf_counts VALUES ('TEA-250', 'Tea 250 g', 2);
INSERT INTO shelf_counts VALUES ('DAL-1K', 'Dal 1 kg', 0);
COMMIT;

-- The staging table: one definition for every session, rows for this transaction only.
CREATE GLOBAL TEMPORARY TABLE shelf_feed (
  line_no   NUMBER(6)    CONSTRAINT shelf_feed_pk PRIMARY KEY,
  sku       VARCHAR2(12) NOT NULL,
  item_name VARCHAR2(60),
  delta     NUMBER(6)    NOT NULL
) ON COMMIT DELETE ROWS;

INSERT INTO shelf_feed VALUES (1, 'RICE-5K', NULL, 4);
INSERT INTO shelf_feed VALUES (2, 'TEA-250', NULL, -3);
INSERT INTO shelf_feed VALUES (3, 'OIL-1L', 'Oil 1 litre', 6);

MERGE INTO shelf_counts t
USING shelf_feed s
ON (t.sku = s.sku)
WHEN MATCHED THEN
  UPDATE SET t.on_shelf = t.on_shelf + s.delta
  DELETE WHERE t.on_shelf <= 0                   -- reads the count after the update
WHEN NOT MATCHED THEN
  INSERT (sku, item_name, on_shelf) VALUES (s.sku, s.item_name, s.delta);

SELECT sku, on_shelf FROM shelf_counts ORDER BY sku;
-- Expected:
-- DAL-1K    0    not in the feed, so the MERGE never touched it, although its count is 0
-- OIL-1L    6    inserted
-- RICE-5K  14    updated: 10 + 4
-- TEA-250 is gone: updated to 2 - 3 = -1, then deleted by DELETE WHERE

ROLLBACK;
DROP TABLE shelf_feed PURGE;
DROP TABLE shelf_counts PURGE;

"""stock_merge.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("merge/stock_merge.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @merge/stock_merge.sql
Table dropped.


Table dropped.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.


Table created.


1 row created.


1 row created.


1 row created.


3 rows merged.


SKU            ON_SHELF
------------ ----------
DAL-1K                0
OIL-1L                6
RICE-5K              14


Rollback complete.


Table dropped.


Table dropped.

"""The shelf-count MERGE of stock_merge.sql, modelled in Python.

A model, not Oracle: it applies the rules of Oracle's MERGE page. A feed line whose SKU matches a row updates it; a
line with no match inserts a row. DELETE WHERE then removes a row only if this MERGE has just updated it, and it
reads the count after the update, not before. A row that is not in the feed is never deleted, whatever its count.

Press Edit, change FEED (add a line for DAL-1K, or a delta that empties RICE-5K), and run it again.
"""

SHELF = {"RICE-5K": 10, "TEA-250": 2, "DAL-1K": 0}
FEED = [("RICE-5K", 4), ("TEA-250", -3), ("OIL-1L", 6)]

shelf = dict(SHELF)
print("before: " + ", ".join(f"{sku} {n}" for sku, n in sorted(shelf.items())))
for line_no, (sku, delta) in enumerate(FEED, 1):
    if sku in shelf:
        old = shelf[sku]
        shelf[sku] = old + delta                                       # WHEN MATCHED THEN UPDATE
        action = f"matched: {old} {'+' if delta >= 0 else '-'} {abs(delta)} = {shelf[sku]}"
        if shelf[sku] <= 0:                                            # DELETE WHERE t.on_shelf <= 0
            del shelf[sku]
            action += ", then deleted by DELETE WHERE"
    else:
        shelf[sku] = delta                                             # WHEN NOT MATCHED THEN INSERT
        action = f"no match: inserted with {delta}"
    print(f"line {line_no}  {sku:<8} {action}")
print("after:  " + ", ".join(f"{sku} {n}" for sku, n in sorted(shelf.items())))
untouched = [sku for sku in SHELF if sku in shelf and sku not in dict(FEED)]
print(f"not in the feed, kept as they were: {', '.join(untouched) or 'none'}")
finished = True

before: DAL-1K 0, RICE-5K 10, TEA-250 2
line 1  RICE-5K  matched: 10 + 4 = 14
line 2  TEA-250  matched: 2 - 3 = -1, then deleted by DELETE WHERE
line 3  OIL-1L   no match: inserted with 6
after:  DAL-1K 0, OIL-1L 6, RICE-5K 14
not in the feed, kept as they were: DAL-1K