How to bind timestamp array

How to bind timestamp array

Postby amirela » Tue 04 Oct 2011 03:11

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
amirela
 
Posts: 15
Joined: Tue 26 Oct 2010 10:51

Postby Shalex » Mon 10 Oct 2011 11:23

The TimeStamp data type is not allowed for PL/SQL parameters:
http://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleParameter~ArrayLength.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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Postby amirela » Tue 11 Oct 2011 01:19

dbms_sql.Date_Table isn't precise enough
amirela
 
Posts: 15
Joined: Tue 26 Oct 2010 10:51

Postby Shalex » Tue 11 Oct 2011 12:19

As a workaround, please use the VarChar parameter datatype and make the following conversions:
DateTime to String (.NET) -> cmd.ExecuteNonQuery() -> VarChar to TimeStamp (Oracle)
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle