It starts innocently enough: you have a PL/SQL procedure or function that returns a number (either as an OUT parameter or a return value respectively) to your VB client application. You know for a fact that the returned value will be less than the possible maximum value for the CLR type Int32 (represented as Integer in your VB code). It's dead easy and nothing can go wrong here, right? Except that from the title of this post you already know something is going to go unexpectedly. But let's not get too far ahead.
In order to understand what may (or may not) go wrong here, let's create a PL/SQL function that is guaranteed to return a valid Int32 value:
create or replace function getone return number
as
begin
return 1;
end;
/
This function does nothing but return the value "1" (as a number) to the caller and that number is clearly an acceptable Int32 value – there's no trickery here.
We can use trusty old SQL*Plus to verify that it does what it says it does:
SQL> select getone from dual;
GETONE
----------
1
1 row selected.
SQL>
It sure does return the expected value, so let's create a simple VB project to test this out. I'm using ODP.NET version 10.2.0.4 for this experiment and here's the first version of the VB code:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Module Module1
Sub Main()
' change connection string for your environment
Dim constr As String = "User Id=hr;" & _
"Password=hr;" & _
"Data Source=oel11gr1;" & _
"Enlist=false;" & _
"Pooling=false"
' create and open connection
Dim con As OracleConnection = New OracleConnection(constr)
con.Open()
' create command and setup to call test function
Dim cmd As OracleCommand = con.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "getone"
' create parameter for the test function return value
Dim retval As OracleParameter = New OracleParameter()
retval.Direction = ParameterDirection.ReturnValue
retval.OracleDbType = OracleDbType.Int32
' add the parameter to the parameters collection
cmd.Parameters.Add(retval)
' execute the function
cmd.ExecuteNonQuery()
' get the return value in a local variable
Dim my_one As Integer = retval.Value
' basic cleanup
retval.Dispose()
cmd.Dispose()
con.Dispose()
End Sub
End Module
NOTE: I added a reference to the ODP.NET assembly to the project.
After entering the above code I built the project and got the all-important "success" message:
[ snip ]
ConversionTest -> C:\My Projects\Test\VB\ConversionTest\bin\Debug\ConversionTest.exe
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
So, let's go ahead and run this in debug mode from the IDE and see what happens…
Oops. It stops on the "Dim my_one As Integer = retval.Value" source line and pops up a dialog indicating that "System.InvalidCastException was unhandled". Digging a bit deeper we see "Conversion from type 'OracleDecimal' to type 'Integer' is not valid."
What? The code compiled with no problem, so what does this mean? Quickly looking at the code again we see that we declared the parameter as "OracleDbType.Int32" which should be able to handle the function return value with no issues, and we also see "Integer" which is the VB flavor of Int32! But why are we seeing "OracleDecimal" in the output and why is that being converted to Integer? We didn't declare an OracleDecimal in our code.
One thing we might try is going to the project properties, clicking the Compile tab, and setting "Option strict:" to "On". OK, done. Now, let's recompile. Hmm, now the project does not compile due to the error: Option Strict On disallows implicit conversions from 'Object' to 'Integer'.
Yuck. There was an implicit conversion that wasn't reported during the first compile due to the default of "Option strict" being "Off". OK, so now we have determined that there is a conversion problem happening, but that doesn't really explain why. For that we can consult the ODP.NET documentation and in particular the section that discusses Parameter Binding. In summary, that part of the documentation details how setting the OracleDbType or the DbType property of an OracleParameter object determines the data type of the output parameter. As stated in the documentation, "ODP.NET allows applications to obtain an output parameter as either a .NET Framework type or an ODP.NET type."
Ahhh, so we got an OracleDecimal as the return value because we specified OracleDbType.Int32 as the parameter type. Since we want an Int32 (or Integer) as the return value we can change the code to set the DbType instead of the OracleDbType:
retval.DbType = DbType.Int32
Now recompile and everything will work great. Except that you may get the following error: Option Strict On disallows implicit conversions from 'Object' to 'Integer'.
If "Option strict" is left "On" you will receive the above message. This is because the OracleParameter.Value is returned as an Object. An easy way out is to set "Option strict" back to "Off" and the implicit conversion will be done behind the scenes and the code will work as expected albeit because of a hidden conversion. To take the explicit route you can change the declaration of "my_one" as follows:
Dim my_one As Integer = DirectCast(retval.Value, Int32)
Now you are getting the value back as an Int32/Integer (wrapped in an object) and explicitly (rather than implicitly) extracting the value via DirectCast as an Int32/Integer. With the implicit conversion the compiler will inject a call to a conversion function into the code and with the DirectCast method an "unbox" operation will take place.
Incidentally, the same sort of thing would need to happen with C#; however, the compiler would warn you of this during the initial compile run since it doesn't do the implicit conversion that VB would try if "Option strict" is "Off".
If you are having conversion problems in your code with "output" or "return values" keep in mind that setting OracleDbType vs. DbType will determine whether a .NET Framework type or an Oracle provider type is returned. Also, the current ODP.NET Beta (11.1.7.10) exposes a new property for the OracleParameter class: OracleDbTypeEx This property allows you to bind values using the OracleDbType but will return values as .NET types.
