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

Question

The following code was used to return a single record from the database. I read that ExecuteScalar is the right way to return a single entry. However, I could never get ExecuteScalar to work. How can 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

From Microsoft

"The ExecuteOracleScalar () method of the OracleCommand class is used to execute an 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 REF CURSOR is returned, not the value of the first column of the first row that REF CURSOR points to. The ExecuteScalar () method of the OracleCommand class is similar to ExecuteOracleScalar (), except that it returns the value as a .NET Framework data type.

Having said that, none of these methods will come in handy when working with Oracle stored procedures. Oracle stored procedures cannot return a value as part of the RETURN statement, only as OUT parameters - see Section Stored Procedures that Do Not Return Data. In addition, you cannot return the result set, except for the output parameter REF CURSOR - this is discussed in the next section.

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

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

Answer 2

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

Answer 3

not sure why another answer is marked as accepted, as it does not answer your question.

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

ExecuteScalar will return only one value - so keep this in mind when writing the request part of your command. The code for doing this will 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