0 Replies Latest reply: Feb 20, 2013 2:04 AM by 984266 RSS

    running sql and updating log file in shell script

    984266
      Hi All,
      I have a problem when creating a script to update a application log and run the sql.

      Sequence:
      1. update Application log
      2. run sql file with commit
      3. update Application log

      But i do not know why no matter how I try, the time stamp for start and finish is the same.
      the more I confuse is this script is cat the file containing the time stamp, but when i cat to the log by running the scipt, it is different from the time stamp in the time stamp file....
      please help


      ============================

      #!/bin/sh
      #SCRIPT name: test_table01_multiple_commits.sh
      ORACLE_BASE=/opt/oracle
      ORACLE_HOME=/opt/oracle/product/10.2/db_1
      PATH=$PATH:$ORACLE_HOME/bin:/usr/bin
      export ORACLE_BASE ORACLE_HOME PATH
      APPLOG=/var/tmp/POC/table1_multiple_commits.log
      TIMESTAMPFILE=/var/tmp/POC/timecontrol
      touch $TIMESTAMPFILE
      ORACLE_SID=test1
      SQLFILE=/var/tmp/POC/test_table01_multiple_commits.sql
      export ORACLE_SID

      STATE=BEGIN
      CONTENT=$SQLFILE
      #touch $TIMESTAMPFILE
      #CURTIMESTAMP=`ls -lE $TIMESTAMPFILE|awk {'print $6 "." $7'}`
      #echo "$CURTIMESTAMP COUNT=$i $STATE $CONTENT" >> $APPLOG

      sqlplus "/ as sysdba" <<EOF
      !/var/tmp/POC/curtimestamp.sh
      !echo "`/usr/bin/cat /var/tmp/POC/timecontrol` COUNT=1 $STATE $CONTENT" >> $APPLOG
      @$SQLFILE
      !/var/tmp/POC/curtimestamp.sh
      !echo "`/usr/bin/cat /var/tmp/POC/timecontrol` COUNT=1 FINISHED" >> $APPLOG
      EOF
      #STATE=FINISHED
      #touch $TIMESTAMPFILE
      #CURTIMESTAMP=`ls -lE $TIMESTAMPFILE|awk {'print $6 "." $7'}`
      #echo "$CURTIMESTAMP COUNT=$i $STATE" >> $APPLOG


      curtimestamp.sh:
      ==============
      #!/bin/sh
      TIMESTAMPFILE=/var/tmp/POC/timecontrol

      /usr/bin/touch $TIMESTAMPFILE
      /usr/bin/ls -lE timecontrol|/usr/bin/awk {'print $6 "." $7'} > $TIMESTAMPFILE

      APPLOG show:
      =======
      $ cat table1_multiple_commits.log
      2013-02-20.15:35:59.118723244 COUNT=1 BEGIN /var/tmp/POC/test_table01_multiple_commits.sql
      2013-02-20.15:35:59.118723244 COUNT=1 FINISHED
      2013-02-20.15:37:25.007471541 COUNT=1 BEGIN /var/tmp/POC/test_table01_multiple_commits.sql
      2013-02-20.15:37:25.007471541 COUNT=1 FINISHED
      2013-02-20.15:45:46.407000592 COUNT=1 BEGIN /var/tmp/POC/test_table01_multiple_commits.sql
      2013-02-20.15:45:46.407000592 COUNT=1 FINISHED
      2013-02-20.15:46:42.999134354 COUNT=1 BEGIN /var/tmp/POC/test_table01_multiple_commits.sql
      2013-02-20.15:46:42.999134354 COUNT=1 FINISHED
      2013-02-20.15:50:26.515685105 COUNT=1 BEGIN /var/tmp/POC/test_table01_multiple_commits.sql
      2013-02-20.15:50:26.515685105 COUNT=1 FINISHED


      $ cat /var/tmp/POC/timecontrol
      2013-02-20.15:52:50.568800408