Running multiple SQLLDR command lines from one script

I have six csv files we need to load every morning.  I've written a script to create dynamic control files for each file, since they have the current date in the file name.  I'm now trying to run this script as well as the six sqlldr scripts from one script.  The script executes and creates the six control files with no problem.  It also runs the first sqlldr script but aborts after it completes.  I'm guessing this is because of bad records.  I want the bad records to write to the .bad file (it does) and continue on to the next sqlldr script.  It just stops.  We're VMS, Oracle 9205.  I've tried silent=all and that had no affect.  Here are the last lines before it aborts.  I'm thinking this may be because of a VMS error message.  Thanks in advance for your time.
...
Commit point reached - logical record count 3392
Commit point reached - logical record count 3439
%NONAME-E-NOMSG, Message number 00000002

Here's a portion of my script:
$!
$@RUN_CR_SQLLDR_CTRL.COM
$!
$@run_sqlldr_hnama.com -- ABORTS after this one
$@run_sqlldr_hnamb.com
$@run_sqlldr_hnamc.com
$@run_sqlldr_hnamd.com
$@run_sqlldr_hname.com
$@run_sqlldr_hnamf.com

Here's the first script that aborts:

$ set verify
$!
$!
$!
$ DATE == F$CVTIME("YESTERDAY",,"DATE")
$ show symbol DATE
$ YY   == F$EXTRACT(2,2,DATE)
$ show symbol YY
$ MM   == F$EXTRACT(5,2,DATE)
$ show symbol MM
$ DD   == F$EXTRACT(8,2,DATE)
$ show symbol DD
$ MMDD = "''MM'''DD'"
$ show symbol MMDD
$ MMDDYY = "''MM'''DD'''YY'"
$ show symbol MMDDYY
$!
$ sqlldr control=hnamaload'''MMDDYY'.ctl,log=hnamaload'''MMDDYY'.log, -
      userid=sla/xxx, discard=hnamaload'''MMDDYY'.dis -
      bad=hnamaload'''MMDDYY'.bad
$!
$!
$exit
$ exit

dgibbs_dbaAsked:
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.

Mark GeerlingsDatabase AdministratorCommented:
As an alternative, could your script instead rename each of the six data files (one at a time) then load the first one using a static controlfile, then rename the resulting: *.bad, *.log and *.dis files, then go on the next file, etc.?
0
dgibbs_dbaAuthor Commented:
Not sure I understand what you mean?  
0
johnsoneSenior Oracle DBACommented:
I think you may be on the right track with the error message.  However, it has been a very very very long time since I have used VMS.  Isn't there something that can be set to ignore warnings and errors?  I cannot recall what it was, but I thought there was something that could be used as an error handler in case the command returned an error or warning.  I think it is defaulting to exiting the script on an error/warning.
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.

radja7Commented:
It seems, you have reached maximum number of insert errors to allow.

See ERRORS command line parameter:

ERRORS (errors to allow)

Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.


ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value specified for ERRORS, then SQL*Loader terminates the load. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.

On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.
0
Mark GeerlingsDatabase AdministratorCommented:
My idea yesterday was in response to this line in your question: "I've written a script to create dynamic control files for each file, since they have the current date in the file name."

I wondered if you could instead use the same (static) control file for each of the data files.  Like Johnsone, I also haven't worked with VMS scripts for many years, so I can't write the script file for you, but here's what I was thinking of in pseudo-code:

1. Check for a data file with the standard name and move, rename or delete it.
2. Find the first data file and rename it to a standard name.
3. Run SQL*Loader using a static script that expects a standard input file name.
4. Rename the data, log, bad and discard files to match data file #1
5. Repeat steps 2-4 for each of the other five data files.
0
johnsoneSenior Oracle DBACommented:
I was responding to this:

It also runs the first sqlldr script but aborts after it completes.



If I am reading this correctly, the issue is that the outer script that runs SQL*Load is aborting if one of the SQL*Loads aborts and that is the problem we are looking at.  Even if there are too many bad records that cause the load to stop, the second SQL*Load should start.
0
dgibbs_dbaAuthor Commented:
I appreciate all the comments.  I've done testing and the issue is the bad records; the script aborts at that point in the first sqlldr run.  I altered the table to fix the bad data issue, reran the full script and it completed successfully.
0
dgibbs_dbaAuthor Commented:
I found the answer - set NOON in the script and VMS ignores the errrors.  Thanks to all to replied.
0
GranModCommented:
PAQed with points refunded (500)

GranMod
Community Support Moderator
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
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.