Re: Can SQL*Loader read standard input?

Pat -

That's a real interesting question. At first I thought of using named pipes, but my IPC background is pretty rusty right now, and I knew that I'd waste time just getting the things to work. I had thought that SQL*Loader
would be unable to read from stdin because of the input file being specified in the control file. But it looks as though I am wrong. Doing
a quickie search through Metalink, I run across bug 876007 that's advertised as fixed in version 8.1.6. Here, a user had difficulty on AIX doing what you want, but apparently not on other operating systems.

I started doing the little test case described in the bug on my own test Win2000 server
until I realized that I don't know how to pipe in Win2000. To test this on Unix would be a pain right now, and so I'll leave it to you to give a try on your end.

Here are the notes that I took until I came to a screeching halt: =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+ 5/15/02:

This is a fun test responding to a newsgroup post regarding the use of standard input to sqlldr.

I see ref to this in Oracle bug 876007 and a small testcase:

create table t1 (c1 varchar2(10), c2 varchar2(10), c3 varchar2(10));

data.ctl file:
load data
infile "-"
append
into table t1

      (C1 position(1:5)  char,
       c2 position(6:10) char,
       c3 position(11:15) char )

data.txt file:
12345678901234567890

Test it by doing this:
cat data.txt | sqlldr scott/tiger control=data.ctl =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

For what it's worth...
TG

"Patrick L. Nolan" wrote:

> I have been experimenting with SQL*Loader (Oracle 9i) on Linux.
> So far I have stuck to having it read real files of data.
> Is there some way I can make it read from standard input, so
> I could pipe a stream of data into it?
>
> I know I can use Pro*C to accomplish the same thing, putting
> SQL statements into the application that creates the data
> stream. However, SQL*Loader in direct mode seems to be much
> faster.
>
> --
> * Patrick L. Nolan *
> * W. W. Hansen Experimental Physics Laboratory (HEPL) *
> * Stanford University *
Received on Wed May 15 2002 - 19:11:56 CDT