コメント |
http://lightbox.cocolog-nifty.com/photos/app/ref_oracle.png
@DIV
Imports System.Data.OracleClient
Imports System.Windows.Forms
Public Class PLSQL
Dim Session As OracleInProcServer.OraSessionClass = New OracleInProcServer.OraSessionClass()
Dim Cn As OracleInProcServer.OraDatabase
Const ORAPARM_INPUT = 1
Const ORAPARM_OUTPUT = 2
Const ORAPARM_BOTH = 3
Const ORATYPE_VARCHAR2 = 1
Const ORATYPE_NUMBER = 2
' ******************************************************
' IN のみの引数の実行( OO4O )
' ******************************************************
Private Sub 引数テストINOO4OToolStripMenuItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles 引数テストINOO4OToolStripMenuItem.Click
' *******************************************
' 接続
' *******************************************
Try
Cn = Session.OpenDatabase("night/xe", "ora01/ora01", 0)
Catch ex As Exception
MessageBox.Show(ex.Message)
Return
End Try
Query = "BEGIN DBMS_OUTPUT.ENABLE(); END;"
' *******************************************
' DBMS_OUTPUT の結果を取得する為に 使用可能にする
' *******************************************
Try
Cn.ExecuteSQL(Query)
Catch ex As Exception
MessageBox.Show(ex.Message)
Return
End Try
' *******************************************
' プロシージャの実行
' *******************************************
Query = "Begin 引数テスト_IN(:PM_STRING, :PM_NUMBER); end;"
Dim params As OracleInProcServer.OraParameters = Cn.Parameters
params.Add("PM_STRING", "0001", ORAPARM_INPUT, ORATYPE_VARCHAR2)
params.Add("PM_NUMBER", 1234, ORAPARM_INPUT, ORATYPE_NUMBER)
Try
Cn.ExecuteSQL(Query)
Catch ex As Exception
MessageBox.Show(ex.Message)
Return
End Try
' *******************************************
' DBMS_OUTPUT の結果を取得する
' *******************************************
Query = "Begin DBMS_OUTPUT.GET_LINE(:line, :status); end;"
For i As Integer = 0 To params.Count - 1
params.Remove(i)
Next
params.Add("line", Nothing, ORAPARM_OUTPUT, ORATYPE_VARCHAR2)
params.Add("status", Nothing, ORAPARM_OUTPUT, ORATYPE_NUMBER)
Try
Cn.ExecuteSQL(Query)
Catch ex As Exception
MessageBox.Show(ex.Message)
Return
End Try
' グリッドの列を作成
Me.LboxGrid1.Reset()
Me.LboxGrid1.AddColumn("LINE", "DBMS_OUTPUT.GET_LINEからのデバッグデータ")
Dim getParam As OracleInProcServer.OraParameter
Do While params.Item("status").value = 0
Me.LboxGrid1.AddRow()
getParam = params.Item("line")
Me.LboxGrid1.SetColumnText("LINE", getParam.Value)
Try
Cn.ExecuteSQL(Query)
Catch ex As Exception
Exit Do
End Try
Loop
' *******************************************
' COM の close メソッドは、なにもしません
' *******************************************
Cn.Close()
End Sub
End Class
@END
@DIV
typedef [uuid(DB33CDC6-8A3F-11D2-AC85-00C04FA32B1D)]
enum {
ORAPARM_INPUT = 1,
ORAPARM_OUTPUT = 2,
ORAPARM_BOTH = 3
} paramMode;
typedef [uuid(F2077174-8A3F-11D2-AC85-00C04FA32B1D)]
enum {
ORATYPE_VARCHAR2 = 1,
ORATYPE_NUMBER = 2,
ORATYPE_SINT = 3,
ORATYPE_FLOAT = 4,
ORATYPE_STRING = 5,
ORATYPE_DECIMAL = 7,
ORATYPE_LONG = 8,
ORATYPE_VARCHAR = 9,
ORATYPE_DATE = 12,
ORATYPE_REAL = 21,
ORATYPE_DOUBLE = 22,
ORATYPE_UNSIGNED8 = 23,
ORATYPE_RAW = 23,
ORATYPE_LONGRAW = 24,
ORATYPE_UNSIGNED16 = 25,
ORATYPE_UNSIGNED32 = 26,
ORATYPE_SIGNED8 = 27,
ORATYPE_SIGNED16 = 28,
ORATYPE_SIGNED32 = 29,
ORATYPE_PTR = 32,
ORATYPE_OPAQUE = 58,
ORATYPE_UINT = 68,
ORATYPE_CHAR = 96,
ORATYPE_CHARZ = 97,
ORATYPE_BFLOAT = 100,
ORATYPE_BDOUBLE = 101,
ORATYPE_CURSOR = 102,
ORATYPE_ROWID = 104,
ORATYPE_MLSLABEL = 105,
ORATYPE_OBJECT = 108,
ORATYPE_REF = 110,
ORATYPE_CLOB = 112,
ORATYPE_BLOB = 113,
ORATYPE_BFILE = 114,
ORATYPE_CFILE = 115,
ORATYPE_RSLT = 116,
ORATYPE_NAMEDCOLLECTION = 122,
ORATYPE_TIMESTAMP = 187,
ORATYPE_TIMESTAMPTZ = 188,
ORATYPE_INTERVALDS = 190,
ORATYPE_INTERVALYM = 189,
ORATYPE_SYSFIRST = 228,
ORATYPE_TIMESTAMPLTZ = 232,
ORATYPE_SYSLAST = 235,
ORATYPE_OCTET = 245,
ORATYPE_SMALLINT = 246,
ORATYPE_VARRAY = 247,
ORATYPE_TABLE = 248,
ORATYPE_OTMLAST = 320,
ORATYPE_RAW_BIN = 2000,
ORATYPE_RAW_HEX = 5
} serverType;
@END
|