How to get value from an OracleParameter object

Question

As the code shown below, I want to get value from the OracleParameter object. Its datatype is datetime.

...

Dim cmd As New OracleCommand("stored_proc_name", cnObject)
cmd.Parameters.Add("tran_date_out", OracleDbType.Date, ParameterDirection.Output)

...

cmd.ExecuteNonQuery()

...

Dim tranDate As Date
tranDate = cmd.Parameters("tran_date_out").Value

When I assign value to tranDate variable, I get an error. But if I code as below, I get only the date.

tranDate = CDate(cmd.Parameters("tran_date_out").Value.ToString)

So how can I get the value both date and time to tranDate variable?

Answer 1

off the top of my head, the OracleParameter.Value, when an out parameter, is assigned to a strange Oracle boxed type. It seems like a completely terrible design or Oracle's part... but instead of returning String, you will get OracleString, etc.

Each of the Oracle types has a .Value that has the system type, but of course they don't all implement a common interface to expose this, so what I did was basically write a method to unbox the types:

/// <summary>
/// The need for this method is highly annoying.
/// When Oracle sets its output parameters, the OracleParameter.Value property
/// is set to an internal Oracle type, not its equivelant System type.
/// For example, strings are returned as OracleString, DBNull is returned
/// as OracleNull, blobs are returned as OracleBinary, etc...
/// So these Oracle types need unboxed back to their normal system types.
/// </summary>
/// <param name="oracleType">Oracle type to unbox.</param>
/// <returns></returns>
internal static object UnBoxOracleType(object oracleType)
{
  if (oracleType == null)
    return null;

  Type T = oracleType.GetType();
  if (T == typeof(OracleString))
  {
    if (((OracleString)oracleType).IsNull)
      return null;
    return ((OracleString)oracleType).Value;
  }
  else if (T == typeof(OracleDecimal))
  {
    if (((OracleDecimal)oracleType).IsNull)
      return null;
    return ((OracleDecimal)oracleType).Value;
  }
  else if (T == typeof(OracleBinary))
  {
    if (((OracleBinary)oracleType).IsNull)
      return null;
    return ((OracleBinary)oracleType).Value;
  }
  else if (T == typeof(OracleBlob))
  {
    if (((OracleBlob)oracleType).IsNull)
      return null;
    return ((OracleBlob)oracleType).Value;
  }
  else if (T == typeof(OracleDate))
  {
    if (((OracleDate)oracleType).IsNull)
      return null;
    return ((OracleDate)oracleType).Value;
  }
  else if (T == typeof(OracleTimeStamp))
  {
    if (((OracleTimeStamp)oracleType).IsNull)
      return null;
    return ((OracleTimeStamp)oracleType).Value;
  }
  else // not sure how to handle these.
    return oracleType;
}

This probably isn't the cleanest solution, but... it was quick and dirty,a nd does work for me.

Just pass the OracleParameter.Value into this method.

---

Actually, I might have only 1/2 read your question before answering. I think Oracle's Date type only contains the date not the time.

The oracle type Timestamp has both the date and time.

Hope that helps! :)