oraclepowershell
sqlplusサンプル

PowerShell から外部SQLファイルの実行結果をログ出力する

2021.11.20

Windows環境からOracleに対しDDL（事前にSQLファイルとして用意済）を実行したくてスクリプトを書きました。（当然DDLに限らず、DMLも可能です）ポイントとなる仕様は次の通りです。

前提
  • PowerShell Version 5.1で動作させる
    （もしPowerShell Version 7以降を利用できる環境であればそちらがおすすめです。Ver.5.1ではどうしても文字コードの扱いが窮屈です）
仕様

１．実行SQL定義ファイル（sqlFiles.txt）に記載した順番で外部SQLファイルを実行する。
２．SQLファイルの実行結果はログファイルへ残す。
３．外部SQLファイルはutf-8(BOMなし)もしくはShift_JISで記載されている。
　　Windows環境SQL*Plusの仕様として外部実行SQLファイルはShift_JISでの記載
　　が必要で、utf-8で記載されている場合は実行直前にShift_JISへ変換する必要が
　　ある。 外部実行SQLファイルの文字コード判定はnkf.exeを利用する
　　（PowerShell nativeでは判定方法が 見つからなかった、、、）
４．外部SQLファイルの中にはパラメータ付のものも存在する
５．Oracleへの接続は１度きり（外部SQLの数だけ接続しなおすような事はしたくない）

簡単な実行イメージ（PowerShellはISEからの実行を前提としています）

全体イメージ

補足：Windows版SQL*plusで外部実行SQLファイルを扱う際、Shift_JISで記載する必要があります。そのため実行前にそれぞれのSQLファイルがShift_JISで記載されている事が確認できればそのまま実行するが、utf-8で記載されている場合、Set-Contentコマンドレットを使いShift_JISのファイルに変換して実行している。(SQLファイルの文字コード判定にnkfを使用）

sqlFiles.txt への記載例 （このファイルはShift_JISで記載してください）

##  execSQLfiles.txt への記載例
##  記載した順番に実行する。
##  #始まりの行はコメント行
##  SQLファイルへのパラメータは空白区切りで渡す
C:\orapfck\connect.sql
# SQLファイルへのパラメータ渡しは「カンマ」で区切る
C:\orapfck\test01.sql,1
# SQLファイルへのパラメータが２つのケース
C:\orapfck\test02.sql,10,20

各種ＳＱＬファイルの記載例

SQLは最後セミコロン『;』で終えてください。１つのSQLファイルに複数SQLの記載も可能です。

-- connect.sql
-- 
set lin 200
col label for a16
col userenv_item for a24
col note for a24

select '01' LNO,'DB_NAME' label,
SYS_CONTEXT('USERENV','DB_NAME') USERENV_ITEM,'データベース名' note
from dual UNION
select '02' LNO,'SERVICE_NAME' label,
SYS_CONTEXT('USERENV','SERVICE_NAME') USERENV_ITEM,'サービス名' note
from dual UNION
select '03' LNO,'INSTANCE_NAME' label,
SYS_CONTEXT('USERENV','INSTANCE_NAME') USERENV_ITEM,'インスタンス名' note
from dual UNION
select '04' LNO,'SERVER_HOST' label,
SYS_CONTEXT('USERENV','SERVER_HOST') USERENV_ITEM,'インスタンス稼働ホスト' note
from dual 
order by 1
;

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

-- test02.sql 
-- 
SET VERIFY OFF
set lin 200
select '&1' as p1,'&2' as p2,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') as "SYSDATE" from dual;
以下をps1ファイル（execsqlfile.ps1）として保存しPowerShell ISEで実行する

以下はnkfコマンドがインストールされていない事を想定したものですので、SQLファイルはutf-8で記載されている事が前提で動作します。もし実行PCにnkfコマンドがセットアップされているのであれば、以下20行目を適宜書き換えてください。SQLファイルがutf-8,Shift_JIS,ascii何れで記載がされている場合でもnkfが自動で判断して動作するようになります。nkfのインストールについてはこちらに記載してみましたが、今回のようにSQLファイルをutf-8で記載する事を前提にするなら不要です。

if ( Test-Path Variable:nkfcmd        ) {Remove-Variable nkfcmd}
if ( Test-Path Variable:tns_admin_pth ) {Remove-Variable tns_admin_pth}
# ベースフォルダ
$basedir        = $PSScriptRoot
# スクリプトファイルが存在しているところへ移動
Set-Location -Path $basedir
# 実行する外部SQLファイルを記載したテキスト(パラメータがある場合カンマで区切る
$setupfiles    = "$basedir\sqlFiles.txt"

$logdir        = "$basedir\logs"
# 実行するSQLファイルを一時的に保管する（文字コード変換後）
$tempdir       = "$basedir\temp"
# Oracleへの接続情報
$connstr       = "conn sooni/sooni@vm013:1521/orau8"
# tnsnames.oraを使いたい場合は以下に指定する。
# 但し既に設定されている場合は設定しても既存の値を優先する
# TNS_ADMIN環境変数への設定フォルダ
$tns_admin_pth = "C:\tools\oracle_client\network\admin"
# nkfのpath
# nkfを使いSQLファイルの文字コードを自動判定したい場合は以下にnkfのパスを記載してください
# コメントのままにしておくとSQLファイルはutf-8で記載されている事を前提で動作します
#$nkfcmd        = "C:\tools\nkf\nkf.exe"
if ( Test-Path Variable:nkfcmd ) {
    write-host "nkfが存在するので一時的にpathへ追加"
    $nkfpath = (Get-Item $nkfcmd).DirectoryName
    #nkfをpathへ追加
    $env:Path+=";"+$nkfpath
    $nkfck = gcm nkf 2> $null   # pathへの追加ができかた確認
    $nkfswitch = $?
}
else
{
    $nkfswitch = $false
    write-host "nkfコマンドが無効なのでINPUTファイルはutf-8である事を想定して動作します"
}

if( -Not (test-path $logdir))
{
    $mkr=New-Item -ItemType Directory -Force -Path $logdir
}

if((test-path $tempdir))
{
    # tempフォルダは毎回再作成する
    Remove-Item -Recurse $tempdir
}
$mkr=New-Item -ItemType Directory -Force -Path $tempdir

if ( ($env:TNS_ADMIN -eq $null) -And (Test-Path Variable:tns_admin_pth) ) {
    write-host "TNS_ADMIN環境変数を設定しました。：" $tns_admin_pth
    $env:TNS_ADMIN = $tns_admin_pth
}
elseif ( $env:TNS_ADMIN -ne $null )
{
    write-host "現在のTNS_ADMIN環境変数には：" $env:TNS_ADMIN "が設定されています"
}

ForEach-Object -Begin {
    # Oracleへの接続
    Write-Output $connstr

} -Process {
    # 実行SQLの表示／非表示を切り替える
    Write-Output "set echo on"
    Write-Output "column logdate new_value OKIKAE"
    Write-Output "set termout off"
    Write-Output "select to_char(sysdate,'YYYYMMDD_HH24MISS') logdate from dual;"
    Write-Output "set termout on"
    Write-Output "spool ""${logdir}\spool_&&OKIKAE..log"""
    Write-Output "set trimspool on"
    # 定義ファイルを１行づつ読み上げながらSQLファイルを実行する
    $i = 0
    Get-Content $setupfiles | Where {$_ -ne ""} | Foreach-Object {
        # 外部実行SQLファイルにパラメータが使われているかどうか
        $array = $_.Split(",")
        $sql = $array[0]
        $p1  = $array[1]
        $p2  = $array[2]
        if( $sql -notlike "#*" ) {
            # 行先頭に#が入っている場合はコメント行として扱う
            $i++
            $str1 = "{0:000}" -f $i
            $basename = (Get-Item $sql).BaseName
            if (  $nkfswitch  )
            {
                # 外部実行SQLファイルの文字コードをnkfを使って調べる
                $filetype = nkf --guess $sql
                if( ($filetype -like "Shift_JIS*") )
                {
                    # Shift_JISはそのまま実行可能なのでコピー
                    $outfile = "$tempdir\$str1" + "_" + "$basename" + "_sjis.sql"
                    Copy-Item -Path $sql $outfile
                }
                elseif( ($filetype -like "CP932*") )
                {
                    # CP932はそのまま実行可能なのでコピー
                    $outfile = "$tempdir\$str1" + "_" + "$basename" + "_sjis.sql"
                    Copy-Item -Path $sql $outfile
                }
                elseif( ($filetype -like "UTF-8*") )
                {
                    # UTF-8からShift_JISへ変換
                    Write-host "変換:"+$sql
                    $outfile = "$tempdir\$str1" + "_" + "$basename" + "_cnv_sjis.sql"
                    get-content -Encoding UTF8 $sql | Set-Content $outfile
                }
                elseif( ($filetype -like "ASCII*") )
                {
                    # ASCIIはそのまま実行可能なのでコピー
                    $outfile = "$tempdir\$str1" + "_" + "$basename" + "_ascii.sql"
                    Copy-Item -Path $sql $outfile
                }
                else
                {
                    Write-host "このファイルは実行できません:"+$sql
                }
            }
            else
            {
                # UTF-8からShift_JISへ変換
                $outfile = "$tempdir\$str1" + "_" + "$basename" + "_cnv_sjis.sql"
                get-content -Encoding UTF8 $sql | Set-Content $outfile
            }
            if( $array.Count -eq 2 )
            {
                Write-output "@""$outfile"" $p1"
            }
            elseif( $array.Count -eq 3 )
            {
                Write-output "@""$outfile"" $p1 $p2"
            }
            else {
                Write-output "@""$outfile"""
            }
        }
    }
} -End {
    Write-Output "spool off"
    Write-Output "exit"
 } |
sqlplus /nolog

if( $nkfswitch )
{
    #nkfをpathから外す
    Set-Item ENV:Path $ENV:Path.Replace(";"+$nkfpath, "")
    write-host "nkfをpathから外す"
}
実行例
PS C:\test\サンプル 001> C:\test\サンプル 001\execsqlfile.ps1
nkfコマンドが無効なのでINPUTファイルはutf-8である事を想定して動作します
現在のTNS_ADMIN環境変数には： C:\tools\oracle_client\network\admin が設定されています

SQL*Plus: Release 19.0.0.0.0 - Production on 日 12月 26 23:10:12 2021
Version 19.11.0.0.0

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

SQL> 接続されました。
SQL> SQL> SQL> SQL> 
LOGDATE
------------------------------
20211226_231012

SQL> SQL> SQL> SQL> SQL> -- connect.sql
SQL> --
SQL> set lin 200
SQL> col label for a16
SQL> col userenv_item for a24
SQL> col note for a24
SQL> 
SQL> select '01' LNO,'DB_NAME' label,
  2  SYS_CONTEXT('USERENV','DB_NAME') USERENV_ITEM,'データベース名' note
  3  from dual UNION
  4  select '02' LNO,'SERVICE_NAME' label,
  5  SYS_CONTEXT('USERENV','SERVICE_NAME') USERENV_ITEM,'サービス名' note
  6  from dual UNION
  7  select '03' LNO,'INSTANCE_NAME' label,
  8  SYS_CONTEXT('USERENV','INSTANCE_NAME') USERENV_ITEM,'インスタンス名' note
  9  from dual UNION
 10  select '04' LNO,'SERVER_HOST' label,
 11  SYS_CONTEXT('USERENV','SERVER_HOST') USERENV_ITEM,'インスタンス稼働ホスト' note
 12  from dual
 13  order by 1
 14  ;

LNO  LABEL	      USERENV_ITEM	       NOTE
---- ---------------- ------------------------ ------------------------
01   DB_NAME	      orau8		       データベース名
02   SERVICE_NAME     orau8		       サービス名
03   INSTANCE_NAME    orau8		       インスタンス名
04   SERVER_HOST      vm013		       インスタンス稼働ホスト

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

LN SYSDATE
-- --------------------------------------
1  2021/12/26 23:10:12

SQL> SQL> -- test02.sql
SQL> --
SQL> SET VERIFY OFF
SQL> set lin 200
SQL> select '&1' as p1,'&2' as p2,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') as "SYSDATE" from dual;

P1   P2   SYSDATE
---- ---- --------------------------------------
10   20   2021/12/26 23:10:12

SQL> SQL> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsとの接続が切断されました。

PS C:\test\サンプル 001>
以下解説
Remove-Variableで不要になった変数を削除する

変数(例：nkfcmd)が定義されているか否かを確認して、定義されていたら削除する場合以下のように記載する必要があります。（bashなら、[ -v nkfcmd ]; で確認しますね）powerShellでスクリプトファイルにして実行する場合、前回実行時に設定した変数が残っているみたいなのです。変数が設定されているか否かで処理分岐ロジックを入れる場合、処理の頭でこのような記載が必要になります。（bashなら必要ないロジックですよね）

if ( Test-Path Variable:nkfcmd ) {Remove-Variable nkfcmd}
pathへの追加と削除
# pathの最後に追加する
$env:Path+=";"+$nkfpath

# pathから除去する
Set-Item ENV:Path $ENV:Path.Replace(";"+$nkfpath, "")
フルパスをディレクトリ名、ベース名、識別子に分割する
PS C:\orapfck> $nkfcmd = "C:\orapfck\lib\nkf.exe"
PS C:\orapfck> (Get-Item $nkfcmd).DirectoryName
C:\orapfck\lib
PS C:\orapfck> (Get-Item $nkfcmd).BaseName
nkf
PS C:\orapfck> (Get-Item $nkfcmd).Extension
.exe
ファイルをUTF-8からShift_JISへ変換
get-content -Encoding UTF8 $sql | Set-Content $outfile
スポンサーリンク
sooni
タイトルとURLをコピーしました