VB.NET + Framework



引数テスト_IN
拡張子:txtvbswsfjsphpjavahtmlutf8sjis
PROCEDURE 引数テスト_IN
(
	PM_STRING IN VARCHAR2
	,PM_NUMBER IN NUMBER
)

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始');
	DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_STRING);
	DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_NUMBER);

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;



PLSQL.vb
拡張子:txtvbswsfjsphpjavahtmlutf8sjis
Imports System.Data.OracleClient
Imports System.Windows.Forms

Public Class PLSQL

	Public myCon As New OracleConnection()
	Public myCommand As New OracleCommand()
	Dim Query As String = Nothing

	' ******************************************************
	' IN のみの引数の実行
	' ******************************************************
	Private Sub 引数テスト_IN_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles 引数テスト_IN.Click

		myCon.ConnectionString = _
		 "Server=night/xe;" + _
		 "User ID=ora01;" + _
		 "Password=ora01;"

		' *******************************************
		' 接続
		' *******************************************
		Try
			myCon.Open()
			myCommand.Connection = myCon
			' コマンドをSQL用に変更
			myCommand.CommandType = CommandType.Text
		Catch ex As Exception
			MessageBox.Show(ex.Message)
			Return
		End Try

		' *******************************************
		' DBMS_OUTPUT の結果を取得する為に 使用可能にする
		' *******************************************
		Query = "BEGIN DBMS_OUTPUT.ENABLE(); END;"
		myCommand.CommandText = Query
		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			myCon.Close()
			MessageBox.Show(ex.Message)
			Return
		End Try

		' コマンドをプロシージャ用に変更
		myCommand.CommandType = CommandType.StoredProcedure

		' *******************************************
		' プロシージャの実行
		' *******************************************
		myCommand.CommandText = "引数テスト_IN"
		myCommand.Parameters.Add("PM_STRING", OracleType.VarChar).Value = "0001"
		myCommand.Parameters.Add("PM_NUMBER", OracleType.Number).Value = 1234

		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			myCon.Close()
			MessageBox.Show(ex.Message)
			Return
		End Try

		' *******************************************
		' DBMS_OUTPUT の結果を取得する
		' *******************************************
		myCommand.Parameters.Clear()
		myCommand.CommandText = "DBMS_OUTPUT.GET_LINE"

		Dim PARAM As OracleParameter = _
		  New OracleParameter("line", OracleType.VarChar)
		PARAM.Direction = ParameterDirection.Output
		PARAM.Size = 200
		myCommand.Parameters.Add(PARAM)
		Dim STATUS As OracleParameter = _
		 New OracleParameter("status", OracleType.Number)
		STATUS.Direction = ParameterDirection.Output
		myCommand.Parameters.Add(STATUS)

		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			myCon.Close()
			MessageBox.Show(ex.Message)
			Return
		End Try

		' グリッドの列を作成
		Me.LboxGrid1.Reset()
		Me.LboxGrid1.AddColumn("LINE", "DBMS_OUTPUT.GET_LINEからのデバッグデータ")

		Do While STATUS.Value = 0

			Me.LboxGrid1.AddRow()
			Me.LboxGrid1.SetColumnText("LINE", PARAM.Value.ToString())

			Try
				myCommand.ExecuteNonQuery()
			Catch ex As Exception
				Exit Do
			End Try
		Loop

		myCommand.Parameters.Clear()
		myCon.Close()

	End Sub
End Class