Home » Other » Client Tools » HTML Report (Oracle 9i)
HTML Report [message #304033] Mon, 03 March 2008 15:49 Go to next message
SQLJerk
Messages: 1
Registered: March 2008
Junior Member
Hi ALL, I am new to this forum.

I am trying to send email with xsl attachement. I am generation a sql statement. Sometimes the query does not return anything. At that time I would like type message "No Records". How can I accomplish it? Please help.

I have follwoing .sql file where i am generating html
------------------------------------------------------------------
set feedback off
set linesize 150
set pagesize 1000
set heading off

--select sysdate from dual;

set heading on

column columna format A10
column columnb format A10


set feed off markup html on spool on
spool reportresults.xls

Select columna, columnb from tablev;

set heading off
spool off
set markup html off spool off

quit;

------------------------------------------------------------------
Then i have following .sh file which actually runs the sql and sends out the results in email as an attachment.

#!/bin/bash

. /home/oracle/xyzProfile

SCRIPT_DIR=/home/oracle/reports
REPORT_SQL=reportresults.sql
REPORT_NAME=reportresults.xls

sqlplus -s USER/pass<<EOF

spool $SCRIPT_DIR/$REPORT_NAME
@$SCRIPT_DIR/$REPORT_SQL
spool off
exit;
EOF

cat $SCRIPT_DIR/$REPORT_NAME | mutt -s "XYZ Report `date`" -a reportresults.xls "xyz@domain.com"
Re: HTML Report [message #304100 is a reply to message #304033] Tue, 04 March 2008 01:07 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a column to act as a flag:
SQL> create table t (col integer);

Table created.

SQL> insert into t values (0);

1 row created.

SQL> commit;

Commit complete.

SQL> def flag=0
SQL> col flag noprint new_value flag
SQL> set feed off
SQL> select col, 1 flag from t where col=1;
SQL> set head off
SQL> select 'No record' from dual where &flag!=1;
No record

Regards
Michel
Previous Topic: Toad and SQL Optimizer
Next Topic: externaly identified
Goto Forum:
  


Current Time: Thu Mar 28 11:04:44 CDT 2024