Hello,
I'm using Oracle XE 11 and SqlDeveloper 4.0.2 on a Windows platform
I have some code that reads an oracle table and calls a procedure to generate an eMail. I need the eMail structured as a table so that it will read well. I've written the following code which generates the eMail fine. The problem is that the headers default to Center Align and need to be Left Align. The data are left aligned.
DECLARE
l_html varchar2(2048);
BEGIN
Select
xmlserialize(
content
xmlelement(
"table"
, xmlconcat(
xmlelement(
"tr"
, xmlconcat(
xmlelement("th", xmlattributes('180' as "Width"), 'Mass Date Time')
, xmlelement("th", xmlattributes('080' as "Width"), 'Cycle')
, xmlelement("th", xmlattributes('100' as "Width"), 'Liturgy')
, xmlelement("th", xmlattributes('100' as "Width"), 'Hymn Type')
, xmlelement("th", xmlattributes('200' as "Width"), 'Hymn Name')
, xmlelement("th", xmlattributes('080' as "Width"), 'Gather #')
, xmlelement("th", xmlattributes('200' as "Width"), 'Setting')
, xmlelement("th", xmlattributes('200' as "Width"), 'Performer')
))
, xmlagg(
xmlelement(
"tr"
, xmlconcat(
xmlelement("td", to_char(use_date_time, 'MM/DD/YYYY HH:MI AM'))
, xmlelement("td", cyl)
, xmlelement("td", liturgy_readings)
, xmlelement("td", use_type)
, xmlelement("td", hymn_full_name)
, xmlelement("td", gather_number)
, xmlelement("td", setting)
, xmlelement("td", performer)
))
order by use_date_time)))
indent size=2) html_table
into l_html
from
HYMN_MAILING;
html_email(p_to => '1@hotmail.com',
p_from => '2@hotmail.com',
p_subject => 'Test Message using Subject and UTL_SMTP',
p_text => 'This is a test message.',
p_html => l_html,
p_smtp_hostname => 'mail.server.com',
p_smtp_portnum => 587);
END;
/
If I change the following line of code:
xmlelement("th", xmlattributes('180' as "Width"), 'Mass Date Time')
to include an alignment attribute to a single column , it also works fine:
xmlelement("th", xmlattributes('180' as "Width", 'left' as "Align"), 'Mass Date Time')
The problem comes when I try to accomplish a left alignment for more than one column. When I add 'left' as "Align" to more than one header column, SQL Developer I receive a generic PL/SQL error. The following code generates the error:
xmlelement("th", xmlattributes('180' as "Width", 'left' As "Align"), 'Mass Date Time')
, xmlelement("th", xmlattributes('080' as "Width", 'left' As "Align"), 'Cycle')
ERROR:
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
Why can I add the "Width" attribute to multiple columns, but I can't seem to add the "Align" attribute to multiple columns?
I'm not well experienced with XML. Is my approach OK?
Thanks for looking at this.