Shell Programming and Scripting

Hi,

I want to write a shell script which connect to my database with the following credentials :

User name : user
PWD : rap_user_1

Hostname : app.Unix.Gsm1900.Org
Port : 7862
SID : PTNC1


Once connected to DB i want to fetch data with the help of a SQL statement and expoet that data in a Excel sheet and then i need to send an email to few ppl.

Please help me with the script.

What have you tried?

I tried sqlplus to connect to my database , but seems like SQLPLUS is not there

Code:

sqlplus -s user/rap_user_1@PTNC1
-ksh: sqlplus: not found [No such file or directory]
$ sqlplus

The error states that the OS cannot find Oracle Database Utility: sqlplus in its PATH.

Check if you have variable ORACLE_HOME defined:

Code:

echo $ORACLE_HOME

If yes, check if sqlplus is available in bin directory:

Code:

ls $ORACLE_HOME/bin/sqlplus

If the utility exists, set your PATH to include $ORACLE_HOME/bin directory:

Code:

PATH=$PATH:$ORACLE_HOME/bin 
export PATH

You can put above in startup scripts to ensure that it is available each time you create a new session.

An alternative with no sw cost is JAVA JDBC tool xigole jisql.

Converting delimited text to proper CSV is a task for a serious tool, but if you have no ',' or '"' in your data, you can just change the delimiter to comma. Now you have a CSV file. You need an attaching batch email client like mutt to send the file as an attachment with your message to your distribution list. Sometimes you can use uuencode to pack the csv for you, or you can send it as a 8 bit binary, using sendmail and various tricks.

You could turn it into an HTML table and send an HTML email. Users can paste the html table into excel.