Use SQL*Loader with Named Pipe

Typically using SQL*Loader assumes that a flat file will be used as the input. The file will need to be created and generated before the SQL*Loader can take the data from the file and load the data into Oracle. The performance can be improved and the disk space can be saved if you use named pipe with SQL*Loader.

What is Named Pipe?

Named Pipe is a way to pass the information between two processes. It was originated from Unix and now also supported in XP. [1][2]

A pipe can be used like a file, but it does not take any disk space.

How to create a Named Pipe?

To create a named pipe, you can use this command under unix shell.

% mkfifo mypipe.dat

The command mkfifo means “make a first in first out buffer”.

The mypipe.dat appears like a file, but it is actually just a memory buffer allocated by UNIX.

There seems no equivalent command in XP. You can only create a named pipe programatically. Also, pipe name in Window always like “\\.\pipe\mypipe.dat” [3]

How to use a named pipe?

To write into the the pipe, you can simply use this command:

% cat file.txt > mypipe.dat &

This command will put the data from the file.txt to the pipe.

At this moment, the mypipe.dat memory space has not been written. the process is actually blocked and waiting for another process to consume and read the data.

You can use a program to write into a pipe. It is also possible to use the ftp client to save the data into the pipe. By writing the remote file directly into a pipe, the data from the remote machine immediately loaded into the database.

Use Named Pipe in SQL*Loader

You can then use SQL*Loader to read the data from the pipe. You use the mypipe.dat just like a file. In a separate process of shell, you can run this command:

% SQLLDR …. data=mypipe.dat &

If you issue this command before you write to the pipe. The SQLLDR command will be hanging and waiting for the data arrived from the other process.

The beauty of this approach is that the process of writing the data into mypipe.dat, like the cat command above, does not write to a file, but directly write into memory and thus to the SQL*Loader. Since no physical file is acutally created, the data aren’t duplicated on disk which conserves disk space, and since no I/O is taking place, this results in a very fast process.

References

[1] Introduction to Interprocess Communication Using Named Pipes

[2] Wikipedia: Named pipe

[3] Named Pipe in MSDN