dotConnect for Oracle Documentation
Devart.Data.Oracle Namespace / OracleLoader Class
Members Example

In This Topic
    OracleLoader Class
    In This Topic
    Serves to load external data into the Oracle database.
    Syntax
    'Declaration
     
    Public Class OracleLoader 
       Inherits Devart.Common.DbLoader
       Implements System.ComponentModel.IComponentSystem.IDisposable 
    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)
    );
    
    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
    Inheritance Hierarchy

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

    Requirements

    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

    See Also