dotConnect for Oracle Documentation
OracleLoader Class
Members  Example  See Also  support@devart.com
Devart.Data.Oracle Namespace : OracleLoader Class

Serves to load external data into the Oracle database.

Inheritance Hierarchy

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

Syntax

Visual Basic (Declaration) 
Public Class OracleLoader 
   Inherits Devart.Common.DbLoader
   Implements IComponentIDisposable 
C# 
public class OracleLoader : Devart.Common.DbLoader, IComponentIDisposable  

Remarks

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.

Example

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)
);
C#csharpCopy Code
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();
}
C#Visual BasicCopy Code
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

Requirements

Namespace: Devart.Data.Oracle

Platforms:Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, Windows Vista, Windows Server 2008 family, Windows 7, Windows 8, Windows 10, Windows Server 2012 family.

Assembly: Devart.Data.Synchronization

See Also