Using SysDate as a parameter to an Oracle function in VB 2013

[topshot]

Just a short post to hopefully help anyone else in the same situation where you need to pass the Oracle SysDate function as a parameter to a function or stored procedure (sproc). I'm converting old VB6 code and have been trying to use the latest and greatest .Net features, which forced me to discover this limitation. When you're adding input parameters you need to provide the value, of course, but in this case the value comes from the server. The only way I initially solved it was by creating a new function specifically for the case of SysDate (no IN Date parameter) since the original function was fine with any other date that you would pass. Then I dug into the OO4O-based code we'd been using to see how it was managing it and discovered that anonymous PL/SQL blocks were the answer. So I converted to using that so my new code can be more consistent with existing function structures we have.

I'm open to critiques and suggestions on how to improve this though. I'm using ODP.Net 12.1.1 (the managed version) though I don't think that matters much except for the OracleDbTypeEx which I think came in at 11.1. That happens to be quite important but I'll leave it to the reader to look up differences between DbType, OracleDbType and OracleDbTypeEx. I couldn't get the shorthand notation for Parameters.Add to work because of it since it uses OracleDbType.

Try
 
            Dim sql As String = "begin :P1 := general_pkg.dateconvertfromGMT(:P2,:P3, SYSDATE); end;"
 
            Using cmd As New OracleCommand(sql, conn)
                Dim P1 As OracleParameter = New OracleParameter()
                P1.ParameterName = "P1"
                P1.OracleDbTypeEx = OracleDbType.Date
                P1.Direction = ParameterDirection.Output
                cmd.Parameters.Add(P1)
                Dim P2 As OracleParameter = New OracleParameter()
                P2.ParameterName = "P2"
                P2.OracleDbTypeEx = OracleDbType.Single
                P2.Direction = ParameterDirection.Input
                P2.Value = -5
                cmd.Parameters.Add(P2)
                Dim P3 As OracleParameter = New OracleParameter()
                P3.ParameterName = "P3"
                P3.OracleDbTypeEx = OracleDbType.Varchar2
                P3.Direction = ParameterDirection.Input
                P3.Value = "USA"
                cmd.Parameters.Add(P3)
                cmd.ExecuteNonQuery()
                Console.WriteLine("Date: " & P1.Value.ToString)
                Dim temp As Date = CDate(P1.Value)
                Console.WriteLine("add 20 min " & CStr(temp.AddMinutes(20)))
            End Using
 
        Catch exOra As OracleException
            errmsg = exOra.ToString
 
        Catch ex As SystemException
            errmsg = ex.ToString
 
        End Try