Shell Programming and Scripting

### Set the global variables.
CONNECT=$1
LOG=$MMHOME/log/ang_stdnld_`date +'%b_%d'`.log
ERR=$MMHOME/error/err.ang_stdnld.`date +'%b_%d'`
##SCRIPT_HOME=$MMHOME/oracle/proc/bin
### Check if path where extracts are to be created is passed as arg.
### If this arg is Null, then default to PWD.
if [ "$2" = "" ]
then
   DIR=`pwd`
   echo "Path is not sent. Defaulting to PWD: "`pwd`
else
   if [ -d $2 ]
   then
      DIR=$2
   else
      echo "Path $2 does not exist"
      exit 1
   fi
fi
#-------------------------------------------------------------------------
# Function Name: remove_files()
# Purpose      : Clean the temporary files created.
#-------------------------------------------------------------------------`
remove_files()
{
  echo "Removing temporary files...\n" >> $LOG
  # Clean up temp list files.
  rm -f $CHAIN
  if [[ -s $ERR ]]
  then
    :
  else
     rm -f $ERR
  fi
  return 0
}
### end remove_files
   echo "Getting the chain name...\n" >>$LOG
   # Set filename to contain this chain 
   CHAIN=$DIR/stdnld_chain.lst 
   $ORACLE_HOME/bin/sqlplus -s $CONNECT <<EOF >>$CHAIN
   set pause off
   set echo off
   set heading off
   set feedback off
   set verify off
   set pages 0
   select distinct sh.chain
     from store st,
          store_hierarchy sh,
          period p
    where st.store = sh.store
      and p.vdate between st.store_open_date and nvl(st.store_close_date,p.vdate)
    order by chain;
EOF
   if [ `grep "^ORA-" $CHAIN | wc -l` -gt 0 ]
   then
      cat $CHAIN >> $ERR
      echo "ORA Error while creating Chain listing in ang_get_chain()." >> $LOG
      exit 1
   fi
   for i in `cat $CHAIN`
   do
     returnedchainvalue=`$ORACLE_HOME/bin/sqlplus -s $CONNECT <<EOF
     set serveroutput on;
     set pause off
     set echo off
     set heading off
     set feedback off
     set verify off
     set pages 0
     Declare
        message VARCHAR2(200);
     Begin
        select ANGELIC_PKG.ANG_GET_CHAIN_NAME($i) into message from dual;
        dbms_output.put_line (message);
     End;
     /
     set serveroutput off;
     EXIT;
EOF`     
 X=`echo $returnedchainvalue`     
$ORACLE_HOME/bin/sqlplus -s $CONNECT <<EOF >>${X}_en-us_stores_001
   set heading off
   set feedback off
   set pages 0 
   set linesize 32000
   set trimspool on
   set trimout on
SELECT 'retailstoreid'||
       chr(9)||
       'name'||
       chr(9)||
       'main phone'||
       chr(9)||
       substr('address line 1',1,80)||
       chr(9)||
       substr('address line 2',1,80)||
       chr(9)||
       substr('city',1,80)||
       chr(9)||
       substr('state',1,80)||
       chr(9)||
       'postal code'||
       chr(9)||
       'country code'||
       chr(9)||
       'home page'||
       chr(9)||
       'hours'||
       chr(9)||
       'category'||
       chr(9)||
       'description'||
       chr(9)||
       'currency'||
       chr(9)||
       'established date'||
       chr(9)||
       'latitude'||
       chr(9)||
       'longitude'
FROM DUAL;
SELECT S.STORE||
       chr(9)||
       S.STORE_NAME||
       chr(9)||
       NVL(S.PHONE_NUMBER, 'xxx-xxx-xxxx')||
       chr(9)||
       substr(A.ADD_1,1,80)||
       chr(9)||
       substr(A.ADD_2,1,80)||
       chr(9)||
       substr(A.CITY,1,80)||
       chr(9)||
       substr(NVL(A.STATE,'xx'),1,80)||
       chr(9)||
       NVL(A.POST, 'xxxxx')||
       chr(9)||
       A.COUNTRY_ID||
       chr(9)||
       ''|| ### here is the place where in I want the $HOMEPAGE value.
       chr(9)||
       ''||
       chr(9)||
       ''||
       chr(9)||
       ''||
       chr(9)||
       S.CURRENCY_CODE||
       chr(9)||
       to_char(S.STORE_OPEN_DATE, 'MM/DD/YYYY')||
       chr(9)||
       ''||
       chr(9)||
       ''
FROM STORE S,
     ADDR A,
     STORE_HIERARCHY SH,
     PERIOD P
WHERE A.MODULE='ST'
  AND A.COUNTRY_ID='US'
  AND A.KEY_VALUE_1=S.STORE
  AND A.ADDR_TYPE=01
  AND S.STORE=SH.STORE
  AND S.STORE_OPEN_DATE <= P.VDATE
  AND S.STORE_CLOSE_DATE >= P.VDATE
  AND SH.CHAIN=$i;
EOF
done
remove_files
### end ang_stdnld

cat myQuery.sql
SET HEAD OFF
SET FEEDBACK OFF
SET VERIFY OFF
SELECT TO_DATE('&&1', 'YYYYMMDD') TEST FROM DUAL;
EXIT

echo "START myQuery.sql 20100827" | sqlplus -S -L <user>/<pass>@${ORACLE_SID}