Oracle sql loader investigations

Today I’ve been exposed to the world of SQL Loader. There is a process that runs on a daily basis that does the following:

1. Parse a text file and copy to a remote server
2. Use SQL Loader to append the contents of this file to a database table
3. A final process to concatenate 2 fields in the database after the insert has completed.

Step 3 is an unnecessary overhead that was introduced a while back. Steps 1 or 2 are the better areas for this fix to be implemented. Having never used SQL Loader it looked a more interesting learning experience, and heres some quick findings.

To read the value into a temporary store, use the BOUNDFILLER key word (apparently supported in Oracle 9+). However, with our implementation, then continue to process the columns in order, before finally coming back to insert the temp values into their relevant columns.

An example:

The text file looks roughly like this:

1[TAB]A[TAB]MODULE[TAB]0LTD0023[TAB]25/03/15[TAB]Username

And the database table has the following columns

ID
SEMESTER
TYPE
CODE
ACCESS_DATE
USERNAME

The SQL Loader to concatenate File Entry [0] and File Entry [3] is as follows:

(
tempID BOUNDFILLER,
SEMESTER,
TYPE,
tempCode BOUNDFILLER,
ACCESS_DATE,
USERNAME,
ID “:tempID”,
CODE “:tempCode||:tempID”
)

Found plenty of examples on the Internet, but few made reference to the fields needing to be the final ones inserted.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s