VBAからOracleのストアドプロシージャを実行する方法

VBAからOracleのストアドプロシージャを実行する方法について記載します。

VBAからOracleに接続する方法は以前の記事を参考にしてください。

エクセルなどのOfficeソフトからADO(ActiveX Data Objects)を使用して、Oracleに接続する方法を記載します。参照設定最初にADOを使用するための参照設定を行います。-をクリックします。参照可能なライブラリファイルから「Microsoft ActiveX Data Objects X.X Library」を選択します。 icon-check-square-o ADOのバージョンはOSなどの環境によって異なります。接続文字列一覧ADOからOracleへ接続するインターフェースにはOLE DBやODBCなどがありますが、ここではOLE DBでの接続文字列を記載します。Oracle社製のOLE DBを使用す...

ストアドプロシージャの作成

入力引数で受け取った文字列の文字数を出力引数として返すストアドプロシージャを作成します。

ストアドプロシージャをOracleでコンパイルしてエラーがないことを確認してください。

CREATE OR REPLACE PROCEDURE sample(str IN VARCHAR2, len OUT NUMBER)
IS
BEGIN
  len := LENGTH(str);
END ;
/

コマンドの設定

コマンドオブジェクトにストアドプロシージャの実行情報を設定します。

Dim cmd As New ADODB.Command
cmd.ActiveConnection = Connectionオブジェクト
cmd.CommandType = コマンドの種類
cmd.CommandText = ストアドプロシージャ名

パラメータの設定

パラメータオブジェクトにストアドプロシージャへの引数を設定します。

Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter
prm.Name = 引数の名前
prm.Type = 引数の型
prm.Direction = 引数のモード
prm.Size = 引数の最大長
prm.Value = 引数の値
cmd.Parameters.Append prm

引数は1行で設定することもできます。

Set prm = cmd.CreateParameter(引数の名前, 引数の型, 引数のモード, 引数の最大長, 引数の値)

ストアドプロシージャの実行

コマンドオブジェクトのExecuteメソッドでストアドプロシージャを実行します。

cmd.Execute

実行結果の表示

実行結果はパラメータオブジェクトに引数名を指定することで取得できます。

cmd.Parameters(引数の名前).Value

サンプルプログラム

Oracleに接続してストアドプロシージャの実行と結果を表示するサンプルプログラムを記載します。

Option Explicit

Sub sample()
  On Error GoTo ERR_HANDLER
  Dim strSQL As String
  Dim i As Long
  
  'オブジェクトの作成
  Dim cn As New ADODB.Connection

  '接続文字列の設定
  cn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=ネットサービス名;User ID=ユーザ名;Password=パスワード"
    
  'Oracle接続
  cn.Open

  'ストアドプロシージャの実行情報を設定
  Dim cmd As New ADODB.Command
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdStoredProc 'コマンドの種類
  cmd.CommandText = "sample" 'ストアドプロシージャ名
  
  'ストアドプロシージャへの引数を設定
  Dim prm As ADODB.Parameter
  Set prm = cmd.CreateParameter
  '入力引数
  prm.Name = "str"      '引数の名前
  prm.Type = adChar     '引数の型
  prm.Direction = adParamInput  '引数のモード
  prm.Size = 10         '引数の最大長
  prm.Value = "テスト"   '引数の値
  cmd.Parameters.Append prm
  '出力引数
  Set prm = cmd.CreateParameter
  prm.Name = "len"      '引数の名前
  prm.Type = adInteger  '引数の型
  prm.Direction = adParamOutput  '引数のモード
  cmd.Parameters.Append prm
  
  'ストアドプロシージャの実行
  cmd.Execute
  
  '実行結果の表示
  ActiveSheet.Range("A1") = cmd.Parameters("len").Value
  
  'オブジェクトのクローズ
  cn.Close
        
  'オブジェクトの開放
  Set prm = Nothing
  Set cmd = Nothing
  Set cn = Nothing
  
Exit Sub
    
ERR_HANDLER:
  'エラーメッセージ
  MsgBox Err.Number & ")" & Err.Description
 
  'オブジェクトのクローズ
  If Not cn Is Nothing Then
    If cn.State = adStateOpen Then cn.Close  
  End If 

  'オブジェクトの開放
  Set prm = Nothing
  Set cmd = Nothing
  Set cn = Nothing
 
End Sub