SQL*Plusから外部ファイルを実行する際のTips

先日SQL*Plusに外部SQLファイルを食わせる形でDDLを実行しているスクリプトに遭遇したのですが、ここでは１SQLファイル毎にSQL*Plusで接続＆切断を繰り返す形で記載されていました。たしかにこれでもいいんですが、やはり無駄な接続切断は避けたいところです。次のような形にするとOracleへの接続は１度で、複数のSQL外部ファイルを実行する事ができます。

Windows環境からPowerShellを使い SQL*Plusを実行する

私はPowerShell ISEから以下のように使っています。（SQLの結果をログファイルへ出力する場合のサンプルはこちらに書きました。SQL*Plusで実行した結果をログファイルに残すのって結構手間ですよね。スクリプト化したので簡単実行可能です。興味のある方はどうぞ）

ForEach-Object -Begin {
    Write-Output "conn sooni/sooni@vm013/orau8"
} -Process {
    Write-Output "@sqlfiles\sql01.sql 1";
    Write-Output "@sqlfiles\sql01.sql 2"; 
} -End {
    Write-Output "exit"
 } |
sqlplus -s /nolog

-- sqlfiles\sql01.sql の中は以下の通り
SET VERIFY OFF
set lin 200
select '&1' as lno,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') as "SYSDATE" from dual;

-- 実行結果

LN SYSDATE
-- --------------------------------------
1  2021/09/06 01:26:19


LN SYSDATE
-- --------------------------------------
2  2021/09/06 01:26:19

Linux環境からShellを使い SQL*Plusを実行する

サクッと終了するようなSQLはPCからPowerShell等で実行しますが、長時間流すようなSQLの場合はサーバ（Linux）からshellを使って実行します。（一晩中流しておくような場合どうしてもPCからでは無理があります）

ヒアドキュメントを使うとお手軽です

#!/bin/sh
sqlplus -s sooni/sooni@vm013/orau8 <<EOF
@sqlfiles/sql01.sql 1
@sqlfiles/sql01.sql 2
exit;
EOF

$ cat sqlfiles/sql01.sql
SET VERIFY OFF
set lin 200
select '&1' as lno,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') as "SYSDATE" from dual;

$ ./test3.sh

L SYSDATE
- -------------------
1 2021/09/06 01:37:15


L SYSDATE
- -------------------
2 2021/09/06 01:37:15

こちらの方法でほとんど問題ないのですが、ある条件下においては困った事になります。例えば、１つ目のSQLファイルを実行した際、３秒のインターバルをあけたいような場合です。DBMS_LOCK.SLEEP(3)使えば実現できなくはないですが、それだとOracle内部でsleepします。しかもそれなりの権限も求められますし、、そんな時に活躍するのがサブシェル実行です。
（補足）シェルでは()「丸かっこ」で囲んだ部分はサブシェルとして実行されます。その昔先輩から「パーレン実行すればいいじゃん」って言われて、、？？？？パーレンって何？って思った事ありましたが、丸かっこをパーレンと呼ぶらしいです。

サブシェルを使ってSQLファイルを実行（パーレン実行）

#!/bin/sh
(
echo 'conn sooni/sooni@vm013/orau8'
echo "@sqlfiles/sql01.sql 1"
sleep 3;
echo "@sqlfiles/sql01.sql 2"
echo exit
) | sqlplus -s /nolog

$ cat test2.sh
#!/bin/sh
(
echo 'conn sooni/sooni@vm013/orau8'
echo "@sqlfiles/sql01.sql 1"
sleep 3;
echo "@sqlfiles/sql01.sql 2"
echo exit
) | sqlplus -s /nolog

[oracle@vm013 test]$ ./test2.sh

L SYSDATE
- -------------------
1 2021/09/06 01:58:37


L SYSDATE
- -------------------
2 2021/09/06 01:58:39

[oracle@vm013 test]$

１つめのSQLファイルを実行した後、Linux側で3秒のsleepが入っている事確認できます。本当はWindows環境でもこれと同じ事できないか探しているのですが見つかっていません。