[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2363
  • Last Modified:

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...
0
digs developer
Asked:
digs developer
  • 5
  • 3
  • 3
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
digs developerAuthor Commented:
Yes he provided documentation link and also he has given some solution in above comments.

Thank you!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now