Oracle® SQLclからJavaScriptを使ってみる

#はじめに

この記事は NTTテクノクロス Advent Calendar 2020 の７日目の記事です。

こんにちはＮＴＴテクノクロスで最近はＤＢ関連のお仕事をしている田島です。

#SQLclについて

Oracle Databaseに接続して操作するツールとしてSQL*Plusがあり、その後GUIで操作できるJavaで作成されたSQL Developerが作成され、このSQL Developernの一部としCUIで操作できるSQLclが作成されました。

SQLclはSQL*Plusの機能をほぼ包含し、美味しい追加機能があるため使いやすいツールとなっています。

SQLclはSQLPlusに比べてJavaで作成されているため、起動が遅い欠点があります。既存のシェルとSQLPlusで作成されていたような処理を同様に作成すると、何度もSQLclを起動せねばならず時間がかかってしまうことが懸念されます。このため、SQLclから使用できるJavaスクリプトを使用して何ができるのか確かめてみたいと思います。
以下のサイトを参考にしています。

#前提条件

今回使用した環境
・OS
　CentOS Linux release 7.7.1908 (Core)
・DB
　Oracle Database 19c Enterprise Edition Version 19.3.0.0.0
・ツール（上記に付属）
　Java version "1.8.0_201"
　SQLcl リリース19.1
・テーブル
　create table t1_tbl(id number primary key,data varchar2(100)) ;

#JavaScript

##グローバル定義

JavaScript内で利用できるもの

グローバル	関数等	内容
args	-	スクリプト呼び出し時の引数の配列
sqlcl	setStmt(String)	SQLclで実行するコマンドを設定する。
↑	run()	上記で設定したコマンドを実行する。
ctx	write(String)	画面出力。改行がないと反映されない。
↑	cloneCLIConnection()	複数のコネクションが必要な場合に利用する。今回は解説しない。
↑	getMap().put(name,value)	サンプルで使用されており、script側からdefineを設定する。
↑	getMap().get(name)	defineの値を取得する。
util	execute(string,binds)	SQLの結果の成功失敗のみを返却する。
↑	executeReturnOneCol(string,binds)	SQLの結果の1行目の1カラムを返却す。
↑	executeReturnListofList(string,binds)	SQLの結果配列[][]で返却する
↑	executeReturnList(string,binds)	SQLの結果配列[]属性{}で返却する
↑	getLastException()	直近のエラーを返却する

##サンプル

###ファイル

用意したファイル

args.js

for(var i=0; i < args.length; i++ ) {
   ctx.write("arg" + i + ":" + args[i]);
   ctx.write("\n");
}

実行した結果

SQL> script args.js a1 a2 a3 a4
arg0:args.js
arg1:a1
arg2:a2
arg3:a3
arg4:a4
SQL>

###直書き
マニュアルを読むとscriptはファイル指定しかできないように読めますが直接実行できます。

SQL> script
  2  ctx.write("Hello, world!\n");
  3  /
Hello, world!
SQL>

###コマンド作成

今回作成したのはsysでCDBに接続してそこらかPDBに変更するコマンドをサンプルを元に作成してみました。

用意したファイル

pdb.js

// 必要なクラスの設定
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
var CommandListener =  Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")

var cmd = {};

// コマンドの処理
cmd.handle = function (conn,ctx,cmd) {
  if ( cmd.getSql().trim().startsWith("pdb ") ) {
    args = cmd.getSql().trim().split(/\s+/);

    sqlcl.setStmt("alter session set container=" + args[1]  + ";");
    sqlcl.run();

    return true;
  }
   return false;
}

// 全コマンドの実行前後の処理があれば記述
cmd.begin = function (conn,ctx,cmd) {
}
cmd.end = function (conn,ctx,cmd) {
}

// 登録用のクラスの作成？

var PdbCommand = Java.extend(CommandListener, {
                   handleEvent:  cmd.handle ,
        beginEvent:  cmd.begin  ,
          endEvent:  cmd.end
});

// コマンドの登録
CommandRegistry.addForAllStmtsListener(PdbCommand.class);

実行した結果
　コマンド登録前はエラーとなったのが、登録後には正常に動作したことが確認できました。

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL>
SQL> pdb ORCLPDB
SP2-0734: "pdb ORCLPD..."で始まる不明なコマンドです - 行の残りは無視されました。
SQL>
SQL> script pdb.js
SQL>
SQL> pdb ORCLPDB

Sessionが変更されました。

SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL>

###SQLサンプル
####execute

SQL> script
  2  var bind={};
  3  bind.id=9;
  4  bind.data="data9";
  5  var ret = util.execute("insert into t1_tbl values(:id,:data)", bind);
  6  ctx.write(ret + "\n");
  7  /
true
SQL>

####executeReturnOneCol

SQL> script
  2  var bind={};
  3  bind.id = 1;
  4  var ret = util.executeReturnOneCol("select data from t1_tbl", bind);
  5  ctx.write(ret + "\n");
  6  /
data1
SQL>

####executeReturnListofList

SQL> script
  2  var bind={};
  3  var ret = util.executeReturnListofList("select * from t1_tbl", bind);
  4  ctx.write(ret + "\n");
  5  for(var i = 0; i < ret.length; i++){
  6    ctx.write(i + ":" + ret[i][0] + "\t" + ret[i][1] + "\n");
  7  }
  8  /
[[ID, DATA], [1, data1], [2, data2], [3, data3], [9, data9]]
0:ID    DATA
1:1     data1
2:2     data2
3:3     data3
4:9     data9
SQL>

####executeReturnList

SQL> script
  2  var bind={};
  3  var ret = util.executeReturnList("select * from t1_tbl", bind);
  4  ctx.write(ret + "\n");
  5  for(var i = 0; i < ret.length; i++){
  6    ctx.write(i + ":" + ret[i].ID + "\t" + ret[i].DATA + "\n");
  7  }
  8  /
[{DATA=data1, ID=1}, {DATA=data2, ID=2}, {DATA=data3, ID=3}, {DATA=data9, ID=9}]
0:1     data1
1:2     data2
2:3     data3
3:9     data9
SQL>

####getLastException

SQL> script
  2  var err = util.getLastException();
  3  ctx.write("err:" + err + "\n");
  4
  5  var bind={};
  6  var ret = util.executeReturnList("no sql", bind);
  7
  8  var err = util.getLastException();
  9  ctx.write("err:" + err + "\n");
 10
 11  /
err:null
err:java.sql.SQLSyntaxErrorException: ORA-00900: SQL文が無効です。

SQL>

###SQLclとJavaScriptの連携、JavaScriptでの入力

以下の内容を確認しています。

a1の変数に値を入れてsqlcl側からjavascript側で確認できること。
a2の変数にjavascript内で入力処理ができること。
a3の変数に値を入れてjavascript側からsqlclで確認できること。

SQL> define a1
SP2-0135: 記号a1は定義されていません
SQL>
SQL> define a2
SP2-0135: 記号a2は定義されていません
SQL>
SQL> define a3
SP2-0135: 記号a3は定義されていません
SQL>
SQL> define a1=sqlcl
SQL>
SQL> define a1
DEFINE A1              = "sqlcl" (CHAR)
SQL>
SQL> script
  2  var a1=ctx.getMap().get("A1");
  3  ctx.write("A1:" + a1 + "\n");
  4  sqlcl.setStmt("accept a2 num prompt 'number?'");
  5  sqlcl.run();
  6  var a2 = ctx.getMap().get("A2");
  7  ctx.write("A2:" + a2 + "\n");
  8  ctx.getMap().put("A3","javascript");
  9  /
number? test
A1:sqlcl
SP2-0425: "test"は有効なNUMBERではありません
number? 1234
A2:1234
SQL>
SQL> define a1
DEFINE A1              = "sqlcl" (CHAR)
SQL>
SQL> define a2
DEFINE A2              = "1234" (CHAR)
SQL>
SQL> define a3
DEFINE A3              = "javascript" (CHAR)
SQL>

#その他の特徴

個人的に気になったところだけピックアップします。

##HISTORY/編集

ヒストリー機能で上矢印キーで過去のコマンドを表示、編集、実行できます。編集のキーアサインはヘルプで確認できます。

SQL> help edit
EDIT
---------

指定されたファイルの内容またはSQLバッファの内容で
オペレーティング・システムのテキスト・エディタを起動します。

 ED[IT] [file_name[.ext]]

DEFINE変数_EDITORを使用して、使用するエディタを設定できます

SQLclでは、_EDITORを"inline"に設定できます。これにより、エディタは
SQLclエディタに設定されます。次のショートカットがサポートされます
\t^R - 現在のバッファを実行します
\t^W - バッファの一番上に移動します
\t^S - バッファの一番下に移動します
\t^A - 行の先頭に移動します
\t^E - 行の末尾に移動します
SQL>

##ansiconsole
デフォルトでは桁数を自分で設定しないといい感じで出力されなかったのが、ansiconsoleを指定するとデータ長に応じて程よい感じで出力されるようになります。
また、ansiconsoleでは、カラムタイトルの「ID DATA」は黄色でアンダーラインとなっています。

・デフォルト

SQL> set sqlformat default
SQL書式がクリアされました
SQL> select * from t1_tbl;

        ID
----------
DATA
--------------------------------------------------------------------------------
         5
,

         6
""

         1
data1


        ID
----------
DATA
--------------------------------------------------------------------------------
         2
data2

         3
data3

         9
data9

・ansiconsole

SQL> set sqlformat ansiconsole
SQL> select * from t1_tbl;
  ID DATA
   5 ,
   6 ""
   1 data1
   2 data2
   3 data3
   9 data9

##CSV出力
あとでLOADできるようにするため、「set feedback off」しています。

SQL> set sqlformat csv
SQL> set feedback off
SQL> spool t1_tbl.csv
SQL> select * from t1_tbl;
"ID","DATA"
6,""""
5,","
1,"data1"
2,"data2"
3,"data3"
9,"data9"
SQL> spool off
SQL>
SQL> ! cat t1_tbl.csv
"ID","DATA"
6,""""
5,","
1,"data1"
2,"data2"
3,"data3"
9,"data9"

##LOAD(CSVファイル)
SQL*Loaderがなくてもデータが読み込めるので便利です。
LOADで読み込んだ後に「commit;」しないと反映されないので注意が必要です。

SQL> truncate table t1_tbl;

Tableが切り捨てられました。

SQL> load t1_tbl t1_tbl.csv
--処理された行数: 6
--エラーのある行数: 0
0 - 成功: ロードがエラーなしで処理されました
SQL> commit;

コミットが完了しました。

SQL> set sqlformat ansiconsole
SQL> select * from t1_tbl;
  ID DATA
   6 ""
   5 ,
   1 data1
   2 data2
   3 data3
   9 data9


6行が選択されました。

#おわりに
Web系でJavaScriptを使用されているユーザの方も多いので、SQLclからJavaScriptを上手に利用してDBを活用できればと良い思います。

それでは引き続き NTTテクノクロス Advent Calendar 2020の８日目もお楽しみください。