In OCI mode OracleLoader uses direct path load interface to speed up loading. In Direct mode it uses array binding feature. To specify the name of table to load set TableName property. Use Columns property to access individual columns. See example below and Loader sample for detailed information.

As an alternative to providing values in code, demonstrated in the sample below, you can use the Devart.Common.DbLoader.LoadTable method overloads to load data directly from a DataTable, IDataReader, or an array of DataRows.

If you use direct path loading without PartitionName specified, the PRIMARY KEY, UNIQUE, and NOT NULL constaints of the target table are enabled when loading data with OracleLoader. Additionally, you can turn off disabling constraints even when PartitionName is specified with OracleLoaderOptions.KeepConstraints option. Keeping these constraints enebled may cause the following consequences:

• An attempt to insert NULL into NOT NULL column leads to ORA-01400 at the end of the load.
• UNIQUE constraints are verified when indexes are rebuilt on OracleLoader.Close(). If UNIQUE constraint is violated, the index state becomes Unusable.
• The PRIMARY KEY constraint means that column is UNIQUE and NOT NULL simultaneously.

Restrictions of OracleLoader (only for direct path loading):

• triggers are not supported; use DML Arrays to overcome this limitation
• check constraints are not supported
• referential integrity constraints are not supported

The following types are supported when using direct path loading:

• OracleDbType.Blob
• OracleDbType.Char
• OracleDbType.Clob
• OracleDbType.Integer
• OracleDbType.IntervalDS
• OracleDbType.IntervalYM
• OracleDbType.Double
• OracleDbType.Date
• OracleDbType.Number
• OracleDbType.Raw
• OracleDbType.Timestamp
• OracleDbType.TimestampLTZ
• OracleDbType.TimestampTZ
• OracleDbType.VarChar

If an unsupported type is found, OracleLoader works via array binding.

You can additionally tweak OracleLoader behavior via its Options property. It allows you to configure OracleLoader to always use array binding even when direct path can be used, not to disable check constraint, aditionally disable indexes, trigger, table logging, etc. See OracleLoaderOptions for more information.

When array binding is used in the OCI connection mode with Oracle 11g Release 2 or higher, OracleLoader generates SQL with the /* APPEND_VALUES */ hint to avoid logging inserts for tables with logging disabled (NOLOGGING).

This class is available only in Professional and Developer Editions. It is not available in Standard and Mobile Editions.

The following example creates an OracleLoader, then populates Columns collection from table description, loads data, flushes the buffer and disposes internal OracleLoader structures. The OracleLoader requires open connection.

The following table is used in this example:

CREATE TABLE LOAD_TABLE(
  ID NUMBER,
  CHAR_FIELD VARCHAR(20),
  DATE_FIELD DATE,
  CONSTRAINT PK_LOAD_TABLE PRIMARY KEY (ID)
);

public void LoadData(OracleConnection conn)
{
  // OracleLoader requires open connection
  if (conn.State == ConnectionState.Closed)
    conn.Open();
  OracleLoader loader = new OracleLoader();
  // Specify connection that OracleLoader will use for loading
  loader.Connection = conn;
  // Set table name that will be loaded into
  loader.TableName = "load_table";
  // Populate Columns collection from table description
  loader.CreateColumns();
  // Prepare OracleLoader for loading
  loader.Open();
  for (int i = 0; i < 10000; i++)
  {
    loader.SetValue("id", i);
    loader.SetValue("char_field", "test string");
    loader.SetValue("date_field", DateTime.Now);
    loader.NextRow();
  }
  // Flush buffer and dispose internal OracleLoader structures
  loader.Close();
}

Public Sub LoadData(conn As OracleConnection)
  ' OracleLoader requires open connection
  If conn.State = ConnectionState.Closed
    conn.Open()
  End If
  Dim loader As OracleLoader
  loader = new OracleLoader
  ' Specify connection that OracleLoader will use for loading
  loader.Connection = conn
  ' Set table name that will be loaded into
  loader.TableName = "load_table"
  ' Populate Columns collection from table description
  loader.CreateColumns()
  ' Prepare OracleLoader for loading
  loader.Open()
  Dim i As Integer
  For i = 0 To 9999
    loader.SetValue("id", i)
    loader.SetValue("char_field", "test string")
    loader.SetValue("date_field", DateTime.Now)
    loader.NextRow()
  Next i
  ' Flush buffer and dispose internal OracleLoader structures
  loader.Close()
End Sub

System.Object
   System.MarshalByRefObject
      System.ComponentModel.Component
         Devart.Common.DbLoader
            Devart.Data.Oracle.OracleLoader

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

OracleLoader Members
Devart.Data.Oracle Namespace
OracleLoaderColumn Class
OracleLoaderColumnCollection Class
OracleDbType Enumeration