What is SQL*Loader and what is it used for?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

How does one use the SQL*Loader utility?

One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:

sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:

load data
 infile 'c:\data\mydata.csv'
 into table emp
 fields terminated by "," optionally enclosed by '"'		  
 ( empno, empname, sal, deptno )

The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:

load data
 infile *
 replace
 into table departments
 (  dept     position (02:05) char(4),
    deptname position (08:27) char(20)
 )
begindata
COSC  COMPUTER SCIENCE
ENGL  ENGLISH LITERATURE
MATH  MATHEMATICS
POLY  POLITICAL SCIENCE

How does one load MS-Excel data into Oracle?

Open the MS-Excel spreadsheet and save it as a CSV (Comma Separated Values) file. This file can now be copied to the Oracle machine and loaded using the SQL*Loader utility.

Possible problems and workarounds:

The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor):

' Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
 Dim TheCell As Range
 On Error Resume Next

 For Each TheCell In ActiveSheet.UsedRange
   With TheCell
     If .HasFormula = False Then
       .Value = Application.WorksheetFunction.Clean(.Value)
     End If
   End With
 Next TheCell
End Sub

Tools:

If you need a utility to load Excel data into Oracle, download quickload from sourceforge at http://sourceforge.net/projects/quickload

Is there a SQL*Unloader to download data to a flat file?

Oracle does not supply any data unload utilities. Here are some workarounds:

Using SQL*Plus

You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (common separated values) file that can be imported into MS-Excel:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
  from tab1
 where col2 = 'XYZ';
spool off

You can also use the "set colsep ," command if you don't want to put the commas in by hand. This saves a lot of typing:

set colsep ,
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
  from tab1
 where col2 = 'XYZ';
spool off

Using PL/SQL

PL/SQL's UTL_FILE package can also be used to unload data. Example:

declare
  fp utl_file.file_type;
begin
  fp := utl_file.fopen('c:\oradata','tab1.txt','w');
  utl_file.putf(fp, '%s, %sn', 'TextField', 55);
  utl_file.fclose(fp);
end;
/

Third-party programs

You might also want to investigate third party tools to help you unload data from Oracle. Here are some examples:

• WisdomForce FastReader - http://www.wisdomforce.com
• IxUnload from ixionsoftware.com - http://www.ixionsoftware.com/products/
• FAst extraCT (FACT) for Oracle from CoSort - http://www.cosort.com/products/FACT
• Unicenter (also ManageIT or Platinum) Fast Unload for Oracle from CA
• Keeptool's Hora unload/load facility (part v5 to v6 upgrade) can export to formats cuch as as Microsoft Excel, DBF, XML, and text.
• TOAD from Quest
• SQLWays from Ispirer Systems
• PL/SQL Developer from allroundautomation

Can one load variable and fixed length data records?

Loading delimited (variable length) data

In the first example we will show how delimited (variable length) data can be loaded into Oracle:

LOAD DATA
 INFILE *
 INTO TABLE load_delimited_data
 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
 (  data1,
    data2
 )
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example:

...
resume char(4000),
...

Loading positional (fixed length) data

If you need to load positional data (fixed length), look at the following control file example:

LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  (  data1 POSITION(1:5),
     data2 POSITION(6:15)
  )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

For example, position(01:05) will give the 1st to the 5th character (11111 and 22222).