ORA-21700: 对象不存在或被标记为删除关联数组作为从 ODP.NET 调用的输入参数 [英] ORA-21700: object does not exist or is marked for delete for Associative Array as input parameter called from ODP.NET
问题描述
我在 Oracle 12c 上有这个包代码
I have this package code on Oracle 12c
CREATE OR REPLACE PACKAGE Support_Data_Types AS
TYPE ttDate IS TABLE OF DATE
END Support_Data_Types;
PROCEDURE GetData
(
tabDates IN SUPPORT_DATA_TYPES.TTDATE,
)
AS
BEGIN
SELECT count(*) INTO n FROM table(tabDates);
END GetData;
如果我从 PL/SQL
代码中调用它,它就可以工作
If I call it from PL/SQL
code it works
declare
dates SUPPORT_DATA_TYPES.TTDATE;
begin
dates(1) := To_DATE('12/31/2005','MM/DD/YYYY');
dates(2) := To_DATE('03/31/2006','MM/DD/YYYY');
dates(3) := To_DATE('06/30/2006','MM/DD/YYYY');
dates(4) := To_DATE('09/30/2006','MM/DD/YYYY');
MyPackage.GETVALUE(dates);
end;
但是如果我从 ODP.NET 调用它,那么我会在 SELECT count(*) INTO n FROM table(tabDates);
But If I call it from ODP.NET then I get error message on the line SELECT count(*) INTO n FROM table(tabDates);
ORA-21700: 对象不存在或被标记为删除
ORA-21700: object does not exist or is marked for delete
我的 .NET 代码
Public Function GetValue(dates As IEnumerable(Of Date))
Using connection As IDbConnection = Odp.ConnectionBuilder.CreateDatabaseConnection()
Using cmd As OracleCommand = New OracleCommand
cmd.Connection = DirectCast(connection, OracleConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MyPackage.GETVALUE"
cmd.BindByName = True
Using datesParam As OracleParameter = New OracleParameter()
datesParam.ParameterName = "tabDates"
datesParam.OracleDbType = OracleDbType.Date
datesParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray
datesParam.Size = dates.Count()
datesParam.Direction = ParameterDirection.Input
datesParam.Value = dates.ToArray()
cmd.Parameters.Add(datesParam)
End Using
cmd.ExecuteNonQuery()
End Using
End Using
End Function
如果我与 Oracle 的 示例代码 进行比较,我不确定这里有什么问题.
If I compare with Oracle's sample code I am not sure what is wrong here.
有人有一些见解吗?
推荐答案
经过几天的谷歌搜索,我把代码改成这样:
after several days of googling, I changed the code this way:
CREATE OR REPLACE PACKAGE Support_Data_Types AS
TYPE ttDate IS TABLE OF DATE
END Support_Data_Types;
PROCEDURE GetData
(
tabDates IN SUPPORT_DATA_TYPES.TTDATE,
)
AS
v_temp SUPPORT_DATA_TYPES.TTDATE:= tabDates; -- assigned the parameter to a temporary variable
BEGIN
SELECT count(*) INTO n FROM table(v_temp);
END GetData;
我所做的唯一一件事就是使用一个看起来很多余的 v_temp.但它有效.我进行此更改是因为我在此处 搜索了这篇文章...它提到:
the only thing I did is to use a v_temp which looks quite redundant. but it works. I made this change because I searched this article here ... it mentioned:
但是请注意,从 12.1 开始,您不能直接在 TABLE 运算符内部调用 table 函数.您必须在 PL/SQL 中调用它,将结果赋给一个变量,然后在 TABLE 中引用该变量.
Note, however, that as of 12.1, you cannot call the table function directly inside the TABLE operator. You must invoke it in PL/SQL, assign result to a variable, and then reference the variable inside TABLE.
尽管我的情况有所不同(我使用的是 12.2),但它解决了我的问题.
though the situation is different in my case (I m using 12.2), it solved my problem.
这篇关于ORA-21700: 对象不存在或被标记为删除关联数组作为从 ODP.NET 调用的输入参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!