Same KSH behaving differently on diff servers

HI all

I have written a ksh to execute PL/sql procedure and generate the log file. The script is working fine to the extent of calling the taking input, executing PL/SQL procedure.

On one server the log file is getting generated properly. i,e it shows the DBMS output . The log file size was something like 378 bytes

But after running the same script on another the DBMS output is getting repeated in the log file and the log file size is like 1MB.

There are no errors getting shown in the logs.

In both the files the only difference is the oracle home variable and the path variable which i have set in separate export statements.

Can anyone please suggest any ideas as to why this is happening.

Both the servers are connected to the same DB? If not, the data may be inconsistent across the 2 DB's.

--ahamed

my script uses sql plus to connect to the DB. The problem is occuring when i run the script on the server which is the same as that DB.

here is my script:

#!/bin/ksh  

. ${BINDIR:=./}/RIM_input.txt

export ORACLE_HOME=/usr/local/opt/oracle/product/10.2.0.5
export PATH=$ORACLE_HOME/bin:$PATH:

DBASE_HOST=$DB_HOST
DBASE_LOGIN=$DB_LOGIN
DBASE_PWD=$DB_PWD
procename=${1}
comit=${2}
sleep=${3}

#. /home/adba/.profile


EMAIL_ADBA=$EMAIL

DATE=$(date "+%Y-%m-%d.%H:%M:%S")

CMDNAME=${0}

SHORTCMDNAME=$(print ${CMDNAME} | awk -F/ '{print $NF}')

HOSTNAME=`hostname`

#################################################################
#################################################################

LOGFILE=${SHORTCMDNAME}_${procename}_${DATE}.log
exec 1>${LOGFILE} 2>&1
print "\n${SHORTCMDNAME}  STARTING => $(date "+%A %B %d, %Y %H:%M:%S")\n"

#################################################################
#################################################################

function Send_Error {

  cat ${LOGFILE}|mailx -s "ERROR: ${procename}" ${EMAIL_ADBA}
  print "\nERROR: ${procename}"
  print "       Sent to ${EMAIL_ADBA} "
  print "\n${SHORTCMDNAME}  ABORTING => $(date "+%A %B %d, %Y %H:%M:%S")\n"
  exit 1
}

function Send_Msg {

#(cat $LOGFILE; uuencode ./${LOGFILE} ./${LOGFILE}) | mailx -s "Message: ${procename}" ${EMAIL_ADBA}

cat ${LOGFILE}|mailx -s "Message: ${procename}" ${EMAIL_ADBA}

#uuencode ${LOGFILE} ${LOGFILE} | mailx -s "Message: ${procename}" ${EMAIL_ADBA}
  print "\nMessage: ${procename}"
  print "         Sent to ${EMAIL_ADBA} "
  print "\n${SHORTCMDNAME}  ENDING => $(date "+%A %B %d, %Y %H:%M:%S")\n"
  exit 0
}


function Delete_Records {

print "\nMessage: Starting ${0} at $(date "+%A %B %d, %Y        %H:%M:%S")" 

sqlplus -S $DBASE_LOGIN/$DBASE_PWD@$DBASE_HOST  << EOF > ${LOGFILE}
set echo on
set time on
set timing on
set serveroutput on size 1000000
exec ${procename}(${comit}, ${sleep});
EOF

 cat ${LOGFILE} 

 if [[ ! -s ${LOGFILE}  ]]; then
   Send_Error "Error in ${LOGFILE}  File is empty or does not exist."
 elif [[ $(grep -c "ORA-" ${LOGFILE}) -ne 0 ]]; then
   Send_Error "Error in ${LOGFILE}.  Oracle error found."
 elif [[ $(grep -c "procedure successfully completed." ${LOGFILE}) -ne 1 ]]; then
   Send_Error "Error in ${LOGFILE}.  Unknow error found."
 fi

print "\nMessage: Ending ${LOGFILE} at $(date "+%A %B %d, %Y        %H:%M:%S")"  

}

#########################################################

if [[ ${procename} = "" || ${comit} = "" || ${sleep} = "" ]]; then
  print "Syntax Error >${SHORTCMDNAME} ${procename}  ${comit} ${sleep}"
   print "PROCNAME=${procename}"
   print "COMMITFREQUENCY=${comit}"
  print "SLEEP=${sleep}"
  Send_Error "Syntax Error in ${SHORTCMDNAME}"
fi

print "\nMessage: Starting ${SHORTCMDNAME} at $(date "+%A %B %d, %Y        %H:%M:%S")"
print "Message: Parameters PROCNAME COMMITFREQUENCY SLEEP " 
print "Message: Parameters ${procename} ${comit} ${sleep}" 
print "Message: Logs can be found in:\n`pwd`  "

Delete_Records ${procename} ${comit} ${sleep}

print "\n${SHORTCMDNAME}  Complete => $(date "+%A %B %d, %Y        %H:%M:%S")\n"
Send_Msg "${SHORTCMDNAME} is Complete"

What is this line for? Appears to conflict with another processes later in the script which writes to ${LOGFILE}.

Does your PL/SQL behave properly both place when run interactively? -- then it most likely is env variables.

Different servers == different environment variables, which is the first place to look.
By default they cannot be completely identical.

Try the following, add this command to the script:

set >  /tmp/env_vars.`hostname`

next, get both env_vars.* files in a single directory, maybe ftp/sftp. Run the diff
command.

I am very suspicious of line 73:

This has the potential to loop because it just displays the log into space which is then picked up by the weird "exec" line I highlighted in my earlier post.

Are you 100% sure that the two scripts are bit-identical? (Try "cksum").

Thank you for your suggestions methyl and jim. Will try them out both and let you know

Not mentioned so far is that the versions of ksh may be different on the different servers.

Hi,

I tested your suggestions. The one which worked was removing the cat command. I am also right now doing a comparison of the env variables.

Thanks for all the help

Hi all i have another problem with same script.

The LOG files getting generated now are getting truncated at certain parts:

For EX:L procedure successfully completed.

Correct thing is PL/SQL procedure successfully completed.

Any reason why this is happening

I have set:

set echo on
set time on
set timing on
set serveroutput on size 1000000