I am facing a weird issue in my code while trying to execute query from .Net. Actually I have a procedure that takes 3 inputs and insert them in my oracle database: 2 decimal and 1 String. Below is my function in .Net:
Public Function MyFunct(ByVal userId As Decimal, ByVal claimId As Decimal, ByVal flag As Integer) As String
Dim returnedResponse As String = ""
Dim cmd As OracleCommand
cmd = New OracleCommand
cmd.BindByName = True
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MyPack.MyProc"
Dim Pflag As String = ""
If flag = 1 Then
Pflag = "Y"
Else
Pflag = "N"
End If
Try
Dim Puser_id As New OracleParameter("Puser_id", [Decimal])
SetParam(Puser_id, userId)
Puser_id.Direction = ParameterDirection.Input
cmd.Parameters.Add(Puser_id)
Dim Pclaimh_id As New OracleParameter("Pclaimh_id", [Decimal])
SetParam(Pclaimh_id, claimId)
Pclaimh_id.Direction = ParameterDirection.Input
cmd.Parameters.Add(Pclaimh_id)
Dim Psmsflag As New OracleParameter("Psmsflag", [Varchar2])
SetParam(Psmsflag, Pflag)
Psmsflag.Direction = ParameterDirection.Input
cmd.Parameters.Add(Psmsflag)
Dim P_ERROR As New OracleParameter("P_ERROR", NVarchar2, 1000)
SetParam(P_ERROR, Nothing)
P_ERROR.Direction = ParameterDirection.Output
cmd.Parameters.Add(P_ERROR)
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
cmd.ExecuteNonQuery()
If cmd.Parameters("P_ERROR").Value.Value IsNot DBNull.Value Then
returnedResponse = cmd.Parameters("P_ERROR").Value.Value
End If
conn.Close()
Catch ex As Exception
returnedResponse = ex.Message
End Try
Return returnedResponse
End Function
This function is used in many classes in the same way:
MyFunct(decUserID, Me.claimH_Id, SMSResponse)
But in some classes it works normally and in others the cmd.ExecuteNonQuery() enters in an infinite load, it never ends executing and I can't imagine why. But I realized something: when the claimId is set manually inside MyFunct, the same issue is faced while executing. I checked the Me.claimH_Id how it is filled, it is the same way for all classes, and in debugging mode I realized that all inputs are filled correctly without any issues and no errors are returned ... So what could be the problem? Why calling it from different places is causing issues? Any suggestions? Please Help!
What I have tried:
I tried to execute the procedure directly from Oracle and it is working in all cases.