310 lines
12 KiB
SQL
310 lines
12 KiB
SQL
-- Random sample data for oracle_ecommerce_schema.sql.
|
|
-- Run once, as the same schema owner, after creating the schema in an empty schema.
|
|
-- The large audit_events load writes exactly 2 GiB of ASCII details payload
|
|
-- (2,147,483,648 bytes); table/index allocation and other rows add Oracle overhead.
|
|
-- Adjust c_target_payload_bytes or c_payload_chars below to change the load size.
|
|
|
|
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
|
|
SET DEFINE OFF
|
|
SET SERVEROUTPUT ON
|
|
|
|
DECLARE
|
|
l_line_id NUMBER := 0;
|
|
l_purchase_line_id NUMBER := 0;
|
|
l_journal_line_id NUMBER := 0;
|
|
l_journal_amount NUMBER(14,2);
|
|
l_batch_size PLS_INTEGER := 5000;
|
|
l_batch_rows PLS_INTEGER;
|
|
l_offset NUMBER;
|
|
l_rows NUMBER;
|
|
c_payload_chars CONSTANT PLS_INTEGER := 2000;
|
|
c_target_payload_bytes CONSTANT NUMBER := 2147483648;
|
|
BEGIN
|
|
DBMS_RANDOM.SEED(20260927);
|
|
|
|
INSERT INTO currencies (currency_code, currency_name, currency_symbol)
|
|
VALUES ('USD', 'US Dollar', '$');
|
|
INSERT INTO currencies (currency_code, currency_name, currency_symbol)
|
|
VALUES ('EUR', 'Euro', 'EUR');
|
|
INSERT INTO currencies (currency_code, currency_name, currency_symbol)
|
|
VALUES ('GBP', 'Pound Sterling', 'GBP');
|
|
|
|
INSERT INTO sales_channels (channel_name) VALUES ('Web Store');
|
|
INSERT INTO sales_channels (channel_name) VALUES ('Mobile App');
|
|
INSERT INTO sales_channels (channel_name) VALUES ('Marketplace');
|
|
INSERT INTO sales_channels (channel_name) VALUES ('Retail POS');
|
|
|
|
FOR i IN 1..1000 LOOP
|
|
INSERT INTO customers (
|
|
email, first_name, last_name, phone_number, customer_since, customer_status
|
|
) VALUES (
|
|
'customer' || TO_CHAR(i) || '@example.test',
|
|
'First' || TO_CHAR(i),
|
|
'Last' || TO_CHAR(i),
|
|
'+1-555-' || TO_CHAR(1000000 + i),
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 2000)),
|
|
CASE WHEN DBMS_RANDOM.VALUE(0, 1) < 0.97 THEN 'ACTIVE' ELSE 'INACTIVE' END
|
|
);
|
|
|
|
INSERT INTO customer_addresses (
|
|
customer_id, address_type, address_line1, city, region, postal_code, country_code, is_default
|
|
) VALUES (
|
|
i, 'BOTH', TO_CHAR(100 + i) || ' Commerce Street',
|
|
'City' || TO_CHAR(MOD(i, 100) + 1), 'Region ' || TO_CHAR(MOD(i, 20) + 1),
|
|
TO_CHAR(10000 + i), 'US', 'Y'
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..50 LOOP
|
|
INSERT INTO suppliers (supplier_name, email, country_code, supplier_status)
|
|
VALUES (
|
|
'Supplier ' || TO_CHAR(i), 'supplier' || TO_CHAR(i) || '@example.test',
|
|
CASE WHEN MOD(i, 3) = 0 THEN 'GB' WHEN MOD(i, 3) = 1 THEN 'US' ELSE 'DE' END,
|
|
'ACTIVE'
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..30 LOOP
|
|
INSERT INTO product_categories (category_name)
|
|
VALUES ('Category ' || TO_CHAR(i));
|
|
END LOOP;
|
|
|
|
FOR i IN 1..500 LOOP
|
|
INSERT INTO products (
|
|
category_id, supplier_id, sku, product_name, unit_price, product_status
|
|
) VALUES (
|
|
MOD(i - 1, 30) + 1, MOD(i - 1, 50) + 1,
|
|
'SKU-' || LPAD(TO_CHAR(i), 6, '0'),
|
|
'Catalog Product ' || TO_CHAR(i),
|
|
ROUND(DBMS_RANDOM.VALUE(5, 500), 2), 'ACTIVE'
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..10 LOOP
|
|
INSERT INTO warehouses (warehouse_name, country_code)
|
|
VALUES (
|
|
'Warehouse ' || TO_CHAR(i),
|
|
CASE WHEN MOD(i, 2) = 0 THEN 'US' ELSE 'DE' END
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..5000 LOOP
|
|
DECLARE
|
|
l_on_hand NUMBER(12,2) := TRUNC(DBMS_RANDOM.VALUE(10, 1000));
|
|
BEGIN
|
|
INSERT INTO inventory_balances (
|
|
product_id, warehouse_id, quantity_on_hand, quantity_reserved, last_counted_at
|
|
) VALUES (
|
|
MOD(i - 1, 500) + 1,
|
|
TRUNC((i - 1) / 500) + 1,
|
|
l_on_hand,
|
|
TRUNC(DBMS_RANDOM.VALUE(0, l_on_hand + 1)),
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 30))
|
|
);
|
|
END;
|
|
END LOOP;
|
|
|
|
FOR i IN 1..10 LOOP
|
|
INSERT INTO gl_accounts (account_code, account_name, account_type)
|
|
VALUES (
|
|
CASE i
|
|
WHEN 1 THEN '1000' WHEN 2 THEN '1010' WHEN 3 THEN '1100'
|
|
WHEN 4 THEN '2000' WHEN 5 THEN '2100' WHEN 6 THEN '3000'
|
|
WHEN 7 THEN '4000' WHEN 8 THEN '4100' WHEN 9 THEN '5000'
|
|
ELSE '5100'
|
|
END,
|
|
CASE i
|
|
WHEN 1 THEN 'Cash' WHEN 2 THEN 'Payment Clearing' WHEN 3 THEN 'Inventory'
|
|
WHEN 4 THEN 'Accounts Payable' WHEN 5 THEN 'Tax Payable' WHEN 6 THEN 'Equity'
|
|
WHEN 7 THEN 'Product Revenue' WHEN 8 THEN 'Shipping Revenue'
|
|
WHEN 9 THEN 'Cost of Goods Sold' ELSE 'Shipping Expense'
|
|
END,
|
|
CASE
|
|
WHEN i <= 3 THEN 'ASSET'
|
|
WHEN i <= 5 THEN 'LIABILITY'
|
|
WHEN i = 6 THEN 'EQUITY'
|
|
WHEN i <= 8 THEN 'REVENUE'
|
|
ELSE 'EXPENSE'
|
|
END
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..1000 LOOP
|
|
INSERT INTO sales_orders (
|
|
order_date, customer_id, channel_id, currency_id,
|
|
billing_address_id, shipping_address_id, order_status,
|
|
subtotal, tax_amount, shipping_amount
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
i, MOD(i - 1, 4) + 1, MOD(i - 1, 3) + 1, i, i,
|
|
CASE WHEN MOD(i, 12) = 0 THEN 'CANCELLED'
|
|
WHEN MOD(i, 5) = 0 THEN 'FULFILLED'
|
|
ELSE 'PAID' END,
|
|
ROUND(DBMS_RANDOM.VALUE(25, 1200), 2),
|
|
ROUND(DBMS_RANDOM.VALUE(1, 100), 2),
|
|
ROUND(DBMS_RANDOM.VALUE(0, 30), 2)
|
|
);
|
|
|
|
FOR line_no IN 1..2 LOOP
|
|
l_line_id := l_line_id + 1;
|
|
INSERT INTO sales_order_lines (
|
|
order_id, product_id, line_number, quantity, unit_price, discount_amount
|
|
) VALUES (
|
|
i, MOD(i * 2 + line_no - 2, 500) + 1, line_no,
|
|
TRUNC(DBMS_RANDOM.VALUE(1, 5)),
|
|
ROUND(DBMS_RANDOM.VALUE(5, 500), 2),
|
|
ROUND(DBMS_RANDOM.VALUE(0, 10), 2)
|
|
);
|
|
END LOOP;
|
|
|
|
INSERT INTO payments (
|
|
payment_date, order_id, currency_id, payment_method,
|
|
payment_status, amount, provider_reference
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
i, MOD(i - 1, 3) + 1,
|
|
CASE MOD(i, 4) WHEN 0 THEN 'BANK' WHEN 1 THEN 'CARD'
|
|
WHEN 2 THEN 'WALLET' ELSE 'CASH' END,
|
|
CASE WHEN MOD(i, 17) = 0 THEN 'FAILED' ELSE 'CAPTURED' END,
|
|
ROUND(DBMS_RANDOM.VALUE(25, 1300), 2),
|
|
'PAY-' || TO_CHAR(i) || '-' || DBMS_RANDOM.STRING('X', 12)
|
|
);
|
|
|
|
INSERT INTO shipments (
|
|
shipped_at, order_id, warehouse_id, tracking_number, carrier_name, shipment_status
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
i, MOD(i - 1, 10) + 1, 'TRK-' || LPAD(TO_CHAR(i), 10, '0'),
|
|
CASE MOD(i, 3) WHEN 0 THEN 'Parcel One' WHEN 1 THEN 'RapidShip' ELSE 'Global Freight' END,
|
|
CASE WHEN MOD(i, 9) = 0 THEN 'IN_TRANSIT' ELSE 'DELIVERED' END
|
|
);
|
|
|
|
INSERT INTO shipment_items (shipment_id, order_line_id, quantity)
|
|
VALUES (i, (i - 1) * 2 + 1, 1);
|
|
INSERT INTO shipment_items (shipment_id, order_line_id, quantity)
|
|
VALUES (i, (i - 1) * 2 + 2, 1);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..100 LOOP
|
|
INSERT INTO purchase_orders (
|
|
ordered_at, supplier_id, warehouse_id, currency_id, purchase_status
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
MOD(i - 1, 50) + 1, MOD(i - 1, 10) + 1, MOD(i - 1, 3) + 1,
|
|
CASE WHEN MOD(i, 6) = 0 THEN 'ORDERED' ELSE 'RECEIVED' END
|
|
);
|
|
|
|
FOR line_no IN 1..2 LOOP
|
|
l_purchase_line_id := l_purchase_line_id + 1;
|
|
INSERT INTO purchase_order_lines (
|
|
purchase_order_id, product_id, line_number, quantity, unit_cost
|
|
) VALUES (
|
|
i, MOD(i * 2 + line_no - 2, 500) + 1, line_no,
|
|
TRUNC(DBMS_RANDOM.VALUE(10, 200)),
|
|
ROUND(DBMS_RANDOM.VALUE(3, 300), 2)
|
|
);
|
|
END LOOP;
|
|
END LOOP;
|
|
|
|
FOR i IN 1..2000 LOOP
|
|
INSERT INTO inventory_movements (
|
|
movement_at, product_id, warehouse_id, order_line_id,
|
|
movement_type, quantity_delta, reference_note
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
MOD(i - 1, 500) + 1, MOD(TRUNC((i - 1) / 500), 10) + 1,
|
|
MOD(i - 1, 2000) + 1,
|
|
'SALE', -1, 'Generated fulfillment movement'
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..1000 LOOP
|
|
l_journal_amount := ROUND(DBMS_RANDOM.VALUE(25, 1300), 2);
|
|
INSERT INTO journal_entries (
|
|
posted_at, currency_id, source_type, source_id, entry_status, memo
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
MOD(i - 1, 3) + 1, 'SALES_ORDER', i, 'POSTED',
|
|
'Generated balanced sales posting ' || TO_CHAR(i)
|
|
);
|
|
|
|
l_journal_line_id := l_journal_line_id + 1;
|
|
INSERT INTO journal_lines (
|
|
journal_entry_id, account_id, line_number, debit_amount, credit_amount, line_memo
|
|
) VALUES (
|
|
i, 1, 1, l_journal_amount, 0, 'Cash or clearing debit'
|
|
);
|
|
|
|
l_journal_line_id := l_journal_line_id + 1;
|
|
INSERT INTO journal_lines (
|
|
journal_entry_id, account_id, line_number, debit_amount, credit_amount, line_memo
|
|
) VALUES (
|
|
i, 7, 2, 0, l_journal_amount, 'Sales revenue credit'
|
|
);
|
|
END LOOP;
|
|
|
|
FOR i IN 1..100 LOOP
|
|
INSERT INTO returns (
|
|
returned_at, order_line_id, customer_id, return_status, quantity, reason_code
|
|
) VALUES (
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
MOD(i - 1, 2000) + 1, TRUNC((MOD(i - 1, 2000)) / 2) + 1,
|
|
CASE WHEN MOD(i, 4) = 0 THEN 'REFUNDED' ELSE 'RECEIVED' END,
|
|
1, CASE MOD(i, 3) WHEN 0 THEN 'DAMAGED' WHEN 1 THEN 'NOT_AS_DESCRIBED' ELSE 'CHANGED_MIND' END
|
|
);
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
DBMS_OUTPUT.PUT_LINE('Reference and transactional sample data inserted.');
|
|
|
|
l_rows := CEIL(c_target_payload_bytes / c_payload_chars);
|
|
l_offset := 1;
|
|
WHILE l_offset <= l_rows LOOP
|
|
l_batch_rows := LEAST(l_batch_size, l_rows - l_offset + 1);
|
|
|
|
INSERT INTO audit_events (
|
|
event_at, actor_customer_id, event_type, source_ip, details
|
|
)
|
|
SELECT
|
|
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(0, 365)),
|
|
CASE WHEN DBMS_RANDOM.VALUE(0, 1) < 0.8
|
|
THEN TRUNC(DBMS_RANDOM.VALUE(1, 1001)) ELSE NULL END,
|
|
CASE MOD(LEVEL, 5)
|
|
WHEN 0 THEN 'CUSTOMER_LOGIN'
|
|
WHEN 1 THEN 'PRODUCT_VIEW'
|
|
WHEN 2 THEN 'ORDER_UPDATE'
|
|
WHEN 3 THEN 'PAYMENT_EVENT'
|
|
ELSE 'ADDRESS_CHANGE'
|
|
END,
|
|
'192.0.2.' || TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 255))),
|
|
DBMS_RANDOM.STRING(
|
|
'A',
|
|
LEAST(
|
|
c_payload_chars,
|
|
c_target_payload_bytes -
|
|
((l_offset + LEVEL - 2) * c_payload_chars)
|
|
)
|
|
)
|
|
FROM dual
|
|
CONNECT BY LEVEL <= l_batch_rows;
|
|
|
|
COMMIT;
|
|
l_offset := l_offset + l_batch_rows;
|
|
IF MOD(l_offset - 1, 50000) < l_batch_rows THEN
|
|
DBMS_OUTPUT.PUT_LINE(
|
|
'Audit events generated: ' || TO_CHAR(LEAST(l_offset - 1, l_rows)) ||
|
|
' / ' || TO_CHAR(l_rows)
|
|
);
|
|
END IF;
|
|
END LOOP;
|
|
|
|
DBMS_OUTPUT.PUT_LINE(
|
|
'Completed audit payload: ' || TO_CHAR(l_rows) ||
|
|
' rows x ' || TO_CHAR(c_payload_chars) ||
|
|
' characters (~2 GiB), plus Oracle row and index overhead.'
|
|
);
|
|
END;
|
|
/
|
|
|
|
PROMPT Random dataset generation complete.
|