Home/ Questions/Q 11598
Next
Answered
computing
  • 4
Asked: 2021-12-08T05:25:22+00:00 In:

Unix Shell Script To Fetch The Data From Db

  • 4

Hi,

I am new to this forum and hope to get my answer.

My Requirement :

1. I want to write a UNIX shell script which will fetch the data from the database (for fetching the data I have sql script written)

2. And write the data into .xls file

3. And want to receive the generated .xls file via email into my inbox. This should be an automated process, means the shell script should be run at specific Date, Time on daily basis and automatically send the email containing the .xls file (having the record fetched from the database.)

4. Also I want to know about what permission are required at database level and on unix server to complete this job as of now I am using the normal user id and password created for me on Unix.

I am not sure about the cronjob can be used here for executing the script at specified date and time on daily basis.

I am using the database as ORACLE 10G

It will be really helpful for me if I get my all answers and able to achieve the success in completing the task.

Thanks in advance

Varun

Share

    1 Answer

    1. Best Answer
      Here is what you have to do if you want to run the script at a particular hour (let us say @ 10:00) without using crontab:

      #!/bin/ksh

      while(true)
      do

      HOUR=`date +”%H”`

      if [ “${HOUR}” -eq 10 ]
      then

      sqlplus -s < Your DB Connection String > << EOF

      set echo off feed off heading on pagesize 0 trimspool on linesize 1000

      spool RESULT.xls

      < your SQL query here >

      spool off;

      exit
      EOF

      cat RESULT.xls | mailx -s “Subject” < email recipient >

      else
      exit 1
      fi

      # Sleep for 1 hour.

      sleep 3600

      done

      • 0