Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a shell script that if run manually or via Cron executes fine and loads a delimited file using sqlldr to the database successfully.

However via Autosys the script executes, sqlldr says it was successful, however the data is never actually loaded into the database.

Has anyone ever experienced this before with the sqlldr/Autosys combination, and if so, knows of a workaround/fix?

share|improve this question
add comment

4 Answers

Could be a bunch of reasons. One that I experienced was that, with a batch job, one of the startup scripts was bypassed and the LANG environment variable was unset, which affected the langauge settings for the database connection, including the default date format/language, which meant that records were rejected because the dates were invalid.

Less obscure ones may be path related (couldn't find the datafile, the control file) or security related (database function may try to record a terminal).

SQLLDR should produce a LOG file and a BAD file of rejected records, which may give a hint

share|improve this answer
add comment

This is the script

!/bin/sh
PATH=$PATH:/usr/local/bin/:/usr/java/jre1.6.0_17/bin/

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_2
export ORACLE_SID=orcl
export ORAENV_ASK=NO
. oraenv

sqlldr system/123456@orcl control=test.ctl,DATA=test.dat,ERRORS=100000000,MULTITHREADING=true,BAD=test.bad

cat test.bad >> rejected.txt
share|improve this answer
add comment

Where is the "oraenv" being dotted in from by the . oraenv line. Could it be that when run from autosys the pwd is different?

Try putting "set -x" in the script after the !/bin/sh in your script.

Should'nt that be a !#/bin/sh rather than !/bin/sh

Autosys should be able to generate some STDERR/STDOUT logs. Can you post those?

share|improve this answer
add comment

This is correct Syntax to run through CRONTAB or any other Scheduling system for sqlldr

export LD_LIBRARY_PATH=/oracle/ora9/lib:$LD_LIBRARY_PATH export ORACLE_HOME=/oracle/ora9 export PATH=/oracle/ora9/bin:$PATH

sqlldr userid=user/password@db \ control=/Path/to/control/file.ctl \ data=/give/datafile/path/datfile \ log=same for log \ bad=same for bad errors=999

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.