调用带有参数的包装程序甲骨文ODP

调用带有参数的包装程序甲骨文ODP

本文介绍了调用带有参数的包装程序甲骨文ODP.NET问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

问题:试图调用一个封装存储的过程,但根据不同的参数的值的呼叫失败。

  ORA-06502:PL / SQL:数字或值错误
ORA-06512:第1行
 

过程定义:

 程序DUP_EXACT(
    SSN的VARCHAR2,
    LASTNAME的VARCHAR2,
    FIRSTNAME的VARCHAR2,
    MASTERRECORD IN VARCHAR2 DEFAULT NULL,
    C_Table出sp_cursor)
 

创建参数:

 对于每个SearchParameter在SearchParameters
        ValueParameter =新的OracleParameter

        ValueParameter.Direction = ParameterDirection.Input
        ValueParameter.OracleDbType = OracleDbType.Varchar2
        ValueParameter.ParameterName = SearchParameter.ParameterFieldName

        如果没有SearchParameter.TransformedFieldValue =没有那么
            ValueParameter.Value = SearchParameter.TransformedFieldValue
        其他
            ValueParameter.Value =的String.Empty
        结束如果

        ExactMatchSearchParameters.Add(ValueParameter)
    下一个

    昏暗MasterRecordParameter作为新的OracleParameter()

    MasterRecordParameter.Direction = ParameterDirection.Input
    MasterRecordParameter.OracleDbType = OracleDbType.Varchar2
    MasterRecordParameter.ParameterName =MASTERRECORD
    MasterRecordParameter.Value =的DBNull.Value

    ExactMatchSearchParameters.Add(MasterRecordParameter)

    昏暗TableParameter作为新的OracleParameter

    TableParameter.ParameterName =C_Table
    TableParameter.OracleDbType = OracleDbType.RefCursor
    TableParameter.Direction = ParameterDirection.Output

    ExactMatchSearchParameters.Add(TableParameter)
 

执行:

 使用命令的OracleCommand =
        新的OracleCommand(
            QualifiedProcedureName,
            Me.Database.Connection)

    Command.CommandType = CommandType.StoredProcedure
    Command.AddToStatementCache =假'

    每个参数在参数
        Command.Parameters.Add(参数)
    下一个

    Command.Connection.Open()

    'Command.Connection.FlushCache()'

    使用阅读器OracleDataReader = Command.ExecuteReader却()
 

例成功案例和放大器;失败:

  ***成功***
[SSN]:6 ####### 0
[姓氏]:W_____x
[名字]:D______e
[MASTERRECORD]:
[C_Table]:

*** 失败 ***
[SSN]:2 ####### _ 1
[姓氏]:C____n
[名字]:L___e
[MASTERRECORD]:
[C_Table]:

*** 失败 ***
[SSN]:5 ####### 5
[姓氏]:C_______s
[名字]:R_____o
[MASTERRECORD]:
[C_Table]:

*** 成功 ***
[SSN]:6 ####### 0
[姓氏]:P___a
[名字]:N______r
[MASTERRECORD]:
[C_Table]:
 

其他测试:

我试图运行跟踪,看看有什么ODP.NET是实际发送到的参数数据库,但跟踪文件没有提供任何有意义的信息(即:实际参数值)

 时间:2013/02 / 14-14:10:19:678
TID:231C
OpsSql prepare2():
SQL:开始PACKAGE.DUP_EXACT(:V0,:V1,:V2,:V3,:V4);结束;
 

示例参数值:

 ?Command.Parameters(0)
{SSN}
    ArrayBindSize:无
    ArrayBindStatus:无
    CollectionType:无{0}
    的DbType:字符串{16}
    方向:输入{1}
    无效的precision:100
    InvalidScale:129
    InvalidSize:-1
    ISNULLABLE:假
    m_bOracleDbTypeExSet:假
    m_bReturnDateTimeOffset:假
    m_collRef:{} Oracle.DataAccess.Client.OracleParameterCollection
    m_commandText:
    m_direction:输入{1}
    m_disposed:假
    m_enumType:ORADBTYPE {4}
    m_modified:假
    m_oraDbType:VARCHAR2 {126}
    m_paramName:SSN
    m_paramPosOrName:
    m_saveValue:无
    MaxScale:127
    MinScale:-84
    偏移:0
    OracleDbType:VARCHAR2 {126}
    OracleDbTypeEx:VARCHAR2 {126}
    ParameterEnumType:ORADBTYPE {4}
    参数名称:SSN
    precision:0
    规模:0
    大小:0
    SourceColumn:
    SourceColumnNullMapping:假
    SourceVersion:当前的{512}
    状态:成功{0}
    UdtTypeName:
    值:4 ####### 0{字符串}
 

解决方案

答案是,有导致intermitten VARCHAR2绑定错误在甲骨文9.2.0.6.0的错误。真棒。

这个论坛后终于给了我答案:

一个快速检查证实,我们的版本受到影响:

  SELECT * FROM V $版本;
-------------------------------------------------- --------------
Oracle9i企业版发行9.2.0.6.0  - 生产
PL / SQL版本9.2.0.6.0  - 生产
核心9.2.0.6.0生产
TNS对32位Windows:版本9.2.0.6.0  - 生产
NLSRTL版本9.2.0.6.0  - 生产
 

幸运的是,我们的生产服务器10g中,所以我们终于更新了我们的开发服务器到10g,以及,中提琴,没有更多的问题。

答径:

参数问题与Oracle REFCURSOR

甲骨文ODP.NET论坛

ODP.NET论坛主题错误ORA-06502 PL / SQL

Problem:Trying to call a packaged stored procedure, but the call is failing depending on the values of the parameters.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

Procedure Definition:

procedure DUP_EXACT (
    SSN in VARCHAR2,
    LASTNAME in VARCHAR2,
    FIRSTNAME in VARCHAR2,
    MASTERRECORD IN VARCHAR2 DEFAULT NULL,
    C_Table out sp_cursor)

Parameter Creation:

    For Each SearchParameter In SearchParameters
        ValueParameter = New OracleParameter

        ValueParameter.Direction = ParameterDirection.Input
        ValueParameter.OracleDbType = OracleDbType.Varchar2
        ValueParameter.ParameterName = SearchParameter.ParameterFieldName

        If Not SearchParameter.TransformedFieldValue = Nothing Then
            ValueParameter.Value = SearchParameter.TransformedFieldValue
        Else
            ValueParameter.Value = String.Empty
        End If

        ExactMatchSearchParameters.Add(ValueParameter)
    Next

    Dim MasterRecordParameter As New OracleParameter()

    MasterRecordParameter.Direction = ParameterDirection.Input
    MasterRecordParameter.OracleDbType = OracleDbType.Varchar2
    MasterRecordParameter.ParameterName = "MASTERRECORD"
    MasterRecordParameter.Value = DBNull.Value

    ExactMatchSearchParameters.Add(MasterRecordParameter)

    Dim TableParameter As New OracleParameter

    TableParameter.ParameterName = "C_Table"
    TableParameter.OracleDbType = OracleDbType.RefCursor
    TableParameter.Direction = ParameterDirection.Output

    ExactMatchSearchParameters.Add(TableParameter)

Execution:

Using Command As OracleCommand =
        New OracleCommand(
            QualifiedProcedureName,
            Me.Database.Connection)

    Command.CommandType = CommandType.StoredProcedure
    'Command.AddToStatementCache = False '

    For Each Parameter In Parameters
        Command.Parameters.Add(Parameter)
    Next

    Command.Connection.Open()

    'Command.Connection.FlushCache() '

    Using Reader As OracleDataReader = Command.ExecuteReader()

Example Successes & Failures:

*** SUCCESS ***
[SSN]: "6#######0"
[LASTNAME]: "W_____x"
[FIRSTNAME]: "D______e"
[MASTERRECORD]: ""
[C_Table]: ""

*** FAILURE ***
[SSN]: "2#######_1"
[LASTNAME]: "C____n"
[FIRSTNAME]: "L___e"
[MASTERRECORD]: ""
[C_Table]: ""

*** FAILURE ***
[SSN]: "5#######5"
[LASTNAME]: "C_______s"
[FIRSTNAME]: "R_____o"
[MASTERRECORD]: ""
[C_Table]: ""

*** SUCCESS ***
[SSN]: "6#######0"
[LASTNAME]: "P___a"
[FIRSTNAME]: "N______r"
[MASTERRECORD]: ""
[C_Table]: ""

Additional Testing:

I tried running a trace to see what ODP.NET was actually sending to the database in the parameters, but the tracefiles did not provide any meaningful information (IE: the actual parameter values)

TIME:2013/02/14-14:10:19:678
TID:231c
OpsSqlPrepare2():
SQL: Begin PACKAGE.DUP_EXACT(:v0, :v1, :v2, :v3, :v4); End;

Example Parameter Values:

?Command.Parameters(0)
{SSN}
    ArrayBindSize: Nothing
    ArrayBindStatus: Nothing
    CollectionType: None {0}
    DbType: String {16}
    Direction: Input {1}
    InvalidPrecision: 100
    InvalidScale: 129
    InvalidSize: -1
    IsNullable: False
    m_bOracleDbTypeExSet: False
    m_bReturnDateTimeOffset: False
    m_collRef: {Oracle.DataAccess.Client.OracleParameterCollection}
    m_commandText: ""
    m_direction: Input {1}
    m_disposed: False
    m_enumType: ORADBTYPE {4}
    m_modified: False
    m_oraDbType: Varchar2 {126}
    m_paramName: "SSN"
    m_paramPosOrName: ""
    m_saveValue: Nothing
    MaxScale: 127
    MinScale: -84
    Offset: 0
    OracleDbType: Varchar2 {126}
    OracleDbTypeEx: Varchar2 {126}
    ParameterEnumType: ORADBTYPE {4}
    ParameterName: "SSN"
    Precision: 0
    Scale: 0
    Size: 0
    SourceColumn: ""
    SourceColumnNullMapping: False
    SourceVersion: Current {512}
    Status: Success {0}
    UdtTypeName: ""
    Value: "4#######0" {String}

The answer is that there is a bug in Oracle 9.2.0.6.0 that causes intermitten VARCHAR2 binding errors. Awesome.

This forum post finally gave me the answer:

A quick check verified that the version we are on is affected:

select * from v$version;
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0   Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

Luckily our production servers are 10g, so we finally updated our dev server to 10g as well, and viola, no more problems.

Answer Trail:

Parameter issue with Oracle RefCursor

Oracle ODP.NET Forum

ODP.NET Forum Thread "Error ORA-06502 PL/SQL"

这篇关于调用带有参数的包装程序甲骨文ODP.NET问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 12:54