Convert PostgreSQL to Oracle

-- Written by IntoSQL from a PostgreSQL dump. Load it with: NLS_LANG=.AL32UTF8 sqlplus -S -L <user>/<password>@<database> @<file>
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
SET DEFINE OFF;
SET SQLBLANKLINES ON;
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION "INTOSQL_CLOB" (p SYS.ODCIVARCHAR2LIST) RETURN CLOB IS
    r CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(r, TRUE);
    FOR i IN 1 .. p.COUNT LOOP
        IF p(i) IS NOT NULL THEN
            DBMS_LOB.WRITEAPPEND(r, LENGTH2(p(i)), p(i));
        END IF;
    END LOOP;
    RETURN r;
END;
/
CREATE OR REPLACE FUNCTION "INTOSQL_BLOB" (p SYS.ODCIVARCHAR2LIST) RETURN BLOB IS
    r BLOB;
    piece RAW(2000);
BEGIN
    DBMS_LOB.CREATETEMPORARY(r, TRUE);
    FOR i IN 1 .. p.COUNT LOOP
        piece := HEXTORAW(p(i));
        DBMS_LOB.WRITEAPPEND(r, UTL_RAW.LENGTH(piece), piece);
    END LOOP;
    RETURN r;
END;
/

CREATE TABLE "CUSTOMERS" (
    "ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1001),
    "NAME" VARCHAR2(4000 CHAR) NOT NULL,
    "EMAIL" VARCHAR2(750 CHAR) NOT NULL,
    "COUNTRY" VARCHAR2(2 CHAR) DEFAULT 'US' NOT NULL,
    "IS_ACTIVE" NUMBER(1) DEFAULT 1 NOT NULL,
    "TAGS" CLOB NOT NULL,
    "SIGNED_UP" DATE,
    "CREATED_AT" TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT "CK_CUSTOMERS_IS_ACTIVE" CHECK ("IS_ACTIVE" IN (0, 1))
);

CREATE TABLE "ORDERS" (
    "ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 5001),
    "CUSTOMER_ID" NUMBER(10) NOT NULL,
    "STATUS" VARCHAR2(9 CHAR) DEFAULT 'pending' NOT NULL,
    "TOTAL" NUMBER(10,2) NOT NULL,
    "ITEMS" NUMBER(5) DEFAULT 1 NOT NULL,
    "ORDERED_AT" TIMESTAMP(6) NOT NULL,
    "SHIPPED_AT" TIMESTAMP(6),
    "NOTE" VARCHAR2(4000 CHAR),
    "DETAILS" CLOB,
    "DELIVERY" VARCHAR2(100 CHAR),
    CONSTRAINT "CK_ORDERS_STATUS" CHECK ("STATUS" IN ('pending', 'paid', 'shipped', 'cancelled')),
    CONSTRAINT "ORDERS_TOTAL_CHECK" CHECK ("TOTAL" >= (0))
);

BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
1,
'Ana Martínez',
'ana@example.com',
'MX',
1,
'["vip"]',
DATE '2023-01-15',
TIMESTAMP '2023-01-15 10:24:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
2,
'Zoë Müller',
'zoe@example.de',
'DE',
1,
'[]',
DATE '2023-02-03',
TIMESTAMP '2023-02-03 08:11:42 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
3,
'José Peña',
'jose@example.es',
'ES',
0,
'["wholesale","late payer"]',
DATE '2023-03-21',
TIMESTAMP '2023-03-21 19:05:17 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
4,
'李雷',
'lilei@example.cn',
'CN',
1,
'[]',
DATE '2023-05-09',
TIMESTAMP '2023-05-09 02:40:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
5,
'Noah O''Brien',
'noah@example.ie',
'IE',
1,
'[]',
NULL,
TIMESTAMP '2023-06-30 14:00:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
6,
'Amélie Laurent',
'amelie@example.fr',
'FR',
1,
'["vip"]',
DATE '2024-01-02',
TIMESTAMP '2024-01-02 09:30:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
7,
'Ravi Patel',
'ravi@example.in',
'IN',
0,
'[]',
DATE '2024-02-29',
TIMESTAMP '2024-02-29 23:59:59 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"TAGS",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
8,
'Sofia Rossi 🌻',
'sofia@example.it',
'IT',
1,
'[]',
DATE '2024-04-12',
TIMESTAMP '2024-04-12 12:12:12 +00:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
1,
1,
'shipped',
129.90,
3,
TIMESTAMP '2024-03-01 10:00:00',
TIMESTAMP '2024-03-02 16:30:00',
NULL,
'{"gift": false}',
'1 day 06:30:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
2,
2,
'paid',
54.00,
1,
TIMESTAMP '2024-03-02 11:15:00',
NULL,
'Gift wrap 🎁',
'{"gift": true, "wrap": "red"}',
NULL
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
3,
1,
'cancelled',
12.50,
1,
TIMESTAMP '2024-03-03 09:45:00',
NULL,
'Customer changed their mind',
NULL,
NULL
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
4,
4,
'shipped',
980.00,
12,
TIMESTAMP '2024-03-05 02:10:00',
TIMESTAMP '2024-03-07 08:00:00',
'请小心轻放',
'{"fragile": true}',
'2 days 05:50:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
5,
5,
'pending',
45.75,
2,
TIMESTAMP '2024-03-08 17:20:00',
NULL,
'Leave at the door; ring twice',
NULL,
NULL
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
6,
6,
'shipped',
310.40,
5,
TIMESTAMP '2024-03-10 13:00:00',
TIMESTAMP '2024-03-11 09:00:00',
'Fragile: "glass"',
'{"fragile": true}',
'20:00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
7,
8,
'paid',
76.20,
2,
TIMESTAMP '2024-03-11 18:45:00',
NULL,
NULL,
NULL,
NULL
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
8,
2,
'shipped',
19.99,
1,
TIMESTAMP '2024-03-12 08:00:00',
NULL,
'Shipped date set to infinity by an old import',
NULL,
NULL
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
9,
3,
'shipped',
64.00,
2,
TIMESTAMP '2024-03-14 15:30:00',
TIMESTAMP '2024-03-15 10:00:00',
'Two parcels',
'{"parcels": [1, 2]}',
'18:30:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"DELIVERY"
) VALUES (
10,
7,
'paid',
1499.00,
1,
TIMESTAMP '2024-03-15 21:05:00',
NULL,
'C:\Users\ravi\invoice.pdf',
NULL,
NULL
);
COMMIT;
END;
/

-- Keys, indexes, foreign keys, counters and triggers, after the rows.
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "UQ_CUSTOMERS_CUSTOMERS_EMAIL_KEY" UNIQUE ("EMAIL");
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("ID");
ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ID");
CREATE INDEX "IX_ORDERS_IDX_ORDERS_CUSTOMER" ON "ORDERS" ("CUSTOMER_ID");
CREATE INDEX "IX_ORDERS_IDX_ORDERS_STATUS_DATE" ON "ORDERS" ("STATUS", "ORDERED_AT");
ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_CUSTOMER_ID_FKEY" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID") ENABLE NOVALIDATE;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" ENABLE VALIDATE CONSTRAINT "ORDERS_CUSTOMER_ID_FKEY"';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Foreign key ' || '"ORDERS_CUSTOMER_ID_FKEY"' || ' has rows without a parent: it stays unvalidated, and new rows are checked.');
END;
/
DECLARE
    next_id NUMBER;
BEGIN
    SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "CUSTOMERS";
    IF next_id < 1001 THEN
        next_id := 1001;
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE "CUSTOMERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
COMMENT ON TABLE "CUSTOMERS" IS 'People who bought at least once';
DECLARE
    next_id NUMBER;
BEGIN
    SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "ORDERS";
    IF next_id < 5001 THEN
        next_id := 5001;
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
DROP FUNCTION "INTOSQL_CLOB";
DROP FUNCTION "INTOSQL_BLOB";
COMMIT;
EXIT;