How to bind timestamp array

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
amirela
Posts: 15
Joined: Tue 26 Oct 2010 10:51

How to bind timestamp array

Post by 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

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 10 Oct 2011 11:23

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

Post by amirela » Tue 11 Oct 2011 01:19

dbms_sql.Date_Table isn't precise enough

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by 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)

Post Reply