Skill Note

平凡なエンジニアがメモ代わりにプログラミング, インフラ, ネットワークを書き綴るブログ

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

      2020/06/02

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

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

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

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

ストアドプロシージャを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

Google AdSense PC

Google AdSense PC

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

  関連記事

microsoft
VBAからOracleに接続する方法(Oracle Provider for OLE DB接続)

VBAから業務システムなどに使用しているデータベースに接続し、データを抽出することができます。 定期的に作成する資料などは、VBAでデータ取得処理を自動化し、ボ …

microsoft
[Oracle]VBAからSELECT文を実行しデータを取得する方法

VBAからOracleデータベースでSELECT文を実行してデータを取得するライブラリを作成しましたので記載します。 Oracle接続にはOracle Clie …

microsoft
[Windows]指定サイズのファイルを作成する

ネットワークの通信速度などを調査する場合にサイズの大きなファイルを作成する必要があります。 この記事では「fsutil」コマンドを使用したダミーファイルの作成方 …

microsoft
Emotetの感染チェックツール「EmoCheck」を定期的に自動実行する方法

Emotetの感染チェックツールEmoCheckをドメイン環境下のパソコンで定期的に自動実行する設定方法を記載します。 自動実行の概要 ① EmoCheckを起 …

microsoft
Windows標準機能を使ってフォルダの作成を自動化する方法

バッチファイルとタスクスケジューラでフォルダの作成を自動化する方法を記載します。 この記事の例では、毎月1日になると指定した場所に年月のフォルダを作成します。 …

microsoft
[Windows]共有フォルダへのアクセスログを取得する方法

VVAULT AUDITを使用したログ管理 Windowsでは監査ポリシーを設定することにより共有フォルダやファイルへのアクセスログを記録し、インベントビューア …

microsoft
[Windows]WSUSサーバの構築手順 2/6

Windows 2008 R2サーバにWSUSサーバを構築する Report Viewerのインストール Microsoftのサイトより[Microsoft R …

microsoft
Outlookでメール送信中になったまま送受信が終了しない場合

開封確認のメールが正常に送信されず、送信中のまま送受信が終了しない場合があります。 原因となる開封確認メールは送信トレイに存在しないため、削除することができませ …

microsoft
[Windows]IISセッション管理(状態サーバ)

IISのセッションをアウトプロセスで管理する方法 IISのセッションはインプロセスが既定設定となっておりワーカープロセスが再起動されるたびにセッション情報が破棄 …

microsoft
[Windows]Windows Server 2008 R2 IISインストール手順

Windows Server 2008 R2にIISをインストールする 環境 OS:Windows Server 2008 R2 IIS:ver7.5 インスト …