| HTML Report [message #304033] |
Mon, 03 March 2008 15:49  |
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  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
