How To Create A Sub-Process Or "Co-Process" That Is Running SQL*Loader In A Pseudo-Terminal (aka "PTY")? (Doc ID 2206660.1)

Last updated on JUNE 29, 2021

Applies to:

Goal

Your are trying to write some code that loads data into the database, via SQL*Loader and a Unix named pipe.

Everything works fine, except that you can't figure out how to leave the SQL*Loader and the pipe "open".

On the server, you can start SQL*Loader with the input as a named pipe... SQL*Loader waits for input on the pipe, which is expected.

Your code can write data to the pipe, but SQL*Loader doesn't load it into the database until the code exits. After loading from the named pipe, SQL*Loader shuts down and is no longer listening on the named pipe.

You wish to leave SQL*Loader and the pipe open, so that the code can, later on, connect to the pipe again and put more records into the database... in other words, you wish to leave SQL*Loader running, listening to the pipe, so that any number of processes can write data to the named pipe and get data into the database.

Running SQLLDR to load a file, or to load from STDIN, works as expected; running SQLLDR to load the same data, via a named pipe, does not work as expected.

SQLLDR appears to handle end-of-line (EOL) and end-of-file (EOF) differently, depending on if it's reading from a real file, versus reading a FIFO or "named pipe".

Given a text file "my_Data.dat"
test record1
test record2
test record3

Running "sqlldr /@user/pw rows=1 data=my_Data.dat", you will see that SQLLDR is loading each record, one by one, and committing after each record. This is expected.

Secondly, if we start SQLLDR to accept input from STDIN, it works as expected.
Step 1: Start SQLLDR with "sqlldr /@user/pw data=\"-\" rows=1 "
Step 2: from terminal window, manually type in each record
test record1 <CR/LF> <--- SQLLDR responds with "1 row committed"
test record2 <CR/LF> <--- SQLLDR responds with "1 row committed"
test record3 <CR/LF> <-- SQLLDR responds with "1 row committed"
<ctrl-D> <-- SQLLDR exits

However, creating a named pipe using "mkfifo my_Pipe" and running SQLLDR to load data from the pipe instead.. .SQLLDR will not load/commit any data until the pipe is closed by the writer program. This is unexpected (and not desired).

Step 1: start SQLLDR via "sqlldr /@user/pw rows=1 data=my_Pipe"
Step 2: write one record at a time to the pipe, using Java or C program
(pseudo code)
open my_Pipe
write("test record1\n");
write("test record2\n");
write("test record3\n");
close my_Pipe <--- SQLLDR does not respond/load/commit any records until client app closes its connection to pipe.

SQLLDR will not respond until the Java or C program "closes" its connection to the pipe.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.