Back to Index

 

How to use the Oracle Bulk Loader Object

If you are using an Oracle database you can use the Oracle Bulk Loader data object in your designs.  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.

BlueSky Integration Studio will automatically generate the control file using the specified properties.   Once the control file has finished loading, the command specified in the Command property will be called, which should either reference SQL*Loader directly, or call it through a batch file.  

Below is an example of the properties for an Oracle Bulk Loader object.   (see Oracle Bulk for a complete description)

Typically, you will create a batch file to call SQL*Loader which will be executed via the Command property.  Below is an example of how to call SQL*Loader.  For additional command line options please see your Oracle Documentation.

C:\Oracle\ora9i\BIN\sqlldr userid=tsmith/[email protected] control=c:\ctlfiles\sales.ctl bad=c:\ctlfiles\sales.bad log=c:\ctlfiles\sales.log DIRECT=TRUE errors=1000000

Notice the control= parameter.  It refers to the ControlFilename in the Oracle Bulk Loader object's properties.  The other parameters tell SQL*Loader where to log the output of the process and where to put bad data, or data that could not be loaded due to some exception.

Useful Notes:

The StringsEnclosedBy and TerminatedBy properties are useful options depending on the type of data you are attempting to load.  For instance, if one of your columns is a description it may contain double quotes.   This can cause problems in loading the data since the default for StringsEnclosedBy is the quote (") character.   

If the description data looks like this:  12" Cutlery knife

and is wrapped in double quotes, then the control file column will look like this:  "12"Cutlery knife"

Notice the problem?  The SQL*Loader process will attempt to parse this data and only end up with 12 because it found a beginning and ending double quotes.  You can resolve this by simply not using any value for the StringsEnclosedBy property, or using a different character.

Same goes for the TerminatedBy property.  By default the fields in a control file are terminated by a comma (,).  However, what if the data itself contains comma's?

If the description data looks like this:  12" Cutlery knife, and holder

The control file's data could look something like this:

10,01-JAN-2003,12" Cutlery knife, and holder

The SQL*Loader parser will see 4 columns instead of three!  In this case, you would want to use Tab as the TerminatedBy character.

As you can see, using different combinations of the StringsEnclosedBy and TerminatedBy properties can be useful depending on the data you are attempting to load.

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


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