title: Possible to call Oracle FUNCTION from .Net using Enterprise Library?
body: <p>I have the following Oracle function:</p>
  
  <pre><code>function get_job_no return number is
          V_job_no number;
        begin
           select appwork.tlm_corphier_job.nextval into V_job_no from dual;
           return V_job_no;
        end get_job_no;
  </code></pre>
  
  <p>PLEASE NOTE:<br>
  1) This is a FUNCTION, not a procedure<br>
  2) This is returning a NUMBER, not a VARCHAR<br>
  3) I happen to be using System.Data.OracleClient rather than Oracle.DataAccess.Client (due to another issue I was having) but a solution with either would be fine.  </p>
  
  <p>I am trying to call this from VB.Net using the MS enterprise library like so:</p>
  
  <pre><code>Imports Microsoft.Practices.EnterpriseLibrary.Data
  Imports System.Data.OracleClient
      Public Function GetNextJobNumber() As Object
          Dim db As Database = DatabaseFactory.CreateDatabase()
          Dim myresult As Object = Nothing
          Using cmd As DbCommand = db.GetStoredProcCommand("CORPHIER_PKG.get_job_no")
              Dim retval As New Data.OracleClient.OracleParameter("retval", GetType(System.Data.OracleClient.OracleNumber))
              retval.Direction = ParameterDirection.ReturnValue
              cmd.Parameters.Add(retval)
              db.ExecuteNonQuery(cmd)
              myresult = retval.Value
          End Using
          Return myresult
      End Function
  </code></pre>
  
  <p>I get the following error:</p>
  
  <p>System.InvalidCastException: Failed to convert parameter value from a RuntimeType to a Decimal. ---> System.InvalidCastException: Object must implement IConvertible.
     at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
     at System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType)
     --- End of inner exception stack trace ---
     at System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType)
     at System.Data.OracleClient.OracleParameter.SetCoercedValueInternal(Object value, MetaType metaType)
     at System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection connection, Int32&amp; offset)
     at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor&amp; rowidDescriptor, ArrayList&amp; resultParameterOrdinals)
     at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor&amp; rowidDescriptor)
     at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
     at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
     at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)</p>