Oracle stored procedure working differently on different versions of windows server?

Question

User816415303 posted

Hello all, I hope someone can help me.  I have a piece of asp.net code that calls an Oracle Stored procedure.  The procedure has an output parameter which can only return "Y" or "N".  This has been working fine when I deploy to my Windows 2003 server, but I've just tried deploying the same code to a new windows 2012 server.  On the 2012 server, the store procedure is returning a null string.  There is no option in the Oracle stored procedure code to return anything other than "Y" or "N", so I'm guessing it must be something different on the server.  Does anyone know what this could be, and what I can do about it?  Here's the ASP code I'm using to call the procedure:

Public Function CHKCODEEXIST(ByVal codelist_code As String, ByVal cdisc_submission_value As String) As String
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionStringSDTMCONV").ConnectionString
Dim returnvalue As String = "?"
Dim oraCmd As New OracleCommand("PKG_VW_MAP_CODELIST_DETAILS.CHKCODEEXIST")
oraCmd.CommandType = System.Data.CommandType.StoredProcedure
oraCmd.Parameters.Add(New OracleParameter("I_CODELIST_CODE", OracleType.NVarChar)).Value = codelist_code
oraCmd.Parameters.Add(New OracleParameter("I_CDISC_SUBMISSION_VALUE", OracleType.NVarChar)).Value = cdisc_submission_value
oraCmd.Parameters.Add(New OracleParameter("O_RETURNVALUE", OracleType.NVarChar)).Direction = ParameterDirection.Output
oraCmd.Parameters("O_RETURNVALUE").Size = 1

Dim oConn As New OracleConnection(connectionString)
oConn.Open()
oraCmd.Connection = oConn
oraCmd.ExecuteNonQuery()
returnvalue = oraCmd.Parameters("O_RETURNVALUE").Value
oConn.Close()
oraCmd.Dispose()

Return returnvalue

End Function

Answer 1

User269602965 posted

1. good practice to clear parameters  oraCmd.Parameters.Clear() before adding new parms.

2. verify your app user account has permission to run the procedure on the database

3. If so, log into Oracle manually with your Client and run the procedure with some test input and see if out put is still null.

4. Oracle OracleType.NVarChar is not often used.  Check the target table and see if it is not VARCHAR2.

5. presumed you debugged your code to capture values of I_CODLIST_CODE et al to determine they are passing correct values to the stored proc

that exist in the database so it would return the values y or n.  Remember, if there is WHERE clause on Oracle side, that is CASE SENSITIVE

or will return NULL.  What you are passing must match the case of the filter var in the Oracle table.

Answer 2

User816415303 posted

Thanks for the pointers.  Unfortunately, I think it must be something to do with the windows server configuration.  After testing, I simplified the Oracle stored procedure so that all it did was return a "Y" value, and even then, this would not work when I called it from my website on the 2012 server.  It still works fine from the old 2003 windows server deployment though.  

Does anyone know what I could look for on the server config that may be affecting the way that website interact with an Oracle DB?

Answer 3

User269602965 posted

Firewall

Server Firewall must allow Oracle TNSLISTENER Port  default is 1521 but can be configured to something else.

Answer 4

User269602965 posted

USING takes care of close and dispose

And capturing exceptions might give you error clues

Public Function CHKCODEEXIST(ByVal codelist_code As String, ByVal cdisc_submission_value As String) As String
Try
 Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionStringSDTMCONV").ConnectionString
 Dim returnvalue As String = "?"
 Using oraCmd As New OracleCommand("PKG_VW_MAP_CODELIST_DETAILS.CHKCODEEXIST")

   oraCmd.CommandType = System.Data.CommandType.StoredProcedure
   oraCmd.Parameters.Add(New OracleParameter("I_CODELIST_CODE", OracleType.NVarChar)).Value = codelist_code
   oraCmd.Parameters.Add(New OracleParameter("I_CDISC_SUBMISSION_VALUE", OracleType.NVarChar)).Value = cdisc_submission_value
   oraCmd.Parameters.Add(New OracleParameter("O_RETURNVALUE", OracleType.NVarChar)).Direction = ParameterDirection.Output
   oraCmd.Parameters("O_RETURNVALUE").Size = 1

     Using oConn As New OracleConnection(connectionString)
       oConn.Open()
       oraCmd.Connection = oConn
       oraCmd.ExecuteNonQuery()
       returnvalue = oraCmd.Parameters("O_RETURNVALUE").Value
     End Using
  End Using   
Catch OraEx AS OracleException
  handle you oracle exceptions here
End Try
Return returnvalue

End Function

Answer 5

User816415303 posted

Thanks all for your suggestions.  In the end it turns out that Oracle was not properly installed on the server.  Two version had been installed, and this was causing some problems.  When they were removed, and Oracle V12 only was installed, the application started working again successfully.