Source: DMF/PuC/AIF/FelipeBkp/drop_all_tables.sql
-- ============================================================================
-- Drop the tables built or landed by the scripts in DMF/PuC/AIF/FelipeBkp
-- ============================================================================
-- 150 tables, taken from a static analysis of all 24 .sql files in this folder.
--
-- NOTHING IS PATTERN-MATCHED. The schema holds other _RAW / _STG / _REJECTED /
-- _TMP / _CTRL tables that these scripts never touch, so the drop is driven off
-- one explicit work list, DMF_CLEANUP_LIST, built in STEP 0. A table that is
-- not in that list cannot be dropped by this script.
--
-- STEP 1 is a SINGLE statement - one PL/SQL block - that drops everything in
-- the list, in step_no order. To exclude something, delete it from the list
-- first (STEP 0b); the block only ever reads from there.
--
-- PURE SQL AND PL/SQL - no client commands. There is deliberately no
-- 'SET SERVEROUTPUT ON': that is a SQL*Plus / SQL Developer directive and a
-- JDBC client (DBeaver, JetBrains) sends it to the server, which rejects it
-- with ORA-00900 'invalid SQL statement'. The block records its outcome into
-- DMF_CLEANUP_LIST (status / message / processed_at), so progress is readable
-- with a plain SELECT in any client - see STEP 2.
--
-- RUNNING A PL/SQL BLOCK - the '/' on its own line after each END; is the
-- SQL*Plus / SQL Developer / SQLcl block terminator. It is NOT SQL, so a client
-- that passes it to the server gets ORA-00900 'invalid SQL statement'.
-- SQL Developer F5 (Run Script) - handles '/'
-- SQLcl / SQL*Plus @drop_all_tables.sql - handles '/'
-- DBeaver Alt+X (Execute script) - handles '/'
-- Ctrl+Enter (Execute statement) does NOT: select from DECLARE
-- down to END; and leave the '/' out of the selection.
-- If you see ORA-00900 immediately after a block appeared to run, that is the
-- stray '/' - the block itself very likely succeeded. Check with:
-- SELECT status, COUNT(*) FROM dmf_cleanup_list GROUP BY status;
--
-- Two other DBeaver defaults worth turning off in
-- Preferences > Editors > SQL Editor > SQL Processing:
-- "Blank line is statement delimiter" - it reads END LOOP; as closing the
-- outer block and then splits at the next blank line (PLS-00103, end-of-file).
-- The blocks below have no blank lines in them for exactly this reason.
--
-- HOW TO RUN
-- STEP 0 build the work list (creates DMF_CLEANUP_LIST, 150 rows)
-- STEP 0b review and curate it:
-- DELETE FROM dmf_cleanup_list WHERE step_no = 9; -- keep targets
-- DELETE FROM dmf_cleanup_list WHERE table_name = '...';
-- COMMIT; -- required
-- STEP 0c see the same-shaped tables that are NOT in the list
-- STEP 1 drop everything in the list <- the one statement
-- STEP 2 review the outcome
-- STEP 3 remove the work list
--
-- ORA-00942 (table does not exist) is recorded as ABSENT. Any other error is
-- recorded as FAILED with its message and the run continues. STEP 1 is
-- re-runnable: it processes rows with status PENDING or FAILED, so a second
-- run retries only the failures.
--
-- NEVER DROPPED - a CHECK constraint on the work list rejects these outright:
-- Master data ITEM_MASTER_CTRL, STORE_ADD_CTRL, WH_CTRL, ITEM_LOC_CTRL
-- (they end in _CTRL but are source master data, not output)
-- Cross-reference XREF_ITEM_IBC_RETAIL, XREF_WH, XREF_LOCATION, XREF_SUPS
-- Not in the list, but not constrained either: XREF_TENDER_TYPE, which IS
-- rebuilt by sales_tender.sql. Add it if you want it gone:
-- INSERT INTO dmf_cleanup_list (step_no, category, table_name)
-- VALUES (3, 'DMF validity scratch', 'XREF_TENDER_TYPE');
-- COMMIT;
--
-- TWO GROUPS WORTH A SECOND THOUGHT BEFORE STEP 1
-- step_no 9 Targets (_CTRL) - the migrated OUTPUT data, not scratch. Only
-- rebuildable by re-running the full load from the RAW tables.
-- ORI_SALES_DISCOUNT_CTRL is created by no script in this folder;
-- sales_discount.sql only inserts into it, so once dropped it must
-- be recreated by hand.
-- step_no 1 RAW landing - 22 of the 26 are landed by an upstream process and
-- are NOT rebuilt here, so dropping them means every feed must be
-- re-landed before any script can run. The 4 PR1_/PV1_ ones are
-- CTAS over SAP and are rebuilt by supplier_invoices_delta.sql.
-- Everything else (steps 2-8) is regenerated by the scripts themselves.
-- ============================================================================
-- ============================================================================
-- STEP 0 - Build the work list
-- ============================================================================
BEGIN EXECUTE IMMEDIATE 'DROP TABLE dmf_cleanup_list PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
/
CREATE TABLE dmf_cleanup_list (
step_no NUMBER(2) NOT NULL,
category VARCHAR2(40) NOT NULL,
table_name VARCHAR2(128) NOT NULL,
status VARCHAR2(10) DEFAULT 'PENDING' NOT NULL,
message VARCHAR2(500),
processed_at TIMESTAMP,
CONSTRAINT pk_dmf_cleanup_list PRIMARY KEY (table_name),
CONSTRAINT ck_dmf_cleanup_status CHECK (status IN ('PENDING','DROPPED','ABSENT','FAILED')),
CONSTRAINT ck_dmf_cleanup_protected CHECK (table_name NOT IN (
'ITEM_MASTER_CTRL',
'STORE_ADD_CTRL',
'WH_CTRL',
'ITEM_LOC_CTRL',
'XREF_ITEM_IBC_RETAIL',
'XREF_WH',
'XREF_LOCATION',
'XREF_SUPS'
))
);
INSERT ALL
-- RAW landing (26)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'CLEARANCE_HISTORY_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_CLEARANCE_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'INIT_PRICE_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_PRICE_FIRST_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'MARGIN_HISTORY_RETAIL_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_MARGIN_RETAIL_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'MARGIN_HISTORY_WHOLESALE_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_MARGIN_WHOLESALE_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_DEALS_HISTORY_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_DEALS_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_INVENTORY_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_INVENTORY_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_MARKDOWN_HIST_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_MARKDOWN_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_SALES_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_SALES_REGULAR_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_SALES_TENDER_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_SALES_TENDER_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_STORE_TRAFFIC_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_STORE_TRAFFIC_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'PR1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'PRICE_HISTORY_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'ORI_PRICE_HISTORY_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'PV1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (1, 'RAW landing', 'PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW')
-- Retained backup (_BKP) (4)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (2, 'Retained backup (_BKP)', 'PR1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW_BKP')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (2, 'Retained backup (_BKP)', 'PR1_SUPPLIER_INVOICES_HEADER_DELTA_RAW_BKP')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (2, 'Retained backup (_BKP)', 'PV1_SUPPLIER_INVOICES_DETAIL_DELTA_RAW_BKP')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (2, 'Retained backup (_BKP)', 'PV1_SUPPLIER_INVOICES_HEADER_DELTA_RAW_BKP')
-- DMF validity scratch (6)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (3, 'DMF validity scratch', 'DMF_VALIDITY__ITEM_VALID')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (3, 'DMF validity scratch', 'DMF_VALIDITY__ORGANIZATION_CSV')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (3, 'DMF validity scratch', 'DMF_VALIDITY__ORGANIZAT_48D17B')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (3, 'DMF validity scratch', 'DMF_VALIDITY__ORGANIZAT_5EBC71')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (3, 'DMF validity scratch', 'DMF_VALIDITY__ORGANIZAT_A4FC2D')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (3, 'DMF validity scratch', 'DMF_VALIDITY__TENDER_TYPE')
-- DMF source snapshot (22)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__DEALS')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__DEALS_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__IC_MARGIN')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__IC_MARGIN_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__IC_MARGIN_WHOLESAL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__INVENTORY')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__INVENTORY_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__MARKDOWN_HISTORY')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__MARKDOWN_HISTORY_D')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__PRICE_CLEARANCE')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__PRICE_CLEARANCE_DE')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__PRICE_HISTORY')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__PRICE_HISTORY_DELT')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__PRICE_INIT')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__PRICE_INIT_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__SALES')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__SALES_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__SALES_TENDER')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__SALES_TENDER_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__STORE_TRAFFIC')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__STORE_TRAFFIC_DELT')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (4, 'DMF source snapshot', 'DMF_SOURCE__SUPPLIER_INVOICES_')
-- DMF duplicate-key scratch (22)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__DEALS')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__DEALS_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__IC_MARGIN')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__IC_MARGIN_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__IC_MARGIN_WHOLESA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__INVENTORY')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__INVENTORY_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__MARKDOWN_HISTORY')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__MARKDOWN_HISTORY_')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__PRICE_CLEARANCE')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__PRICE_CLEARANCE_D')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__PRICE_HISTORY')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__PRICE_HISTORY_DEL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__PRICE_INIT')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__PRICE_INIT_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__SALES')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__SALES_DELTA')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__SALES_TENDER')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__SALES_TENDER_DELT')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__STORE_TRAFFIC')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__STORE_TRAFFIC_DEL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (5, 'DMF duplicate-key scratch', 'DMF_DUPKEYS__SUPPLIER_INVOICES')
-- Staging (_STG) (22)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'CLEARANCE_HISTORY_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'CLEARANCE_HISTORY_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'INIT_PRICE_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'INIT_PRICE_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'MARGIN_HISTORY_RETAIL_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'MARGIN_HISTORY_RETAIL_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'MARGIN_HISTORY_WHOLESALE_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'MARGIN_HISTORY_WHOLESALE_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_DEAL_INCOME_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_DEAL_INCOME_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_INVENTORY_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_INVENTORY_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_MARKDOWN_HIST_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_MARKDOWN_HIST_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_SALES_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_SALES_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_SALES_TENDER_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_SALES_TENDER_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_STORE_TRAFFIC_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'ORI_STORE_TRAFFIC_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'PRICE_HISTORY_DELTA_STG')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (6, 'Staging (_STG)', 'PRICE_HISTORY_STG')
-- Rejects (_REJECTED) (23)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'CLEARANCE_HISTORY_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'CLEARANCE_HISTORY_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'INIT_PRICE_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'INIT_PRICE_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'MARGIN_HISTORY_RETAIL_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'MARGIN_HISTORY_RETAIL_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'MARGIN_HISTORY_WHOLESALE_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'MARGIN_HISTORY_WHOLESALE_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_DEALS_HISTORY_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_DEALS_HISTORY_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_INVENTORY_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_INVENTORY_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_MARKDOWN_HIST_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_MARKDOWN_HIST_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_SALES_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_SALES_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_SALES_TENDER_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_SALES_TENDER_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_STORE_TRAFFIC_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_STORE_TRAFFIC_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'ORI_SUPPLIER_INVOICES_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'PRICE_HISTORY_DELTA_REJECTED')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (7, 'Rejects (_REJECTED)', 'PRICE_HISTORY_REJECTED')
-- Temp (_TMP) (1)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (8, 'Temp (_TMP)', 'ORI_SUPPLIER_INVOICES_DELTA_TMP')
-- Targets (_CTRL) (24)
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'CLEARANCE_HISTORY_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'CLEARANCE_HISTORY_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'INIT_PRICE_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'INIT_PRICE_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'MARGIN_HISTORY_RETAIL_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'MARGIN_HISTORY_RETAIL_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'MARGIN_HISTORY_WHOLESALE_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'MARGIN_HISTORY_WHOLESALE_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_DEAL_INCOME_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_DEAL_INCOME_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_INVENTORY_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_INVENTORY_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_MARKDOWN_HIST_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_MARKDOWN_HIST_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_SALES_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_SALES_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_SALES_DISCOUNT_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_SALES_TENDER_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_SALES_TENDER_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_STORE_TRAFFIC_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_STORE_TRAFFIC_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'ORI_SUPPLIER_INVOICES_DELTA_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'PRICE_HISTORY_CTRL')
INTO dmf_cleanup_list (step_no, category, table_name) VALUES (9, 'Targets (_CTRL)', 'PRICE_HISTORY_DELTA_CTRL')
SELECT 1 FROM dual;
COMMIT;
-- Expect 150.
SELECT COUNT(*) AS tables_in_work_list FROM dmf_cleanup_list;
-- ============================================================================
-- STEP 0a - Repair a work list left over from an earlier version
-- ============================================================================
-- Only needed if DMF_CLEANUP_LIST already existed without the status columns -
-- STEP 1 would then fail with ORA-06550 / ORA-00904 "STATUS": invalid
-- identifier. Adds whatever is missing, in place, so any curation you already
-- did survives. A no-op on a list built by STEP 0 above.
DECLARE
l_cnt PLS_INTEGER;
PROCEDURE ensure_column(p_col VARCHAR2, p_ddl VARCHAR2) IS
n PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO n
FROM user_tab_columns
WHERE table_name = 'DMF_CLEANUP_LIST' AND column_name = p_col;
IF n = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE dmf_cleanup_list ADD (' || p_ddl || ')';
DBMS_OUTPUT.PUT_LINE('added column : ' || p_col);
END IF;
END ensure_column;
PROCEDURE ensure_constraint(p_name VARCHAR2, p_ddl VARCHAR2) IS
n PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO n
FROM user_constraints
WHERE constraint_name = p_name AND table_name = 'DMF_CLEANUP_LIST';
IF n = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE dmf_cleanup_list ADD CONSTRAINT ' || p_name || ' ' || p_ddl;
DBMS_OUTPUT.PUT_LINE('added constraint : ' || p_name);
END IF;
END ensure_constraint;
BEGIN
DBMS_OUTPUT.ENABLE(NULL);
SELECT COUNT(*) INTO l_cnt FROM user_tables WHERE table_name = 'DMF_CLEANUP_LIST';
IF l_cnt = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'DMF_CLEANUP_LIST does not exist - run STEP 0 first.');
END IF;
ensure_column('STATUS', 'status VARCHAR2(10) DEFAULT ''PENDING'' NOT NULL');
ensure_column('MESSAGE', 'message VARCHAR2(500)');
ensure_column('PROCESSED_AT', 'processed_at TIMESTAMP');
ensure_constraint('CK_DMF_CLEANUP_STATUS',
'CHECK (status IN (''PENDING'',''DROPPED'',''ABSENT'',''FAILED''))');
ensure_constraint('CK_DMF_CLEANUP_PROTECTED',
'CHECK (table_name NOT IN (''ITEM_MASTER_CTRL'',''STORE_ADD_CTRL'',''WH_CTRL'','
|| '''ITEM_LOC_CTRL'',''XREF_ITEM_IBC_RETAIL'',''XREF_WH'',''XREF_LOCATION'',''XREF_SUPS''))');
DBMS_OUTPUT.PUT_LINE('work list shape OK');
END;
/
-- Expect step_no, category, table_name, status, message, processed_at.
SELECT column_name, data_type, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'DMF_CLEANUP_LIST'
ORDER BY column_id;
-- ============================================================================
-- STEP 0b - Review and curate the work list BEFORE running STEP 1
-- ============================================================================
-- One row per table STEP 1 will attempt to drop. EXISTS_NOW = N means it is
-- already gone. DELETE what you want to keep, then COMMIT.
SELECT l.step_no,
l.category,
l.table_name,
NVL2(t.table_name, 'Y', 'N') AS exists_now,
t.num_rows,
t.last_analyzed
FROM dmf_cleanup_list l
LEFT JOIN user_tables t ON t.table_name = l.table_name
ORDER BY l.step_no, l.table_name;
-- Count per group, and how many are actually present.
SELECT l.step_no,
MIN(l.category) AS category,
COUNT(*) AS in_list,
COUNT(t.table_name) AS present_in_schema
FROM dmf_cleanup_list l
LEFT JOIN user_tables t ON t.table_name = l.table_name
GROUP BY l.step_no
ORDER BY l.step_no;
-- ============================================================================
-- STEP 0c - What this script will NOT touch
-- ============================================================================
-- Tables in the schema whose names follow the same conventions but that are
-- outside the 24 scripts. Listed for confidence only - STEP 1 cannot reach
-- them, because it reads from dmf_cleanup_list.
SELECT t.table_name, t.num_rows
FROM user_tables t
WHERE ( t.table_name LIKE 'DMF\_VALIDITY\_\_%' ESCAPE '\'
OR t.table_name LIKE 'DMF\_SOURCE\_\_%' ESCAPE '\'
OR t.table_name LIKE 'DMF\_DUPKEYS\_\_%' ESCAPE '\'
OR t.table_name LIKE '%\_RAW' ESCAPE '\'
OR t.table_name LIKE '%\_BKP' ESCAPE '\'
OR t.table_name LIKE '%\_STG' ESCAPE '\'
OR t.table_name LIKE '%\_REJECTED' ESCAPE '\'
OR t.table_name LIKE '%\_TMP' ESCAPE '\'
OR t.table_name LIKE '%\_CTRL' ESCAPE '\')
AND t.table_name <> 'DMF_CLEANUP_LIST'
AND NOT EXISTS (SELECT 1 FROM dmf_cleanup_list l WHERE l.table_name = t.table_name)
ORDER BY t.table_name;
-- ============================================================================
-- STEP 1 - Drop every table in the work list (one statement)
-- ============================================================================
-- Names are read into a collection first, so the DDL commits inside the loop
-- never run against an open cursor on dmf_cleanup_list.
--
-- No blank lines inside the block, deliberately: DBeaver reads "END LOOP;" as
-- closing the outer block, and would then treat a following blank line as a
-- statement delimiter and send only the first half (PLS-00103, end-of-file).
DECLARE
TYPE t_names IS TABLE OF dmf_cleanup_list.table_name%TYPE;
l_names t_names;
l_dropped PLS_INTEGER := 0;
l_absent PLS_INTEGER := 0;
l_failed PLS_INTEGER := 0;
-- SQLCODE and SQLERRM are PL/SQL functions and cannot be referenced inside a
-- SQL statement (ORA-00904). They are captured here first, then used.
l_code PLS_INTEGER;
l_err VARCHAR2(500);
l_tab VARCHAR2(128);
BEGIN
DBMS_OUTPUT.ENABLE(NULL);
SELECT table_name
BULK COLLECT INTO l_names
FROM dmf_cleanup_list
WHERE status IN ('PENDING','FAILED')
ORDER BY step_no, table_name;
DBMS_OUTPUT.PUT_LINE('-- ' || l_names.COUNT || ' tables to process');
FOR i IN 1 .. l_names.COUNT LOOP
l_tab := l_names(i);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || l_tab || ' CASCADE CONSTRAINTS PURGE';
l_dropped := l_dropped + 1;
UPDATE dmf_cleanup_list
SET status = 'DROPPED', message = NULL, processed_at = SYSTIMESTAMP
WHERE table_name = l_tab;
DBMS_OUTPUT.PUT_LINE('dropped : ' || l_tab);
EXCEPTION WHEN OTHERS THEN
l_code := SQLCODE;
l_err := SUBSTR(SQLERRM, 1, 500);
IF l_code = -942 THEN
l_absent := l_absent + 1;
UPDATE dmf_cleanup_list
SET status = 'ABSENT', message = NULL, processed_at = SYSTIMESTAMP
WHERE table_name = l_tab;
DBMS_OUTPUT.PUT_LINE('absent : ' || l_tab);
ELSE
l_failed := l_failed + 1;
UPDATE dmf_cleanup_list
SET status = 'FAILED', message = l_err, processed_at = SYSTIMESTAMP
WHERE table_name = l_tab;
DBMS_OUTPUT.PUT_LINE('FAILED : ' || l_tab || ' -> ' || l_err);
END IF;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('-- done: ' || l_dropped || ' dropped, '
|| l_absent || ' already absent, ' || l_failed || ' failed');
END;
/
-- ============================================================================
-- STEP 2 - Outcome
-- ============================================================================
-- Reads the status STEP 1 recorded, so it works in any client whether or not
-- DBMS_OUTPUT is displayed.
--
-- STEP 2a below is a SINGLE statement covering every check. Use it if your
-- client keeps merging adjacent statements (ORA-00933, "SQL command not
-- properly ended", pointing at the last token of an otherwise valid query).
-- The individual queries after it say the same thing, one question each.
SELECT step_no, category, status, COUNT(*) AS tables
FROM dmf_cleanup_list
GROUP BY step_no, category, status
ORDER BY step_no, status;
-- Anything that did not drop cleanly. Re-running STEP 1 retries these.
SELECT step_no, category, table_name, status, message, processed_at
FROM dmf_cleanup_list
WHERE status IN ('FAILED','PENDING')
ORDER BY step_no, table_name;
-- Cross-check against the data dictionary: should return no rows.
SELECT l.step_no, l.category, l.table_name, l.status
FROM dmf_cleanup_list l
JOIN user_tables t ON t.table_name = l.table_name
ORDER BY l.step_no, l.table_name;
-- Confirm the tables that must survive are still there.
SELECT table_name
FROM user_tables
WHERE table_name IN ('ITEM_MASTER_CTRL','STORE_ADD_CTRL','WH_CTRL','ITEM_LOC_CTRL',
'XREF_ITEM_IBC_RETAIL','XREF_WH','XREF_LOCATION','XREF_SUPS',
'XREF_TENDER_TYPE')
ORDER BY table_name;
-- ============================================================================
-- STEP 2a - Every check, as one statement
-- ============================================================================
-- Rows under A or B are problems. Rows under C are informational: tables in the
-- work list that were already gone before STEP 1 ran.
SELECT 'A. still present - did not drop' AS check_name,
l.step_no AS step_no,
l.category AS category,
l.table_name AS table_name,
l.status AS status
FROM dmf_cleanup_list l
JOIN user_tables t ON t.table_name = l.table_name
UNION ALL
SELECT 'B. protected table MISSING',
CAST(NULL AS NUMBER),
CAST(NULL AS VARCHAR2(40)),
x.column_value,
CAST(NULL AS VARCHAR2(10))
FROM TABLE(SYS.ODCIVARCHAR2LIST(
'ITEM_MASTER_CTRL','STORE_ADD_CTRL','WH_CTRL','ITEM_LOC_CTRL',
'XREF_ITEM_IBC_RETAIL','XREF_WH','XREF_LOCATION','XREF_SUPS',
'XREF_TENDER_TYPE')) x
WHERE NOT EXISTS (SELECT 1 FROM user_tables t WHERE t.table_name = x.column_value)
UNION ALL
SELECT 'C. was already absent',
l.step_no,
l.category,
l.table_name,
l.status
FROM dmf_cleanup_list l
WHERE l.status = 'ABSENT'
ORDER BY 1, 2, 4;
-- ============================================================================
-- STEP 3 - Remove the work list
-- ============================================================================
-- Only once STEP 2 looks right - the list is the record of what was dropped.
-- DROP TABLE dmf_cleanup_list PURGE;