Sqlplus HTML Report

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

Can someone help as what I am missing in the following report. 
Below SQL works and create report when there are some rows but comes out blank when there is no rows. I want to display the title atleast in blank as it is sent as email. Blank email confuse as what is the report


@htmlon

 

spool myreport3.htm

COLUMN name NEW_VALUE _instname NOPRINT

select upper(name) name from v$database;




Column Title Entmap off

set heading on

TTITLE  CENTER '<h1>Daily Report : - Inactive User Session </h1>' -

RIGHT 'Page:' FORMAT 999 SQL.PNO 

 

select username, sid,serial#,machine,program,logon_time

from v$session s

where  username IS NOT NULL AND username NOT IN ('SYS','SYSTEM','DBSNMP')

and status = 'INACTIVE'

order by logon_time desc;

spool off


@htmloff

Sanjay

[Reardon, Bruce (BBA)]

Hi Sanjay,
There may well be better ways, but what about doing UNION ALL with a select of 6 NULLs from dual?
This will give you a blank row in your report, and you'd want to turn feedback off as the number of rows returned message would be misleading.
Regards,
Bruce

----------------------------

Date: Tue, 21 Aug 2018 00:27:26 +0000 (UTC)
From: "Sanjay Mishra" <dmarc-***@freelists.org> (Redacted sender
Subject: Sqlplus HTML Report

Can someone help as what I am missing in the following report. Below SQL works and create report when there are some rows but comes out blank when there is no rows. I want to display the title atleast in blank as it is sent as email. Blank email confuse as what is the report

@htmlon



spool myreport3.htm

COLUMN name NEW_VALUE _instname NOPRINT

select upper(name) name from v$database;




Column Title Entmap off

set heading on

TTITLE CENTER '<h1>Daily Report : - Inactive User Session </h1>' -

RIGHT 'Page:' FORMAT 999 SQL.PNO



select username, sid,serial#,machine,program,logon_time

from v$session s

where username IS NOT NULL AND username NOT IN ('SYS','SYSTEM','DBSNMP')

and status = 'INACTIVE'

order by logon_time desc;

spool off


@htmloff

Sanjay


________________________________
This email is confidential and may also be privileged. If you are not the intended recipient, please notify us immediately and delete this message from your system without first printing or copying it. Any personal data in this email (including any attachments) must be handled in accordance with the Rio Tinto Group Data Protection Policy and all applicable data protection laws.

[Mark W. Farnham]

A trivial solution is to union all that with selecting the correct number of nulls from dual, as long as you are not processing the row by row results.



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC)
Sent: Monday, August 20, 2018 8:27 PM
To: Oracle-L Freelists
Subject: Sqlplus HTML Report



Can someone help as what I am missing in the following report.



Below SQL works and create report when there are some rows but comes out blank when there is no rows. I want to display the title atleast in blank as it is sent as email. Blank email confuse as what is the report





@htmlon



spool myreport3.htm

COLUMN name NEW_VALUE _instname NOPRINT

select upper(name) name from v$database;



Column Title Entmap off

set heading on

TTITLE CENTER '<h1>Daily Report : - Inactive User Session </h1>' -

RIGHT 'Page:' FORMAT 999 SQL.PNO



select username, sid,serial#,machine,program,logon_time

from v$session s

where username IS NOT NULL AND username NOT IN ('SYS','SYSTEM','DBSNMP')

and status = 'INACTIVE'

order by logon_time desc;

spool off

@htmloff





Sanjay

[Stefan Knecht]

Or just use prompt to indicate if there is no data it means there is no
data - and the report didn't fail or something. E.g.:

prompt "Active session report below - if no sessions are currently active,
no rows are returned:"

Post by Mark W. Farnham
A trivial solution is to union all that with selecting the correct number
of nulls from dual, as long as you are not processing the row by row
results.
freelists.org] *On Behalf Of *Sanjay Mishra (Redacted sender "smishra_97"
for DMARC)
*Sent:* Monday, August 20, 2018 8:27 PM
*To:* Oracle-L Freelists
*Subject:* Sqlplus HTML Report
Can someone help as what I am missing in the following report.
Below SQL works and create report when there are some rows but comes out
blank when there is no rows. I want to display the title atleast in blank
as it is sent as email. Blank email confuse as what is the report
@htmlon
spool myreport3.htm
COLUMN name NEW_VALUE _instname NOPRINT
select upper(name) name from v$database;
Column Title Entmap off
set heading on
TTITLE CENTER '<h1>Daily Report : - Inactive User Session </h1>' -
RIGHT 'Page:' FORMAT 999 SQL.PNO
select username, sid,serial#,machine,program,*logon_time*
from v$session s
where username IS NOT NULL AND username NOT IN ('SYS','SYSTEM','DBSNMP')
and status = 'INACTIVE'
order by logon_time desc;
spool off
@htmloff
Sanjay

["Sanjay Mishra" (Redacted sender "smishra_97" for DMARC)]

Thanks, Mark and Stefan
Both solutions worked and this is what I was looking for.
RegardsSanjay

On Tuesday, August 21, 2018, 9:02:10 AM EDT, Stefan Knecht <***@gmail.com> wrote:

Or just use prompt to indicate if there is no data it means there is no data - and the report didn't fail or something. E.g.:
prompt "Active session report below - if no sessions are currently active, no rows are returned:"


On Tue, Aug 21, 2018 at 5:58 PM, Mark W. Farnham <***@rsiz.com> wrote:


A trivial solution is to union all that with selecting the correct number of nulls from dual, as long as you are not processing the row by row results.

 

From: oracle-l-***@freelists.org [mailto:oracle-l-bounce@ freelists.org] On Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC)
Sent: Monday, August 20, 2018 8:27 PM
To: Oracle-L Freelists
Subject: Sqlplus HTML Report

 

Can someone help as what I am missing in the following report. 

 

Below SQL works and create report when there are some rows but comes out blank when there is no rows. I want to display the title atleast in blank as it is sent as email. Blank email confuse as what is the report

 

 

@htmlon

 

spool myreport3.htm

COLUMN name NEW_VALUE _instname NOPRINT

select upper(name) name from v$database;

 

Column Title Entmap off

set heading on

TTITLE  CENTER '<h1>Daily Report : - Inactive User Session </h1>' -

RIGHT 'Page:' FORMAT 999 SQL.PNO 

 

select username, sid,serial#,machine,program,lo gon_time

from v$session s

where  username IS NOT NULL AND username NOT IN ('SYS','SYSTEM','DBSNMP')

and status = 'INACTIVE'

order by logon_time desc;

spool off

@htmloff

 

 

Sanjay