Generate HTML Report from PLSQL by Example II

============================
General
============================
This is an example of generating a HTML report from a PLSQL.
The Report in this example, reports Tablespace usage  and Top Segments.
In this example, there 

============================
The Flow
============================
In this example:
Loop on a table holding a list of remote Database Links.
Per each Database Link - run SQL to get the max space, used space, free space, and %free.
It the %free is below a limit (in this example 15%) write this record to report.
addHtmlLine is responsible for adding each line in the report.
Per each Tablespace, report top Segments
============================
Generated Report Example

============================

============================
Code parts
============================
SQL - to get the Tablespace usage data
SQL - to get the top Segments usage data
Main program
addHtmlLine procedure

============================

SQL - to get the Tablespace usage data
============================
SELECT TABLESPACE_NAME, 
       MAX(USED_SPACE) AS USED_SPACE_MB, 
       MAX(MAX_SPACE) AS MAX_SPACE_MB, 
       ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
       CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE 
  FROM ( 
       SELECT tablespace_name,  
              ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
              0 AS MAX_SPACE, 
              0 AS USED_SPACE   
        FROM DBA_FREE_SPACE
       WHERE tablespace_name LIKE '%TBS_A%' OR tablespace_name LIKE '%TBS_B%' OR tablespace_name LIKE '%TBS_C%' OR tablespace_name = 'SYSTEM'  
    GROUP BY tablespace_name  
    UNION ALL 
      SELECT tablespace_name, 
              0 AS FREE_SPACE, 
              ROUND(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END/1024/1024) AS MAX_SPACE, 
              0 AS USED_SPACE 
         FROM DBA_DATA_FILES 
        WHERE tablespace_name LIKE '%TBS_A%' OR tablespace_name LIKE '%TBS_B%' OR tablespace_name LIKE '%TBS_C%' OR tablespace_name = 'SYSTEM' 
    UNION ALL 
       SELECT tablespace_name, 
              0 AS FREE_SPACE, 
              0 AS MAX_SPACE, 
              ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
         FROM DBA_SEGMENTS
        WHERE tablespace_name LIKE '%TBS_A%' OR tablespace_name LIKE '%TBS_B%' OR tablespace_name LIKE '%TBS_C%' OR tablespace_name = 'SYSTEM' 
     GROUP BY tablespace_name 
     ) 

GROUP BY tablespace_name;  

============================

SQL - to get the top Segments usage data
============================
SELECT TABLESPACE_NAME, SEGMENT_NAME, USED_MB
FROM (
  SELECT tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB 
    FROM DBA_SEGMENTS
   WHERE tablespace_name = 'TGS_DATA_01'
  GROUP BY tablespace_name,segment_name
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
  ) WHERE ROWNUM < 11;

  

============================
main program
============================
C_HTML_COLOR_GREEN  VARCHAR2(100) := '#00FF00';
C_HTML_COLOR_YELLOW VARCHAR2(100) := '#FFFF66'; 
C_HTML_COLOR_RED    VARCHAR2(100) := '#00FF00';
C_LIMIT_FOR_RED     NUMBER        := 5;  
C_EMPTY_HTML        VARCHAR2(5)  := '&nbsp';

PROCEDURE reportDiskSpaceOnGates IS

  c_cursor SYS_REFCURSOR;

  CURSOR getDbListCur IS
  SELECT ALL_DEST_VW.service_name,
  ALL_DEST_VW.customer_name,
  ALL_DEST_VW.user_name,
  ALL_DEST_VW.db_link, 
  USEFUL_LINKS.host host
  FROM (           
 SELECT DBA_DB_LINKS.host,
                 MIN(ALL_DEST_VW.user_name||ALL_DEST_VW.service_name || ALL_DEST_VW.db_link) AS service_db_link
       FROM DBA_DB_LINKS,
         ALL_DEST_VW
   WHERE active = 'Y'
   AND ALL_DEST_VW.db_link = DBA_DB_LINKS.db_link     
                 GROUP BY DBA_DB_LINKS.host
 ORDER BY 1,2) USEFUL_LINKS,
 ALL_DEST_VW,
 DBA_DB_LINKS
  WHERE USEFUL_LINKS.service_db_link =
                     ALL_DEST_VW.user_name||ALL_DEST_VW.service_name||ALL_DEST_VW.db_link
    AND ALL_DEST_VW.db_link = DBA_DB_LINKS.db_link
    AND ALL_DEST_VW.active = 'Y'
    AND ALL_DEST_VW.active = 'Y'      
  ORDER BY service_name, customer_name;

  v_MailModuleName  VARCHAR2(100);
  v_tablespace_name DBA_TABLESPACES.tablespace_name%TYPE;
  v_segment_name    USER_SEGMENTS.segment_name%TYPE;
  v_free_space      NUMBER(9);
  v_used_space      NUMBER(9);
  v_max_space       NUMBER(9);  
  v_free_pct        NUMBER(9);
  v_add_more_space  VARCHAR2(1);

  v_run_date         VARCHAR2(10);
  v_report_text      VARCHAR2(32000) := '';

  v_sql_get_free_space_base VARCHAR2(2000);
  v_sql_get_free_space      VARCHAR2(2000); 
  v_sql_get_seg_usage_base  VARCHAR2(2000);    
  v_sql_get_seg_usage       VARCHAR2(2000);    

  v_send_report_ind         VARCHAR2(1);
  v_counter                 NUMBER;
  v_html_color              VARCHAR2(100);
  v_close_db_link_base      VARCHAR2(200);
  v_close_db_link_sql       VARCHAR2(200);
  v_error_msg               VARCHAR2(200);

  TYPE tbs_rec_type IS RECORD (service_name    ALL_DEST_VW.service_name%TYPE,
          customer_name   ALL_DEST_VW.customer_name%TYPE,
       schema_name     ALL_DEST_VW.user_name%TYPE,
                               db_link         ALL_DEST_VW.db_link%TYPE,
       host            DBA_DB_LINKS.host%TYPE,
       tablespace_name USER_SEGMENTS.tablespace_name%TYPE);

  TYPE tbs_tab_type IS TABLE OF tbs_rec_type;
  v_tbs_tab tbs_tab_type;

BEGIN

  v_send_report_ind := 'N';
  v_MailModuleName := 'REPORT_DISK_SPACE_ON_GATES';   
  v_close_db_link_base := 'ALTER SESSION CLOSE DATABASE LINK ';
  v_run_date := TO_CHAR(SYSDATE-1,'DD/MM/YYYY');
  v_counter := 0;  
  v_report_text := '';

  v_report_text := v_report_text||'<HTML>';
  v_report_text := v_report_text||'<HEAD>';  
  v_report_text := v_report_text||'<TITLE>Tablespace Storage Report</TITLE>';
  v_report_text := v_report_text||'<STYLE>';    
  v_report_text := v_report_text||'H1 {color:red;} H2 {color:red;} H3 {color:blue;} p {color:blue;}';
  v_report_text := v_report_text||'</STYLE>';
  v_report_text := v_report_text||'</HEAD>';
  v_report_text := v_report_text||'<BODY>';    
  v_report_text := v_report_text||'<H2 align="left">Tablespace Storage Report for date: '||v_run_date||'</H2>';
  v_report_text := v_report_text||'<BR>';
  v_report_text :=v_report_text||'<TABLE BORDER=1 BGCOLOR="DARKBLUE">';
  v_report_text :=v_report_text||'<TR BGCOLOR="WHITE">';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Service Name</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Customer Name</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:20%">Schema</FONT></TH>';      
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">DB Link</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Host</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Tablespace Name</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Free Space MB</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Used Space MB</FONT></TH>';
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Max Space MB</FONT></TH>';  
  v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">% Free</FONT></TH>';  
  v_report_text :=v_report_text||'</TR>';

  v_sql_get_free_space_base :=   
SELECT tablespace_name, '||
       '(MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB, '||                              'MAX(USED_SPACE) AS USED_SPACE_MB, '||
       'MAX(MAX_SPACE) AS MAX_SPACE_MB, '||
       'ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, '||
       'CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN ''Y'' ELSE ''N'' END AS ADD_MORE_SPACE '||
'FROM ( '||
      'SELECT tablespace_name,  '||
      'ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, '||
      '0 AS MAX_SPACE, '||
      '0 AS USED_SPACE   '||
        'FROM DBA_FREE_SPACE@DB_LINK_XXX '||
       'WHERE tablespace_name LIKE ''%IGT%'' OR tablespace_name LIKE ''%DWH%'' OR tablespace_name LIKE ''%REPORTS%'' OR tablespace_name = ''SYSTEM''  '||
'GROUP BY tablespace_name  '||
'UNION ALL '||
'SELECT tablespace_name, '||
      '0 AS FREE_SPACE, '||
      'ROUND(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END/1024/1024) AS MAX_SPACE, '||
      '0 AS USED_SPACE '||
'FROM DBA_DATA_FILES@DB_LINK_XXX '||
        'WHERE tablespace_name LIKE ''%IGT%'' OR tablespace_name LIKE ''%DWH%'' OR tablespace_name LIKE ''%REPORTS%'' OR tablespace_name = ''SYSTEM'' '||
    'UNION ALL '||
        'SELECT tablespace_name, '||
        '0 AS FREE_SPACE, '||
'0 AS MAX_SPACE, '||
'ROUND(SUM(bytes/1024/1024)) AS USED_SPACE '||
  'FROM DBA_SEGMENTS@DB_LINK_XXX '||
 'WHERE tablespace_name LIKE ''%IGT%'' OR tablespace_name LIKE ''%DWH%'' OR tablespace_name LIKE ''%REPORTS%'' OR tablespace_name = ''SYSTEM'' '||
'GROUP BY tablespace_name '||
  ') '||
  'GROUP BY tablespace_name ';            


  v_sql_get_seg_usage_base :=
   'SELECT TABLESPACE_NAME, SEGMENT_NAME, USED_MB '||
'FROM ( '||
         'SELECT TABLESPACE_NAME,SEGMENT_NAME, ROUND(SUM(bytes)/1024/1024)AS USED_MB                  FROM USER_SEGMENTS@DB_LINK_XXX '||
      'GROUP BY tablespace_name,segment_name '||
      'ORDER BY ROUND(SUM(bytes)/1024/1024) DESC '||
      ') WHERE ROWNUM < 6';

  FOR getDbListRec IN getDbListCur LOOP
  BEGIN
v_sql_get_free_space := REPLACE(v_sql_get_free_space_base, 'DB_LINK_XXX', getDbListRec.db_link);   
OPEN c_cursor FOR v_sql_get_free_space;
LOOP
 FETCH c_cursor INTO v_tablespace_name,v_free_space, v_used_space, v_max_space, v_free_pct,v_add_more_space;        
 EXIT WHEN c_cursor%NOTFOUND;
IF v_add_more_space = 'Y' THEN
 v_send_report_ind := 'Y';
 v_counter := v_counter + 1;
 IF MOD(v_counter,2)=0 THEN
v_html_color := C_HTML_COLOR_GREEN;
 ELSE
v_html_color := C_HTML_COLOR_YELLOW;              
 END IF;  
 IF v_free_pct <= C_LIMIT_FOR_RED THEN 
v_html_color := C_HTML_COLOR_RED;
 END IF;  
    
 addHtmlLine(v_html_color,
v_report_text,
getDbListRec.service_name,
getDbListRec.customer_name,
getDbListRec.user_name,
getDbListRec.db_link,
getDbListRec.host,                 
v_tablespace_name,
v_free_space,
v_used_space,                       
v_max_space,
v_free_pct);    
 
SELECT getDbListRec.service_name,
  getDbListRec.customer_name,
  getDbListRec.user_name,
  getDbListRec.db_link,
  getDbListRec.host,                 
  v_tablespace_name
BULK COLLECT INTO v_tbs_tab  
FROM DUAL;                           
      END IF;  
END LOOP;      
CLOSE c_cursor;

--Close the DB LINKS
BEGIN
 commit;
 DBMS_LOCK.sleep(2);
 v_close_db_link_sql := v_close_db_link_base || getDbListRec.db_link; 
 EXECUTE IMMEDIATE v_close_db_link_sql;
 DBMS_LOCK.sleep(2);
END;
  

------------------------------      
  EXCEPTION
WHEN OTHERS THEN
 IF SQLCODE = -2081 THEN --DBLINK NOT OPEN
NULL;
 ELSIF (SQLCODE = -12170 --TNS Connection Timeout
OR
SQLCODE = -12560 --TNS Protocol Adapter Error
OR
SQLCODE = -942 --Table or View Does not Exists
)
  THEN
IF SQLCODE = -12170 THEN   
  v_error_msg := 'Error: ORA-12170 TNS Connection Timeout';
ELSIF  SQLCODE = -12560 THEN  
  v_error_msg := 'Error:ORA-12560 TNS Protocol Adapter Error';  
ELSIF  SQLCODE = -942 THEN 
  v_error_msg := 'Error: ORA-00942 Table or View Does not Exists'; 
END IF;  
  
--Close the DB LINKS
BEGIN
 commit;
 DBMS_LOCK.sleep(2);
 v_close_db_link_sql := v_close_db_link_base || getDbListRec.db_link; 
 EXECUTE IMMEDIATE v_close_db_link_sql;
 DBMS_LOCK.sleep(2);
EXCEPTION
 WHEN OTHERS THEN
NULL;  
END;
 
 ELSE
RAISE;
 END IF;  
  END;
  ------------------------------    
  END LOOP;

  v_report_text := v_report_text || '</TABLE>';
  --Add Top segments info    
  v_counter := 0;
  IF v_send_report_ind = 'Y' THEN
    v_report_text :=v_report_text||'<BR><BR>';
    v_report_text := v_report_text||'<H3 align="left">Segments Storage Info</H3>';
    v_report_text :=v_report_text||'<TABLE BORDER=1 BGCOLOR="DARKBLUE">';
    v_report_text :=v_report_text||'<TR BGCOLOR="WHITE">';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Service Name</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Customer Name</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:20%">Schema</FONT></TH>';      
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">DB Link</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Host</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Tablespace Name</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Segment Name</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Used MB</FONT></TH>';
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">'||C_EMPTY_HTML||'</FONT></TH>';  
    v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">'||C_EMPTY_HTML||'</FONT></TH>';  
    v_report_text :=v_report_text||'</TR>';
  
    FOR i_tbs_tab_ix IN v_tbs_tab.FIRST .. v_tbs_tab.LAST LOOP
v_sql_get_seg_usage := REPLACE(v_sql_get_seg_usage_base, 'DB_LINK_XXX', v_tbs_tab(i_tbs_tab_ix).db_link  ) ;

OPEN c_cursor FOR v_sql_get_seg_usage;
LOOP
 FETCH c_cursor INTO v_tablespace_name, v_segment_name, v_used_space;        
 EXIT WHEN c_cursor%NOTFOUND;  
 
v_counter := v_counter + 1;
IF MOD(v_counter,2)=0 THEN
 v_html_color := C_HTML_COLOR_GREEN;
ELSE
 v_html_color := C_HTML_COLOR_YELLOW;              
END IF;                                 
addHtmlLine(v_html_color,
  v_report_text,
  v_tbs_tab(i_tbs_tab_ix).service_name,
  v_tbs_tab(i_tbs_tab_ix).customer_name,
  v_tbs_tab(i_tbs_tab_ix).schema_name,
  v_tbs_tab(i_tbs_tab_ix).db_link,
  v_tbs_tab(i_tbs_tab_ix).host,                 
  v_tablespace_name,
  v_segment_name,
  v_used_space,                       
  C_EMPTY_HTML,
  C_EMPTY_HTML);   
END LOOP;
CLOSE c_cursor;
    END LOOP;

    v_report_text := v_report_text ||'</TABLE>';
    v_report_text := v_report_text||'</BODY>';      
    v_report_text := v_report_text||'</HTML>';      
  END IF;

  IF v_send_report_ind = 'N' THEN
    v_report_text := v_report_text || '<BR><BR>NOTHING  TO  REPORT';
  END IF;

EXCEPTION  
  WHEN OTHERS THEN        
    v_report_text := v_report_text ||CHR(10)||v_MailModuleName|| ' has Failed.'||CHR(10)||'Error Details: '||SUBSTR(SQLERRM, 1, 900);
    SEND_MAIL_BY_MODULE(pModuleName => v_MailModuleName, pMailMessage => v_report_text);
    RAISE;

END reportDiskSpaceOnGates;

============================
addHtmlLine procedure
============================
   PROCEDURE addHtmlLine(p_bgcolor IN VARCHAR2,
                         p_msg     IN OUT VARCHAR2,
                         p_msg_part1 IN VARCHAR2, 
                         p_msg_part2 IN VARCHAR2, 
                         p_msg_part3 IN VARCHAR2, 
                         p_msg_part4 IN VARCHAR2,
                         p_msg_part5 IN VARCHAR2, 
                         p_msg_part6 IN VARCHAR2, 
                         p_msg_part7 IN VARCHAR2,
                         p_msg_part8 IN VARCHAR2,
                         p_msg_part9 IN VARCHAR2,
                         p_msg_part10 IN VARCHAR2                         
                         ) IS
                           
     v_bgcolor       VARCHAR2(100);                           
     v_record_str    VARCHAR2(32000);
     
   BEGIN
     IF p_bgcolor IS NULL THEN
       v_bgcolor := 'BGCOLOR="WHITE"';
     ELSE
       v_bgcolor := 'BGCOLOR="'||p_bgcolor||'"';
     END IF;    
   
      v_record_str := p_msg ||'<TR '||v_bgcolor||'>';
      v_record_str := v_record_str||'<TD>'|| p_msg_part1 ||'</TD>';
      v_record_str := v_record_str||'<TD>'|| p_msg_part2 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part3 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part4 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part5 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part6 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part7 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part8 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part9 ||'</TD>';         
      v_record_str := v_record_str||'<TD>'|| p_msg_part10 ||'</TD>';         
      v_record_str := v_record_str ||'</TR>';
      p_msg := v_record_str;

   END addHtmlLine;