2008年09月16日

VBA + oo4o de Oracle

ExcelVBAからオラクルへ接続してストアドプロシージャを実行するサンプル。

oo4oで接続して、通常のSQLを発行できる人向け。

お世話になったサイト:
SAK Streets VB oo4o + SQL 応用編8 oo4o ストアドファンクション - PL/SQL ブロック
@634 oo4o - ストアドプログラムの利用

Oracle側にテスト用ストアドを作成。
CREATE OR REPLACE PROCEDURE HOGE.OO4OTEST(
P_YMD IN VARCHAR2
, P_OUT OUT VARCHAR2
)
IS
BEGIN
SELECT TO_CHAR(TO_DATE(P_YMD || '01', 'YYYYMMDD') - 1,'YYYYMMDD') INTO P_OUT FROM DUAL;
END;

VBA側ではセッションオブジェクト・データソースオブジェクトのCreateまではいつものお約束どおり。
ストアドで受け渡しに使うパラメータを以下のようにセットする。
OraDB.Parameters.Add "バインド変数名", "初期値", IN/OUTパラメータ
OraDB.Parameters("バインド変数名").ServerType = 型
IN/OUTパラメータは ORAPARM_INPUT/ORAPARM_OUTPUT/ORAPARM_BOTH
型は ORATYPE_VARCHAR2/ORATYPE_NUMBER など。
バインド変数の値を変更する場合は
OraDB.Parameters("バインド変数名").Value = "値"


VBA側のソース
Option Explicit

Private OraSess As Object
Private OraDB As Object
Private username As String
Private passwd As String
Private dbname As String

'=====================================
' 準備
'=====================================
Private Const ORADB_DEFAULT = &H0
Private Const ORAPARM_INPUT = 1
Private Const ORAPARM_OUTPUT = 2
Private Const ORAPARM_BOTH = 3
Private Const ORATYPE_VARCHAR2 = 1
Private Const ORATYPE_NUMBER = 2
Private Const ORATYPE_SINT = 3
Private Const ORATYPE_FLOAT = 4
Private Const ORATYPE_STRING = 5
Private Const ORATYPE_VARCHAR = 9
Private Const ORATYPE_DATE = 12
Private Const ORATYPE_UINT = 68
Private Const ORATYPE_CHAR = 96
Private Const ORATYPE_CHARZ = 97
Private Const ORATYPE_CURSOR = 102

'=====================================
' メイン処理
'=====================================
Sub main()
On Error GoTo Err_Han

dbname = "TNSNAME" '//tnsnames.oraのTNS名
username = "HOGE"
passwd = "HOGEHOGE"
'// オラクルオブジェクトが無い場合は接続する
If OraDB Is Nothing Then
Call Conn
End If
'// コネクションOKの場合、ストアド呼び出し
If OraDB.ConnectionOK = False Then
Call Conn
End If
'// データ取得メイン処理を呼び出す
CallOraProcedure
'// 終了処理
Call Terminate
'// 処理終了
Exit Sub

Err_Han:
' エラー処理
MsgBox (Err.Description)
'// 終了処理
Call Terminate
End Sub

'=====================================
' データベース接続処理
'=====================================
Private Sub Conn()
'// セッションオブジェクトの生成
Set OraSess = CreateObject("OracleInProcServer.XOraSession")
If Err <> 0 Then
MsgBox "データベースに接続出来ません。" & Chr(10) _
& "CreateObject - Oracle oo4o エラー"
End
End If
'// oo4oデータベースオブジェクトの生成
Set OraDB = OraSess.OpenDatabase(dbname, username & "/" & passwd, ORADB_DEFAULT)
If Err <> 0 Then
MsgBox "データベースに接続出来ません。" & Chr(10) _
& Err & ": " & Error
End
End If
End Sub

'=====================================
'データ取得メイン処理
'=====================================
Private Sub CallOraProcedure()
' 変数定義
Dim ret As String '//MsgBox用
' IN パラメータの設定
OraDB.Parameters.Add "par_in", "200404", ORAPARM_INPUT
OraDB.Parameters("par_in").ServerType = ORATYPE_VARCHAR2
' OUTパラメータの設定
OraDB.Parameters.Add "par_out", "", ORAPARM_OUTPUT
OraDB.Parameters("par_out").ServerType = ORATYPE_VARCHAR2
' 実行
OraDB.ExecuteSQL ("begin oo4otest(:par_in, :par_out); end;")
If Err.Description <> "" Then
MsgBox Err.Description, vbOKOnly + vbExclamation, "エラー"
Exit Sub
Else
ret = MsgBox(OraDB.Parameters("par_out").Value, vbOKOnly, "結果")
End If
End Sub

'=====================================
' データベース切断処理
'=====================================
Private Sub Terminate()

If Not OraDB Is Nothing Then
Call OraDB.Close
Set OraDB = Nothing
End If

Set OraSess = Nothing

End Sub


タグ:VBA EXCEL oo4o

posted by まきすけ at 17:30 | Comment(0) | TrackBack(0) | Linuxとか
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

この記事へのトラックバックURL
http://blog.seesaa.jp/tb/106654922

この記事へのトラックバック