Hi,

I have to send the query output as an excel attachmet from UNIX Shell script, for this i have written following shell script and sql file this shell script has created the ho.xls file with correct query output but it doesn't generate the mail that shold contain query output as an excel attachment, could anyone please tell me the correct script for my requirement.

Will the script generate the log file as an excel file?

I am using Sun OS 5.6 and Oracle 8i

ho.sh
------

#!/bin/csh
setenv ORACLE_HOME /opt/app/oracle/product/8.1.7
setenv ORACLE_SID environ
set BINARIES=/usr/bin
set APPDIR=/u08/Temp
$ORACLE_HOME/bin/sqlplus uname/pwd @$APPDIR/ho.sql > $APPDIR/ho.xls
uuencode $APPDIR/ho.xls $APPDIR/ho.xls | /usr/bin/mailx -s "Attachment" karthikeyanjo@hcl.in

ho.sql
------
set lines 200
set pagesize 100
select to_char(transaction_date,'HH24')||':00 - '||to_char(transaction_date,'HH24')||':59' "Hour",
count(*) "Total_Hits"
from adsl_interface_log_cng
where trunc(transaction_date)=trunc(sysdate-1)
AND (decode(TRANSLATE(UPPER (adsl_ra_reasoncode),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ!"##$%^&*()-_=+[{]}@;:#~,<.>/?`?/'' ','1234567890'),0,NULL,
TRANSLATE(UPPER (adsl_ra_reasoncode),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ!"##$%^&*()-_=+[{]}@;:#~,<.>/?`?/'' ','1234567890'))
/10) < 2
group by to_char(transaction_date,'HH24')
order by to_char(transaction_date,'HH24');

I do not see anything wrong in syntax with your command.

a) can you manually ftp the .xls file to your pc and verify it as good?
b) instead of uuencode the .xls, can you try and send the ho.sh file?

The answer to these two questions should start the error-correction process.

Hi,

Is it possible to generate an excel file by UNIX Shell script?

I have modify the ho.sh script as follows and i have made spool file in sql file as spool /u08/Temp/ho.txt

ho.sh
------

#!/bin/csh
#!/bin/bash
#!/bin/ksh
setenv ORACLE_HOME /opt/app/oracle/product/8.1.7
setenv ORACLE_SID riker
set BINARIES=/usr/bin
set APPDIR=/u08/Temp
$ORACLE_HOME/bin/sqlplus adsl_stats/adsl_stats01 @$APPDIR/ho.sql
uuencode $APPDIR/ho.txt $APPDIR/ho.txt | mailx -s "Attachment" mail@com

The above script generate the mail with text file that conatins the query output as an attachment in crontab but while ftping the file it shows permission denied( i hve given chmod 777 to this file).

Could you please tell me any command or script that should convert the text file into an excel file in UNIX, i nedd to schedule it in crontab with mail generation option.

Thanks in advance

Two methods for this:
(a) There are specific add-ons you can obtain that allow fairly extensive use of Excel commands and functions to be created by a unix shell.
or...
(b) Create a .csv file. Excel can open (and write) .csv files. So, you could build a file along the following idea. Please note is is not complete code, but a sample of some of the commands to use. [I do many of these in my job - creating reports others can read in Excel.]

Hi,

Thank you for your reply

Finally i got the correct output with following scripts

ho.sh
------

#!/bin/csh
#!/bin/bash
#!/bin/ksh
setenv ORACLE_HOME /opt/app/oracle/product/8.1.7
setenv ORACLE_SID riker
set BINARIES=/usr/bin
set APPDIR=/u08/Temp
$ORACLE_HOME/bin/sqlplus adsl_stats/adsl_stats01 @$APPDIR/ho.sql > ho.xls
uuencode ho.xls ho.xls | mailx -s "Attachment" karthikeyanjo@hcl.in

ho.sql
------
set feedback off
set echo off
set pagesize 100
select to_char(transaction_date,'HH24')||':00 - '||to_char(transaction_date,'HH24')||':59' "Hour",
count(*) "Total_Hits"
from adsl_interface_log_cng
where trunc(transaction_date)=trunc(sysdate-1)
AND (decode(TRANSLATE(UPPER (adsl_ra_reasoncode),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ!"##$%^&*()-_=+[{]}@;:#~,<.>/?`?/'' ','1234567890'),0,NULL,
TRANSLATE(UPPER (adsl_ra_reasoncode),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ!"##$%^&*()-_=+[{]}@;:#~,<.>/?`?/'' ','1234567890'))
/10) < 2
group by to_char(transaction_date,'HH24')
order by to_char(transaction_date,'HH24');

If i create the spool file in sql script as ho.xls also gave the output but the output is combined in single column and the spool file size is more compare to above one.

Hi,

Can u send me the script/command you have used.

Regards,
Karthik