title: C# DataTable to Oracle Stored Procedure
body: <p>What I need to do is to pass a C# DataTable to an Oracle stored procedure.</p>
  
  <p>Here is what I have done:</p>
  
  <p>Oracle side:</p>
  
  <ol>
  <li><p>Created a type:</p>
  
  <pre><code>create or replace TYPE CUSTOM_TYPE AS OBJECT 
  ( 
      attribute1 VARCHAR(10),
      attribute2 VARCHAR(10)
  );
  </code></pre></li>
  <li><p>Created a table</p>
  
  <pre><code>create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE;
  </code></pre></li>
  <li><p>Created a stored procedure</p>
  
  <pre><code>create or replace PROCEDURE SP_TEST
  (
      P_TABLE_IN IN CUSTOM_TYPE_ARRAY,
      P_RESULT_OUT OUT SYS_REFCURSOR 
  ) AS 
  --P_TABLE_IN CUSTOM_TYPE_ARRAY;
  BEGIN
      OPEN P_RESULT_OUT FOR
  
      SELECT attribute1, attribute2
      FROM TABLE(P_TABLE_IN);
  END SP_TEST;
  </code></pre></li>
  </ol>
  
  <p>C# side:</p>
  
  <pre><code>void Run()
  {
          OracleConnection oraConn = new OracleConnection();
          oraConn.ConnectionString = ConfigurationManager.ConnectionStrings["NafasV2ConnectionString"].ToString();
          DataSet dataset = new DataSet();
          DataTable Dt = new DataTable();
          OracleDataAdapter da = new OracleDataAdapter();
          OracleCommand cmd = new OracleCommand();
  
          try
          {
              FormTVP(ref Dt);
              PopulateTVP(ref Dt);
              oraConn.Open();
              cmd.Connection = oraConn;
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
              cmd.CommandText = "SP_TEST";
  
              OracleParameter parm1 = new OracleParameter("P_TABLE_IN", OracleDbType.RefCursor,100,"xx");
              parm1.Value = Dt;
              parm1.Direction = ParameterDirection.Input;
              cmd.Parameters.Add(parm1);
  
              OracleParameter parm2 = new OracleParameter("P_RESULT_OUT", OracleDbType.RefCursor);
              parm2.Direction = ParameterDirection.Output;
              cmd.Parameters.Add(parm2);
  
              da.SelectCommand = cmd;
              da.Fill(dataset);
              ASPxLabel1.Text = "OK!!";
          }
          catch (Exception ex)
          {
              ASPxLabel1.Text = "DIE. REASON: " + ex.Message;
          }
          finally
          {
              da.Dispose();
              cmd.Dispose();
              oraConn.Close();
              oraConn.Dispose();
          }
  
      }
  
      void FormTVP(ref DataTable Dt)
      {
          DataColumn attribute1 = Dt.Columns.Add("ATTRIBUTE1", typeof(String));
          DataColumn attribute2 = Dt.Columns.Add("ATTRIBUTE2", typeof(String));
          Dt.AcceptChanges();
      }
  
      void PopulateTVP(ref DataTable Dt)
      {
          DataRow Dr = Dt.NewRow();
          Dr["ATTRIBUTE1"] = "MK1";
          Dr["ATTRIBUTE2"] = "MK2";
          Dt.Rows.Add(Dr);
  
          DataRow Dr1 = Dt.NewRow();
          Dr1["ATTRIBUTE1"] = "HH1";
          Dr1["ATTRIBUTE2"] = "HH2";
          Dt.Rows.Add(Dr1);
  
          Dt.AcceptChanges();
      }
  </code></pre>
  
  <p>But I'm getting an error:</p>
  
  <blockquote>
    <p>Invalid parameter binding Parameter name: P_TABLE_IN </p>
  </blockquote>
  
  <p>HELP!</p>