OCIクラウド移行ガイドとは

オンプレミスやAWSなど、複数のプラットフォームからOracle Cloud Infrastructureへの移行プロジェクトに取り組んでいるクラウドエンジニア（@araidon,@kazunishi,@yama6,@tktk2712,@ritokuna,@nomu_kyou）による、OCI移行手順をまとめたシリーズ記事です。
各回、サンプルワークロードから対象サービスを取り上げ、移行手順をガイドいたします。
まとめ記事は以下になります。

移行するサービス：RDS for PostgreSQL

今回、移行対象とするのはAmazon RDS for PostgreSQLです。
Autonomous Databaseで利用可能なDBMS_CLOUD_MIGRATIONパッケージを用いて、Amazon RDS for PostgreSQLをAutonomous Databaseに移行する手順を解説します。

DBMS_CLOUD_MIGRATIONパッケージ

DBMS_CLOUD_MIGRATIONパッケージを使用すると、PostgreSQLからOracle SQLへのSQLコードの変換が容易になります。
このパッケージからはいくつかのプロシージャが提供されています。今回は、その中でもDBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャを使用します。DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャは、オブジェクト・ストレージのPostgreSQLファイル内のSQL文を変換し、Oracle SQLを含む新しいファイルを生成します。

一言で言うと、RDS for PostgreSQLでDumpしたPostgreSQLの.sqlファイルをOracleの.sqlファイルに変換してくれるという代物です。

DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャ

このプロシージャにはDBエンジン差異による一部制約があるため、生成されたファイルに記載されたSQL文を実行し、DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャで変換されたSQL文の整合性を検証します。

Oracle以外のSQLからOracle SQLへの移行および変換の制限

検証データ

移行対象とするサンプルデータとして、PostgreSQL Tutorialが提供するDVDレンタルデータベースを使用します。

このデータベースには下記オブジェクトが含まれています。

No	オブジェクト	数量
1	sequences	13
2	tables	15
3	trigger	15
4	views	7
5	functions	9
6	domain	1
-	SUM	60

検証 Summary

検証手順が長くなったため、先に検証結果を記載します。

No	オブジェクト	全数量	改修数量	結果	コメント
1	sequences	13	0	🙆‍♂️	---
2	tables	15	2	⚠️	address.phone,film.fulltext 一部改修
3	trigger	15	1	⚠️	film_fulltext_trigger 書き直し
4	views	7	2	⚠️	customer_list,nicer_but_slower_film_list 一部改修
5	functions	9	9	❌	GET_CUSTOMER_BALANCE,REWARDS_REPORT 未対応
6	domain	1	1	❌	domainに直接対応するオブジェクトがないため、OracleDBのtypeで実現

全60オブジェクトのうち、75%にあたる45オブジェクトについては変換されたSQL文のまま実行することができましたが、残り25%のオブジェクトについては改修もしくは1から書き直しが必要となりました。
functionsについては9本中、9本が書き直しとなりました。
うち、の2つについてはサンプルデータ起因でPostgreSQL側でもうまく動作確認ができなかったため改修検証の対象外としました。

前提条件

DBバージョン

RDS for PostgreSQL：14.11
Autonomous Database：19c

SQLの実行について

実行方法

検証目的のため、Oracle SQLに変換された.sqlファイルから、該当部分のSQL文を逐一抜き出して実行しています。本番の移行方法で採択されなさそうな回りくどい方法となっている点、SQLの実行順がDumpfileの記載順となっていない点をご容赦ください。

オブジェクトの作成順

オブジェクト作成の実行順は下記の通りです。
SEQUENCE → TYPE(domain) → TABLE → INDEX → TRIGGER → VIEW → FUNCTION

スキーマ名の変更　"public"→"postgres"

サンプルデータのスキーマ名となっている"public"がプロシージャで変換されません。"public"はOracleの予約語のため、そのまま実行するとエラーが発生します。そのため、Oracle Databaseで"postgres"というユーザーを作成した上で、Dumpfileでpublicスキーマを指定している部分はpostgresに置換して実行しています。

移行手順

EC2作成
RDS for PostgreSQL作成
サンプルデータのインポート
サンプルデータのエクスポート
Object Storageの作成
Autonomous Databaseの作成
資格証明の作成
DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャの実行
変換されたSQL文の実行
1. SEQUENCE
2. TABLE
3. INDEX
4. TRIGGER
5. VIEW
6. TYPE
7. FUNCTION
データロード
動作検証

1. EC2作成

RDS操作用のインスタンスとして、Amazon Linux2023でEC2を作成します。
作成方法は下記の記事等を参考にしてください。

EC2へはCloudShellから接続し、postgresql15をインストールしておきます。

$ sudo dnf install postgresql15

2. RDS for PostgreSQL作成

下記手順に概ね沿い、RDS for PostgreSQLを作成します。

EC2との接続オプションを選ぶと、自動的にセキュリティグループを設定してくれて便利ですね。
下記コマンドでEC2からRDSへ接続できれば成功です。

$ psql --host=test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=postgres

3. サンプルデータのインポート

下記サイトよりサンプルデータをダウンロードします。
今回の検証では、DVD レンタルデータベースを使用します。
https://neon.tech/postgresql/postgresql-getting-started/postgresql-sample-database

一度ローカルPCにデータをダウンロードし、cloudShellにアップロードします。
scpコマンドを使用し、CloudShellからEC2へファイルを転送します。

$ scp -i posgre-test.pem dvdrental.zip ec2-user@XXX.XX.XXX.XXX:/home/ec2-user/
dvdrental.zip 100%  538KB  66.7MB/s   00:00

RDSへ接続し、データのインポート前にdvdrentalという名前のDatabaseを作ります。

$ psql --host=test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=postgres
postgres=>create database dvdrental;

exitコマンドでEC2に戻り、pg_restoreコマンドでzipファイルからDBを作成します。

$ pg_restore -U postgres -h test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com -d dvdrental -Ft dvdrental.tar -v

再度RDSへ接続し、インポートデータを確認します。

$ psql --host=test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --dbname=postgres --username=postgres
postgres=> \c dvdrental
dvdrental=> dvdrental=#
dvdrental-> \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

4. サンプルデータのエクスポート

EC2上からpg_dumpコマンドで、メタデータをエクスポートします。

$ pg_dump -U postgres -h test-postgres.xxxxxx.ap-northeast-1.rds.amazonaws.com -s -E 'UTF8' -d dvdrental -f dvdrental_metadata.sql

データ本体は、のちにSQL Loaderでインポートします。
RDSに接続し、テーブル単位でcsvファイルに出力します。

dvdrental-> \copy ACTOR to ACTOR.csv with csv delimiter ',' force quote * null as '' header;
COPY 200
dvdrental-> \copy ADDRESS       to ADDRESS.csv      with csv delimiter ',' force quote * null as '' header;
COPY 603
dvdrental-> \copy CATEGORY      to CATEGORY.csv     with csv delimiter ',' force quote * null as '' header;
COPY 16
dvdrental-> \copy CITY          to CITY.csv         with csv delimiter ',' force quote * null as '' header;
COPY 600
dvdrental-> \copy COUNTRY       to COUNTRY.csv      with csv delimiter ',' force quote * null as '' header;
COPY 109
dvdrental-> \copy CUSTOMER      to CUSTOMER.csv     with csv delimiter ',' force quote * null as '' header;
COPY 599
dvdrental-> \copy FILM          to FILM.csv         with csv delimiter ',' force quote * null as '' header;
COPY 1000
dvdrental-> \copy FILM_ACTOR    to FILM_ACTOR.csv   with csv delimiter ',' force quote * null as '' header;
COPY 5462
dvdrental-> \copy FILM_CATEGORY to FILM_CATEGORY.csv with csv delimiter ',' force quote * null as '' header;
COPY 1000
dvdrental-> \copy INVENTORY     to INVENTORY.csv    with csv delimiter ',' force quote * null as '' header;
COPY 4581
dvdrental-> \copy LANGUAGE      to LANGUAGE.csv     with csv delimiter ',' force quote * null as '' header;
COPY 6
dvdrental-> \copy PAYMENT       to PAYMENT.csv      with csv delimiter ',' force quote * null as '' header;
COPY 14596
dvdrental-> \copy RENTAL        to RENTAL.csv       with csv delimiter ',' force quote * null as '' header;
COPY 16044
dvdrental-> \copy STAFF         to STAFF.csv        with csv delimiter ',' force quote * null as '' header;
COPY 2
dvdrental-> \copy STORE         to STORE.csv        with csv delimiter ',' force quote * null as '' header;
COPY 2

上記実行すると、EC2上にファイルが生成されます。
先ほどご紹介したscpコマンド等を使い、何らかの手段でこれらのエクスポートファイルをローカルPCに保存します。

5. Object Storageの作成

下記手順に沿い、Object Storageを作成します。

4でエクスポートしたデータ群をアップロードします。

アップロード後、オブジェクト一覧右のボタンを押下し、「オブジェクト詳細」の表示を押下してこれをコピーします。

のちのプロシージャ実行にて、オブジェクトのURIが必要となります。

6. Autonomous Databaseの作成

下記手順に従ってAutonomous Data Warehouseを作成します。

ワークロード・タイプには、トランザクション・ワークロードを対象とするAutonomous Transaction Processingを選択します。

データベース・バージョンは19cを選択しました。

ネットワーク・アクセスは、「すべての場所からのセキュア・アクセス」を選択ました。

Autonomous Databaseの作成ボタンを押下し、プロビジョニングの完了を確認します。

Autonomous Databaseの作成作業は以上です。

Autonomous Databaseへの接続

Autonomous Databaseの詳細画面から「データベース接続」を選択し、クライアント資格証明(ウォレット)をダウンロードします。

ウォレット保存先ディレクトリとして"/network/admin"を作成した上で、これをcloudshellにアップロードして展開します。

$ mkdir -p ~/network/admin

TNS_ADMIN 環境変数にウォレット保存先のパスを設定します。

$ export TNS_ADMIN=~/network/admin

viコマンドなどを使用し、sqlnet.oraにこの環境変数を設定します。

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=$TNS_ADMIN)))

環境変数”ORACLE_HOME”を設定します。

$  export ORACLE_HOME=~

sqlplusから接続します。

$ sqlplus admin/<password>@adb4psql_tp
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 27 12:58:04 2024
Version 19.10.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Fri Dec 27 2024 07:28:03 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0

SQL>

7. 資格証明の作成

5で作成したObject Storageへ、6で作成したAutonomous Databaseから操作を行うために権限を付与します。今回は、Object Storageへの操作権限を持つユーザーの認証トークンを使用して権限を付与しました。
リソース・プリンシパル等でも設定が可能です。詳しくは下記をご参照ください。

認証トークンの取得

OCIコンソール画面右上のユーザーアイコンを押下し、「ユーザー設定」を押下します。

画面遷移後、画面左下「リソース」のナビゲーションペインから「認証トークン」を選択します。

トークンの作成ボタンを押下し、説明を記述後、「トークンの作成」ボタンを押下します。

遷移後の画面で表示される認証トークンの文字列をコピーします。

認証トークンの設定

ユーザーの認証トークンをAutonomous Databaseに設定します。
権限付与には、DBMS_CLOUDパッケージのCREATE_CREDENTIALプロシージャを使用します。

SQL> BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( 
 credential_name => 'OCI_NATIVE_CRED', 
 username => '<ユーザー名>', 
 password => '<認証トークンの文字列>'
 );
 END;
/
PL/SQL procedure successfully completed.

これでAutonomous DatabaseにObject Storageへの操作権限が付与されました。
6で作成したバケットのURIを指定して、下記SQL文を実行した結果、アップロードしたファイル名が表示されれば権限付与は成功です。

SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(
NULL, 
'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<NameSpace名>/b/<Bucket名>/o/'
);
/

オブジェクトストレージへの DBMS_CLOUD アクセスのトラブルシューティングには下記の記事をご参照ください。

8. DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャの実行

本題である、PostgreSQLからOracleへSQLを変換するMIGRATE_FILEプロシージャを実行します。
実行前のObject Storageは、先ほどアップロードした"dvdrental_metadata.sql"のみ格納されている状態です。

6で取得したURIをlocation_uriに指定し、下記のようにプロシージャを実行します。

SQL> BEGIN DBMS_CLOUD_MIGRATION.MIGRATE_FILE ( 
 credential_name => 'OCI_NATIVE_CRED', 
 location_uri => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<NameSpace名>/b/<Bucket名>/o/dvdrental_metadata.sql', 
 source_db => 'POSTGRES'
 ); 
 END;
 /
 PL/SQL procedure successfully completed.

実行後、"dvdrental_metadata_oracle.sql"というファイルが追加されていることがわかります。
これがMIGRATE_FILEプロシージャによりOracle SQLへ変換された.sqlファイルとなります。

このファイルに記載されたSQL文を実行し、Autonomous Database上でデータベースを構築します。

9. 変換されたSQL文の実行

出力されたSQL文から、下記順でオブジェクトを作成していきます。
SEQUENCE → TYPE(domain) → TABLE → INDEX → TRIGGER → VIEW → FUNCTION

冒頭のサマリ通り、一部オブジェクトについては改修または書き直しが必要となったため、該当オブジェクトについては注釈を示します。ここまでの手順はdvdrentalのデータベースを使用すれば共通のアウトプットとなるので、そちらと見比べながら実行してください。

9-1. Database Actionsの開始

以降のSQL文はDatabase Actionsを利用してブラウザから実行していきます。
Autonomous Databaseの詳細画面から、「データベース・アクション」を押下してください。
DBにログインされていれば、別タブで「Oracle Database Actions」が立ち上がります。
ログインされていない場合は、Autonomous Database作成時に設定したパスワードを利用し、ユーザー名：adminで認証します。

SQLの画面からSQL文を実行していきます。

9-2. ユーザ作成

CREATE USER postgres IDENTIFIED BY <パスワード>;
ALTER USER postgres quota unlimited on DATA;

postgresというユーザを作成します。
また、postgresに対するリソースの制限を解除します。

9-3. シーケンスの作成

シーケンスを作成します。
"dvdrental_metadata_oracle.sql"におけるユーザ名を"public"に置換し、実行します。

CREATE SEQUENCE postgres.customer_customer_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

残り12シーケンスについても同様に実行します。

シーケンス作成

CREATE SEQUENCE postgres.actor_actor_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

CREATE SEQUENCE postgres.category_category_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

CREATE SEQUENCE postgres.film_film_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 2;

CREATE SEQUENCE postgres.address_address_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 2;

CREATE SEQUENCE postgres.city_city_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 2;

CREATE SEQUENCE postgres.country_country_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

CREATE SEQUENCE postgres.inventory_inventory_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;


CREATE SEQUENCE postgres.language_language_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

CREATE SEQUENCE postgres.payment_payment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

CREATE SEQUENCE postgres.rental_rental_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

CREATE SEQUENCE postgres.staff_staff_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;
    
CREATE SEQUENCE postgres.store_store_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE 
    NOMAXVALUE 
    CACHE 2;

実行後、下記SQL文を実行し、シーケンスが13個確認できれば成功です。

select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='SEQUENCE' order by 1;

9-4. タイプの作成

タイプ（PostgreSQLで言うところのドメイン）を作成します。

CREATE or REPLACE TYPE postgres."year" AS OBJECT (year_type NUMBER(10));

9-5. テーブルの作成

テーブルを作成します。
"dvdrental_metadata_oracle.sql"におけるユーザ名を"public"に置換し、実行します。
下記テーブルについては、のちに実データをロードする際にエラーが発生します。
これは実データでNULL値が入っているためです。ALTER文で部分的にNULL値を許可します。

CREATE TABLE postgres.address (
    address_id NUMBER(10) DEFAULT postgres.address_address_id_seq.nextval NOT NULL,
    address VARCHAR2(50) NOT NULL,
    address2 VARCHAR2(50),
    district VARCHAR2(20) NOT NULL,
    city_id NUMBER(5) NOT NULL,
    postal_code VARCHAR2(10),
    phone VARCHAR2(20) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
alter table postgres.address modify phone NULL;

CREATE TABLE postgres.film (
    film_id NUMBER(10) DEFAULT postgres.film_film_id_seq.nextval NOT NULL,
    title VARCHAR2(255) NOT NULL,
    description VARCHAR2(4000),
    release_year postgres."year",
    language_id NUMBER(5) NOT NULL,
    rental_duration NUMBER(5) DEFAULT 3 NOT NULL,
    rental_rate NUMBER(4,2) DEFAULT 4.99 NOT NULL,
    length NUMBER(5),
    replacement_cost NUMBER(5,2) DEFAULT 19.99 NOT NULL,
    rating VARCHAR2(4000) CHECK (rating IN ('G','PG','PG-13','R','NC-17')) ,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL,
    special_features CLOB,
    fulltext CLOB NOT NULL
);
alter table postgres.film modify fulltext NULL;

残りの13テーブルについても同様にCRETAE文を実行します。

テーブル作成

CREATE TABLE postgres.customer (
    customer_id NUMBER(10) DEFAULT postgres.customer_customer_id_seq.nextval NOT NULL,
    store_id NUMBER(5) NOT NULL,
    first_name VARCHAR2(45) NOT NULL,
    last_name VARCHAR2(45) NOT NULL,
    email VARCHAR2(50),
    address_id NUMBER(5) NOT NULL,
    activebool CHAR(1) DEFAULT 1 NOT NULL,
    create_date date DEFAULT CAST((CAST(SYSDATE AS date)) AS date) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP,
    active NUMBER(10)
);

CREATE TABLE postgres.actor (
    actor_id NUMBER(10) DEFAULT postgres.actor_actor_id_seq.nextval NOT NULL,
    first_name VARCHAR2(45) NOT NULL,
    last_name VARCHAR2(45) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE TABLE postgres.category (
    category_id NUMBER(10) DEFAULT postgres.category_category_id_seq.nextval NOT NULL,
    name VARCHAR2(25) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
); 

CREATE TABLE postgres.film_actor (
    actor_id NUMBER(5) NOT NULL,
    film_id NUMBER(5) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
); 
CREATE TABLE postgres.film_category (
    film_id NUMBER(5) NOT NULL,
    category_id NUMBER(5) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.city (
    city_id NUMBER(10) DEFAULT postgres.city_city_id_seq.nextval NOT NULL,
    city VARCHAR2(50) NOT NULL,
    country_id NUMBER(5) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.country (
    country_id NUMBER(10) DEFAULT postgres.country_country_id_seq.nextval NOT NULL,
    country VARCHAR2(50) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.inventory (
    inventory_id NUMBER(10) DEFAULT postgres.inventory_inventory_id_seq.nextval NOT NULL,
    film_id NUMBER(5) NOT NULL,
    store_id NUMBER(5) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.language (
    language_id NUMBER(10) DEFAULT postgres.language_language_id_seq.nextval NOT NULL,
    name character(20) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
); 
CREATE TABLE postgres.payment (
    payment_id NUMBER(10) DEFAULT postgres.payment_payment_id_seq.nextval NOT NULL,
    customer_id NUMBER(5) NOT NULL,
    staff_id NUMBER(5) NOT NULL,
    rental_id NUMBER(10) NOT NULL,
    amount NUMBER(5,2) NOT NULL,
    payment_date timestamp NOT NULL
);
CREATE TABLE postgres.rental (
    rental_id NUMBER(10) DEFAULT postgres.rental_rental_id_seq.nextval NOT NULL,
    rental_date timestamp NOT NULL,
    inventory_id NUMBER(10) NOT NULL,
    customer_id NUMBER(5) NOT NULL,
    return_date timestamp,
    staff_id NUMBER(5) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.store (
    store_id NUMBER(10) DEFAULT postgres.store_store_id_seq.nextval NOT NULL,
    manager_staff_id NUMBER(5) NOT NULL,
    address_id NUMBER(5) NOT NULL,
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE postgres.staff (
    staff_id NUMBER(10) DEFAULT postgres.staff_staff_id_seq.nextval NOT NULL,
    first_name VARCHAR2(45) NOT NULL,
    last_name VARCHAR2(45) NOT NULL,
    address_id NUMBER(5) NOT NULL,
    email VARCHAR2(50),
    store_id NUMBER(5) NOT NULL,
    active CHAR(1) DEFAULT 1 NOT NULL,
    username VARCHAR2(16) NOT NULL,
    password VARCHAR2(40),
    last_update timestamp DEFAULT SYSTIMESTAMP NOT NULL,
    picture BLOB
);

実行後、下記SQL文を実行し、テーブルが15個確認できれば成功です。

select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='TABLE' order by 1;

9-6. キーの作成

主キー、外部キーを作成します。

主キー作成

ALTER TABLE postgres.actor ADD CONSTRAINT actor_pkey PRIMARY KEY (actor_id);

ALTER TABLE postgres.address
ADD CONSTRAINT address_pkey PRIMARY KEY (address_id);

ALTER TABLE postgres.category
ADD CONSTRAINT category_pkey PRIMARY KEY (category_id);

ALTER TABLE postgres.city
ADD CONSTRAINT city_pkey PRIMARY KEY (city_id);

ALTER TABLE postgres.country
ADD CONSTRAINT country_pkey PRIMARY KEY (country_id);

ALTER TABLE postgres.customer
ADD CONSTRAINT customer_pkey PRIMARY KEY (customer_id);
ALTER TABLE postgres.film_actor
ADD CONSTRAINT film_actor_pkey PRIMARY KEY (actor_id, film_id);

ALTER TABLE postgres.film_category
ADD CONSTRAINT film_category_pkey PRIMARY KEY (film_id, category_id);

ALTER TABLE postgres.film
ADD CONSTRAINT film_pkey PRIMARY KEY (film_id);

ALTER TABLE postgres.inventory
ADD CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id);

ALTER TABLE postgres.language
ADD CONSTRAINT language_pkey PRIMARY KEY (language_id);

ALTER TABLE postgres.payment
ADD CONSTRAINT payment_pkey PRIMARY KEY (payment_id);

ALTER TABLE postgres.rental
ADD CONSTRAINT rental_pkey PRIMARY KEY (rental_id);

ALTER TABLE postgres.staff
ADD CONSTRAINT staff_pkey PRIMARY KEY (staff_id);

ALTER TABLE postgres.store
ADD CONSTRAINT store_pkey PRIMARY KEY (store_id);

実行後、15個の主キーが確認できれば成功です。

select constraint_name,constraint_type,table_name,status from dba_constraints where owner='POSTGRES' and constraint_type='P' order by 1;

外部キーの作成

ALTER TABLE  postgres.customer
    ADD CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id) REFERENCES postgres.address(address_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.film_actor
    ADD CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES postgres.actor(actor_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.film_actor
    ADD CONSTRAINT film_actor_film_id_fkey FOREIGN KEY (film_id) REFERENCES postgres.film(film_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.film_category
    ADD CONSTRAINT film_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES postgres.category(category_id)  ON DELETE CASCADE;
ALTER TABLE  postgres.film_category
    ADD CONSTRAINT film_category_film_id_fkey FOREIGN KEY (film_id) REFERENCES postgres.film(film_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.film
    ADD CONSTRAINT film_language_id_fkey FOREIGN KEY (language_id) REFERENCES postgres.language(language_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.address
    ADD CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES postgres.city(city_id);

ALTER TABLE  postgres.city
    ADD CONSTRAINT fk_city FOREIGN KEY (country_id) REFERENCES postgres.country(country_id);
    
ALTER TABLE  postgres.inventory
    ADD CONSTRAINT inventory_film_id_fkey FOREIGN KEY (film_id) REFERENCES postgres.film(film_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.payment
    ADD CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES postgres.customer(customer_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.payment
    ADD CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id) REFERENCES postgres.rental(rental_id)  ON DELETE SET NULL;

ALTER TABLE  postgres.payment
    ADD CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id) REFERENCES postgres.staff(staff_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.rental
    ADD CONSTRAINT rental_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES postgres.customer(customer_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.rental
    ADD CONSTRAINT rental_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES postgres.inventory(inventory_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.rental
    ADD CONSTRAINT rental_staff_id_key FOREIGN KEY (staff_id) REFERENCES postgres.staff(staff_id);

ALTER TABLE  postgres.staff
    ADD CONSTRAINT staff_address_id_fkey FOREIGN KEY (address_id) REFERENCES postgres.address(address_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.store
    ADD CONSTRAINT store_address_id_fkey FOREIGN KEY (address_id) REFERENCES postgres.address(address_id)  ON DELETE CASCADE;

ALTER TABLE  postgres.store
    ADD CONSTRAINT store_manager_staff_id_fkey FOREIGN KEY (manager_staff_id) REFERENCES postgres.staff(staff_id)  ON DELETE CASCADE;

実行後、18個の外部キーが確認できれば成功です。

select constraint_name,constraint_type,table_name,status from dba_constraints where owner='POSTGRES' and  constraint_type='R' ORDER by 1;

9-7. インデックスの作成

インデックスを作成します。

インデックス作成


CREATE INDEX postgres.idx_actor_last_name ON  postgres.actor  (last_name);

CREATE INDEX postgres.idx_fk_address_id ON  postgres.customer  (address_id);

CREATE INDEX postgres.idx_fk_city_id ON  postgres.address  (city_id);

CREATE INDEX postgres.idx_fk_country_id ON  postgres.city  (country_id);

CREATE INDEX postgres.idx_fk_customer_id ON  postgres.payment  (customer_id);

CREATE INDEX postgres.idx_fk_film_id ON  postgres.film_actor  (film_id);

CREATE INDEX postgres.idx_fk_inventory_id ON  postgres.rental  (inventory_id);

CREATE INDEX postgres.idx_fk_language_id ON  postgres.film  (language_id);


CREATE INDEX postgres.idx_fk_rental_id ON  postgres.payment  (rental_id);

CREATE INDEX postgres.idx_fk_staff_id ON  postgres.payment  (staff_id);

CREATE INDEX postgres.idx_fk_store_id ON  postgres.customer  (store_id);
CREATE INDEX postgres.idx_last_name ON  postgres.customer  (last_name);
CREATE INDEX postgres.idx_store_id_film_id ON  postgres.inventory  (store_id, film_id);

CREATE INDEX postgres.idx_title ON  postgres.film  (title);

CREATE UNIQUE INDEX postgres.idx_unq_manager_staff_id ON  postgres.store  (manager_staff_id);

CREATE UNIQUE INDEX postgres.idx_unq_rental_rental_date_inventory_id_customer_id ON  postgres.rental  (rental_date, inventory_id, customer_id);

実行後、16個のインデックスが確認できれば成功です。

select index_name,index_type,table_name,status from dba_indexes where owner='POSTGRES' and index_name like 'IDX_%' order by 1;

9-8. トリガーの作成

トリガーを作成します。

トリガー作成

CREATE OR REPLACE TRIGGER postgres.last_updated BEFORE UPDATE ON postgres.actor FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_1 BEFORE UPDATE ON postgres.address FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;   
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_2 BEFORE UPDATE ON  postgres.category FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_3 BEFORE UPDATE ON  postgres.city FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_4 BEFORE UPDATE ON  postgres.country FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_5 BEFORE UPDATE ON  postgres.customer FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;
CREATE OR REPLACE TRIGGER postgres.last_updated_6 BEFORE UPDATE ON  postgres.film FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_7 BEFORE UPDATE ON  postgres.film_actor FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_8 BEFORE UPDATE ON  postgres.film_category FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_9 BEFORE UPDATE ON  postgres.inventory FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_10 BEFORE UPDATE ON  postgres.language FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_11 BEFORE UPDATE ON  postgres.rental FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_12 BEFORE UPDATE ON  postgres.staff FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.last_updated_13 BEFORE UPDATE ON  postgres.store FOR EACH ROW
BEGIN
    :NEW.last_update := SYSTIMESTAMP;
END;

CREATE OR REPLACE TRIGGER postgres.film_fulltext_trigger
BEFORE INSERT OR UPDATE ON postgres.film
FOR EACH ROW
BEGIN
    :NEW.fulltext := :NEW.title || ' ' || :NEW.description;
END;

実行後、15個のトリガーが確認できれば成功です。

select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='TRIGGER' order by 1;

9-9. ビューの作成

ビューを作成します。

ビュー作成

CREATE OR REPLACE TYPE postgres.group_concat_aggr AS OBJECT (
    aggregated_text CLOB,
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER
);
CREATE OR REPLACE FUNCTION postgres.group_concat (input CLOB) RETURN CLOB AGGREGATE USING group_concat_aggr;

CREATE OR REPLACE TYPE BODY postgres.group_concat_aggr AS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER IS
    BEGIN
        sctx := group_concat_aggr(NULL);
        RETURN ODCIConst.Success;
    END;
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER IS
    BEGIN
        IF self.aggregated_text IS NULL THEN
            self.aggregated_text := value;
        ELSE
            self.aggregated_text := self.aggregated_text || ', ' || value;
        END IF;
        RETURN ODCIConst.Success;
    END;
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER IS
    BEGIN
        IF ctx2.aggregated_text IS NOT NULL THEN
            IF self.aggregated_text IS NULL THEN
                self.aggregated_text := ctx2.aggregated_text;
            ELSE
                self.aggregated_text := self.aggregated_text || ', ' || ctx2.aggregated_text;
            END IF;
        END IF;
        RETURN ODCIConst.Success;
    END;
    MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnValue := self.aggregated_text;
        RETURN ODCIConst.Success;
    END;
END;

CREATE VIEW postgres.actor_info AS
 SELECT a.actor_id,
    a.first_name,
    a.last_name,
    postgres.group_concat(DISTINCT ((CAST((c.name) AS VARCHAR2(4000)) || CAST(': ' AS VARCHAR2(4000))) || ( SELECT postgres.group_concat(CAST((f.title) AS VARCHAR2(4000))) AS group_concat
           FROM ((postgres.film f
             JOIN postgres.film_category fc_1 ON ((f.film_id = fc_1.film_id)))
             JOIN postgres.film_actor fa_1 ON ((f.film_id = fa_1.film_id)))
          WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id))
          GROUP BY fa_1.actor_id))) AS film_info
   FROM (((postgres.actor a
     LEFT JOIN postgres.film_actor fa ON ((a.actor_id = fa.actor_id)))
     LEFT JOIN postgres.film_category fc ON ((fa.film_id = fc.film_id)))
     LEFT JOIN postgres.category c ON ((fc.category_id = c.category_id)))
  GROUP BY a.actor_id, a.first_name, a.last_name;

CREATE VIEW postgres.customer_list AS
 SELECT cu.customer_id AS id,
    ((CAST((cu.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((cu.last_name) AS VARCHAR2(4000))) AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool= 't' THEN CAST('active' AS VARCHAR2(4000))
            ELSE CAST('' AS VARCHAR2(4000))
        END AS notes,
    cu.store_id AS sid
   FROM (((postgres.customer cu
     JOIN postgres.address a ON ((cu.address_id = a.address_id)))
     JOIN postgres.city ON ((a.city_id = city.city_id)))
     JOIN postgres.country ON ((city.country_id = country.country_id)));


CREATE VIEW postgres.film_list AS
 SELECT film.film_id AS fid,
    film.title,
    film.description,
    category.name AS category,
    film.rental_rate AS price,
    film.length,
    film.rating,
    postgres.group_concat(((CAST((actor.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((actor.last_name) AS VARCHAR2(4000)))) AS actors
   FROM ((((postgres.category
     LEFT JOIN postgres.film_category ON ((category.category_id = film_category.category_id)))
     LEFT JOIN postgres.film ON ((film_category.film_id = film.film_id)))
     JOIN postgres.film_actor ON ((film.film_id = film_actor.film_id)))
     JOIN postgres.actor ON ((film_actor.actor_id = actor.actor_id)))
  GROUP BY film.film_id, film.title, film.description, category.name, film.rental_rate, film.length, film.rating;


CREATE VIEW postgres.nicer_but_slower_film_list AS
 SELECT film.film_id AS fid,
    film.title,
    film.description,
    category.name AS category,
    film.rental_rate AS price,
    film.length,
    film.rating,
    postgres.group_concat((((upper(SUBSTR(CAST((actor.first_name) AS VARCHAR2(4000)), 1, 1)) || lower(SUBSTR(CAST((actor.first_name) AS VARCHAR2(4000)), 2))) || upper(SUBSTR(CAST((actor.last_name) AS VARCHAR2(4000)), 1, 1))) || lower(SUBSTR(CAST((actor.last_name) AS VARCHAR2(4000)), 2)))) AS actors
   FROM ((((postgres.category
     LEFT JOIN postgres.film_category ON ((category.category_id = film_category.category_id)))
     LEFT JOIN postgres.film ON ((film_category.film_id = film.film_id)))
     JOIN postgres.film_actor ON ((film.film_id = film_actor.film_id)))
     JOIN postgres.actor ON ((film_actor.actor_id = actor.actor_id)))
  GROUP BY film.film_id, film.title, film.description, category.name, film.rental_rate, film.length, film.rating;

CREATE VIEW postgres.sales_by_film_category AS
 SELECT c.name AS category,
    sum(p.amount) AS total_sales
   FROM (((((postgres.payment p
     JOIN postgres.rental r ON ((p.rental_id = r.rental_id)))
     JOIN postgres.inventory i ON ((r.inventory_id = i.inventory_id)))
     JOIN postgres.film f ON ((i.film_id = f.film_id)))
     JOIN postgres.film_category fc ON ((f.film_id = fc.film_id)))
     JOIN postgres.category c ON ((fc.category_id = c.category_id)))
  GROUP BY c.name
  ORDER BY (sum(p.amount)) DESC;

CREATE VIEW postgres.sales_by_store AS
 SELECT ((CAST((c.city) AS VARCHAR2(4000)) || CAST(',' AS VARCHAR2(4000))) || CAST((cy.country) AS VARCHAR2(4000))) AS store,
    ((CAST((m.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((m.last_name) AS VARCHAR2(4000))) AS manager,
    sum(p.amount) AS total_sales
   FROM (((((((postgres.payment p
     JOIN postgres.rental r ON ((p.rental_id = r.rental_id)))
     JOIN postgres.inventory i ON ((r.inventory_id = i.inventory_id)))
     JOIN postgres.store s ON ((i.store_id = s.store_id)))
     JOIN postgres.address a ON ((s.address_id = a.address_id)))
     JOIN postgres.city c ON ((a.city_id = c.city_id)))
     JOIN postgres.country cy ON ((c.country_id = cy.country_id)))
     JOIN postgres.staff m ON ((s.manager_staff_id = m.staff_id)))
  GROUP BY cy.country, c.city, s.store_id, m.first_name, m.last_name
  ORDER BY cy.country, c.city;

CREATE VIEW postgres.staff_list AS
 SELECT s.staff_id AS id,
    ((CAST((s.first_name) AS VARCHAR2(4000)) || CAST(' ' AS VARCHAR2(4000))) || CAST((s.last_name) AS VARCHAR2(4000))) AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
    s.store_id AS sid
   FROM (((postgres.staff s
     JOIN postgres.address a ON ((s.address_id = a.address_id)))
     JOIN postgres.city ON ((a.city_id = city.city_id)))
     JOIN postgres.country ON ((city.country_id = country.country_id)));

実行後、7個のビューが確認できれば成功です。

select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='VIEW' order by 1 ;

9-10. ファンクションの作成

ファンクションを作成します。

ファンクション作成

CREATE OR REPLACE TYPE postgres.group_concat_aggr AS OBJECT (
    aggregated_text CLOB,
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY postgres.group_concat_aggr AS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT group_concat_aggr) RETURN NUMBER IS
    BEGIN
        sctx := group_concat_aggr(NULL);
        RETURN ODCIConst.Success;
    END;
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT group_concat_aggr, value IN CLOB) RETURN NUMBER IS
    BEGIN
        IF self.aggregated_text IS NULL THEN
            self.aggregated_text := value;
        ELSE
            self.aggregated_text := self.aggregated_text || ', ' || value;
        END IF;
        RETURN ODCIConst.Success;
    END;
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT group_concat_aggr, ctx2 IN group_concat_aggr) RETURN NUMBER IS
    BEGIN
        IF   ctx2.aggregated_text IS NOT NULL THEN
            IF self.aggregated_text IS NULL THEN
             self.aggregated_text := ctx2.aggregated_text;
            ELSE
                self.aggregated_text := self.aggregated_text || ', ' || ctx2.aggregated_text;
            END IF;
        END IF;
        RETURN ODCIConst.Success;
    END;
    MEMBER FUNCTION ODCIAggregateTerminate(self IN group_concat_aggr, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnValue := self.aggregated_text;
        RETURN  ODCIConst.Success;
    END;
END;

CREATE OR REPLACE FUNCTION postgres.group_concat (input CLOB) RETURN CLOB AGGREGATE USING group_concat_aggr;

CREATE OR REPLACE FUNCTION postgres.inventory_in_stock (p_inventory_id NUMBER) RETURN CHAR IS
    v_rentals NUMBER(10) := 0;
    v_out     NUMBER(10) := 0;
BEGIN
    SELECT COUNT(*) INTO v_rentals
    FROM postgres.rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
        RETURN '1';
    END IF;

    SELECT COUNT(rental.rental_id) INTO v_out
    FROM postgres.inventory inventory
    LEFT JOIN postgres.rental rental ON inventory.inventory_id = rental.inventory_id
    WHERE inventory.inventory_id = p_inventory_id
      AND rental.return_date IS NULL;

    IF v_out > 0 THEN
        RETURN '0';
    ELSE
        RETURN '1';
    END IF;
END;

CREATE OR REPLACE FUNCTION postgres.film_in_stock(p_film_id NUMBER, p_store_id NUMBER, p_film_count OUT NUMBER) return sys_refcursor is 
    l_ret_var sys_refcursor;
 BEGIN
     OPEN l_ret_var FOR SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id) = 1;
dbms_sql.return_result(l_ret_var);
END; 

CREATE OR REPLACE FUNCTION postgres.film_not_in_stock(p_film_id NUMBER, p_store_id NUMBER, p_film_count OUT NUMBER) return sys_refcursor is 
    l_ret_var sys_refcursor;
 BEGIN
    OPEN l_ret_var FOR SELECT inventory_id
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND NOT inventory_in_stock(inventory_id) = 0;
dbms_sql.return_result(l_ret_var);
END;

CREATE OR REPLACE FUNCTION postgres.inventory_held_by_customer(p_inventory_id NUMBER) RETURN NUMBER IS
    v_customer_id NUMBER(10);
BEGIN
    
    SELECT customer_id
    INTO v_customer_id
    FROM postgres.rental
    WHERE return_date IS NULL
      AND inventory_id = p_inventory_id;


    RETURN v_customer_id;

EXCEPTION
    -- Handle the case where no rows are found (e.g., inventory is not held by any customer)
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

CREATE OR REPLACE FUNCTION postgres.last_day(f_param1 TIMESTAMP) RETURN DATE IS
    l_ret_var DATE;  
BEGIN
    l_ret_var := LAST_DAY(f_param1);

    RETURN l_ret_var;
END;

CREATE OR REPLACE  FUNCTION postgres.rewards_report (
    min_monthly_purchases NUMBER,
    min_dollar_amount_purchased NUMBER
) RETURN SYS_REFCURSOR IS
    l_ret_var SYS_REFCURSOR;
    last_month_start DATE;
    last_month_end DATE;
BEGIN

    IF min_monthly_purchases <= 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Minimum monthly purchases parameter must be greater than 0');
    END IF;

    IF min_dollar_amount_purchased <= 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Minimum monthly dollar amount purchased parameter must be greater than $0.00');
    END IF;

    last_month_start := TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM');
    last_month_end := LAST_DAY(last_month_start);

    OPEN l_ret_var FOR
        SELECT c.*
        FROM customer c
        WHERE c.customer_id IN (
            SELECT p.customer_id
            FROM payment p
            WHERE p.payment_date BETWEEN last_month_start AND last_month_end
            GROUP BY p.customer_id
            HAVING SUM(p.amount) > min_dollar_amount_purchased
               AND COUNT(p.customer_id) > min_monthly_purchases
        );

    RETURN l_ret_var;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20003, 'No customers found matching the criteria.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20004, 'Unexpected error occurred: ' || SQLERRM);
END rewards_report;

CREATE OR REPLACE FUNCTION postgres.get_customer_balance (p_customer_id NUMBER, p_effective_date DATE) RETURN NUMBER IS

    v_rentfees NUMBER(5, 2);
    v_overfees NUMBER(10);
    v_payments NUMBER(5, 2);
    v_replacement_cost NUMBER(5, 2);
    v_rental_duration_interval INTERVAL DAY TO SECOND;
BEGIN
    SELECT NVL(SUM(film.rental_rate), 0) INTO v_rentfees
    FROM film
    JOIN inventory ON film.film_id = inventory.film_id
    JOIN rental ON inventory.inventory_id = rental.inventory_id
    WHERE rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

    SELECT NVL(SUM(CASE
        WHEN (rental.return_date - rental.rental_date) > (film.rental_duration * INTERVAL '1' DAY) THEN
            EXTRACT(DAY FROM (rental.return_date - rental.rental_date) - (film.rental_duration * INTERVAL '1' DAY))
        ELSE 0 END), 0) INTO v_overfees
    FROM rental
    JOIN inventory ON rental.inventory_id = inventory.inventory_id
    JOIN film ON inventory.film_id = film.film_id
    WHERE rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

    SELECT NVL(SUM(CASE
        WHEN (rental.return_date - rental.rental_date) > (film.rental_duration * 2 * INTERVAL '1' DAY) THEN
            film.replacement_cost
        ELSE 0 END), 0) INTO v_replacement_cost
    FROM rental
    JOIN inventory ON rental.inventory_id = inventory.inventory_id
    JOIN film ON inventory.film_id = film.film_id
    WHERE rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

    SELECT NVL(SUM(payment.amount), 0) INTO v_payments
    FROM payment
    WHERE payment.payment_date <= p_effective_date
      AND payment.customer_id = p_customer_id;

    RETURN v_rentfees + v_overfees + v_replacement_cost - v_payments;
END;

実行後、8個のファンクションが確認できれば成功です。

select object_name,object_type,status from dba_objects where owner='POSTGRES' and object_type='FUNCTION' order by 1;

9-11. 主な変更点

No	オブジェクト	オブジェクト名	変更点
1	TYPE	postgres."year"	「year 」 →「"year" 」へ変更
2	TABLE	address.phone	実データにNULLあるので、NULL許可。
3	TABLE	film.fulltext	同上
4	VIEW	customer_list	「cu.activebool=1」→「cu.activebool= 't‘」へ変更
5	VIEW	nicer_but_slower_film_list	「“substring”」 →「SUBSTR」へ変更
6	TRIGGER	CREATE TRIGGER film_fulltext_trigger	tsvector_update_triggerが変換されていないため、書き直し
7	FUNCTION	inventory_in_stock	「CREATE TABLE」→「SELECT」へ変更
8	FUNCTION	inventory_held_by_customer	「CREATE TABLE」→「SELECT」へ変更
9	FUNCTION	get_customer_balance	「CAST(‘1 day’ AS interval DAY(5) TO SECOND) 」→「INTERVAL ‘1’ DAY」へ変更
10	FUNCTION	postgres.film_in_stock	「inventory_in_stock(inventory_id) 」→「inventory_in_stock(inventory_id) = 1’へ変更
11	FUNCTION	postgres.film_not_in_stock	「inventory_in_stock(inventory_id) 」→「inventory_in_stock(inventory_id) = 0’へ変更
12	FUNCTION	postgres.group_concat	書き直し

10. データロード

SQL*Loaderを使用してデータをロードします。

10-1. SQL*Loaderの設定

SQL*Loaderの設定

$ wget https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-tools-linux.x64-21.4.0.0.0dbru.zip
$ wget https://download.oracle.com/otn_software/linux/instantclient/2111000/instantclient-basic-linux.x64-21.11.0.0.0dbru.zip
$ unzip instantclient-tools-linux.x64-21.4.0.0.0dbru.zip
$ unzip instantclient-basic-linux.x64-21.11.0.0.0dbru.zip
$ cd /home/<OCIユーザ名>/network/admin
$ mv Wallet_adb4psql.zip ../instantclient_21_11/network/admin
$ cd ../instantclient_21_11/network/admin
$ unzip Wallet_adb4psql.zip

viで編集

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/<OCIユーザ名>/network/admin/instantclient_21_11/network/admin")))
SSL_SERVER_DN_MATCH=yes

パスの設定

$ export TNS_ADMIN=/home/<OCIユーザ名>/network/admin/instantclient_21_11/network/admin
$ export PATH=$PATH:/home/<OCIユーザ名>/network/admin/instantclient_21_4/

10-2. ロードファイルの準備

viコマンド等を使用し、ロード用のファイルを作成します。

actor.ctl

LOAD DATA
INFILE 'ACTOR.csv'
INTO TABLE postgres.ACTOR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  ACTOR_ID "TO_NUMBER(:ACTOR_ID, '99999')",
  FIRST_NAME,
  LAST_NAME,
  LAST_UPDATE TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6"
)

10-3. データロードの実行

ロード実行前に、外部制約キーが設定されているテーブルはエラーが出るため、一度外部制約を無効化します。

ALTER TABLE POSTGRES.ADDRESS DISABLE CONSTRAINT FK_ADDRESS_CITY;
ALTER TABLE POSTGRES.CITY DISABLE CONSTRAINT FK_CITY;
ALTER TABLE POSTGRES.CUSTOMER DISABLE CONSTRAINT CUSTOMER_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.FILM_CATEGORY DISABLE CONSTRAINT FILM_CATEGORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM_ACTOR DISABLE CONSTRAINT FILM_ACTOR_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM DISABLE CONSTRAINT FILM_LANGUAGE_ID_FKEY;
ALTER TABLE POSTGRES.INVENTORY DISABLE CONSTRAINT INVENTORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT DISABLE CONSTRAINT PAYMENT_STAFF_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT DISABLE CONSTRAINT PAYMENT_RENTAL_ID_FKEY;
ALTER TABLE POSTGRES.RENTAL DISABLE CONSTRAINT RENTAL_STAFF_ID_KEY;
ALTER TABLE POSTGRES.STAFF DISABLE CONSTRAINT STAFF_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.STORE DISABLE CONSTRAINT STORE_MANAGER_STAFF_ID_FKEY;

順次、SQL*Loaderでデータをロードします。
actor.ctlを実行すると下記のような実行結果になります。

$ sqlldr admin/<パスワード>@adb4psql_high control=actor.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Dec 29 05:22:37 2024
Version 19.10.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 201

Table POSTGRES.ACTOR:
  200 Rows successfully loaded.

Check the log file:
  actor.log
for more information about the load.

他のロード文は下記の通りとなります。

$ sqlldr admin/<パスワード>@adb4psql_high control=address.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=category.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=city.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=country.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=customer.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=film_category.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=film_actor.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=film.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=inventory.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=language.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=payment.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=rental.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=staff.ctl
$ sqlldr admin/<パスワード>@adb4psql_high control=store.ctl

ロードが成功したら、外部制約キーを有効化します。

ALTER TABLE POSTGRES.ADDRESS ENABLE CONSTRAINT FK_ADDRESS_CITY;
ALTER TABLE POSTGRES.CITY ENABLE CONSTRAINT FK_CITY;
ALTER TABLE POSTGRES.CUSTOMER ENABLE CONSTRAINT CUSTOMER_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.FILM_CATEGORY ENABLE CONSTRAINT FILM_CATEGORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM_ACTOR ENABLE CONSTRAINT FILM_ACTOR_FILM_ID_FKEY;
ALTER TABLE POSTGRES.FILM ENABLE CONSTRAINT FILM_LANGUAGE_ID_FKEY;
ALTER TABLE POSTGRES.INVENTORY ENABLE CONSTRAINT INVENTORY_FILM_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT ENABLE CONSTRAINT PAYMENT_STAFF_ID_FKEY;
ALTER TABLE POSTGRES.PAYMENT ENABLE CONSTRAINT PAYMENT_RENTAL_ID_FKEY;
ALTER TABLE POSTGRES.RENTAL ENABLE CONSTRAINT RENTAL_STAFF_ID_KEY;
ALTER TABLE POSTGRES.STAFF ENABLE CONSTRAINT STAFF_ADDRESS_ID_FKEY;
ALTER TABLE POSTGRES.STORE ENABLE CONSTRAINT STORE_MANAGER_STAFF_ID_FKEY;

11. 動作検証

行数をカウントするSQL文を実行し、RDS for PostgreSQLとAutonomous Databaseで行数がマッチするか検証します。ACTOR TABLEの例を下記に示します。

Oracle

SQL> select count(*) from postgres.ACTOR;
COUNT(*)
----------
       200

PostgreSQL

dvdrental=>  select count(*) from public.ACTOR;
 count 
-------
   200
(1 row)

まとめ

RDS for PostgreSQLからAutonomous Databaseへの移行検証は以上になります。
DBエンジンを変換する移行方法を試してみました。
DBMS_CLOUD_MIGRATION.MIGRATE_FILEプロシージャを使用することで、DumpファイルからOracle用のSQL文を自動変換してくれることは便利です。ただ、100%変換した上で自動実行してくれる代物ではないため、現時点では一部手動で改修する前提が必要です。
変換までしきれずとも、自動試行まで実施した上で、変換のエラー部分を特定してくれるとさらに便利だと思いました。（よりよい方法をご存じのOraclemanがいらっしゃれば教えてください・・・）
とはいえ、このような変換ファイルなく、1からSQL文を書いて実行するのは大変工数がかかる作業ですので、その一助にはなりえる移行ツールであることは間違いなさそうです。