Trapping errors from SQL*Loader

sdupre · Post by **sdupre** » Tue Mar 16, 2004 10:37 am

I have a job that loads data from a flat file into oracle.

Datastage generates an SQL*Loader to perform this.

Problem is, the logs in director show that all the records have been processed (and all lights are green) although half the records are invalid and didn't load because of not null constraints.

The SQL*Loader log does show the errors (which is burried deep in working directories) but nowhere in datastage does it hint that not all the record have loaded.

How do I make the job fail / show red lights / scream ??

I've tried to add the environment variable $APT_ORACLE_LOAD_OPTIONS and set to OPTIONS (ERRORS=1) but even that does not make datastage show any errors.

Obviously I do not want to add a transformer and test all the constraints myself...

sdupre · Post by **sdupre** » Tue Mar 16, 2004 12:58 pm

more info...

-The write method of the Oracle stage is "load"

-Neither the flat file nor the Oracle stage support reject links

-reject mode = fail does nothing

I must be missing something obvious because it can't be that uncommon to want a job to fail when the SQL loader fails.

sdupre · Post by **sdupre** » Tue Mar 16, 2004 3:07 pm

nevermind...

found that it does not trap errors when running in parallel but it does when running in sequential.

I'll open a case with ascential.