使用 Oracle.ManagedDataAccess.Client 调用 Oracle 存储过程时参数顺序不正确 [英] Parameter order is incorrect when calling Oracle stored procedures using Oracle.ManagedDataAccess.Client

问题描述

我使用最新的 Oracle.ManagedDataAccess.Client 11.2 .dll 将数据从 Oracle 获取到 .net 应用程序.是否需要按照存储过程预期的顺序传递参数?

I am using latest Oracle.ManagedDataAccess.Client 11.2 .dll for getting data from Oracle to .net application. Is it required to pass parameter in same order as expected by the stored procedure?

从应用程序端，我们将参数顺序添加到命令对象 cmd 是

From application side we are add parameter order to command object cmd is

KK_C
KK_C2
KK_C1

存储过程如

KK_C
KK_C1
KK_C2

这是通用代码，我无法按照存储过程预期的顺序传递参数.因为不同的存储过程期望不同的参数顺序

And this is generic code I am not able pass parameter same order what the stored procedure expected. Because different stored procedures expect different parameter order

我的方法:

Public Function GetDataTable(ByRef xmlParams As XmlNodeList) As DataTable
{
     Dim param As OracleParameter

     Dim params As List(Of OracleParameter) = New List(Of OracleParameter)()

     For Each node As XmlNode In xmlParams

param = New OracleParameter()

param.ParameterName = Convert.ToString(node.SelectSingleNode("name").InnerText)

param.OracleDbType = CType("112", OracleDbType)

 param.Value =Convert.ToString(node.SelectSingleNode("name").InnerText)


   Next

 Dim addparam As OracleParameter
            Dim cmd As OracleCommand
            Dim objdt As DataTable
            OpenConnection()
            cmd = New OracleCommand()           
            cmd.Connection = _oracleConn            
            cmd.CommandText = sql           
            cmd.CommandType = CommandType.StoredProcedure           

            If Not params Is Nothing Then
                For Each param As OracleParameter In params             
                    addparam = New OracleParameter()

                    With addparam                   
                        .Direction = param.Direction
                        .OracleDbType = param.OracleDbType 
                        If Left(param.ParameterName, 2) <> "KK_" Then
                            .ParameterName = "KK_" & param.ParameterName
                        Else
                            .ParameterName = param.ParameterName
                        End If
                        .Size = param.Size
                        .Value = param.Value
                    End With
                    cmd.Parameters.Add(addparam)

                Next
            End If

            addparam = New OracleParameter("OO_remcursor", OracleDbType.RefCursor)
            addparam.Direction = ParameterDirection.Output
            cmd.Parameters.Add(addparam)

            'fill the datatable
            objdt = New DataTable(tblname)

            Using objda As New OracleDataAdapter(cmd)

                objda.Fill(objdt)

            End Using
            Return objdt
}

存储过程:

PROCEDURE GetDATA(KK_C IN NUMBER,KK_C1 IN NUMBER, KK_C2 IN NUMBER OO_remCursor OUT o_Cursor)
    AS
    BEGIN
        OPEN o_remCursor FOR
        SELECT ....        ORDER BY LOWER(brand_alias);

    END GetDATA;

推荐答案

正如 "Praveen G" 所建议的，如果你想调用带有命名参数的存储过程而不考虑参数声明顺序，只需设置 "BindByName"为真

As suggested by "Praveen G", if you want to call a stored procedure with named parameters without taking care of the paramaters declaration order, simply set "BindByName" to true

cmd.BindByName = True

不幸的是，默认情况下此属性不是 true(可能是出于性能原因)...

Unfortunately, this property is not true by default (maybe for performance reasons)...

这篇关于使用 Oracle.ManagedDataAccess.Client 调用 Oracle 存储过程时参数顺序不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！