Hi ,
I want to understand how the PIPE works in unix . Precisely what I am doing is this .

1. Creating a Named PIPE with the command

mknod sqlldr.dat p

2. Directing a file output to the PIPE file in the background

cat abc > sqlldr.dat
3.SQL Loader in oracle is reading the data from this .

Now I want to undertsnad how it's really working ?

My concern is file abc is going to be a big one . Does this cause any memory problem

Links to how pipe works will be helfful

Does any body has a idea of reading a PIPE file from utl_file in oracle ?

a pipe is an intermediate file that allows to separate process to communicate.
The file uses FIFO I/O - first-in first-out. The last thing written to the file is the first thing read from the file.

I don't see any advantage to invoking sqlldr with a pipe. In fact, it may be a problem because pipes have a limit on "record" size - see /usr/include/limits.h _POSIX_PIPE_BUF and PIPE_BUF - and tables with several long columns will cause a problem with overflow.

One way pipes are really useful is to have two separate Oracle sessions comminuicating to one another using DBMS_PIPE

I had an issue like this .

I have to delete few records from the file in the unix box and then load the data into the database .Precisely the records whose size is less then 250 . I think there is nothing in the sql loader which offers this . Checked when cluase etc .

What I am doing now is this : Read file and copy all the records with 250 chracters to a temp file . Then load the data from the temp file

I thougt the pipe will be useful in this case .

Thanks
Ashok

Pipes and fifos cannot overflow. A fifo, aka named pipe, is more of a interprocess communication mechanism than a file. Unlimited writes to fifos are required by Posix. If you exceed PIPE_BUF, you lose the guarantee of atomicity. This only has an effect in the case of multiple writing processes. Some people wanted a PIPE_MAX and such a constant is available but it is the same as the max value in a ssize_t field. Setting O_NONBLOCK and writing more than PIPE_BUF will result in partial writes and a poorly designed application could lose data under those circumstances, but that is not exactly an overflow.

With one process reading from a pipe and another writing to a pipe, they will take turns running. In theory, a pipeline like:
yes | cat > /dev/null
can run for all eternity and that continues to be true if a fifo is used instead.

In fact, this sort of mechanism works very well when having to export Oracle data when the data exceeds 2 GB and the Oracle exp utility doesn't support large filesystems.

FWIW -
Under HPUX 11.0 Oracle 9.2.0.4.0 - 64bit Production - sqlldr does exactly that - when
the length of the logical record exceeds PIPE_BUF, data is sometimes lost and log errors about incomplete or lost data show up.

Overflow is not the right technical word - but I wasn't sure what the OP's level of understanding was. pipes really can't overflow, just lose track of EOR.