Convert SQLite to Oracle

-- Written by IntoSQL from a SQLite 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(19) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 9),
    "NAME" VARCHAR2(4000 CHAR) NOT NULL,
    "EMAIL" VARCHAR2(750 CHAR) NOT NULL,
    "COUNTRY" VARCHAR2(4000 CHAR) DEFAULT 'US' NOT NULL,
    "IS_ACTIVE" NUMBER(1) DEFAULT 1 NOT NULL,
    "SIGNED_UP" DATE,
    "CREATED_AT" TIMESTAMP(0) DEFAULT LOCALTIMESTAMP NOT NULL,
    CONSTRAINT "CK_CUSTOMERS_IS_ACTIVE" CHECK ("IS_ACTIVE" IN (0, 1)),
    CONSTRAINT "CHECK1" CHECK (LENGTH("COUNTRY") = 2)
);

CREATE TABLE "ORDERS" (
    "ID" NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 11),
    "CUSTOMER_ID" NUMBER(19) NOT NULL,
    "STATUS" VARCHAR2(750 CHAR) DEFAULT 'pending' NOT NULL,
    "TOTAL" NUMBER(10,2) NOT NULL,
    "ITEMS" NUMBER(10) DEFAULT 1 NOT NULL,
    "ORDERED_AT" TIMESTAMP(0) NOT NULL,
    "SHIPPED_AT" TIMESTAMP(0),
    "NOTE" VARCHAR2(4000 CHAR),
    "RECEIPT" BLOB,
    CONSTRAINT "CHECK1_2" CHECK ("STATUS" IN ('pending', 'paid', 'shipped', 'cancelled'))
);

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 10:24: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 08:11:42'
);
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 19:05:17'
);
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 02:40: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 14:00: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 09:30: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-02-29 23:59:59'
);
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 12:12:12'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
1,
1,
'shipped',
129.9,
3,
TIMESTAMP '2024-03-01 10:00:00',
TIMESTAMP '2024-03-02 16:30:00',
NULL,
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
2,
2,
'paid',
54,
1,
TIMESTAMP '2024-03-02 11:15:00',
NULL,
'Gift wrap 🎁',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
3,
1,
'cancelled',
12.5,
1,
TIMESTAMP '2024-03-03 09:45:00',
NULL,
'Customer changed their mind',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
4,
4,
'shipped',
980,
12,
TIMESTAMP '2024-03-05 02:10:00',
TIMESTAMP '2024-03-07 08:00:00',
'请小心轻放',
HEXTORAW('25504446')
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
5,
5,
'pending',
45.75,
2,
TIMESTAMP '2024-03-08 17:20:00',
NULL,
'Leave at the door;'
|| CHR(10)
|| 'ring twice',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
6,
6,
'shipped',
310.4,
5,
TIMESTAMP '2024-03-10 13:00:00',
TIMESTAMP '2024-03-11 09:00:00',
'Fragile: "glass"',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
7,
8,
'paid',
76.2,
2,
TIMESTAMP '2024-03-11 18:45:00',
NULL,
NULL,
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
8,
2,
'paid',
19.99,
1,
TIMESTAMP '2024-03-12 08:00:00',
NULL,
'Paid at the counter',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
9,
3,
'shipped',
64,
2,
TIMESTAMP '2024-03-14 15:30:00',
NULL,
'Shipping date never recorded',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"RECEIPT"
) VALUES (
10,
7,
'paid',
1499,
1,
TIMESTAMP '2024-03-15 21:05:00',
NULL,
'C:\Users\ravi\invoice.pdf',
NULL
);
COMMIT;
END;
/

-- Keys, indexes, foreign keys, counters and triggers, after the rows.
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("ID");
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "UQ_CUSTOMERS_EMAIL" UNIQUE ("EMAIL");
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 "FK_ORDERS" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID") ON DELETE CASCADE 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 "CUSTOMERS";
    IF next_id < 9 THEN
        next_id := 9;
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE "CUSTOMERS" 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 "ORDERS";
    IF next_id < 11 THEN
        next_id := 11;
    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;