2 Replies Latest reply: Aug 14, 2014 4:49 AM by PhilMan2 RSS

    xmlelement xmlattribute and column alignmenet




      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.



      l_html varchar2(2048);








        , xmlconcat(



        , 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(



        , 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




        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);




      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 report -

      ORA-06502: PL/SQL: numeric or value error

      ORA-06512: at line 5

      06502. 00000 -  "PL/SQL: numeric or value error%s"





      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.