はじめに
MySQLやPostgreSQLで行数の絞り込みに使用するLIMIT句、便利ですよね。一方Oracle Databaseでの行数の絞り込みは、ANSI SQL標準のFETCH FIRST句を使用します。しかし実行環境はAutonomous Database Cloud Service(以下ADB)に限られますが、Oracle DatabaseでもLIMIT句を使う方法が提供されましたので方法を共有したいと思います。
事前準備
ADBにはDatabase Actionsのような便利なWebツールがついていますが、残念ながらそういった環境では動きません。SQL*PlusやSQLcl、あるいはアクセスドライバ経由で開発言語経由での利用が前提となります。
事前準備といっても簡単で、以下のSQLを発行するだけです。
BEGIN
DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION ('POSTGRES');
END;
/
SQLのCALL文(CALL DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION ('POSTGRES'))でも構いませんし、SQL*PlusやSQLclであればツールのEXECコマンド(EXEC DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION ('POSTGRES'))でも構いません。
ADMIN以外のユーザーで実行する場合は、ADMINユーザーにてこのパッケージへの実行権限を付与してから該当ユーザーでログインする必要があります。下記「scott」の部分を権限付与対象のユーザー名に変更してください。
SQL> grant execute on dbms_cloud_migration to scott;
Grant succeeded.
ところでパッケージ名に「MIGRATION」と入っているのが気になるかもしれません。このパッケージ自体は本記事執筆時点ではPostgreSQLのみが対象ですが、本来はOracle Databaseへの移行を目的に他のDBMSのSQL文をOracle DatabaseのSQL文に変換するパッケージです。基本的には本記事では紹介していない、変換後のSQL文を出力する機能が利用の中心なのですが、今回紹介しているコマンドは指定したDBMSのSQL文を発行すると、Oracle Database側で内部的にOracle Databaseに変換して実行できるようにします。結果、Oracle Databaseに元のDBMSの書き方のままのSQL文を発行することを可能にします。
実行してみる
SQL> exec DBMS_CLOUD_MIGRATION.ENABLE_TRANSLATION ('POSTGRES')
PL/SQL procedure successfully completed.
SQL> select * from sh.costs
2 where promo_id = 999 and channel_id = 2
3 order by time_id
4 limit 5;
PROD_ID TIME_ID PROMO_ID CHANNEL_ID UNIT_COST UNIT_PRICE
---------- --------- ---------- ---------- ---------- ----------
117 01-JAN-98 999 2 9.3 9.3
41 01-JAN-98 999 2 47.88 47.88
48 01-JAN-98 999 2 13 13
39 01-JAN-98 999 2 40.05 40.05
116 01-JAN-98 999 2 12.4 12.4
ちゃんと動きますね。
上記の状態のまま、Oracle Database固有のSQL文も流せます。
SQL> select * from sh.costs
2 where promo_id = 999 and channel_id = 2
3 order by time_id
4 fetch first 5 rows only;
PROD_ID TIME_ID PROMO_ID CHANNEL_ID UNIT_COST UNIT_PRICE
---------- --------- ---------- ---------- ---------- ----------
117 01-JAN-98 999 2 9.3 9.3
41 01-JAN-98 999 2 47.88 47.88
48 01-JAN-98 999 2 13 13
39 01-JAN-98 999 2 40.05 40.05
116 01-JAN-98 999 2 12.4 12.4
終了方法
開始方法から想像がつくかもしれませんが、PostgreSQL互換モード終了方法は以下になります。もっとも、先述のとおり互換モードでもOracle Database構文も通るので、終了させる意味は薄いかも知れません。
BEGIN
DBMS_CLOUD_MIGRATION.DISABLE_TRANSLATION ();
END;
/
終了の場合引数は不要です。
その他注意点など
本記事執筆時点のこの機能は、実際には対応範囲が狭いです。筆者が試した範囲ではLIMIT句とテーブルのデータ型を変換してくれる程度です。一例としてはSQL関数にはほとんど対応していません。ただし、元々Oracle Database 23aiではFROM句なしのSELECTやUPDATEでのJOINなど、MySQLやPostgreSQLではできるけど以前のバージョンのOracle Databaseではできなかったことの多くが素でできるようになっています。今後どこまで拡張されるかはわかりませんが、今後の機能拡張にご期待ください。
変更履歴
- 2024/9/12 初版