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.  ®



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


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




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


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.


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.


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.


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.


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.


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.




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


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 loads data into Oracle faster because it bypasses the redo logs and write blocks directly into the database.  ex:  DIRECT=TRUE


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


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 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



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


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)


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





        d = a1.MyDateColumn

' Assign the input column data to the DateTime variable



       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



     Return ""

' Return empty string

End If


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


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


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:  


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


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.


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.


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




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


The Tab character will be used as the column delimiter



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




Encoding for the system's current ANSI code page.


Encoding for the ASCII (7 bit) character set.


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


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


Encoding for the UTF-7 format.


Encoding for the UTF-8 format.



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