digs developer
asked on
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...
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...
And sqlldr will give you an error file (a .bad file) when it cannot load all your records. Just check for that file.
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.
Also, the .bad file varies per file loaded, it is easier to check the return code as that is constant.
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.
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.
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.
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.
ASKER
need some examples
>> need some examples
What examples do you need?
Do you already have loader scripts and control files? Can you post what you have already?
What examples do you need?
Do you already have loader scripts and control files? Can you post what you have already?
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.
ASKER
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,
Need some code for all above things,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes he provided documentation link and also he has given some solution in above comments.
Thank you!
Thank you!
Basically what you are looking to do is:
sqlldr ....
ret=$?
if [ $ret -ne 0 ] ; then
exit
fi
sqldr ...