This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Sample of INSERT/UPDATE/DELETE to an Oracle Database

IDM Engineer over 8 years ago

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.

Parents

0 Paul Walker over 8 years ago

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

Reply

0 Paul Walker over 8 years ago

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

Children

No Data