この記事は「Oracle Cloud Infrastructure Advent Calendar 2019」の12月11日の記事として書いています。
####概要
Oracle CloudのAutonomous Database(ADB = Autonomous Transaction Processing と Autonomous Data Warehouse)は、サーバーレス型(*1)のフルマネージドサービスを志向していて、データベースが動作しているサーバーのOSへログインすることはできません。
ADBに接続してSQL,PL/SQLを実行したいときも、ADBのサーバーにSSHで接続してSQL*PLUSを起動する、ということはできません。いくつかほかの方法が提供されています。
SQL Developer Web(SDW)はそのうちの1つで、Oracle CloudとHTTPS(ポート443)で接続できるPC上のブラウザであれば動作させることができます。ADBに対してSQL,PL/SQLを実行する方法としては、もっとも手軽で汎用的な方法と言えそうです。そんなわけでADBを利用する場合は、まずSDWでデータに対する操作をする、という事になると思います。SDWの機能や操作方法をザっと知っておくと、ADBを使いこなす上でプラスも多いと考えられます。
本記事で紹介しているSQL Developer Web(SDW)ですが、名前が変更され、Database Actions(データベースアクション)という名前になっております。画面も本記事を作成したときからかなり変わっています(機能・用途が大きく変わっているわけではないです）。(2021/5追記)
本記事はSQL Developer Web(SDW *2)をADBで利用しはじめる際の最初の1歩に該当する内容です。2019年12月ごろの情報・環境を元に本記事は記載しています。
(参考)Autonomous Database(ADB, ADW, ATP)の使い方に関しては、下記の記事で、さまざまな場面での詳細な手順が紹介されています。使いはじめのユーザーを意識した内容です。実際に実施したいことが下記の記事にないか確認いただくとよいです。(2020/1 追記)
Autonomous Database ハンズオンラボ（HOL）
*1 本記事で扱う対象は、Autonomous Databaseは(「専用インフラストラクチャ」タイプではなく）「サーバーレス」タイプのデプロイのADBです。
*2 SDWはADBだけではなくOracle Cloudのデータベースシステム(OCI Database)でも利用可能です。ADBに組み込まれているSDWの画面のタブは、データベースシステム(OCI Database)で利用する場合のSDWのサブセットになっています。(「DBA」「OS」のタブは、ADBのSDW画面にはありません。）
####1. Autonomous Database(ADB)に接続してSQL,PL/SQLを実行する方法
ADBに組み込まれたSDWの操作を紹介する前に、ADBに接続してSQL,PL/SQLを実行する方法にどのようなものが考えられるか紹介し、それぞれの特徴をあげます。どの方法がよいか選択する際の参考情報です。
A. 作業PC(Windowsなど)に、SQL Developerをインストールして、そこからADBへ接続して実行する
ADBがリリースされた当初は、SQL Developer Web(SDW)が組み込まれてなかったため、多くのADBの資料やセミナーがこの方式でSQLを実行する手順を説明しています。Oracle Clientをインストールしたオンプレミスの環境にあるSQL*Plusを使う方法も似た方法になります。
**この方法の場合、SQL Developerが動作する環境(作業しているWindowsのPCなど)から、Oracle Cloud上のADBへポート1522で接続できる必要があります。**作業を実施するPCとOracle Cloudの間にあるファイアウォールでこのポート1522をオープンしなければならない点がこの方式の課題です。1522のポートはファイアウォールで閉じている環境は多いと考えられるからです。
B. Oracle Cloud上にSQL*Plusを実行できる環境を用意して、そこからADBへ接続して実行する
Oracle CloudにIaaSの仮想サーバー(compute)を用意して、その環境にSQLPlusなどが含まれるOracle Clientをインストールして、そこからADBへ接続する方法です。IaaSの仮想サーバーへは、Tera Termなどからsshで接続し、そこでSQLPlusを起動します。
A.の方法と異なりポート1522を開ける必要はありません。ただしsshのポート22を開ける必要があります。また、この方式では「IaaSの仮想サーバー」を用意する必要があるので、そのための費用や工数も考慮しなければなりません。この方式は、SQL,PL/SQLの実行だけでなく、ADBに対してオンプレミスのDBサーバー上のツール( datapump(impdbとexpdb)やSQL*Loader )を利用したい場合は、有力な選択肢となります。
C. ADBに組み込まれているSQL Developer Web(SDW)を利用する
ADBに組み込まれているSQL Developer Web(SDW)は、Windows環境で利用できるSQL Developerの機能のサブセットを持つブラウザ版、といえるツールです。ブラウザからOracle CloudへHTTPS(ポート443)でアクセスできれば利用できるため、ネットワークの観点でADBに対するSQL実行ツールとしてはA.やB.と比べて利用できる場面が多い(汎用的)と言えると思います。また、ADBに組み込まれており、インストール作業が不要なため、A.やB.より手軽に利用できます。
SDWはWindows環境で利用できるSQL Developerの機能のサブセットで、A.の方式を利用できる場合は無理に使わなければならない理由はあまりありません。
D. ADBに組み込まれているApplication Express(APEX)を利用する
ADBに組み込まれているApplication Express(APEX)は、ローコードでデータベースアプリケーションを開発するためのツールで、操作はブラウザ上で実行します。中にはSQLを実行するためのSQL Workshopが含まれていますので、これを使ってADBに対してSQL,PL/SQLを実行することができます。
E. ADBに組み込まれているOracle ML SQL Notebookを利用する
ADBに組み込まれているOracle ML SQL Notebookは、その名(ML = Machine Learning)の通り機械学習機能を利用するためのツールです。ツールの中でADBに対してSQL,PL/SQLを実行することができます。
D.とE.の方法は、それぞれ「アプリケーション開発」「機械学習」のためのツールなので、それらを実施する場合に付属的にSQL,PL/SQLを実行する、という意味合いになります。「ADBに対してSQL,PL/SQLを実行するためだけ」にD.やE.の方法を利用することは、SDWがある現在は一般的ではない、と思われます。
C.のSDWを利用できれば、A.やB.の方法は検討しなくてもいいか、と言うと、一概にそうとは言えません。たとえば**既存のSQL,PL/SQLのコード・スクリプトがSQLPlusに対応したもので、スクリプト中で "@sample.sql" のように他のスクリプトを読み込んでいるような場合、SDWでは実行できません。**既存のSQLPlusの資産をそのまま実行しやすい環境は、B.の環境という事になると考えられます。
またA.の「Windows環境で利用できるSQL Developer」はSDWより高機能です。Windows版のSQL Developerしかない機能を利用したいことも考えられます。
C.のSDWを使う方法は、ネットワーク面でもっともハードルが低く、手軽にADBに対してSQL,PL/SQLを実行できる方法です。
#####SDWの注意点
SDWでデータ更新するINSERT,UPDATE,DELETEなどのSQL文を実行する場合、**1文ごとに自動でcommitされます。**Windows環境で利用できるSQL DeveloperやSQL*Plusでは、デフォルトでは自動コミット(autocommit)はオフなので挙動が異なります。
####2. SQL Developer Web(SDW)を使い始めるにあたり参考になるドキュメント
ADBでSDWを使い始める際に参考にするとよいドキュメントを紹介します。
Oracle Cloud Infrastructure の Autonomous Databaseで使える SQL Developer Web (ブラウザベース)
ADBからSDWを使い始める手順をわかりやすく説明しています。本記事の手順は、この記事に書いてある手順を実施いただいた上で実行いただくように説明してあります。
マニュアル:Oracle Autonomous Transaction Processingの使用 - 組込みSQL Developer Webとの接続
Autonomous Transaction Processingのマニュアルの中のSDWに関する部分です。
Using Oracle® SQL Developer Web
SDWのマニュアルです。ADBに組み込まれているSDWではなく、Oracle Cloudのデータベースシステム(OCI Database)で利用できるSDWのマニュアルでADB上のSDWより多くの機能を説明しています。
Announcing Oracle SQL Developer Web!
OracleのProduct ManagerのJeff Smith氏によるSDWがリリースされたときのデモを含むプレゼン動画です。
####3. 本記事の作業の前提
-
作成済みのADBインスタンス。
本記事中で紹介している画面はAutonomous Transaction Processingからキャプチャしたものですが、Autonomous Data Warehouseでも違いはありません。 -
Oracle Cloud Infrastructure の Autonomous Databaseで使える SQL Developer Web (ブラウザベース)の手順にしたがって、admin以外のデータベースユーザーでSDWに接続できるようになっていること。
この記事の「SQL Developer Web へのアクセス（adminの場合）」「SQL Developer Web へ、admin以外のユーザで接続する」の手順で実行できます。本記事では、suga_carpというユーザーを作成してSDWへ接続させています。またsuga_carpに対しては、下記のようなSQLを実行して、ADBに最初から作成されているサンプルデータへアクセスできるようにします。下記のSQLはSDWからadminユーザーで接続して実行します。
GRANT
SELECT ANY TABLE,
UPDATE ANY TABLE,
DELETE ANY TABLE,
INSERT ANY TABLE
TO (作成したユーザー名:suga_carp);
####4. ADBへSDWからデータベース・ユーザーで接続
SDWから(admin以外の）データベース・ユーザーで接続します(ここの例ではsuga_carpで接続しています)。
SDWから接続する場合のポイントは、URLがデータベース・ユーザー毎に異なることです。ユーザー毎にSDWに接続するための設定をして、ユーザー毎に異なるURLでアクセスします。アクセスすると次のような画面が表示されます。
画面右上にはアクセスしているユーザー名が表示されます。
#####言語環境の変更
先に紹介した画面は「日本語」に設定した画面です。英語環境にすることもできます。画面右上のユーザー名のところをクリックして表示されるメニューから「プリファレンス」を選択します。
次のような画面が表示されるので「言語」から選択します。
SDWの画面からクリックして遷移することのできるヘルプの言語も、ここでの設定によって変わります。ただし日本語ヘルプがなくリンク切れとなってしまう場合もあります。そのような場合でも、言語設定を英語にしてからヘルプをクリックすると表示される場合があります(もちろん、英語のヘルプになります)。
#####サインアウト
SDWを終了したいときはサインアウトします。画面右上のユーザー名のところをクリックして表示されるメニューから「サインアウト」を選択します。
####5. SDWのワークシートを使ってみる
それではSDWからSQL,PL/SQLを入力、実行していってみましょう。
#####ワークシートでのSQLの入力と実行
SQL,PL/SQLの入力は、次の図のように、画面上部のタブで「ワークシート」を選択した上で、画面右側上部にします。ここの例ではselect * from sh.productsと入力しています。
1つのSELECT文を実行する場合、入力した上にあるボタンから「文の実行」をクリックします。ボタンをクリックする代わりに「Ctrl+Enterキー」でも実行できます。
「文の実行」での結果は画面下部にExcelシートのように出力されます。
SQL文は「スクリプトの実行」ボタンでも実行できます。「スクリプトの実行」は複数のSQL文をまとめて実行したり、PL/SQLを実行するときに利用します。複数のSQL文を入力して実行するときは、各SQLを「;」で区切ります(SQL*Plusと同じです)。ボタンをクリックする代わりに「F5キー」でも実行できます。
「スクリプトの実行」をした結果は、下図のように画面下部にテキストで出力されます。
「文の実行」で出力した結果は、さらにいくつかの形式でファイルに出力することができます。出力結果の画面上部の「ダウンロード」のボタンをクリックして表示されるメニューから形式を選択してクリックするとファイルに出力されます。
「文の実行」で出力した結果に関しては、出力結果の画面上部の「情報の表示(丸付きのiと表示されている)」ボタンでその結果に対応するSQL文を確認できます。
ワークシートに入力したコード(SQL,PL/SQL)を消去するには、コード入力したところの上にあるボタンから「クリア」ボタンをクリックするか、「Ctrl+Dキー」とします。ショートカットキーを入力するときは、カーソルが入力したコードになければなりません。
#####タイポなど間違いを見つける
入力したコードにタイポなど間違いがあれば、ワークシートにそれを見つけてくれる機能があります。select * form sh.productsとワークシートに入力してみます。
間違いを見つけた行の左に赤丸が表示され、間違い（と考えられる）箇所に赤の波線が表示されます。ポインターをその箇所に持っていくと訂正の候補が表示されます。（もちろん、完璧に間違いを見つけられるわけではありません）
#####コード(SQL,PL/SQL)の補完
ワークシートに入力中のコードを補完するための機能があります。入力の際に「Ctrl+スペースキー」を押すと、カーソルがある箇所の入力候補を表示します。下の例ではselect * from sh.と入力したところで補完をさせている例です。コードの該当箇所にはSHスキーマの表名が想定されますが、そのリストをデータベースから表示させています。
#####コード(SQL,PL/SQL)の整形
ワークシートに入力したコードを整形する機能があります。ワークシートに下記のように入力してみます。
declare x integer;
begin
select count(*) into x from sh.sales;
dbms_output.put_line('shのsales表には ' || x || ' レコードあります');
end;
コード入力したところの上にあるボタンから「フォーマット」ボタンをクリックするか、「Ctrl+F7キー」とします。すると下のようにPL/SQLのコードが整形されます。
#####DBMS_OUTPUTの出力を確認する
上記で入力したPL/SQLコードには、dbms_output.put_lineで出力している箇所があります。「スクリプトの実行」ボタンなどで実行してみます。PL/SQLの中のdbms_outputパッケージの出力は、ワークシート下部の「スクリプト出力」と「DBMS出力」に出力されます。下図は「DBMS出力」に出力された様子です。
#####履歴から入力する
SDWで実行したSQL,PL/SQLの履歴を確認することができます。ワークシート下部の「SQL履歴」をクリックすると履歴を表示できます。表示されたSQL,PL/SQL文をダブルクリックすると上部の入力箇所に、そのクリックしたコードがコピーされます（追加されます）。
また、**ワークシートのコード入力箇所にカーソルがある状態で「Ctrl+上↑キー」「Ctrl+下↓キー」**で履歴にあるSQL,PL/SQLをそのまま読み込むことができます。
#####実行計画を確認する
SDWで入力したSQLの実行計画を確認することができます。ワークシートにselect * from sh.productsのようにSQLを入力して、入力した上にあるボタンから「実行計画」をクリックします。ボタンをクリックする代わりに「F10キー」でも実行できます。すると画面下部に実行計画が表示されます。
この機能で表示される実行計画は、SQLを実際に実行することなく実行計画だけを取得し表示しています。参考：SQLの実行計画をEXPLAIN PLANとAUTOTRACE(SQL*Plus)で確認する際SQLは実行されるか
#####autotraceの結果を確認する（記事を作成した時点では統計データ取得がエラーとなります）
SDWで入力したSQLのautotraceの結果を確認することができます。ワークシートにselect * from sh.productsのようにSQLを入力して、入力した上にあるボタンから「自動トレース」をクリックします。ボタンをクリックする代わりに「F6キー」でも実行できます。すると画面下部に結果が表示されます。
しかし記事を作成した時点で実行した限りでは、ORA-01031、ORA-06512のエラーとなってしまい、本来表示されると考えられる実行時の統計データは表示されません。実行計画は表示されます。
なおWindowsなどのPC上で動作させるSQL Developerでは、本機能で実行時の統計データを確認できます。
この画面はPC上のSQL Developerでautotraceで実行させたときのものです。このように実行時の統計情報を確認できます。
#####ナビゲーターからSQLを生成させる
SDWの画面の左の「ナビゲータ」のタブを選択すると、アクセスしているデータベースユーザーが参照できる表などのオブジェクトを確認できるようになっています。参照するだけでなく、オブジェクトに対する操作へのメニューもあります。
「ナビゲータ」のタブを選択して表示させ、一番上のユーザー名（スキーマ名）を選択できるところで「SH」、その下で「表」をそれぞれ選択してください。すると、SHスキーマの表が下部に表示されます。どれか1つをポインタで指して右クリックすると、いくつかのメニューが表示されます。
この右クリックのメニューにある「データのロード」の機能を使って、ADB上の表に対してPC上にあるファイルをロードする方法が下記リンク先に紹介されています。
SQL Developer Web で Autonomous Database にローカルファイル(xlsx)をロードする
オブジェクトを、ワークシートのコード入力のところにドラッグアンドドロップすると、そのオブジェクトに関するSQLなどをコードに追記できます。追記されるのは、オブジェクト名やSQLのINSERT,UPDATE,DELETE,SELECT文のテンプレートとなる（オブジェクト名が入っている）文です。オブジェクトから「PRODUCTS」をドラッグアンドドロップしてみます。
「挿入のタイプを選択します」と表示されるので、ここでは「選択（SELECT文のことです）」を選択して「適用」をクリックします。
ワークシートにドロップしたSH.PRODUCST表をSELECTするSQL文が追記されます。
#####ワークシートの保存
ワークシートに入力したコードは、名前を付けたワークシートとして接続しているデータベースに保存しておくことができます。保存したワークシートは、SDWに接続したユーザー毎に管理され、次に同じユーザーでSDWからアクセスしたときに読み込むことができます。ワークシートをクリア(Ctrl-Dキー)して、下記のようなコードを入力してください。
-- サンプルです。
declare x integer;
begin
select count(*) into x from sh.sales;
dbms_output.put_line('shのsales表には ' || x || ' レコードあります');
end;
入力した上にあるボタンから「保存」をクリックするか「Ctrl+Sキー」とします。
入力したコードを保存するワークシートの名前を求められるので、例えば「sample」と入力して、「保存」をクリックします。
これで「sampleワークシート」に入力したコードが保存されました。ワークシートの画面で「sample」と表示されsampleワークシートを編集していることを示します。画面左側のタブで「ワークシート」をクリックすると、保存されているワークシートに「sample」があることがわかります。
このままコードを編集して、次に保存するときはsampleワークシートに上書きされます。別名で保存したいときは、コード入力部の上のワークシート名のところをクリックしてメニューを表示させ「別名保存」とします。
ワークシートとして保存しているコードは、同じユーザーでSDWからアクセスすれば、画面左の「ワークシート」タブに表示され、右クリックから「開く」を選択することで読み込むことができます。
データベース側に名前付きのワークシートとして保存するのではなく、PC上のファイルとして保存したい場合は、コード入力上部の「エディタ・コンテンツのダウンロード」あるいは「Ctrl+lキー」とします。これで入力しているコードをファイルにダウンロードできます。
####6. SDWのデータ・モデラ―を使ってみる
SDWには「データ・モデラ―」という機能があり、接続しているデータベースの表の定義をリバースエンジニアリングして、ER図として表示させることができます。スキーマにスクリプトで複数の表を作成したあとから、表の依存関係を図で理解することができます。
少し試してみます。画面上部で「データ・モデラー」を選択します。画面左のナビゲータから「SH」「表」と選択します。SHスキーマの表のリストが左下部に表示されます。「PRODUCTS」にポインタを持っていって右クリックし「依存性のあるオブジェクトをダイアグラムに追加」を選択します。
これでSH.PRODUCTSと依存性のある表をまとめてER図にすることができます。
####7. 最後に
SDWには従来からのSQL Developerの機能のサブセットとはいえ様々な機能があり、今回紹介してないものもあります。ADBに組み込まれているため、ADBのデータベースを作成すればすぐに利用し始めることができます。ADBを利用するにあたっては使い方に慣れておくと、何かと便利だと思います。