.NET/VB使用Oracle OCI 12c/19c驱动时DeriveParameters返回0参数

问题现象 #

使用.NET Framework 4.6.1搭配Oracle 12c+/19c OCI驱动连接Oracle 11g后端时，调用OracleCommandBuilder.DeriveParameters无法获取包内存储过程TESTPARAMS_PKG.test1param的参数，返回参数数量为0；但使用Oracle 11g OCI驱动连接同一数据库时可正常返回1个参数。

原因 #

Oracle 12c及后续版本的OCI驱动默认采用新的基于XML的元数据获取机制，该机制在连接11g数据库时存在兼容性问题，导致无法正确解析包内存储过程的参数信息。

解决方案 #

方案1：显式定义参数（推荐生产环境使用） #

OracleCommandBuilder.DeriveParameters需要额外数据库往返获取元数据，性能较差且兼容性不稳定，建议直接显式声明参数：

Imports System.Data.OracleClient

Private Sub btnFakeStoredProcedure(sender As Object, e As EventArgs) Handles btnStoredProcedure.Click
    Dim OConn = New OracleConnection()
    OConn.ConnectionString = "Password=...; User ID=...; Data Source=...;"
    OConn.Open()

    Dim Command = New OracleCommand("TESTPARAMS_PKG.test1param", OConn)
    Command.CommandType = CommandType.StoredProcedure

    ' 显式添加参数，替代DeriveParameters
    Command.Parameters.Add(New OracleParameter("param1", OracleType.VarChar))

    Dim txtParameters = "The parameters: " & vbCrLf & Command.Parameters.Count & vbCrLf
    OConn.Close()

    For Each parameter As OracleParameter In Command.Parameters
        txtParameters += "Parameter Name: " & parameter.ParameterName & vbCrLf
        txtParameters += "Parameter Direction: " & parameter.Direction & vbCrLf
        txtParameters += "Parameter Value: " & If(parameter.Value Is DBNull.Value, "DBNull", parameter.Value.ToString()) & vbCrLf & vbCrLf
    Next

    txtStoredProcedure.Text = txtParameters
End Sub

方案2：修改连接字符串兼容旧元数据机制 #

在连接字符串中添加MetaDataXml=False，强制驱动使用11g兼容的元数据获取方式：

OConn.ConnectionString = "Password=...; User ID=...; Data Source=...; MetaDataXml=False;"

添加该参数后，原代码中的OracleCommandBuilder.DeriveParameters即可正常获取参数。

重现代码与环境 #

VB.NET 代码 #

Imports System.Data.OracleClient

Private Sub btnFakeStoredProcedure(sender As Object, e As EventArgs) Handles btnStoredProcedure.Click
        Dim OConn = New OracleConnection()

        OConn.ConnectionString = "Password=...; User ID=...; Data Source=...;"
        OConn.Open()

        Dim Command = New OracleCommand("TESTPARAMS_PKG.test1param", OConn)
        Command.CommandType = CommandType.StoredProcedure

        OracleCommandBuilder.DeriveParameters(Command)

        Dim txtParameters = "The parameters: " & vbCrLf & Command.Parameters.Count & vbCrLf
        OConn.Close()

        For Each parameter As OracleParameter In Command.Parameters
            txtParameters += "Parameter Name: " & parameter.ParameterName & vbCrLf
            txtParameters += "Parameter Direction: " & parameter.Direction & vbCrLf
            txtParameters += "Parameter Value: " & parameter.Value.ToString() & vbCrLf & vbCrLf
        Next

        txtStoredProcedure.Text = txtParameters

    End Sub

Oracle 存储过程定义 #

CREATE OR REPLACE PACKAGE TESTPARAMS_PKG AS 

  -- just 1 varchar param with and without default, implicit IN
  PROCEDURE test1param (param1 VARCHAR2);

END TESTPARAMS_PKG;
/

CREATE OR REPLACE PACKAGE BODY TESTPARAMS_PKG AS

  PROCEDURE test1param (param1 VARCHAR2) AS
  BEGIN
    -- TODO: Implementierung for PROCEDURE TESTPARAMS_PKG.test1param erforderlich
    NULL;
  END test1param;

END TESTPARAMS_PKG;
/

版本对比 #

• "Oracle in instantclient_11.2.0_x86"：正常返回参数数量
• "Oracle in OraClient12cR2_x86"：参数数量为0

内容的提问来源于stack exchange，提问作者clemens utschig