Calling Oracle stored procedure- error

[eshaanye]

-Using .Net managed provider for Oracle.

I am trying to call an Oracle stored procedure with 2 in parameters and 1 out parameter.

I am getting an error at the point - mobjCmd.ExecuteNonQuery()
------------------------------------------------------------
An unhandled exception of type 'System.Data.OracleClient.OracleException' occurred in system.data.oracleclient.dll

Additional information: System error.
------------------------------------------------------------


Any help is appreciated!


Here is the code frag-
------------------------------
mobjCmd = New OracleCommand()

mstrConexion.Open()

mobjCmd.Connection = mstrConexion
mobjCmd.CommandText = "SP_UPDATE_INDUSTRY_CLASS"
mobjCmd.CommandType = CommandType.StoredProcedure
mobjCmd.Parameters.Add(New OracleParameter("pSicCode", OracleType.VarChar)).Value = strCode
mobjCmd.Parameters.Add(New OracleParameter("pSicDescription", OracleType.VarChar)).Value = strDescription
mobjCmd.Parameters.Add(New OracleParameter("pSuccess", OracleType.VarChar, 1)).Direction = ParameterDirection.Output

mobjCmd.ExecuteNonQuery()
strUpdateSuccess = mobjCmd.Parameters(2).Value

[smcgarry]

I'm not as familiar with the oracle model but I think your problem lies in how you're adding parameters to your parameter collection. In SQL, you can't add the value at the same time you physically add the paramter to the collection. Have you tried this:

mobjCmd.Parameters.Add(New OracleParameter("pSicCode", OracleType.VarChar))
mobjCmd.Parameters("pSicCode").Value = strCode
mobjCmd.Parameters.Add(New OracleParameter("pSicDescription", OracleType.VarChar))
mobjCmd.Parameters("pSicDescription").Value = strDescription
mobjCmd.Parameters.Add(New OracleParameter("pSuccess", OracleType.VarChar, 1))
mobjCmd.Parameters("pSuccess").Direction = ParameterDirection.Output

I know with SQL this is the basic method I use and it seems to work ok. If you've used your original method before to call Oracle stored procedures and they have executed fine, then I would suggest checking the syntax of the stored procedure...there may be a problem within the stored procedure itself that is forcing oracle to raise an error (which is a strong possible since the error being raise is an OracleException).