Convert SQL Server to Oracle

-- Written by IntoSQL from a SQL Server 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 "ORDERS" (
    "ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1),
    "CUSTOMER_ID" NUMBER(10) NOT NULL,
    "STATUS" VARCHAR2(10 CHAR) DEFAULT 'pending' NOT NULL,
    "TOTAL" NUMBER(19,4) NOT NULL,
    "ITEMS" NUMBER(3) DEFAULT 1 NOT NULL,
    "ORDERED_AT" TIMESTAMP(0) NOT NULL,
    "SHIPPED_AT" TIMESTAMP(7),
    "NOTE" CLOB,
    "DETAILS" CLOB,
    "ORDER_REF" VARCHAR2(36 CHAR) NOT NULL,
    "TOTAL_WITH_TAX" NUMBER(12,2) GENERATED ALWAYS AS (CAST(CAST("TOTAL" * (1.16) AS NUMBER(12,2)) AS NUMBER(12,2))) VIRTUAL,
    CONSTRAINT "CK_ORDERS_ITEMS" CHECK ("ITEMS" >= 0),
    CONSTRAINT "CHECK2" CHECK ("STATUS" = 'cancelled' OR "STATUS" = 'shipped' OR "STATUS" = 'paid' OR "STATUS" = 'pending'),
    CONSTRAINT "CHECK3" CHECK ("TOTAL" >= (0))
);

CREATE TABLE "CUSTOMERS" (
    "ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1),
    "NAME" VARCHAR2(100 CHAR) NOT NULL,
    "EMAIL" VARCHAR2(200 CHAR) NOT NULL,
    "COUNTRY" VARCHAR2(2 CHAR) DEFAULT 'US' NOT NULL,
    "IS_ACTIVE" NUMBER(1) DEFAULT 1 NOT NULL,
    "SIGNED_UP" DATE,
    "CREATED_AT" TIMESTAMP(0) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    "ROW_VERSION" RAW(8),
    CONSTRAINT "CK_CUSTOMERS_IS_ACTIVE" CHECK ("IS_ACTIVE" IN (0, 1))
);

BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
1,
'Ana Martínez',
'ana@example.com',
'MX',
1,
DATE '2023-01-15',
TIMESTAMP '2023-01-15 04:24:00.0000000 -06:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
2,
'Zoë Müller',
'zoe@example.de',
'DE',
1,
DATE '2023-02-03',
TIMESTAMP '2023-02-03 09:11:42.0000000 +01:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
3,
'José Peña',
'jose@example.es',
'ES',
0,
DATE '2023-03-21',
TIMESTAMP '2023-03-21 20:05:17.0000000 +01:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
4,
'李雷',
'lilei@example.cn',
'CN',
1,
DATE '2023-05-09',
TIMESTAMP '2023-05-09 10:40:00.0000000 +08:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
5,
'Noah O''Brien',
'noah@example.ie',
'IE',
1,
NULL,
TIMESTAMP '2023-06-30 15:00:00.0000000 +01:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
6,
'Amélie Laurent',
'amelie@example.fr',
'FR',
1,
DATE '2024-01-02',
TIMESTAMP '2024-01-02 10:30:00.0000000 +01:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
7,
'Ravi Patel',
'ravi@example.in',
'IN',
0,
DATE '2024-02-29',
TIMESTAMP '2024-03-01 05:29:59.0000000 +05:30'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
8,
'Sofia Rossi 🌻',
'sofia@example.it',
'IT',
1,
DATE '2024-04-12',
TIMESTAMP '2024-04-12 14:12:12.0000000 +02:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
1,
1,
'shipped',
129.9000,
3,
TIMESTAMP '2024-03-01 10:00:00.0000000',
TIMESTAMP '2024-03-02 16:30:00.1234567',
NULL,
'{"gift": false}',
'0b7a4c2e-3f1d-4e8a-9c55-1a2b3c4d5e6f'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
2,
2,
'paid',
54.0000,
1,
TIMESTAMP '2024-03-02 11:15:00.0000000',
NULL,
'Gift wrap 🎁',
'{"gift": true, "wrap": "red"}',
'1c8b5d3f-4a2e-4f9b-8d66-2b3c4d5e6f70'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
3,
1,
'cancelled',
12.5000,
1,
TIMESTAMP '2024-03-03 09:45:00.0000000',
NULL,
'Customer changed their mind',
NULL,
'2d9c6e4a-5b3f-4a0c-9e77-3c4d5e6f7081'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
4,
4,
'shipped',
980.0000,
12,
TIMESTAMP '2024-03-05 02:10:00.0000000',
TIMESTAMP '2024-03-07 08:00:00.0000000',
'请小心轻放',
'{"fragile": true}',
'3e0d7f5b-6c4a-4b1d-8f88-4d5e6f708192'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
5,
5,
'pending',
45.7500,
2,
TIMESTAMP '2024-03-08 17:20:00.0000000',
NULL,
'Leave at the door; ring twice',
NULL,
'4f1e806c-7d5b-4c2e-9a99-5e6f708192a3'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
6,
6,
'shipped',
310.4000,
5,
TIMESTAMP '2024-03-10 13:00:00.0000000',
TIMESTAMP '2024-03-11 09:00:00.0000000',
'Fragile: "glass"',
'{"fragile": true}',
'50f2917d-8e6c-4d3f-8baa-6f708192a3b4'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
7,
8,
'paid',
76.2000,
2,
TIMESTAMP '2024-03-11 18:45:00.0000000',
NULL,
NULL,
NULL,
'6103a28e-9f7d-4e40-9cbb-708192a3b4c5'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
8,
2,
'shipped',
19.9900,
1,
TIMESTAMP '2024-03-12 08:00:00.0000000',
TIMESTAMP '9999-12-31 23:59:59.9999999',
'Shipped date set to the maximum by an old import',
NULL,
'7214b39f-a08e-4f51-8dcc-8192a3b4c5d6'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
9,
3,
'shipped',
64.0000,
2,
TIMESTAMP '2024-03-14 15:30:00.0000000',
TIMESTAMP '2024-03-15 10:00:00.0000000',
'Two parcels',
'{"parcels": [1, 2]}',
'8325c4a0-b19f-4062-9edd-92a3b4c5d6e7'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"DETAILS",
"ORDER_REF"
) VALUES (
10,
7,
'paid',
1499.0000,
1,
TIMESTAMP '2024-03-15 21:05:00.0000000',
NULL,
'C:\Users\ravi\invoice.pdf',
NULL,
'9436d5b1-c2a0-4173-8fee-a3b4c5d6e7f8'
);
COMMIT;
END;
/

-- Keys, indexes, foreign keys, counters and triggers, after the rows.
ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ID");
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("ID");
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "UQ_CUSTOMERS_EMAIL" UNIQUE ("EMAIL");
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 "FK_ORDERS" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID") ENABLE NOVALIDATE;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" ENABLE VALIDATE CONSTRAINT "FK_ORDERS"';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Foreign key ' || '"FK_ORDERS"' || ' 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 "ORDERS";
    IF next_id < 1 THEN
        next_id := 1;
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
DECLARE
    next_id NUMBER;
BEGIN
    SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "CUSTOMERS";
    IF next_id < 1 THEN
        next_id := 1;
    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';
DROP FUNCTION "INTOSQL_CLOB";
DROP FUNCTION "INTOSQL_BLOB";
COMMIT;
EXIT;