title: Oracle.DataAccess.Client.BulkCopy - trying to cast varchar2 as number when it shouldn't
body: <p>I have a table <code>(A)</code>  that includes a column named <code>confirmation_number</code>.  It is defined as a <code>varchar2(30)</code> and contains something like <code>SMITERI-2012-02-31-4567</code>.</p>
  
  <p>I have another table <code>(B)</code> that also includes a column named <code>confirmation_number</code>, and it is also a <code>varchar2(30)</code>.</p>
  
  <p>I have a stored procedure that extracts the data from <code>A</code>, performing some transformation on it, and it includes the confirmation number column.  </p>
  
  <p>I use <code>C#</code> and <code>ODP.Net</code> to run the stored procedure and load a <code>DataTable</code> with it.  I then perform some additional transformations.  However, I never touch the confirmation number column.</p>
  
  <p>Finally, I create an <code>OracleBulkCopy</code> object, set the column mappings, tell it to <code>update</code> Table <code>B</code>, and call <code>loader.WriteToServer(dataTable)</code>.</p>
  
  <p><strong>I am getting the following error</strong>:</p>
  
  <pre><code>Input string was not in a correct format.
     at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer&amp; number, NumberFormatInfo info, Boolean parseDecimal)
     at System.Number.ParseInt64(String value, NumberStyles options, NumberFormatInfo numfmt)
     at Oracle.DataAccess.Client.OracleBulkCopy.PerformBulkCopy()
  </code></pre>
  
  <p>I have confirmed that the error occurs on the <code>confirmation_number</code> column, because if I don't load that column, the dataload succeeds.</p>
  
  <p>Any ideas why the <code>BulkCopy</code> would be trying to turn a <code>varchar(2)</code> into a <code>Number</code>?</p>
  
  <p><strong>EDIT:</strong>
  I have confirmed the following relevant issues:</p>
  
  <ol>
  <li><p>This is happening on more than just the confirmation number.  This
  appears to only be a problem on string columns - whether from a
  string to a string or a string to an integer. </p></li>
  <li><p>This doesn't happen on every varchar2 column - last name and first name have no trouble.</p></li>
  <li><p>This happens both on columns that have no null values (confirmation
  number) and on columns that may have null values (street type).</p></li>
  <li><p>Investigating the contents of the datatable at the moment before the
  loader begins shows no difference in column definition between those
  that successfully go in and those that do not.</p></li>
  </ol>
  
  <p>I'm beginning to wonder if there's a bug in the BulkCopy itself and if I need to do this load using another utility.</p>
  
  <p><strong>EDIT:</strong> </p>
  
  <p>Below is the trimmed down code.</p>
  
  <p><strong>Table A</strong></p>
  
  <pre><code>  CREATE TABLE "APPS_UNPROCESSED" 
     (    "LAST_NAME" VARCHAR2(30 BYTE), 
      "FIRST_NAME" VARCHAR2(30 BYTE), 
      "MIDDLE_NAME" VARCHAR2(30 BYTE), 
      "NAME_SUFFIX" VARCHAR2(30 BYTE), 
      "GENDER" VARCHAR2(1 BYTE), 
      "DATE_OF_BIRTH" VARCHAR2(10 BYTE), 
      "ID" NUMBER, 
       :
       :
      "CONFIRMATION_NUMBER" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
      "IS_FBO" NUMBER(1,0), 
       :
       :
       CONSTRAINT "VR_APPS_UNPROCESSED_PK" PRIMARY KEY ("CONFIRMATION_NUMBER")
  </code></pre>
  
  <p><strong>Table B</strong></p>
  
  <pre><code>  CREATE TABLE "APPS_PROCESSED"
     (    "LAST_NAME" VARCHAR2(30 BYTE), 
      "FIRST_NAME" VARCHAR2(30 BYTE), 
      "MIDDLE_NAME" VARCHAR2(30 BYTE), 
      "NAME_SUFFIX" VARCHAR2(30 BYTE), 
      "GENDER" NUMBER(1,0), 
      "DATE_OF_BIRTH" DATE,
       :
       :
      "CONFIRMATION_NUMBER" VARCHAR2(30 BYTE), 
      "ID" NUMBER, 
      "IS_FBO" NUMBER(1,0),
       :
       :
      "ID" NUMBER NOT NULL ENABLE, 
       :
       :
       CONSTRAINT "VR_APPLICATION_PK" PRIMARY KEY ("ID")
  </code></pre>
  
  <p><strong>Procedure</strong></p>
  
  <pre><code> procedure getUnprocessedApps(results OUT sys_refcursor)
     as
     BEGIN
      open results for
        select 
          a.last_name
          ,a.first_name
          ,a.middle_name
          ,a.name_suffix
          ,decode(a.gender, 'M', 1, 0) as gender_id
          ,a.gender as gender_code
          ,to_char(to_date(a.date_of_birth, 'MM-DD-YYYY'), 'DD-mon-YYYY') as date_of_birth
          ,cast(a.confirmation_number as varchar2(30)) as confirmation_number
          ,a.id
          ,0 as is_fbo
       :
       :
        from vr_apps_unprocessed a
  </code></pre>
  
  <p><strong>fetching the data</strong></p>
  
  <pre><code>            DataSet data = new DataSet();
                  OracleConnection conn = null;
                  OracleCommand cmd = null;
                  OracleDataAdapter data_adapter = new OracleDataAdapter();
  
  
                  //fetch the data and load
                  using (conn = new OracleConnection(connString))
                  {
                      using (cmd = new OracleCommand())
                      {
  
                          cmd.Connection = conn;
                          cmd.CommandText = "APPS.getUnprocessedApps";
                          cmd.CommandType = CommandType.StoredProcedure;
                          cmd.Parameters.Add("results", OracleDbType.RefCursor, ParameterDirection.Output);
                          conn.Open();
                          data_adapter.SelectCommand = cmd;
                          data_adapter.Fill(data, "results");
                      }
                  }
                  return data.Tables["results"];
  </code></pre>
  
  <p><strong>setup the column mapping</strong></p>
  
  <pre><code> private static Dictionary&lt;string, string&gt; ColumnMappings()
      {
          Dictionary&lt;string, string&gt; mapping = new Dictionary&lt;string, string&gt;();
          mapping.Add("last_name", "last_name");
          mapping.Add("first_name", "first_name");
          mapping.Add("middle_name", "middle_name");
          mapping.Add("name_suffix", "name_suffix");
          mapping.Add("gender_id", "gender"); 
          mapping.Add("date_of_birth", "date_of_birth");
          mapping.Add("is_fbo", "is_fbo"); 
          mapping.Add("confirmation_number", "confirmation_number");
          mapping.Add("id", "id"); 
       :
       :
              return mapping;
      }
  </code></pre>
  
  <p><strong>Load files</strong></p>
  
  <pre><code> private static void loadFiles(DataTable dt, string connString)
          {
              OracleConnection orclconn = null;
              OracleBulkCopy loader = null;
  
              //now insert the data into Oracle               
              using (orclconn = new OracleConnection(connString))
              {
                  orclconn.Open();
                  using (loader = new OracleBulkCopy(orclconn)) 
                  {
                      loader.DestinationTableName = "APPS_Processed"
  
                      foreach (KeyValuePair&lt;string, string&gt; k in ColumnMappings())
                      {
                          loader.ColumnMappings.Add(k.Key, k.Value);
                      }
  
                      loader.WriteToServer(dt);
  
                  } //end using oracle bulk loader
  
              } //end using oracle connection
  
          }
  </code></pre>
  
  <p><strong>EDIT:</strong>
  I have been unable to get the bulkcopy to work, but I have gotten the traditional method of using arrays to work: <a href="http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html" rel="nofollow">http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html</a></p>
  
  <p>It is definitely slower than a successful bulk load, but at least I can get the data in.  I'd still love if anyone has any ideas on why the bulkcopy itself keeps failing, though.</p>