FTP Data Directly to SQL Loader

This post describes how to minimize the data load time when using FTP to load large amount of data. It takes considerable amount of time, when we FTP the data first to a target location and invoke SQL Loader to load the FTPed data. Instead of doing this, we will attempt to save time and some amount of disk space by making the FTP operation to feed the data directly to SQL Loader. This essentially means, every time we invoke the FTP operation, SQL Loader would automatically insert the data into Oracle for us. Let us see how to accomplish this.

1) As the first step, it is required to create a named pipe in the box where SQL Loader will run. The name of the pipe should be the name of the control file. As an example to create named pipe, run the command below

bash-2.03$ mknod test.ctl p

prw-r--r--   1 UserID   0 Mar  1 10:00 test.ctl

2) We can now invoke SQL Loader which reads the data from the PIPE that we created.

sqlldr userID/password@DB control=test

Note that the name of the control file is the pipe that we created just now. When this is executed, SQL Loader will wait, as it has not received any input for processing.  If you attempt to terminate the job at this point, you will receive the following error on the screen

SQL*Loader-500: Unable to open file (test.ctl)

SQL*Loader-554: error opening file

SQL*Loader-509: System error: Interrupted system call

3) We now create a simple control file  in the source machine. (from where you want to do the FTP)

load data 

infile * 

into table test_load 

fields terminated by ',' 

( 

name,rollno 

) 

BEGINDATA 

Ramu,100

Anitha,101

Arun,102

Dave,103

Antony,104

Peter,105

Sam,106

test_load is the name of our table..Name this file as "topush.ctl". Make sure that the table test_load is available in the target database. If not, you can create it with the following statement

create table test_load
(
name varchar2(15),
rollno number
)

4) Invoke FTP from the source machine and push this control file. As an example;

ftp> put topush.ctl test.ctl

200 PORT command successful.

150 ASCII data connection for test.ctl.

226 Transfer complete.

local: topush.ctl remote: test.ctl

191 bytes sent in 0.0003 seconds (623.82 Kbytes/s)

5) And that is it..if you have done these steps correctly, you can find SQL loader automatically pushing this into the table test_load. In my case, when I executed the put command, my SQL Loader window processed it as expected..

Commit point reached - logical record count 8

6) The result when the table test_load is queried from the database;

Ramu    100

Anitha   101

Arun      102

Dave      103

Antony 104

Peter     105

Sam       106