Home » SQL & PL/SQL » Client Tools » HTML Report (Oracle 9i)
HTML Report [message #304033] Mon, 03 March 2008 15:49 Go to next message
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


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


. /home/oracle/xyzProfile


sqlplus -s USER/pass<<EOF

spool off

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: 67839
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

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

Current Time: Fri May 07 10:14:17 CDT 2021