Unix shell script and sqlldr

Hi all,
Is there any command in Unix, which will stop the load process if any error is found in the i/p file?

I am trying to load 5 files sequentially.
A then
B then
C then
D then
E end;

1) If A encounter's any error while data load, the load process should stop, and it should not continue
2) If A load's, then B load's, then C has errors, then it should stop and should not continue to load D and E.

Appreciate any help, with Unix shell script...
digs developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Yes you can.  You need to check the return code from SQL*Loader.  They are documented here -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL1048

Basically what you are looking to do is:

sqlldr ....
ret=$?
if [ $ret -ne 0 ] ; then
  exit
fi
sqldr ...
0
Gerwin Jansen, EE MVETopic Advisor Commented:
And sqlldr will give you an error file (a .bad file) when it cannot load all your records. Just check for that file.
0
johnsoneSenior Oracle DBACommented:
I would not rely on a .bad file.  It is not always created.  If there is a fatal error, I am sure there isn't one and I don't believe that one is created in the case of warnings.

Also, the .bad file varies per file loaded, it is easier to check the return code as that is constant.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gerwin Jansen, EE MVETopic Advisor Commented:
What is your definition of "error is found in the i/p file"? Do you mean that one or more records are not loaded?

How did you configure the .ctl files for each loader? If you didn't specify a .bad file, then you would not get it (you are correct there johnsone).

Do you get log files of each loader? The log files should show you how many records were loaded properly and if any were not, or skipped.
0
johnsoneSenior Oracle DBACommented:
Just to clarify, a bad file is only created if there are rejected records.  The documentation specifically states:

the bad file created by SQL*Loader to store records that cause errors during insert or that are improperly formatted

The file is created regardless of whether the parameter is specified.  If the parameter is not specified then a default file name is generated.

These are not the only things that can cause a load to fail (and it depends on your definition of fail).  For instance, if you filled the tablespace, that would not generate records in a bad file, it would just cause SQL*Loader to exit with an appropriate error code.
0
digs developerAuthor Commented:
need some examples
0
Gerwin Jansen, EE MVETopic Advisor Commented:
>> need some examples
What examples do you need?

Do you already have loader scripts and control files? Can you post what you have already?
0
johnsoneSenior Oracle DBACommented:
The first post that I have here has a simple example of how to do it.  If you are looking for more than that, you are going to need to provide some information on what you are looking for.  Also, do you stop on a warning or not?  I assume that errors and fatal errors you would want to stop on.
0
digs developerAuthor Commented:
Need some examples means need syantx of Sqlldr  and how an we load tha data in different tables with 3 different Sqlldr statements andif ene o the file occur any error then stop the procees with message.
Need some code for all above things,
0
johnsoneSenior Oracle DBACommented:
Without any sample data and the table structure being loaded into, we cannot make it up.  The best I can do is point you to the documentation -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL004

That should describe all of the command line options available.

If you post sample data and table structures for one of the table loads, I can see if I can get some time to come up with a sample for the one, but you would be on your own to do the rest.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
digs developerAuthor Commented:
Yes he provided documentation link and also he has given some solution in above comments.

Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.