How do you use ExecuteScalar to return a single value from an Oracle Database?

Question

Been using the code below to return a single record from the database. I have read that ExecuteScalar is the right way to return a single record. I have never been able to get ExecuteScalar to work though. How would I change this to return a single value in VB.Net using ExecuteScalar?

    Dim oracleConnection As New OracleConnection
    oracleConnection.ConnectionString = LocalConnectionString()

    Dim cmd As New OracleCommand()
    Dim o racleDataAdapter As New OracleClient.OracleDataAdapter

    cmd.Connection = oracleConnection
    cmd.CommandText = "FALCON.CMS_DATA.GET_MAX_CMS_TH"
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add(New OracleParameter("i_FACID_C", OracleType.Char)).Value = facilityShortName
    cmd.Parameters.Add(New OracleParameter("RS_MAX", OracleType.Cursor)).Direction = ParameterDirection.Output

    Try
        Using oracleConnection
            oracleConnection.Open()
            Using oracleDataAdapter
                oracleDataAdapter = New OracleClient.OracleDataAdapter(cmd)

                Dim workingDataSet As DataSet
                oracleDataAdapter.TableMappings.Add("OutputSH", "RS_MAX")
                workingDataSet = New DataSet
                oracleDataAdapter.Fill(workingDataSet)


                For Each row As DataRow In workingDataSet.Tables(0).Rows

                    Return CDate(row("MAXDATE"))

                Next
            End Using
        End Using

Answer 1

ExecuteScalar returns a single value (scalar) not a record.

Answer 2

not sure why the other answer is marked as accepted, as it doesn't appear to answer your question

How would I change this to return a single value in VB.Net using ExecuteScaler

ExecuteScalar will only return a Single Value - so keep that in mind when writing the query portion of your command. The code to accomplish this would be as follows:

oracleConnection.Open
Dim obj as object 'Object to hold our return value
obj = cmd.ExecuteScalar()
oracleConnection.Close

If obj IsNot Nothing then
 Return CDate(obj)
end if

Answer 3

From Microsoft

"The ExecuteOracleScalar() method of the OracleCommand class is used to execute a SQL statement or stored procedure that returns a single value as an OracleType data type. If the command returns a result set, the method returns the value of the first column of the first row. The method returns a null reference if a REF CURSOR is returned rather than the value of the first column of the first row to which the REF CURSOR points. The ExecuteScalar() method of the OracleCommand class is similar to the ExecuteOracleScalar() method, except it returns a value as a .NET Framework data type.

Having said that, neither of these methods is useful when working with Oracle stored procedures. Oracle stored procedures cannot return a value as part of the RETURN statement, only as OUT parameters—see the Stored Procedures That Do Not Return Data section. Also, you cannot return a result set except through a REF CURSOR output parameter—this is discussed in the next section.

You can retrieve the return value for an Oracle function only by using a RETURN parameter (shown in the previous section) and not by using the one of the ExecuteScalar methods."

http://msdn.microsoft.com/en-us/library/ms971506.aspx