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]IISセッション管理(状態サーバ)

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

microsoft
エクセルで円と銭の通貨単位を表示する

Excelで入力した数値に円と銭の単位を表示する方法を記載します。 2種類の表示方法がありますので、用途に応じて使い分けてください。 セルの書式設定で表示 セル …

microsoft
VBAで形式を指定して日付を取得する

VBAで西暦や和暦の日付形式に変換するには「FORMAT」関数に引数を指定することで意図した日付形式に変換することができます。 日付を表示 Date関数で今日の …

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

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

microsoft
既存パソコンを有効活用した、Windows 10への移行方法を考える

企業や個人用など数多くのパソコンで稼働しているOS、Windows 7のサポート期間が2020年1月14日に終了します。 サポート期間終了に向けてWindows …

microsoft
VBAでひらがな・カタカナの小文字(捨て仮名)を全角に変換する

VBAで半角文字から全角文字に変換したり、ひらがなをカタカナに変換するには、「StrConv」関数に引数を指定することで意図した文字に変換することができます。た …

Microsoft TeamsのアカウントがないユーザをWEB会議に招待する方法

Microsoft Teamsは、Microsoftが提供しているコミュニケーションツールです。Teamsの機能として、チャットや音声通話・WEB会議などを利用 …

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

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

microsoft
Excel VBAでOracleのデータを参照するマスター検索機能を作成する

エクセルのVBAでOracleに保存されているマスタを検索する機能を作成する機会がありましたので、サンプルプログラムを記載します。 Oracleへの接続設定 V …

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

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