Back to Index

 

The Oracle Bulk Loader Data Object

The Oracle Bulk loader object builds an Oracle bulk load control file that you can use in conjunction with Oracle's SQL*Loader tool.   Oracle Bulk loader provides a great way to load large amounts of data into an Oracle database a lot faster than using conventional SQL statements.   (see also: Using the Oracle Bulk Loader)

Oracle, SQL*Net, SQL*Loader, PL/SQL are either registered trademarks or trademarks of the Oracle Corporation in the United States and/or other countries.  ®

Properties

Alias

Alias identifier.  Aliases are used on all design objects to simplify coding and to ensure each object has a unique identifier.

BulkLoadType

Oracle control file parameter specifying how SQL*Loader should handle loading the data.

Option

Description

INSERT

INSERT is SQL*Loader's default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows

APPEND

If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

REPLACE

With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.

TRUNCATE

Using this method, SQL*Loader uses the SQL TRUNCATE statement to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error. The table must be in your schema, or you must have the DELETE ANY TABLE privilege.

ColumnLayout

The column layout (or file structure) of the table.  Click on the Ellipse (...) button to fill in the column layout, or select an existing column layout from the repository.

Command

Shell process command to run when the Oracle Bulk loader output stream has completed.  Given that this object generates the bulk load control file, you still need to execute the Oracle SQL*Loader program to actually perform the task of bulk loading the information into Oracle.   This property allows you to shell out and execute either SQL*Loader directly or a script (batch file) or program that will execute it for you.  For example:  C:\oracle\ora9i\bin\sqlldr.exe  or  c:\myscripts\loaddata.bat which in turn calls the SQL*Loader program.

CommandLineArgs

SQL*Loader requires several parameters on the command line including things such as the userid, control, bad, and direct parameters.  Review your current Oracle documentation for exact usage of these parameters.  Below are a few of the commonly used parameters with an example.

Option

Description

userid

userid is your Oracle username and password with optional @ directive that points to a specific database instance.  Ex:  userid=jsmith/[email protected]

control

control is the path and filename to the control file that SQL*Loader will use to bulk load the data.  This is same path and filename used in the ControlFileName property shown below.  ex:  control=c:\myfiles\loadfile.ctl

DIRECT

Direct loads data into Oracle faster because it bypasses the redo logs and write blocks directly into the database.  ex:  DIRECT=TRUE

errors

The errors parameter specifies how many errors to allow before SQL*Loader will abort the process.  ex:  errors=50000

bad

bad is the path and filename where you want bad records to go.  If for some reason SQL*Loader is unable to load a record from the control file it will place it in this file for later review.  ex: bad=c:\myfiles\loadfile.bad

log

log is the path and filename where you want SQL*Loader to write its logging information describing exactly what occurred during the SQL*Loader session.   ex: c:\myfiles\loadfile.log

There are quite a few other parameters that Oracle gives you for SQL*Loader that may be useful to you, please review your Oracle documentation related specifically to the version of Oracle you are running.

A full example of how you might call SQL*Loader from a batch file might be:

c:\oracle\ora9i\bin\sqlldr.exe userid=admin/[email protected] control=c:\myfiles\loadfile.ctl bad=c:\myfiles\loadfile.bad  log=c:\myfiles\loadfile.log DIRECT=TRUE errors=50000

Or, calling SQL*Loader directly through the Command and CommandLineArgs properties

Command:  c:\oracle\ora9i\bin\sqlldr.exe

CommandLineArgs: userid=admin/[email protected] control=c:\myfiles\loadfile.ctl bad=c:\myfiles\loadfile.bad  log=c:\myfiles\loadfile.log DIRECT=TRUE errors=50000

 

Comments

Comment area to give descriptive text about the data object and its purpose.

ControlFilename

The filename of the control file to create.  Enter a complete path and filename.  You can name the file anything you want.  (For example:   C:\MyOracleFiles\SalesData.ctl)

DateMask

The date mask parameter is used in conjunction with DateTime field types when describing the data to SQL*Loader.   The date mask must match the format that you are using when outputting the data. Ex: DD-MON-YYYY (your data is formatted like: 01-JAN-2003).  You can format dates anyway you would like in the code behind window using standard VB.NET functions.  For instance, the code to format a DateTime column to the above format would be:

Dim d As DateTime

' Dim a DateTime variable

 

 

Try

 

        d = a1.MyDateColumn

' Assign the input column data to the DateTime variable

Catch

 

       d = Nothing

' The input column's date is invalid or empty

End Try

 

 

 

If Not(d = Nothing) Then

 

     Return Format(d, "dd-MMM-yyyy").ToUpper

' Return the formatted date

Else

 

     Return ""

' Return empty string

End If

 

* Note:  This is an excellent candidate as a CodeStore function.

EOLMarker

DOS / UNIX.   Describes the end-of-line markers to use in the control file.

Filename

The filename of the hash file you want to read or write.  This represents the filename only with no Path.  Use the Path property to specify the files location.  You can use any filename you would like, however it is best to use the .ht file extension to distinguish the file as a hash table.  (Example:  myfile.ht)  

Name

Descriptive name for this object.  You can enter any text to describe the object

Options

OPTIONS is a control file parameter that you can use to insert Oracle bulk loader specific options.  Example: (SILENT=FEEDBACK, DIRECT=TRUE).  See your Oracle documentation for specific OPTION parameters.

StringsEnclosedBy

Optional character used to enclose String type (or Varchar type) fields.  This is a parameter used in the control file and controls how strings are output into the control file data area.

TerminatedBy

The character delimiter that will be used to terminate each column in the Bulk loader control file.

Option

Description

Comma

The Comma (,) will be used as the column delimiter

Tab

The Tab character will be used as the column delimiter

 

TextEncoding

Text encoding gives you the capability to handle different types of encoded text files such as ASCII, UTF and Unicode.

Option

Description

Default

Encoding for the system's current ANSI code page.

ASCII

Encoding for the ASCII (7 bit) character set.

BigEndianUnicode

Encoding for the Unicode format in the big-endian byte order.

Unicode

Encoding for the Unicode format in little-endian byte order.

UTF7

Encoding for the UTF-7 format.

UTF8

Encoding for the UTF-8 format.

 

 


© 2003 - 2007 Relational Solutions, Inc. - All rights reserved