We help IT Professionals succeed at work.

Running multiple SQLLDR command lines from one script

dgibbs_dba
dgibbs_dba asked
on
Medium Priority
2,114 Views
Last Modified: 2008-01-09
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

Comment
Watch Question

Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.?

Author

Commented:
Not sure I understand what you mean?  
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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

Commented:
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.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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

Author

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.

Author

Commented:
I found the answer - set NOON in the script and VMS ignores the errrors.  Thanks to all to replied.
Commented:
PAQed with points refunded (500)

GranMod
Community Support Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.