ソース掲示板




すべてから検索

キーワード   条件 表示 現行ログ 過去ログ トピックス 名前 本文
【VB.NET + OO4O】 PL/SQL へのアクセス ( No.1 )
日時: 2008/06/05 21:35
名前: lightbox





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
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;