Oracle provides a file loader utility called SQLLDR to load data from file into a empty or populated table.

Oracle SQLLoader

Oracle provides a file loader utility called SQLLDR to load data from file into a empty or populated table.

Employee Table: For loading data into table using sample sqlloader script.

CREATE  TABLE employee(
   emp_no INTEGER,
   emp_name VARCHAR(50),
   job_title VARCHAR(30),
   manager_id INTEGER,
   hire_date Date,
   salary DECIMAL(18,2),
   commission DECIMAL(18,2),
   dept_no INTEGER
 )
  Primary key(emp_no);

Sample Sqlloader Script 1:

LOAD DATA
CHARACTERSET UTF8 length semantics char
TRUNCATE INTO TABLE employee
FIELDS TERMINATED BY ','
TRAILING NULLCOLS  
(
 emp_no        
,emp_name             
,job_title       
,manager_id            
,TYPE_IND          
,hire_date     "to_date(:hire_date, 'yyyy-mm-dd')"        
,salary         
,commission         
,dept_no      
)

Note: Points to be noted for sqlloader file.

1. 'CHARACTERSET' specification is important for loading UNICODE data from file.
2. 'FIELDS TERMINATED BY' specify the delimiter used in the outfile.
3. Date Columns: If date columns in the file are not in the Oracle specifc format(DD-MON-YYYY) then user has to cast the format to date.

   to_date(:hire_date, 'yyyy-mm-dd')
   

4. Long String:
   • The default datatype for character in SQLLDR is char(255) and if user try to load beyond that size then loader will fail with below error.

     Error Field in data file exceeds maximum length
     

   • To accomodate bigger character data in column,add column size as below example in sqlldr file to allocate a big enough buffer to hold the data.

     emp_name    char(4000),
     

   • Maximum length for a text column in sqlloader can be 1048544000

     TEXT CHAR(1048544000),
     

5. File to be loaded can also be added in the sqlldr scrip by using " infile 'filepath+filename'" after LOAD DATA command.
   
6. If user wants to TRUNCATE table before loading then 'TRUNCATE INTO TABLE' command can be used and if user want to append data to the existing table then 'APPEND INTO TABLE' should be used.

Sample Sqlloader Script 2:

LOAD DATA
CHARACTERSET UTF8 length semantics char
TRUNCATE INTO TABLE employee_1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS  
(
 emp_no        
,emp_name FILLER           
,job_title       
,manager_id            
,TYPE_IND          
,hire_date     "to_date(:hire_date, 'yyyy-mm-dd')"        
,salary         
,commission         
,dept_no
,updated_date "SYSDATE"
,updated_by "USER"
,processed_ind "1"
)

Note: Points to be noted for sqlloader file.

1. If any of the field need to be ignored from file then user can use FILLER keyword.

   emp_name FILLER --2nd field in the file will be ignored since this is specified on 2nd position
   

2. If table columns need to be populated with System user or system date/timestamp then user can specify in below format.

   updated_date "SYSDATE"   --System date will be populated in updated_date column
   updated_by "USER"        --System user value will be populated in updated_by column
   

3. If table columns need to be populated with any hardcoded value then user can just pass that in double quote against the column.

   processed_ind "1"  -- Number 1 will be populated in processed_ind column.
   

Syntax 1: Calling Sqlloader

sqlldr username/password@//host:port/sid control=[sqlloader_file] data=[datafile]

Example: sqlldr tutorial_owner/tutorial_password@//host:1521/orcl control=ora_load.ctl data=sample_outfile.out

Syntax 2: Calling Sqlloader on a remote machine

sqlldr username/password@'"(description=(address=(host=hostname)(protocol=tcp)(port=portNo))(connect_data=(sid=sidname)))"'  control=ora_load.ctl data=sample_outfile.out

Example: sqlldr tutorial_owner/tutorial_password@'"(description=(address=(host=hostname.com)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))"'  control=ora_load.ctl data=sample_outfile.out

Syntax 3: Calling Sqlloader with direct path option

sqlldr username/password@//host:port/sid control=[sqlloader_file] data=[datafile] direct=true

Example: sqlldr tutorial_owner/tutorial_password@//host:1521/orcl control=ora_load.ctl data=sample_outfile.out direct=true

Download Sample Files

• Data File
• Sqlloader File

What is the difference between using Direct Path and Conventional Path Sqlloader?

• Conventional Path: This will perform commit after each and every row which is loaded,this makes it slower than direct path.
• Direct Path: This will process data to directly write to data files and performs commit once the complete data is loaded or the sqlloader is terminated in middle of execution. Loads data faster than conventional path.