OO4O调用存储过程转ODP.NET，关联数组参数适配问题求助

问题描述 #

我是VB.NET和Oracle新手，维护工作中需将基于OO4O的存储过程调用迁移至ODP.NET以移除过时依赖。核心问题集中在三个AddTable对应的参数：Oracle端定义为TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER类型，因无Oracle端权限无法获取存储过程更多细节。

原OO4O代码中这三个参数未赋值即可正常运行，但ODP.NET实现中：

• 不赋值时提示OracleParameter.Value is invalid
• 赋值后触发ORA-06502: PL/SQL: numeric or value error: character string buffer too small及ORA-06512: at line 1错误

原OO4O可用代码 #

Dim oraParams As OracleInProcServer.OraParameters

'Return parameters
oraParams.Add("NUMBER", -1, pmORAPARM_OUTPUT, vbLong)

'Pass parameters to the stored procedure.
oraParams.Add("a_st", strA_st, pmORAPARM_INPUT, vbString)
oraParams.Add("a_st_version", strA_st_version, pmORAPARM_BOTH)
oraParams.Add("a_sc", strA_sc, pmORAPARM_BOTH, vbString)
oraParams.Add("a_ref_date", objA_ref_date, pmORAPARM_INPUT, ORATYPE_DATE)
oraParams.Add("a_create_ic", strA_create_ic, pmORAPARM_INPUT, vbString)
oraParams.Add("a_create_pg", strA_create_pg, pmORAPARM_INPUT, vbString)
oraParams.Add("a_userid", strA_userid, pmORAPARM_INPUT, vbString)
oraParams.AddTable("a_fieldtype_tab", pmORAPARM_INPUT, ORATYPE_VARCHAR2, 1, 20)
oraParams.AddTable("a_fieldnames_tab", pmORAPARM_INPUT, ORATYPE_VARCHAR2, 1, 20)
oraParams.AddTable("a_fieldvalues_tab", pmORAPARM_INPUT, ORATYPE_VARCHAR2, 1, 40)
oraParams.Add("a_nr_of_rows", iA_nr_of_rows, pmORAPARM_INPUT, vbLong)
oraParams.Add("a_modify_reason", strA_modify_reason, pmORAPARM_INPUT, vbString)

g_cOraDatabase.SQL_Execute("Begin :NUMBER:=UNAPISC.CreateSam(:a_st, :a_st_version, :a_sc, :a_ref_date, :a_create_ic, :a_create_pg, :a_userid, :a_fieldtype_tab, :a_fieldnames_tab, :a_fieldvalues_tab, :a_nr_of_rows, :a_modify_reason);commit;end;")

ODP.NET尝试代码 #

Dim strA_fieldtype_tab As String()
Dim strA_fieldnames_tab As String()
Dim strA_fieldvalues_tab As String()
Dim testOraCMD As New OracleCommand
Dim testCon As New OracleConnection

'DB and Stored Proc Settings
testCon.ConnectionString = "user id=" & g_strDBUser & ";password=" & g_strDBPassword & ";data source=" & g_strDBDatabase & ";"

testCon.Open()
testOraCMD.Connection = testCon
testOraCMD.CommandText = "UNAPISC.CreateSam"
testOraCMD.CommandType = System.Data.CommandType.StoredProcedure

'Return parameters
testOraCMD.Parameters.Add(New OracleParameter("NUMBER", OracleDbType.Long)).Direction = ParameterDirection.ReturnValue

'Pass parameters to the stored procedure.
testOraCMD.Parameters.Add(New OracleParameter("A_ST", OracleDbType.Varchar2, ParameterDirection.Input)).Value = strST
testOraCMD.Parameters.Add(New OracleParameter("A_ST_VERSION", OracleDbType.Varchar2, 2000, ParameterDirection.InputOutput)).Value = strA_st_version
testOraCMD.Parameters.Add(New OracleParameter("A_SC", OracleDbType.Varchar2, 2000, ParameterDirection.InputOutput)).Value = strA_sc
testOraCMD.Parameters.Add(New OracleParameter("A_REF_DATE", OracleDbType.Date, ParameterDirection.Input)).Value = objA_ref_date
testOraCMD.Parameters.Add(New OracleParameter("A_CREATE_IC", OracleDbType.Varchar2, 2000, ParameterDirection.Input)).Value = strA_create_ic
testOraCMD.Parameters.Add(New OracleParameter("A_CREATE_PG", OracleDbType.Varchar2, 2000, ParameterDirection.Input)).Value = strA_create_pg
testOraCMD.Parameters.Add(New OracleParameter("A_USERID", OracleDbType.Varchar2, 2000, ParameterDirection.Input)).Value = strA_userid

Dim f_type As OracleParameter
f_type = testOraCMD.Parameters.Add(New OracleParameter("A_FIELDTYPE_TAB", OracleDbType.Varchar2, ParameterDirection.Input))
f_type.CollectionType = OracleCollectionType.PLSQLAssociativeArray
f_type.Value = strA_fieldtype_tab
f_type.Size = strA_fieldtype_tab.Length

Dim f_name As OracleParameter
f_name = testOraCMD.Parameters.Add(New OracleParameter("A_FIELDNAMES_TAB", OracleDbType.Varchar2, ParameterDirection.Input))
f_name.CollectionType = OracleCollectionType.PLSQLAssociativeArray
f_name.Value = strA_fieldnames_tab
f_name.Size = strA_fieldnames_tab.Length

Dim f_values As OracleParameter
f_values = testOraCMD.Parameters.Add(New OracleParameter("A_FIELDVALUES_TAB", OracleDbType.Varchar2, ParameterDirection.Input))
f_values.CollectionType = OracleCollectionType.PLSQLAssociativeArray
f_values.Value = strA_fieldvalues_tab
f_values.Size = strA_fieldvalues_tab.Length

testOraCMD.Parameters.Add(New OracleParameter("A_NR_OF_ROWS", OracleDbType.Long, ParameterDirection.Input)).Value = iA_nr_of_rows
testOraCMD.Parameters.Add(New OracleParameter("A_MODIFY_REASON", OracleDbType.Varchar2, 2000, ParameterDirection.Input)).Value = strA_modify_reason

'Call Stored Proc
testOraCMD.ExecuteNonQuery()

'Clean up
testCon.Close()
testCon.Dispose()
testOraCMD.Dispose()

解决建议 #

1. 显式初始化空数组 #

原OO4O的AddTable未赋值时实际创建了空关联数组，ODP.NET不能直接传入未初始化的Nothing数组，需显式创建空数组：

' 替换原声明，显式初始化空数组
Dim strA_fieldtype_tab As String() = New String() {}
Dim strA_fieldnames_tab As String() = New String() {}
Dim strA_fieldvalues_tab As String() = New String() {}

2. 匹配Oracle端的Varchar2长度 #

原OO4O的AddTable指定了元素长度（20/20/40），ODP.NET必须显式对应这个长度，否则会用默认小长度触发缓冲区错误：

' a_fieldtype_tab 对应Oracle VARCHAR2(20)
Dim f_type As OracleParameter = testOraCMD.Parameters.Add(
    New OracleParameter("A_FIELDTYPE_TAB", OracleDbType.Varchar2, 20, ParameterDirection.Input)
)
f_type.CollectionType = OracleCollectionType.PLSQLAssociativeArray
f_type.Value = strA_fieldtype_tab
f_type.Size = strA_fieldtype_tab.Length

' a_fieldnames_tab 对应Oracle VARCHAR2(20)
Dim f_name As OracleParameter = testOraCMD.Parameters.Add(
    New OracleParameter("A_FIELDNAMES_TAB", OracleDbType.Varchar2, 20, ParameterDirection.Input)
)
f_name.CollectionType = OracleCollectionType.PLSQLAssociativeArray
f_name.Value = strA_fieldnames_tab
f_name.Size = strA_fieldnames_tab.Length

' a_fieldvalues_tab 对应Oracle VARCHAR2(40)
Dim f_values As OracleParameter = testOraCMD.Parameters.Add(
    New OracleParameter("A_FIELDVALUES_TAB", OracleDbType.Varchar2, 40, ParameterDirection.Input)
)
f_values.CollectionType = OracleCollectionType.PLSQLAssociativeArray
f_values.Value = strA_fieldvalues_tab
f_values.Size = strA_fieldvalues_tab.Length

3. 同步数组长度与a_nr_of_rows #

确保iA_nr_of_rows的值和数组长度一致，空数组时设为0，避免存储过程内部遍历数组时出现索引越界。

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