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

  • 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

    Tuesday, February 16, 2016 11:37 AM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 20, 2016 2:37 PM

All replies

  • 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.

    Tuesday, February 16, 2016 9:46 PM
  • 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?

    Wednesday, February 17, 2016 12:44 PM
  • User269602965 posted

    Firewall

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

    Wednesday, February 17, 2016 7:45 PM
  • 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
    

    Wednesday, February 17, 2016 9:46 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 20, 2016 2:37 PM