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


Running multiple SQLLDR command lines from one script

Posted on 2006-03-21
Medium Priority
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_sqlldr_hnama.com -- ABORTS after this one

Here's the first script that aborts:

$ set verify
$ 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 -
$ exit

Question by:dgibbs_dba
  • 3
  • 2
  • 2
  • +2
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16252281
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 Comment

ID: 16252506
Not sure I understand what you mean?  
LVL 35

Expert Comment

ID: 16253035
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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Expert Comment

ID: 16253535
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.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16257577
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.
LVL 35

Expert Comment

ID: 16260029
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 Comment

ID: 16260673
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 Comment

ID: 16262888
I found the answer - set NOON in the script and VMS ignores the errrors.  Thanks to all to replied.

Accepted Solution

GranMod earned 0 total points
ID: 16559933
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question