vb.net中的入oracle参数和出oracle参数会导致null错误

Question

所以这段代码只使用out参数，但是当你添加in参数时，奇怪的空值开始出现，而且直线输入参数也可以工作，所以它只是在有in和out参数的情况下。

有问题的部分是in和out参数不想共存

            cmd.Parameters.Add("PKEY", OracleDbType.Int64, ParameterDirection.Output)
            For Each kvp As KeyValuePair(Of String, String) In sqlParams
                Dim newParam As New OracleParameter(kvp.Key, OracleDbType.Varchar2, kvp.Value, Data.ParameterDirection.Output)
                newParam.Size = 400
                cmd.Parameters.Add(newParam)
            Next

这是可行的

Public Shared Sub Run_Oracle_Query(ByVal queryToRun As String, ByVal sqlParams As Dictionary(Of String, String))
        Dim dbConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ITSS").ConnectionString
        Dim con As OracleConnection = New OracleConnection(dbConnString)
        Dim cmd = con.CreateCommand()
        Try
            con.Open()
            cmd.CommandText = queryToRun
            cmd.CommandType = CommandType.Text
            For Each kvp As KeyValuePair(Of String, String) In sqlParams
                cmd.Parameters.Add(kvp.Key, kvp.Value)

            Next
            cmd.ExecuteNonQuery()
            'log all sql queryies very expensive operation
            LogThisString("Log All queries: " & queryToRun, "Always")
        Catch ex As OracleException ' catches only Oracle errors
            OracleExceptionLogging(ex.Number, queryToRun, ex)
        Catch ex As Exception
            LogThisString("General Error SQL: " + ex.Message.ToString(), "Always")
            ' MsgBox("Could Not Perform This Database Operation")
        Finally
            con.Close()
            cmd.Dispose()
            con.Dispose()
        End Try
    End Sub

这是可行的

    Public Shared Function Run_Insert_Oracle_Query_Return_ID(ByVal queryToRun As String, ByVal IDcolumnName As String) As String
        Dim dbConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ITSS").ConnectionString
        Dim con As OracleConnection = New OracleConnection(dbConnString)
        Dim cmd = con.CreateCommand()
        Dim strUkey As String = ""
        Try
            con.Open()
            cmd.CommandText = queryToRun & " RETURNING " & IDcolumnName & " INTO :UKEY"
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("PKEY", OracleDbType.Int64, ParameterDirection.Output)
            cmd.ExecuteNonQuery()
            strUkey = cmd.Parameters("PKEY").Value.ToString
            Return strUkey
        Catch ex As OracleException ' catches only Oracle errors
            OracleExceptionLogging(ex.Number, queryToRun, ex)

        Catch ex As Exception
            LogThisString("General Error SQL: " + ex.Message.ToString(), "Always")
            PopupMsgBox("Database Error", "Database Integrity Constants Violated SQL Operation Will Not Be Executed")
        Finally
            con.Close()
            cmd.Dispose()
            con.Dispose()
        End Try

        Return strUkey
    End Function

这不起作用

Public Shared Function Run_Insert_Oracle_Query_Return_ID(ByVal queryToRun As String, ByVal IDcolumnName As String, ByVal sqlParams As Dictionary(Of String, String)) As String

        Dim dbConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ITSS").ConnectionString
        Dim con As OracleConnection = New OracleConnection(dbConnString)
        Dim cmd = con.CreateCommand()
        Dim strUkey As String = ""
        Try
            con.Open()
            cmd.CommandText = queryToRun & " RETURNING " & IDcolumnName & " INTO :UKEY"
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("PKEY", OracleDbType.Int64, ParameterDirection.Output)
            For Each kvp As KeyValuePair(Of String, String) In sqlParams
                Dim newParam As New OracleParameter(kvp.Key, OracleDbType.Varchar2, kvp.Value, Data.ParameterDirection.Output)
                newParam.Size = 400
                cmd.Parameters.Add(newParam)
            Next
            cmd.ExecuteNonQuery()
            strUkey = cmd.Parameters("PKEY").Value.ToString
            Return strUkey
        Catch ex As OracleException ' catches only Oracle errors
            OracleExceptionLogging(ex.Number, queryToRun, ex)

        Catch ex As Exception
            LogThisString("General Error SQL: " + ex.Message.ToString(), "Always")
            PopupMsgBox("Database Error", "Database Integrity Constants Violated SQL Operation Will Not Be Executed")
        Finally
            con.Close()
            cmd.Dispose()
            con.Dispose()
        End Try

        Return strUkey
    End Function

Answer 1

废话，所以我猜它确实是在寻找顺序，输出也需要在最后，就像this.....this works解决了我自己的问题一样

        For Each kvp As KeyValuePair(Of String, String) In sqlParams
            Dim newParam As New OracleParameter(kvp.Key, OracleDbType.Varchar2, kvp.Value, Data.ParameterDirection.Input)
            newParam.Size = 400
            cmd.Parameters.Add(newParam)
        Next
cmd.Parameters.Add("PKEY", OracleDbType.Int64, ParameterDirection.Output)