Re: SQL*Loader Through Pipes

"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote

> Has anyone been successful running SQL*Loader via DBMS_PIPES > or any other means.

To add to what Kyle posted. I almost always use Unix pipes when dealing with Oracle loading and unload (SQL*Loader, Export and Import).

Basically you create a pipe (using the mkfifo or mknod).

You start the reader, e.g. SQL*Loader, to read from the pipe.

Next you start the writer, e.g. an uncompress that writes a zipped file to the pipe.

Here's a very basic script that performs an export and automatically compresses the exported data using the Unix compress command.

--
#! /bin/sh



# sample Unix script for exporting Oracle data

# to a compress/zipped file


rm export.dmp > /dev/null   # delete the pipe/file
mkfifo -p export.dmp        # creates a pipe



# start the compressor as a background job

compress < export.dmp  > export.dmp.Z&



# we read the PID (process id) of the compressor

# child process

COMPRESS_PID=$!
echo "Job $COMPRESS_PID started"



# now we sleep for 5 seconds

sleep 5



# then we check if the compressor is still

# running, just to make sure that its running

# okay

ps -p $COMPRESS_PID > /dev/null
if [ "$?" != "0" ]     # if process does not exist
then
  echo "Compressor unexpectedly died."
  echo "Export aborted".
  rm export.dmp
  rm export.dmp.Z
  exit 1
fi



# now we start the export - the output file specified

# in the parameter file, points to the pipe we created

# i.e. file=export.dmp

echo "Starting exporter"
exp parfile=foobar.par
echo ""



# export completed - we sleep for 10

# seconds to give the compressor a change

# to complete

sleep 10



# if the compressor is still running there

# is a problem - it should have read the eof

# marker written to the pipe by export

ps -p $COMPRESS_PID > /dev/null
if [ "$?" = "0" ]
then
  echo "Compressor job is still active. It should have terminated by
now."
  echo "Killing the job..."
  kill -9 $COMPRESS_PID
  echo ""
  echo "WARNING: This export terminated abnormally. Please check the
export data file and log for errors!"
else
  echo "Export terminated normally"
fi



# clean up

rm export.dmp
--

SQL*Loader can be run the exact same way. The most complex one I've
seen in a production environment:

mainframe file --> ftp --> pipe --> dd (EBCDIC to ASCII) --> pipe -->
SQL*Loader --> Oracle

This used two pipes and two background processes. Worked pretty well.
At the time we were still limited to 2GB file systems and the
mainframe data file was a 4GB file. This solved the problem with
limited file system size.

Yet another reason why NT sucks when it comes to hardcore batch
processing.. :-)

--
Billy