Beim Betrieb einer Oracle Apex-Anwendung ist es oft notwendig tabellarische SQL-Berichte per Mail zu versenden. Dies kann eine Admin-Mail über den Zustand der Apex-Anwendung sein aber auch inhaltliche Benachrichtigungen an bestimmte Benutzerkreise oder Stakeholder der Apex-Anwendung. Gerade im Letzteren Fall sollte die Formatierung des SQL-Berichtes stimmen und das Layout ansprechend sein. Auf Nummer sicher geht man, wenn man in diesem Fall einen PDF-Bericht erzeugt und ihn im Anhang der Mail mitsendet. Dies ist allerdings auch entsprechend aufwendig.
Als einfachere Alternative bietet sich an, den SQL-Bericht als HTML-Tabelle in einer HTML-Mail per Apex Mail zu versenden.
Im Folgenden stelle ich eine generische Funktion vor, die einen beliebigen SQL-Select ausführt und die Ergebnismenge als HTML-Tabelle rendered.
Zusätzlich stelle ich ein Beispielskript vor, das die HTML-Tabelle per Apex Mail versendet. Dabei bereite ich das Layout des Berichts über mehrere allgemeine CSS-Regeln auf.
Funktion PRINT_RESULT:
Hierbei habe ich einfach die Funktion in http://www.java2s.com/Tutorial/Oracle/0601__System-Packages/Printtablewithdynamicquery.htm um die HTML-Option erweitert.
create or replace FUNCTION "PRINT_RESULT" ( p_query IN VARCHAR2, p_markup IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB
AUTHID CURRENT_USER
IS
/**
* Prints the result of the SQL-Statement (p_query) as TEXT-Output or HTML-Output (p_markup)
* This function enhances the following code
* http://www.java2s.com/Tutorial/Oracle/0601__System-Packages/Printtablewithdynamicquery.htm
*/
l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnValue VARCHAR2(32676);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_even BOOLEAN;
l_row_css VARCHAR2(1000);
l_return_str CLOB :='';
BEGIN
DBMS_SESSION.SET_ROLE('ALL');
dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
IF p_markup = 'HTML' THEN
l_return_str := l_return_str || '
'; END IF;for i in 1 .. l_colCnt loop
l_columnValue := l_descTbl(i).col_name;
IF p_markup = 'HTML' THEN
l_columnValue := ''|| l_columnValue || '
';
END IF;
l_return_str := l_return_str ||substr(l_columnValue, 0, 300);
end loop;
IF p_markup = 'HTML' THEN
l_return_str := l_return_str || '
';
ELSE
for i in 1 .. l_colCnt loop
l_return_str := l_return_str || rpad( '----', 200, '-' );
end loop;
END IF;
l_return_str := l_return_str ||CHR(10);
l_even := FALSE;
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
IF p_markup = 'HTML' THEN
IF l_even THEN
l_row_css := 'style="background-color:#f5f5ff;"';
ELSE
l_row_css := '';
END IF;
l_return_str := l_return_str || '
<tr '||l_row_css||'>';
END IF;
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
IF p_markup = 'HTML' THEN
l_columnValue := ''||l_columnValue || '
';
END IF;
l_return_str := l_return_str || substr(l_columnValue, 0, 300);
end loop;
IF NOT l_even THEN
l_even := TRUE;
ELSE
l_even := FALSE;
END IF;
IF p_markup = 'HTML' THEN
l_return_str := l_return_str || '
';
END IF;
l_return_str := l_return_str || CHR(10);
end loop;
IF p_markup = 'HTML' THEN
l_return_str := l_return_str || '
';
END IF;
RETURN l_return_str;
EXCEPTION
when others then dbms_sql.close_cursor( l_theCursor ); RAISE;
END;
/
Versand der Apex-Mail inkl. CSS-Regeln:
declare
l_mail_text CLOB;
l_mail_text_html CLOB;
l_statement VARCHAR2(32676);
l_mail_id NUMBER;
PROCEDURE setup_apex_mail
IS
l_security_group_id NUMBER;
BEGIN
SELECT workspace_id
INTO l_security_group_id
FROM apex_workspaces
WHERE workspace='YOUR_WORKSPACE'; /* Use your workspace name here */
wwv_flow_api.set_security_group_id(l_security_group_id);
END;
FUNCTION get_html_mail_header
RETURN VARCHAR2
IS
l_home_url VARCHAR2(1000);
BEGIN
l_home_url := 'intranet.home-site';
RETURN '<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=utf-8" http-equiv="CONTENT-TYPE" />
<style type="text/css">#content, body { color:#324242; font-family: Arial, sans-serif; width:60em; border:1px solid #EEE; padding:3em 5em 5em; margin:2em; } body { color:#324242; } a { color:#1F4F80; } .i
nfo { font-size:75%; color:#324242; padding:0; } .details { background:#1F4F80 none repeat scroll 0 0; color:#CDD6DF; font-weight:normal; border:2px solid #F18900; text-align:left; } .content th { col
or:#CDD6DF; font-weight:normal; } .details td { color:#ffffff; font-weight:bold; } .header { background:#1F4F80 none repeat scroll 0 0; border:2px solid #F18900; text-align:center; border-collapse:col
lapse; color:#CDD6DF; } .header td { padding:4px 0; } .detailsth { background:#1F4F80; color:#4D5508; font-weight:bold; color:#ffffff; } .event { margin:auto; text-align:center; width:85%; } .statusDe
scr { font-size:0.85em; line-height:2em; } .hide { display: none; } td {border:1px solid #999;} th {border:1px solid #99b; background-color:#dde;} </style>
</head>
<body text="#000000" bgcolor="#ffffff">
<div id="content">
<a href="'||l_home_url||'">
<i>Home-Site</i>
</a>
<div style="display:none">
<br/><br/><br/>
to display this HTML-Mail properly <br/>
enable **HTML-format** in your Mail-client <br/>
</div>
<br />
<br />
<br />
<b>Hello ,</b>
<br />
<br />';
END;
FUNCTION get_html_mail_footer
RETURN VARCHAR2
IS
BEGIN
RETURN '
<br />
<br />
<br />
<b>
<span class="firstLetter">Regards,</span>
</b>
<br />
<br />
Your ApexTipps-Team
<br />
<br />
This email is being sent automatically, please do not answer it
</div>
</body>
</html></div>';
END;
BEGIN
l_mail_text := get_html_mail_header;
l_mail_text := l_mail_text || 'Apex DB-Info '||'<br/>'||'<br/>'||sysdate;
l_statement := 'select owner, table_name, tablespace_name, status, num_rows
from all_tables
where owner=''HR''';
l_mail_text := l_mail_text || '<br/>' ||'<br/>' || '<strong>HR-Tables</strong> '||'<br/>'||'<br/>'||
print_result(l_statement, 'HTML');
l_mail_text := l_mail_text || get_html_mail_footer;
setup_apex_mail;
l_mail_id := apex_mail.send(
p_to => 'some.address@email.domain', -- change to your email address
p_from => 'some.address@email.domain',
p_subj => '[Apex-Info] Query-Report',
p_body => l_mail_text,
--p_body => null,
p_body_html=> l_mail_text);
end;
/
BEGIN
APEX_MAIL.PUSH_QUEUE;
END;
/
Pingback: Sending a tabular HTML report via APEX Mail | Notes by Jens Marre