Friday, May 24, 2013

Oracle 10g:Get Archives details in Mail


1.Create the stored procedure generate mail.

CREATE OR REPLACE PROCEDURE USP_Daily_Generated_Logs
AS
vmsg varchar2(10000);
i int;
begin
vmsg:='<html><table border=1 align="center"><tr bgcolor="#8B6161"><td align="center" colspan=26><b>Daily Genereated Archive Logs</b></td></tr>' ;
vmsg:= vmsg||'<tr><td>day</td><td>00</td><td>01</td><td>02</td><td>03</td><td>04</td><td>05</td><td>06</td><td>07</td><td>08</td><td>09</td><td>10</td><td>11</td><td>12</td><td>14</td><td>13</td><td>15</td><td>16</td><td>17</td><td>18</td><td>19</td><td>20</td><td>21</td><td>22</td><td>23</td><td>TOT</td></tr>';

for log in
(select
  to_char(first_time,'YY-MM-DD') day,
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
  COUNT(*) TOT
from v$log_history
where trunc(first_time) =trunc(sysdate)
group by to_char(first_time,'YY-MM-DD')
)
loop
vmsg:= UTL_TCP.CRLF ||vmsg||'<tr><td>'||log.day||'</td><td>'||log."00"||'</td><td>'||log."01"||'</td><td>'||log."02"||'</td><td>'||log."03"||'</td><td>'||log."04"||'</td><td>'||log."05"||'</td><td>'||log."06"||'</td><td>'||log."07"||'</td><td>'||log."08"||'</td><td>'||log."09"||'</td><td>'||log."10"||'</td><td>'||log."11"||'</td><td>'||log."12"||'</td><td>'||log."14"||'</td><td>'||log."13"||'</td><td>'||log."15"||'</td><td>'||log."16"||'</td><td>'||log."17"||'</td><td>'||log."18"||'</td><td>'||log."19"||'</td><td>'||log."20"||'</td><td>'||log."21"||'</td><td>'||log."22"||'</td><td>'||log."23"||'</td><td>'||log."TOT"||'</td></tr>';
i:=log.TOT;
end loop;
vmsg:=UTL_TCP.CRLF ||vmsg||'</table><br />';
vmsg:=UTL_TCP.CRLF ||vmsg||'</html>';
if(i>=60) THEN
UTL_MAIL.SEND(sender=>'bhavesh.thakkar@de.co.in', recipients=>'bhavesh.thakkar@de.co.in', cc =>'' , subject=>'*** Generated Logs', message => vmsg,  mime_type => 'text/html');
END IF;
END;



2.Create the scheduler which mail info into mail.


No comments:

Post a Comment

Dear User,

Thank you very much for your kind response