2

I want to load data from multiple files for a project by executing the script via crontab. The script runs well when executed manually but when the script is executed by crontab routine, the sqlldr does not commit the results but moves to the next iteration, resulting in no data loading.

The sqlldr lines work fine with out the loop.

any help in altering the script so that the desired results are gained will be highly appreciated

Please find Below the code segment

ORACLE_HOME=/oracle/oracle/product/10g/db_1
$ORACLE_HOME/bin/sqlldr 

export ORACLE_HOME

totaltotalFileNumber=1000

count=1

while [ $count -lt $totalFileNumber ] 

do

$ORACLE_HOME/bin/sqlldr $DBUser/$DBPassword@testdb control=$controlFilePath direct=true parallel=true

count=`expr $count + 1`

done

-----------CTL file content------------
options(errors=20000)
LOAD DATA
INFILE '/settlement/(fileName).unl'
BADFILE '/settlement/(fileName).BAD'
DISCARDFILE '/settlement/(fileName).DIS'
append
INTO TABLE MO_SMSC
FIELDS TERMINATED BY "," optionally enclosed by '"'
Trailing NULLCOLS
(             
 colA,
 colB,
 colC,
 colD,
 colE
 )
Hammad Ahmed
  • 75
  • 9

2 Answers2

1

Whenever you manually try to execute the .sh file you are using a session from your linux/unix which when logging in automatically executes the .bash_profile/.profile files which initiates some of the variables like DBUSER etc.

When the .sh file is executed from the cronjob, this .bash_profile/ .profile files are not executed automatically. Therefore you need to call it yourself in the .sh file.

Try something like this [for RHEL] in the first line of the .sh file:

. $HOME/.bash_profile
pOrinG
  • 896
  • 3
  • 13
  • 27
0

You probably should source with source $HOME/.bashrc to setup your environment variables in the cron command, e.g. something like this:

00 22 * * 0-6 source $HOME/.bashrc ;  $PATH_TO_SCRIPT/my_sqlload_script.sh
J. Chomel
  • 8,193
  • 15
  • 41
  • 69