This discussion is archived
13 Replies Latest reply: Mar 13, 2013 10:47 AM by Christian Erlinger RSS

Unable to add HTML tags in Oracle UTL_MAIL package

750019 Newbie
Currently Being Moderated
Dear All,
I am trying to send email through UTL_MAIL package. The requirement is to send mail in table form. So, for that i need to add html tags. Previously i did this with UTL_SMTP but arabic charaters was the issue there. Now i tried this package. The Arabic characters are displayed fine now but HTML is not working.

Can anybody tell me how to add HTML tags using UTL_MAIL.

Regards:

Naik Amal Khan
  • 1. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    John Stegeman Oracle ACE
    Currently Being Moderated
    When you used Google to search for "UTL_MAIL HTML" what did you find?
  • 2. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    750019 Newbie
    Currently Being Moderated
    Hi Jhon,

    I tried all the options from Google but was unable to send email in html tabular form. If you have any solution, please share it.

    Regards:

    Naik Amal Khan
  • 3. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    Christian Erlinger Guru
    Currently Being Moderated
    Maybe if you show us your code and tell us what error you get (if any) you will enable us to help you much more efficiently. And by the way indicating your forms/database versions also doesn't hurt in general...

    cheers
  • 4. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    750019 Newbie
    Currently Being Moderated
    Hi Chritian,

    The HTML tags are not converted. It is displayed in email as <TD> and <TR> etc.
    '<TR> <TD> ' || 'Employee Number' || ' </TD> <TD> ' || I.employee_number || ' </TD> </TR>'

    I think UTL_SMTP does not support HTML.

    Thanks
  • 5. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    Christian Erlinger Guru
    Currently Being Moderated
    I do know how HTML code looks like. I do not know how your code invoking utl_smtp looks like..

    +"Talk is cheap. Show me the code."+
    - Linus Torvalds

    also a quick google search on utl_smtp and html revealed this one as the very fist hit: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1739411218448

    Yes, utl_smtp can send html mails.

    cheers
  • 6. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    750019 Newbie
    Currently Being Moderated
    Hi Christian,
    Below is the complete code. Please have a look into it and advice the solution or share any sample code which you have.

    CREATE OR REPLACE
    PROCEDURE XX_LEAVE_BALANCE_ALERT_PROC(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) is
    V_INSTANCE VARCHAR2(50);
    V_SUB VARCHAR2(32767) := ' Employee Leave Balance for the month of '||to_char(trunc(sysdate),'Mon-YYYY');
    V_INTRO VARCHAR2(2000);
    V_INTRO1 VARCHAR2(2000);
    V_INTRO2 VARCHAR2(2000);
    V_BODY VARCHAR2(32767) := NULL;
    V_HEADING VARCHAR2(2000):=NULL;
    V_FOOTER VARCHAR2(2000):=' ******************************************************'
    ||chr(10)||chr(10)|| ' هذا الأميل هو تلقائى مرسل من نظام الأوراكل للموارد البشرية والمرتبات, برجاء عدم الرد. '|| chr(10);
    -- ||chr(10)||chr(10)||'Note. This is an auto generated message, Please do not reply to this E-mail.';

    ---------------------------------

    CURSOR C1 IS
    SELECT distinct
    pas.employee_number,
    PAS.FULL_NAME,
    pas.email_address EMAIL_ADDRESS ,
    (select substr(pj.name ,instr(pj.name,'.')+1) from per_jobs pj where job_id =paf.job_id) Job,
    hou.name department,
    get_leave_days(81,paf.assignment_id,'Annual Leave',
    to_date(to_char((TO_CHAR(PAS.EFFECTIVE_START_DATE,'DD'))||'/'||TO_CHAR(PAS.EFFECTIVE_START_DATE,'MM')||'/'||
    case when TO_CHAR(PAS.EFFECTIVE_START_DATE,'MM') >= TO_CHAR(sysdate,'MM') and
    round(months_between (((to_date(trunc(sysdate),'DD/MM/RRRR'))),
    to_date(to_char(pas.effective_start_date,'DD')||'/'||to_char(pas.effective_start_date,'MM')||'/'||(to_char(sysdate,'RRRR')-1),'DD/MM/RRRR'))/12,2)> '1'
    then
    to_number(TO_CHAR(sysdate,'RRRR')) else to_number(TO_CHAR(sysdate,'RRRR') - 1)end),'DD-MM-RRRR') -1
    ,
    to_date(to_char((TO_CHAR(PAS.EFFECTIVE_START_DATE,'DD'))||'/'||TO_CHAR(PAS.EFFECTIVE_START_DATE,'MM')||'/'||
    case when TO_CHAR(PAS.EFFECTIVE_START_DATE,'MM') >= TO_CHAR(sysdate,'MM') and
    round(months_between (((to_date(trunc(sysdate),'DD/MM/RRRR'))),
    to_date(to_char(pas.effective_start_date,'DD')||'/'||to_char(pas.effective_start_date,'MM')||'/'||(to_char(sysdate,'RRRR')-1),'DD/MM/RRRR'))/12,2)> '1'
    then
    to_number(TO_CHAR(sysdate,'RRRR')) + 1 else to_number(TO_CHAR(sysdate,'RRRR'))end),'DD-MM-RRRR')-1) ABSENCE_DAYS ,
    (select pg.name from per_grades pg where grade_ID = paf.grade_ID and business_group_id = pas.business_group_id ) grade,
    XX_ABSCENCE_PKG.Get_Net_Accrual(paf.assignment_id,61,paf.business_group_id,-1,1061,to_date(SYSDATE,'DD-MON-RRRR')) current_bal,
    XX_ABSCENCE_PKG.Get_Net_Accrual(paf.assignment_id,61,paf.business_group_id,-1,1061,
    to_date(to_char((TO_CHAR(PAS.EFFECTIVE_START_DATE,'DD'))||'/'||TO_CHAR(PAS.EFFECTIVE_START_DATE,'MM')||'/'||
    case when TO_CHAR(PAS.EFFECTIVE_START_DATE,'MM') >= TO_CHAR(sysdate,'MM') and
    round(months_between (((to_date(trunc(sysdate),'DD/MM/RRRR'))),
    to_date(to_char(pas.effective_start_date,'DD')||'/'||to_char(pas.effective_start_date,'MM')||'/'||(to_char(sysdate,'RRRR')-1),'DD/MM/RRRR'))/12,2)> '1'
    then
    to_number(TO_CHAR(sysdate,'RRRR')) + 1 else to_number(TO_CHAR(sysdate,'RRRR'))end),'DD-MM-RRRR')-1)
    total_bal
    from
    per_all_people_f pas,
    per_all_assignments_f paf,
    hr_all_organization_units hou
    where
    paf.person_id(+)=pas.person_id
    AND PAS.CURRENT_EMPLOYEE_FLAG = 'Y'
    and paf.primary_flag ='Y'
    and hou.organization_id(+) =paf.organization_id
    --AND PAF.ORGANIZATION_ID=nvl(118,PAF.ORGANIZATION_ID)
    -- AND PAS.PERSON_ID = 195
    and sysdate BETWEEN pas.effective_start_date and nvl(pas.effective_end_date, to_date('31/12/4712', 'DD/MM/YYYY'))
    and sysdate BETWEEN paf.effective_start_date and nvl(paf.effective_end_date, to_date('31/12/4712', 'DD/MM/YYYY'))
    and sysdate between pas.effective_start_date and pas.effective_end_date
    and pas.person_type_id =1126
    and pas.email_address IS NOT NULL
    and pas.employee_number not in(
    select value from PAY_USER_COLUMN_INSTANCES_F where user_column_id =5061
    and sysdate between effective_start_date and effective_end_date );
    --and pas.person_id = 195;

    BEGIN
    FOR I IN C1 LOOP

    -- BEGIN
    /* v_intro :='Dear :'||' <p> Your leave balance as of now is given below: </p> <TABLE BORDER="1">';
    V_BODY:= '<TR> <TD> ' || 'Employee Number' || ' </TD> <TD> ' || I.employee_number || ' </TD> </TR>'||
    '<TR> <TD> ' || 'Employee Name ' || ' </TD> <TD> ' || I.full_name || ' </TD> </TR>'||
    -- '<TR> <TD> ' || 'Job' || ' </TD> <TD> ' || I.Job || ' </TD> </TR>'||
    -- '<TR> <TD> ' || 'Department,' || ' </TD> <TD> ' || I.department || ' </TD> </TR>'||
    -- '<TR> <TD> ' || 'Absence Days' || ' </TD> <TD> ' || I.ABSENCE_DAYS || ' </TD> </TR>'||
    -- '<TR> <TD> ' || 'Grade' || ' </TD> <TD> ' || I.grade || ' </TD> </TR>'||
    '<TR> <TD> ' || 'Current Balance' || ' </TD> <TD> ' || I.current_bal || ' </TD> </TR>';
    -- '<TR> <TD> ' || 'Total Balance' || ' </TD> <TD> ' || I.total_bal || ' </TD> </TR>';
    -- v_from := 'dummy@xyz.ae'; */
    v_intro := 'أسم الموظف' ||' : '|| I.full_name||' '||
    ' رقم الموظف'||' : ' || I.employee_number || ' '||
    'رصيد أيام الأجازات حتى تاريخه '|| ' : '|| I.current_bal ;
    BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = 'IP''';
    UTL_MAIL.send(sender => 'dummy@abc.ae',
    recipients => i.email_address,
    subject => V_SUB ,
    message => ' '||v_intro||chr(10)||v_body ||' '||chr(10)||chr(10)||v_footer ,
    mime_type => NULL);
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    ================================================== code for UTL_SMTP wich worked fine before but Arabic was giving issues.
    /*
    XX_HTML_EMAIL_LONG
    (P_TO => 'dummy@abc.ae' -- i.email_address
    ,P_FROM=> 'dummy@abc.ae' -- v_from
    ,P_SUBJECT=> V_SUB
    ,P_HTML=> v_intro||v_body ||'</TABLE>'||v_footer --v_message_s
    ,P_REPLY_TO => NULL
    ,P_CC=> NULL --i.cc_email
    ,P_BCC=> NULL -- i.bcc_email
    );
    -- DBMS_OUTPUT.PUT_LINE(I.EMAIL_ADDRESS);
    */
    -- END;

    END LOOP;
    errbuf := '';
    retcode := '0';

    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    errbuf := SQLERRM;
    retcode := '2';
    WHEN OTHERS
    THEN
    NULL;
    errbuf := SQLERRM;
    retcode := '2';
    --COMMIT;
    end XX_LEAVE_BALANCE_ALERT_PROC;

    /

    Thanks in Advance.

    Edited by: Naik Amal Khan on Mar 12, 2013 10:04 PM
  • 7. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    Christian Erlinger Guru
    Currently Being Moderated
    ugh...well I guess I asked for that ;)

    anyway; could you please post a slightly smaller sample, say, try to send a HTML Mail containing
    <b>hello</b>
    also please format your code using the tags to make it easier to read;

    this is how you do it:

    {noformat}
    your code goes here
    {noformat}

    cheers
  • 8. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    750019 Newbie
    Currently Being Moderated
    Dear Christian,

    If you have any suggestion or Sample code, Please share it. I have shared my code.

    Thanks.
  • 9. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    Christian Erlinger Guru
    Currently Being Moderated
    If you have any suggestion or Sample code, Please share it. I have shared my code.
    If you want sample code then look at the asktom article I gave you as it contains a working sample. I don't know what else I should give you. I will not copy/paste the code from asktom, and I don't see the point in posting my code when there is already one online free to look it up. And most certainly I won't go through 100+ lines of unformatted uncommented code where I have no clue what it's supposed to do.

    My suggestion was/is that you won't solve a problem by staring at 100+ lines of code, so you need to break it down into smaller pieces. For instance try to send a simple HTML mail, if this also doesn't work show us your samplecode, if it does see what is different to your code. This is how I would search for a solution however; you certainly can do as you please ;)

    cheers
  • 10. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    InoL Guru
    Currently Being Moderated
    Previously i did this with UTL_SMTP but arabic charaters was the issue there.
    UTL_MAIL is just a wrapper, so I guess it won't matter. Anyway, you'd better ask this in the PL/SQL forum.
  • 11. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    750019 Newbie
    Currently Being Moderated
    Yes you are right that you provided a link to aktom but that code was all about UTL_SMTP and my concern is with UTL_UTP. So, That is useless for me. I also i tried what you are saying..(try simple email). I have already did that but html is not working.

    Below is the shorter code:

    BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '127,162.1.1''';
    UTL_MAIL.send(sender => 'dummy@abc.ae',
    recipients => i.email_address,
    subject => V_SUB ,
    message => ' '||v_intro||chr(10)||v_body ||' '||chr(10)||chr(10)||v_footer ,
    mime_type => NULL);
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

    Thanks
  • 12. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    Christian Erlinger Guru
    Currently Being Moderated
    This
    mime_type => NULL);
    If you look closer at the asktom article you'll notice the mimetype is set to content-type: text/html;

    cheers
  • 13. Re: Unable to add HTML tags in Oracle UTL_MAIL package
    750019 Newbie
    Currently Being Moderated
    Dear Chritian,
    I have tried this option also but it was not displaying the arabic characters correctly. it was displaying ????? instead of Arabic characters. Anyhow i will try again with this option and will update you.

    Thanks for your time.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points