This discussion is archived
1 Reply Latest reply: Jul 16, 2010 11:55 AM by gdarling - oracle RSS

OracleBulkCopy - Date Conversion Issue

785564 Newbie
Currently Being Moderated
Hi Guys,

I am relatively new to the Oracle realm, we are trying to utilize the OracleBulkCopy class...

I have a C# strongly typed dataset with a few columns; one of which is of type System.DateTime. Normally i would handle conversion from System.DateTime into a format that Oracle likes prior to the execution of a stored procedure by adjusting the parameter options available.

However with the OracleBulkCopy class i don't seem to have such luxuries...

Can anyone suggest how data type conversion can be achieved using the OracleBulkCopy class?

Thanks in advance.

Cheers,
Dave
  • 1. Re: OracleBulkCopy - Date Conversion Issue
    gdarling - oracle Expert
    Currently Being Moderated
    What specific error are you getting?

    DateTimes don't have a "format" per se afaik, are you using string converstion somewhere? Displaying a DateTime has a format, but the storage doesnt. If the datacolumn is datetime and the db column is date, that should work, I'd think.

    At least, I tried the following and it worked for me (11.2 odp and 11.2 db, for the record). Perhaps you can break it and send it back?

    I'm not very familiar with strongly typed datasets though, so if my lack of understanding is shining through, I apologize ahead of time.

    Corrections/comments welcome.
    Hope it helps,
    Greg
    //create table testdatetab(col1 number, col2 date);
    
    using System;
    using System.Data;
    using Oracle.DataAccess.Client;
    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
                //create a datatable
                DataTable dt = new DataTable();
                DataColumn dc = new DataColumn();
                dc.DataType = Type.GetType("System.Int32");
                dc.ColumnName = "col1";
                dt.Columns.Add(dc);
                DataColumn dc2 = new DataColumn();
                dc2.DataType = Type.GetType("System.DateTime");
                dc2.ColumnName = "col2";
                dt.Columns.Add(dc2);
                for (int i = 0; i < 10; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["col1"] = i;
                    dr["col2"] = System.DateTime.Now;
                    dt.Rows.Add(dr);
                }
    
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy("data source=orcl;user id=scott;password=tiger", OracleBulkCopyOptions.Default))
                {
                    bulkCopy.DestinationTableName = "testdatetab";
                    bulkCopy.WriteToServer(dt);
                }
            }
        }
    }

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points