4

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;

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;

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: object does not exist or is marked for delete

My .NET code

    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

If I compare with Oracle's sample code I am not sure what is wrong here.

Anybody has some insight?

hardywang
  • 4,864
  • 11
  • 65
  • 101
  • i might know what your problem is... Remove this `datesParam.Size = dates.Count()` and set `datesParam.ArrayBindSize`. Also, with differed execution, you executing twice - here: `dates.Count()` and here: `dates.ToArray()`. Make this function accept `date()` and call it `GetValue(enumerableDate.ToArray())`. See my similar answer, only there parameters are OUT https://stackoverflow.com/a/31387136/1704458 – T.S. Feb 08 '19 at 01:05
  • And you don't need to go length and use `using` on parameter. Look inside - it does practically nothing. – T.S. Feb 08 '19 at 01:23
  • I am having the similar problem. However, the same code code works on another Oracle server. I do not know what caused the difference. – shrimp rice Jun 18 '19 at 00:55

1 Answers1

6

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;

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:

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.

though the situation is different in my case (I m using 12.2), it solved my problem.

shrimp rice
  • 321
  • 4
  • 22
  • 1
    I know my answer looks strange, and I do not believe it myself at first. It is just a workaround for the 12.2 bug. If you give this a downvote, please let me know the reason. thanks! – shrimp rice Jun 20 '19 at 16:51
  • 1
    OMG, I too spend days googling this same issue. You saved me from going insane! Thank you! – ArcherBird Oct 28 '19 at 17:03