oracle ex dup value,Oracle ODP.NET問題調用包參數的程序-CSDN博客

博客内容涉及使用OracleODP.NET尝试调用存储过程时遇到的问题，具体表现为某些参数值导致调用失败，出现ORA-06502和ORA-06512错误。博主分享了存储过程的定义、参数创建和执行的代码片段，并给出了成功和失败的参数示例。尝试通过跟踪ODP.NET参数发送但未能获取有用信息。此外，博主已尝试手动设置参数值来解决问题，部分情况得到改善但仍存在未解决的失败案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

問題：嘗試調用打包的存儲過程，但調用失敗，具體取決於參數的值。Oracle ODP.NET問題調用包參數的程序

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 1

過程定義：

procedure DUP_EXACT (

SSN in VARCHAR2,

LASTNAME in VARCHAR2,

FIRSTNAME in VARCHAR2,

MASTERRECORD IN VARCHAR2 DEFAULT NULL,

C_Table out sp_cursor)

參數創建：

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)

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)

執行：

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)

Command.Connection.Open()

'Command.Connection.FlushCache() '

Using Reader As OracleDataReader = Command.ExecuteReader()

例成功案例&失敗：

*** 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]: ""

額外的測試：

我試圖運行的軌跡，看看有什麼ODP.NET實際的參數發送到數據庫，但跟蹤文件沒有提供任何有意義的信息(IE：實際參數值)

TIME:2013/02/14-14:10:19:678

TID:231c

OpsSqlPrepare2():

SQL: Begin PACKAGE.DUP_EXACT(:v0, :v1, :v2, :v3, :v4); End;

實例參數值：

?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}

您是否嘗試過測試，可以手動硬編碼值建立你的OracleParameter對象，並將它們添加到您的OracleCommand ？ ...用您的動態代碼來解決問題。 –

2013-02-14 20:47:59

解決了1個案例，但另一個仍然失敗 –

2013-02-14 20:59:37