Hello,
Can someone please provide sample piece of code to do INSERT (or UPDATE/DELETE) to an external Oracle database.
How to handle Oracle stored procedure calls? Some samples would be great.
Thanks.
Hello,
Can someone please provide sample piece of code to do INSERT (or UPDATE/DELETE) to an external Oracle database.
How to handle Oracle stored procedure calls? Some samples would be great.
Thanks.
Here's a script that I found in an archive, I can't guarantee it's using the correct types for version 7 of 1IM, might give you a leg up...
#If Not SCRIPTDEBUGGER Then References Devart.Data.Oracle.dll Imports Devart.Data.Oracle References Devart.Data.dll Imports Devart.Data #End If Public Function Call_Update_Disabled(ByVal sUserName As String, ByVal sConnection As String) As Integer ' Stored procedure names Const cSPName As String = "XXXXXXX" Const cParm1Name As String = "i_loginname" Const cParm2Name As String = "o_success" Const cParm3Name As String = "o_errormessage" ' Status variables Dim iSuccess As Int32 = 0 Dim sErrMsg As String = String.Empty Dim conn As New OracleConnection(sConnection) Try conn.Open() ' Define and configure the command as a stored procedure Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = cSPName ' Parameter for username Dim p1 As OracleParameter = cmd.Parameters.Add(cParm1Name, OracleDbType.NVarChar, ParameterDirection.Input) p1.Value = sUserName ' Parameter for success Dim p2 As OracleParameter = cmd.Parameters.Add(cParm2Name, OracleDbType.Number, ParameterDirection.Output) p2.Value = 0 ' Parameter for Error Message Dim p3 As OracleParameter = cmd.Parameters.Add(cParm3Name, OracleDbType.NVarChar, ParameterDirection.Output) p3.Value = String.Empty cmd.ExecuteNonQuery() ' Collate status values iSuccess = CInt(p2.Value) sErrMsg = p3.Value.ToString AppData.Instance.RaiseMessage(MsgSeverity.Info, "Result=" & iSuccess.ToString & ", Error Message=" & sErrMsg) Catch ex As Exception Throw New Exception(ex.Message,ex.InnerException) Finally conn.close() End Try If iSuccess <> 1 Then Throw New Exception(String.Format("Error: {0}",sErrMsg)) Else Return iSuccess End If End Function
Here's a script that I found in an archive, I can't guarantee it's using the correct types for version 7 of 1IM, might give you a leg up...
#If Not SCRIPTDEBUGGER Then References Devart.Data.Oracle.dll Imports Devart.Data.Oracle References Devart.Data.dll Imports Devart.Data #End If Public Function Call_Update_Disabled(ByVal sUserName As String, ByVal sConnection As String) As Integer ' Stored procedure names Const cSPName As String = "XXXXXXX" Const cParm1Name As String = "i_loginname" Const cParm2Name As String = "o_success" Const cParm3Name As String = "o_errormessage" ' Status variables Dim iSuccess As Int32 = 0 Dim sErrMsg As String = String.Empty Dim conn As New OracleConnection(sConnection) Try conn.Open() ' Define and configure the command as a stored procedure Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = cSPName ' Parameter for username Dim p1 As OracleParameter = cmd.Parameters.Add(cParm1Name, OracleDbType.NVarChar, ParameterDirection.Input) p1.Value = sUserName ' Parameter for success Dim p2 As OracleParameter = cmd.Parameters.Add(cParm2Name, OracleDbType.Number, ParameterDirection.Output) p2.Value = 0 ' Parameter for Error Message Dim p3 As OracleParameter = cmd.Parameters.Add(cParm3Name, OracleDbType.NVarChar, ParameterDirection.Output) p3.Value = String.Empty cmd.ExecuteNonQuery() ' Collate status values iSuccess = CInt(p2.Value) sErrMsg = p3.Value.ToString AppData.Instance.RaiseMessage(MsgSeverity.Info, "Result=" & iSuccess.ToString & ", Error Message=" & sErrMsg) Catch ex As Exception Throw New Exception(ex.Message,ex.InnerException) Finally conn.close() End Try If iSuccess <> 1 Then Throw New Exception(String.Format("Error: {0}",sErrMsg)) Else Return iSuccess End If End Function