ExcelのVBAでOracleのtnsnames.oraのある場所を知るには

Question

クライアント側の環境、
Windows7,Excel2003～2010のVBA
Oracle10gもしくは11gもしくはInstantClient

ExcelのVBAからADO2.8のMicrosoft ODBC for OracleでOracleに接続しており、
Oracleサーバーが複数あります。

ユーザー環境のtnsnames.oraに接続文字列が無い場合、
VBAでエラーになるのでそれをキャッチして、
tnsnames.oraに接続文字列を書き加えようとしているのですが、
tnsnames.oraの場所がクライアントごとに違っていて、
取得方法に困っています。

環境変数'TNS_ADMIN'があればそれを取得するのですが、
無いクライアントもありました。

Cドライブをすべて検索していけば見つかるとは思うのですが、
それだと時間がかかってしまうし、
tnsnames.oraが複数あった場合、
Oracleが参照していないファイルを見つけることもあります。

なにか良い手は無いでしょうか？

よろしくおねがいします。

Answer 1

Windows Clientの場合はtnsnames.ora(=ORACLE_HOME)の場所を突き止めるのは容易ではないようです。

gusao様が教えてくださったHPの
NLS_LANG、ORACLE_HOME、ORACLE_SID などの設定を調べるには

"Windows におけるオラクル環境変数の確認方法"を見る限り、
ORACLE_HOMEはレジストリに書き込まれているので、そこから確認するのが確実かなと思います。

VBAで以下のようにプログラムを組めばできる気がします(VBAは詳しくありません…)

①"HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<任意>"でレジストリ検索
※ただし複数インストールされている場合はインストール数だけ見つかるのでループ処理？

②その下にある"ORACLE_HOME"からnetwork/admin/tnsnames.oraを特定して追記。

＃＃一般的かわかりませんので、是非有識者のご意見を伺いたいところです…(；´Д｀)＃＃

ところでODBCドライバを使用して接続しているということは、
端末1台ずつODBCドライバを設定する手順が発生しているということですよね？
その手順の中にtnsnames.oraの追記とODBC接続テストを含めれば良いのではないでしょうか。

Comments

[jm1156]

ありがとうございます。レジストリでとれるか試してみます。
ODBCドライバは設定不要です。
Microsoft ODBC for Oracleは基本的にインストールされているので、
ADOならコントロールパネルに設定なしでも接続が可能なのです。

[Yuna.S]

あぁ自分の無知がさらけ出されました…大変失礼しました(´・ω・`)

Answer 2

WScript.Shellを利用して標準出力の結果を取得する

ORACLE_HOMEのレジストリ値取得であれば以下のようなコードでできると思います。

Sub Sample()
    Dim strPath As String
    strPath = GetOraHome("OraClient11g_home1")
    
    MsgBox strPath
    
End Sub

Function GetOraHome(ByVal vHOME_NAME As String) As String
    Dim WSH As Object
    Dim wExec As Object
    
    Dim sCmd As String
    
    Dim Result As String
    Dim strPath As String
    
    Set WSH = CreateObject("WScript.Shell")
    
    sCmd = "reg query ""HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_" & vHOME_NAME & """ /v ""ORACLE_HOME"""
    Set wExec = WSH.Exec("%ComSpec% /c " & sCmd)
    Do While wExec.Status = 0
        DoEvents
    Loop
    
    Result = wExec.StdOut.ReadAll
    
    strPath = Trim(Mid(Result, InStr(Result, "REG_SZ") + 6, LenB(Result)))
    
    GetOraHome = strPath
    
    Set wExec = Nothing
    Set WSH = Nothing
    
End Function

Oracle環境が複数インストールされているということでしたので、これとは別に対象環境の特定が必要だと思いますが、参考までに。

Comments

[jm1156]

jawaさん、返信ありがとうございます。

sCmd = "reg query ""HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_""" & vHOME_NAME & " /v ""ORACLE_HOME"""
この部分を、

sCmd = "reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_" & vHOME_NAME & " /v ""ORACLE_HOME"""
こう変更したら取得できました。

対象環境の特定は、いまのところ10g,11g,InstantClient3パターンしかないので、
全部を検索するようにしたいと思います。

[jawa]

ダブルクォートの位置が間違ってましたね。。失礼しました。
```
sCmd = "reg query ""HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_" & vHOME_NAME & """ /v ""ORACLE_HOME"""
```
でも私の環境では提示したコードでちゃんと取得できてしまうのが不思議(^-^;

何とかなりそうでよかったです。がんばってください。

Answer 3

コマンドプロンプトでpathと入力してそれらしいのを探す。。。ってのじゃあまりに乱暴なので、
こんな方法はいかがでしょうか？

1.SQL*Plusでログインする
2.「@?」とコマンドを入力して実行する
この時に出力されるパスの.sqlを省いた部分がORACLE_HOMEにあたります。

実行例

SQL>@?
SP2-0310: ファイル"C:\app\client\product\12.1.0\client_1.sql"をオープンできません。

まぁ、ORACLE_HOME配下にフォルダ名と同一のsqlファイルがないことが条件にはなりますが。。。
とりあえず、ご参考までに。

Comments

[jm1156]

回答ありがとうございます。
しかしVBAのマクロで行いたいのです・・・。

[Tommy.103]

実行結果をファイルに出力するまでをバッチ化することはできます。あとはバッチを実行した後で出力したファイルの中身を確認することはVBAでもできます。

かなりな無理やり感はいなめませんがね（苦笑

[Tommy.103]

そういえば、SQL*Plusがインストールされていることも条件でした。
失礼しました。

Answer 4

TNS_ADMINが設定されていない場合は、$ORACLE_HOME/network/adminを参照するようです。

参考：http://www.shift-the-oracle.com/oracle-net/tnsnames.html

Comments

[jm1156]

環境変数でORACLE_HOMEを探したのですがみつかりません・・・。
環境変数ではないのでしょうか？

[gusao]

ORACLE_HOMEは環境変数です。以下のページを参考に探してみてください。
http://www.shift-the-oracle.com/config/show-environment-variable.html

勘違いだったら申し訳ないですが、ORACLE_HOMEはOracleインストール時に自動で設定されたはずなので、存在すると思いますが…

[Yuna.S]

コメントだけで申し訳ありません。

=====(以下抜粋)=====
Windows の場合にはデフォルトの環境変数相当のものがレジストリに設定されているために改めてコントロールパネルなどから設定することは少ない。
特に、別途設定したい場合にはコントロールパネル「システム」プロパティの「詳細」タブの環境変数で上書きする。
================

なのでインストールして使っていく上でORACLE_HOMEという環境変数は作られないようです。
使用しているPCにOracle11が入っていますが、やはりORACLE_HOMEという環境変数はありませんでした。

[gusao]

Yuna.Sさん
ご指摘ありがとうございます。
環境変数と勘違いしていました。ORACLE_HOMEはレジストリに登録されているんですね。

jm1156さん
勘違いで混乱させてしまい申し訳ありません。
ORACLE_HOMEはレジストリに登録されているので、レジストリ値を取得すればtnsnames.oraのパスが取得できると思います。

[gusao]

すみません、VBA標準でアクセスできるレジストリは限られているようです。

http://officetanaka.net/excel/vba/tips/tips43.htm
>なお、VBAのコマンドで操作できるレジストリは、HKEY_CURRENT_USERの下にあるSoftware内のVB and VBA Program Setting内です。そのほかの場所を操作したいときはAPIなどを使います。

[jm1156]

みなさんコメントありがとうございます。
やはりレジストリなんですね。
ちょっと調べて試してみます。