Oracle のエクスポートを古いまたは新しい Oracle へ移す

-- Written by IntoSQL from a Oracle 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 SEQUENCE "INVOICE_NUMBERS" START WITH 1020 INCREMENT BY 1 MINVALUE 1;
CREATE TABLE "CUSTOMERS" (
    "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1),
    "NAME" VARCHAR2(100 CHAR) NOT NULL,
    "EMAIL" VARCHAR2(150 CHAR) NOT NULL,
    "COUNTRY" CHAR(2 BYTE) DEFAULT 'US' NOT NULL,
    "IS_ACTIVE" NUMBER(1,0) DEFAULT 1 NOT NULL,
    "SIGNED_UP" DATE,
    "CREATED_AT" TIMESTAMP(0) DEFAULT LOCALTIMESTAMP NOT NULL,
    CONSTRAINT "CHECK1" CHECK ("IS_ACTIVE" IN (0, 1))
);

CREATE TABLE "ORDERS" (
    "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1),
    "CUSTOMER_ID" NUMBER(10,0) NOT NULL,
    "INVOICE" NUMBER(10,0) DEFAULT ("INVOICE_NUMBERS".NEXTVAL),
    "STATUS" VARCHAR2(10 CHAR) DEFAULT 'pending' NOT NULL,
    "TOTAL" NUMBER(10,2) NOT NULL,
    "ITEMS" NUMBER(5,0) DEFAULT 1 NOT NULL,
    "ORDERED_AT" DATE NOT NULL,
    "SHIPPED_AT" DATE,
    "NOTE" VARCHAR2(255 CHAR),
    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,
TIMESTAMP '2023-01-15 00:00:00',
TIMESTAMP '2023-01-15 10:24:00.000000000'
);
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,
TIMESTAMP '2023-02-03 00:00:00',
TIMESTAMP '2023-02-03 08:11:42.000000000'
);
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,
TIMESTAMP '2023-03-21 00:00:00',
TIMESTAMP '2023-03-21 19:05:17.000000000'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
4,
'李雷',
'lilei@example.cn',
'CN',
1,
TIMESTAMP '2023-05-09 00:00:00',
TIMESTAMP '2023-05-09 02:40:00.000000000'
);
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.000000000'
);
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,
TIMESTAMP '2024-01-02 00:00:00',
TIMESTAMP '2024-01-02 09:30:00.000000000'
);
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,
TIMESTAMP '2024-02-29 00:00:00',
TIMESTAMP '2024-02-29 23:59:59.000000000'
);
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,
TIMESTAMP '2024-04-12 00:00:00',
TIMESTAMP '2024-04-12 12:12:12.000000000'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
1,
1,
1000,
'shipped',
129.9,
3,
TIMESTAMP '2024-03-01 10:00:00',
TIMESTAMP '2024-03-02 16:30:00',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
2,
2,
1001,
'paid',
54,
1,
TIMESTAMP '2024-03-02 11:15:00',
NULL,
'Gift wrap 🎁'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
3,
1,
1002,
'cancelled',
12.5,
1,
TIMESTAMP '2024-03-03 09:45:00',
NULL,
'Customer changed their mind'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
4,
4,
1003,
'shipped',
980,
12,
TIMESTAMP '2024-03-05 02:10:00',
TIMESTAMP '2024-03-07 08:00:00',
'请小心轻放'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
5,
5,
1004,
'pending',
45.75,
2,
TIMESTAMP '2024-03-08 17:20:00',
NULL,
'Leave at the door; ring twice'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
6,
6,
1005,
'shipped',
310.4,
5,
TIMESTAMP '2024-03-10 13:00:00',
TIMESTAMP '2024-03-11 09:00:00',
'Fragile: "glass"'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
7,
8,
1006,
'paid',
76.2,
2,
TIMESTAMP '2024-03-11 18:45:00',
NULL,
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
8,
2,
1007,
'paid',
19.99,
1,
TIMESTAMP '2024-03-12 08:00:00',
NULL,
'Paid at the counter'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
9,
3,
1008,
'shipped',
64,
2,
TIMESTAMP '2024-03-14 15:30:00',
NULL,
'Shipping date never recorded'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"INVOICE",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE"
) VALUES (
10,
7,
1009,
'paid',
1499,
1,
TIMESTAMP '2024-03-15 21:05:00',
NULL,
'C:\Users\ravi\invoice.pdf'
);
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_CUSTOMERS_EMAIL_UK" UNIQUE ("EMAIL");
ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ID");
CREATE INDEX "IX_ORDERS_ORDERS_STATUS_DATE_IX" ON "ORDERS" ("STATUS", "ORDERED_AT");
ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_CUSTOMER_FK" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID") ENABLE NOVALIDATE;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" ENABLE VALIDATE CONSTRAINT "ORDERS_CUSTOMER_FK"';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Foreign key ' || '"ORDERS_CUSTOMER_FK"' || ' 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 < 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';
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;
/
DROP FUNCTION "INTOSQL_CLOB";
DROP FUNCTION "INTOSQL_BLOB";
COMMIT;
EXIT;