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
[Windows]WSUSサーバの構築手順 1/6

Windows 2008 R2サーバにWSUSサーバを構築する Windows Server Update Services(WSUS)とは Windows S …

アクセス(Access)を購入せずに無料のランタイムだけで使う方法

Accessは、マイクロソフトが提供しているリレーショナルデータベースソフトです。 データベースにデータを蓄積することで、条件に一致するデータを抽出することやデ …

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

Windows 2008 R2サーバにWSUSサーバを構築する WSUS(Windows Server Update Service)のインストール [スタート …

エクセルファイルを無料のビューア(Excel Mobile)で開く【Excel Viewerの代替手段】

Microsoft Officeがインストールされていないパソコンでエクセルを開くために、マイクロソフトが提供していたMicrosoft Excel Viewe …

microsoft
Windows10パソコンを使ってKY-01Lの電話帳・連絡先を登録する方法

NTTドコモのカードケータイ KY-01Lの電話帳・連絡先の取り込みは、BluetoothやSIMカードを使った方法が説明書に記載されています。 Bluetoo …

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

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

microsoft
[Windows]ネットワーク一覧にコンピュータ名を表示させない方法

Windowsのエクスプローラでネットワーク上に存在するコンピュータの一覧を表示することができます。 一覧の中には開発機やユーザに公開したくないサーバなども表示 …

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

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

microsoft
[Windows]リモートデスクトップのシングルサインオン

ドメイン参加PCでシングルサインオンする Active Directoryのドメインに所属した場合、既定の設定のままでは、資格情報が使用できないため、 リモート …

microsoft
[Windows]「このリモートコンピューターのIDを識別できません。接続しますか?」の対応方法

警告の原因と対応方法 リモートデスクトップ接続をしたときに表示されるこのメッセージ 「このリモートコンピューターのIDを識別できません。接続しますか?」 「はい …