ExecuteNonQuery problem

[HarshaJoshi]

Hello All,

I am facing issue when executing the statement ExecuteNonQuery in VB.net WinForms application.

Issue:
I am trying to execute a stored procedure that has INSERT statements using ExecuteNonQuery. But after executing the stored procedure, the control is not coming back to VB.net and it just keeps hanging/not responding.

Code:
Dim strOutputParam As String
oraCmd = New OracleCommand
LoadParamsToCommand(oraCmd, oraParamArray, strOutputParam)
With oraCmd
.CommandType = CommandType.StoredProcedure
.CommandText = strSpName
.Connection = objTrans.Connection
.Transaction = objTrans
.ExecuteNonQuery()
If InStr(strSpOutputSuccess, .Parameters(strOutputParam).Value) > 0 Then
'Execution Success
Return True
Else
'Execution Failure
Return False
End If
End With



Can anyone help me immediately please to solve this issue? Would appreciate that.

Thanks,
Harsha

 

[kulrom]

Hmm i think you have a several mistakes in your code.

1. you do not open the connection.
2. If your SP contains Output Parameter you should add it to the command e.g.

.Parameters.Add("@ParamName", TypeHere).Direction = Output

3. if you want to get the Output param you should check if it's DbNull prior e.g.

If Not IsDbNull(oraCmd.Parameters("@ParamName").Value) Then
    ''Execution Success
End If

4. you should put all that code outside With/End With statement

 

[kulrom]

Ok i guess i am not right about the suggestion #2 as i just saw this line: LoadParamsToCommand(oraCmd, oraParamArray, strOutputParam)
I guess it adds the parameters to the command. right?

 

[HarshaJoshi]

Kulrom,

Thanks for you reply. Appreciate that.

Your questions:
1. Connection - Connection is open, we open the connection before calling this small code
2. Yes, you are right, parameters are added in LoadParamsToCommand() function/sub

More information:
We executed the Stored from Backend. That worked and stored procedure execution completed in 30 mins. According to our DBA's, they say that the stored proc is getting completed in almost around 30 mins only. They say this, when we start our session from front end and ask them to trace it. Finally, they say session is completed. But, but, but, our front end keeps hanging at that ExecuteNonQuery statement itself. It does not come out even after 2 days. We need to EndTask it.

COMMIT is present in front end. Since the control did not return from stored proc, and we EndTask, COMMIT does not happen.
Very fed up with this one boss.

Please help...

Thanks,
Harsha

 

[HarshaJoshi]

Anyone there, please help...

 

[kulrom]

Please post the full code and show where you are trying to use the retrieved data.

Do you have a Try Catch block over there ? I don't see it from here.

 

[cjard]

2. If your SP contains Output Parameter you should add it to the command e.g.

.Parameters.Add("@ParamName", TypeHere).Direction = Output

Small note: oracle uses : for parameter marker, not @ and also it doesnt use the : in the name

If your sproc is:

PROCEDURE test (
  input_str IN VARCHAR2,
  output_str OUT VARCHAR2
)

Your .Parameters is:

.Parameters.Add(New OracleParameter("input_str", value.. type.. etc..)
.Parameters.Add(New OracleParameter("output_str", value.. type.. direction output etc..)

Seem to recall that AddWithValue doesnt work with OracleCommand (or maybe it does with the microsoft-oracle one but not the oracle-oracle one)


If your SPROC is doing a SELECT..FOR UPDATE.. then you can try adding NOWAIT and see if you get an error that ORacle cannot acquire the lock it wants on the rows

I note youre using a transaction, and I'm thinking you might have deadlocked your own app with whatever your SPROC does

If the sproc is on its own, then you DO NOT need it as part of a transaction. Rather than have the transaction managed in VB, if you have 2 sprcs (A and B) that must be in a stransaction, make another SPROC C that calls 2 sprocs together and run the transaction entirely within oracle

3. if you want to get the Output param you should check if it's DbNull prior e.g.

I don't think this throws an error, except if youre using datatables and the column is set to If Null (Throw Exception) so it's not strictly necessary to check..

 

[cjard]

ps; it's taking your proc 30 minutes to complete? Wow.. that's a big proc.. Will need to increase timeout on your .net connection/command. Try making that sproc faster, optimize the quieries within or restructure the operation so it's not so lengthy/in smaller parts. Be aware that your oracle might run out of rollback/undo space if the query is running for too long. i try to keep all my qieries under 5 mins run time. If you need help optimising the proc itself just make a posting..

 

[kulrom]

Yeah that's what i though too (wow) ... 30 minutes is way too much. Your DBA's should definitely read some DB performance tunning tips.
If you ask me i would make them read at least one book on this subject e.g. APRESS.COM : SQL Server Query Performance Tuning Distilled, Second Edition : 9781590594216 (it may certainly be of help although you are dealing with Oracle)

Please don't get me wrong but 30 minutes is out of every logic. Simply it makes no sense.

 

[cjard]

Heh.. we have a financial cycle completion process that runs for 22 hours, and then says "Error: there was an error" - glad I'm not the one debugging that..