How to bind timestamp array

[amirela]

DotConnect for Oracle 6.30.202:

Code: Select all

DDL:

CREATE OR REPLACE procedure test (p_Date     IN                    dbms_sql.TimeStamp_Table) 
  AS 
  BEGIN 
   NULL; 
  END; 


C#
            OracleConnection conn = new OracleConnection("User Id=***;password=***;server=***;Direct=false;");
            conn.Open();
            DateTime[] dates = new DateTime[] { DateTime.Now, DateTime.Now, DateTime.Now };

            OracleCommand cmd = new OracleCommand("test", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.PassParametersByName = true;


            cmd.Parameters.AddWithValue("p_Date", dates);
            cmd.Parameters["p_Date"].ArrayLength = dates.Length;

            cmd.ExecuteNonQuery();
            conn.Close(); 

A have an error:
PLS-00418:array bind type must match PL/SQL table row type

[Shalex]

The TimeStamp data type is not allowed for PL/SQL parameters:
http://www.devart.com/dotconnect/oracle ... ength.html.

As a workaround, use dbms_sql.Date_Table instead of dbms_sql.TimeStamp_Table as a data type of your parameter in the definition of stored procedure.

[amirela]

dbms_sql.Date_Table isn't precise enough

[Shalex]

As a workaround, please use the VarChar parameter datatype and make the following conversions:
DateTime to String (.NET) -> cmd.ExecuteNonQuery() -> VarChar to TimeStamp (Oracle)