OracleCommand StoredProcedure returning "null" instead of DBNull

Question

I have a stored procedure that in certain situations returns null. When I ToString() the null it returns "null" as a string instead of an empty string, however if I don't use the .ToString() method it causes an error.

Public Function ReturnValue(ByVal lngId As Long) As String
    Dim adoCmd As New OracleCommand
    Dim strReturn As String = ""

    With adoCmd

        .CommandText = "BUS_TEST.ReturnValue"
        .CommandType = CommandType.StoredProcedure

        .Parameters.Add("return", OracleDbType.Char, 256, strReturn, ParameterDirection.ReturnValue)
        .Parameters.Add("lngId", OracleDbType.Double, lngId, ParameterDirection.Input)
    End With

    objSalUtil.ExecFunc(ocEWBConnection, adoCmd, "ReturnValue")

    Return adoCmd.Parameters(0).Value.ToString

End Function

The above code returns "null"

Public Function ReturnValue(ByVal lngId As Long) As String
    Dim adoCmd As New OracleCommand
    Dim strReturn As String = ""

    With adoCmd

        .CommandText = "BUS_TEST.ReturnValue"
        .CommandType = CommandType.StoredProcedure

        .Parameters.Add("return", OracleDbType.Char, 256, strReturn, ParameterDirection.ReturnValue)
        .Parameters.Add("lngId", OracleDbType.Double, lngId, ParameterDirection.Input)
    End With

    objSalUtil.ExecFunc(ocEWBConnection, adoCmd, "ReturnValue")

    Return adoCmd.Parameters(0).Value

End Function

The above code causes an InvalidCastException: Conversion from type 'OracleString' to type 'String' is not valid.

Public Function ReturnValue(ByVal lngId As Long) As String
    Dim adoCmd As New OracleCommand
    Dim strReturn As String = ""

    With adoCmd

        .CommandText = "BUS_TEST.ReturnValue"
        .CommandType = CommandType.StoredProcedure

        .Parameters.Add("return", OracleDbType.Char, 256, strReturn, ParameterDirection.ReturnValue)
        .Parameters.Add("lngId", OracleDbType.Double, lngId, ParameterDirection.Input)
    End With

    objSalUtil.ExecFunc(ocEWBConnection, adoCmd, "ReturnValue")

    Return strReturn

End Function

And lastly, the above code returns a proper empty string, however it does so in all cases, regardless of whether something should have been returned or not.

I've used stored procedures like this a lot in the past, however they've always returned a value in all cases. I'm not quite sure how to handle this null. I'd prefer to not check for a string containing "null" first, as this is a little hacky, and I'd like to know for the future what I'm doing wrong.

Answer 1

I think, you're using ODP.NET. In this case, instead of this

Return adoCmd.Parameters(0).Value

You do this

Dim oraStr As OracleString = CType(adoCmd.Parameters(0).Value, OracleString)
Return oraStr.Value ' return .net string

What happening is, this adoCmd.Parameters(0).Value returns you Oracle null. If you cast your return value into Oracle Type, now you can access .Net-typed value. when you use adoCmd.Parameters(0).Value.ToString() you get Oracle implementation of ToString, which simply returns some word null.

This is something to remember when you use odp.net Oracle parameters, Stored Procedure or parametrized query with return values - doesn't matter, like here

Dim sql as String = "insert into table ...) returning fld1 into :1"

Interesting Observation: In SqlClient this is illegal Parameters(0).Value = Nothing. It wants DBNull.Value. In ODP.NET is not a problem. It takes any - Nothing or/and DBNull.Value

When you use OracleReader, especially through IDataReader - this is not a problem. Reader("fld1") will return .net type.

Dim r As IdataReader = cmd.ExecuteReader...
Dim i As Integer = reader("fld1")

Where you need to be careful is in matching Oracle DB Type to .NET. Problem is, they don't match. If you declare table field Number(9) it will return .net Integer but Number(9) not large enough to fit Integer.MaxValue. If you declare table field Number(10), Reader("fld1") will return Long. So, .net Integer falls somewhere between Number(9) and Number(10). You need to use convert

 Dim i As Integer = convert.ToInt32(reader("fld1"))

Answer 2

It turns out adding this line to between the two parameters fixed the problem.

adoCmd.Parameters(0).DbType = DbType.String