Help With Shell Scripts

Shell Programming and Scripting
1

if I run the below mentioned code, i am getting error. I dunno why. plz help me out this code runs a sql query and sends the result by email

#!/bin/ksh
#
# Purpose: - Report Workflows that have Persistence turned ON
echo "Workflow Persistence Report Started: `date '+%Y%m%d_%H%M%S'`" >> master.log

umask 000

if [ -f ./Workflow_Persistence_env.rc ]
then
   . ./Workflow_Persistence_env.rc
else
   echo "FATAL: Error sourcing fusion_employee_list.rc !" >> master.log
   exit -1
fi

# Obtain the secure username and password (if it exists)
#
if [ -f "$BATCH_USER_DIR/batch_user.ksh" ]
then
   TMPPWD=`pwd`               
   cd $BATCH_USER_DIR         
   . ./batch_user.ksh        
   RTNCD=$?                 
   cd $TMPPWD              
   if [ $RTNCD = 0 ]
   then
      export SIEBEL_USERNAME=$USERID
      export SIEBEL_PASSWORD=$PASSWORD
   else
      echo "FATAL: batch_user.ksh returned an error ($RTNCD)" >> master.log
      exit -1
   fi
else
   echo "ERROR: batch_user.ksh does not exist, unable to source in variables" >> master.log
   exit -1
fi
export LT=`date +%Y%m%d_%H%M%S`
export RDATE=`date +%Y-%m-%d`
sqlplus -S $SIEBEL_DB_USERNAME/$SIEBEL_DB_PASSWORD@$SIEBEL_DB_ORACLESID >> master.log <<!
set head off 
set pagesize 0 
set linesize 1000
set trimspool off
spool $REPORT_FILE;
SELECT "||A.NAME||'|'||B.STATUS_CD||" FROM $SIEBEL_DB_TABLE_OWNER.S_WF_STEP A, $SIEBEL_DB_TABLE_OWNER.S_WF_STEP_INST B where 

B.STATUS_CD = 'ACTIVE';
spool off
QUIT;
!

####################
# Send file in e-mail
####################
usr/bin/mailx -s "Workflow Persistence On" $MAIL_LIST <<-EOF1
Open this file in Excel with a , (comma) as a delimiter.

Please DO NOT respond to this email, instead contact the WB: E-CRM System Support group for any issues.

Thank you.
~< ! uuencode $CSV_PATH/$REPORT_FILE $REPORT_FILE

~.
EOF1
2

well, thanks for sharing the 'details' of the 'getting error'.
Let us help you if you can help US to better understand what the 'error' is.

BTW, the path to the 'mailx' is missing the leading slash.

3

hi

the error it gives is usr/bin/mailx -s not found.

thanks

4

guys i am new to shell scripts and i dont even know how to execute a script.

my file name is Workflow_Persistence.ksh and when I try to execute it using
sh Workflow_Persistence.ksh, it gives an error like this

Workflow_Persistence.ksh: -r: is not an identifier
Workflow_Persistence.ksh: -1: bad number

If i just type Workflow_Persistence.ksh, it gives an error like this

ksh: Workflow_Persistence.ksh: cannot execute

please help me how to execute this file guys.

thanks

5

I am finally able to execute the script and everything is working fine. I am running a sql query and it works fine, when it saves the results to a file. it dosen't save the name of the column. For Eg, the column STATUS_CD has a value "ACTIVE". Just ACTIVE is getting stored in the file, the name STATUS_CD is not getting saved. What should I do to get the column name as well.

Regards
Raghav

6

Well for one thing your script is causing the the column heading to be turned off.

set head off
set pagesize 0

will both turn off column headings but that isn't going to help you since you are concatenating name and status_cd so you need to give your column a meaningful alias like "status"

set heading on
set pages 9999
SELECT "||A.NAME||'|'||B.STATUS_CD||" STATUS FROM ...

or

SELECT 'status=' || "||A.NAME||'|'||B.STATUS_CD||" FROM
7

Markile,

Thanks for your reply. I tried what you said, but i still didnt get the column alias names. can you explain me further.

thanks
raghav