Home » SQL & PL/SQL » SQL & PL/SQL » In SEND EMAIL code, how to remove Boundaries from the output (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 )
In SEND EMAIL code, how to remove Boundaries from the output [message #677787] Fri, 11 October 2019 12:36 Go to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi,

Hopefully the Subject wording is correct here.


Here is my issue. I created a procedure that sends out an email with some custom wording I provided. It works, however I get some garbage data before the p_text and the p_html, and after the p_html.
I would like to remove them if possible so the email can be clean.

I noticed that in our in-house email package, there are 2 procedures that have the garbage wording that I am trying to get rid of. I will paste pictures below...

Here is my sample procedure I shortened up for your help...


create or replace PROCEDURE PAY_SEND_TREASURY_EMAIL_PROC as 
 begin
  for cur_r in (select empl_email, 
                       n_txt_1 DISPLAY_SUBJECT, 
                       n_txt_2 DISPLAY_MSG
        FROM      TEST_SEND_EMAIL_To_joe 
               ) loop
           
    anc_pkg.p_html_email(
                         p_to       => cur_r.empl_email 
                        ,p_from     => 'do_not_reply@nycha.nyc.gov'
                        ,p_subject  => cur_r.DISPLAY_SUBJECT
                        ,p_text     => 'IT TESTING !!!  Please complete forms ASAP, Thank you.'
                        ,p_html     =>  cur_r.DISPLAY_MSG
                  );
  end loop;
end;
/




EXECUTE PAY_SEND_TREASURY_EMAIL_PROC

This is my email message below that was generated with the garbage data(.jpg also uploaded):

Reply-To: do_not_reply@nycha.nyc.gov
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii

IT TESTING !!!  Please complete forms ASAP, Thank you.

--a1b2c3d4e3f2g1
content-type: text/html;


Year--> 2019    PP--> 09    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )

Year--> 2019    PP--> 21    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )

--a1b2c3d4e3f2g1--



How do I remove these 3 boundary sections from my email message above? The user does not need to see that.

Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii

--a1b2c3d4e3f2g1
content-type: text/html;


--a1b2c3d4e3f2g1--



This is the boundary code in the in-house email package



l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';

l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
                         chr(34) || l_boundary ||  chr(34) || chr(13) ||
                         chr(10);

----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13)||chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

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

    -- Write the HTML boundary
    l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
                    chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' ||
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);



Can someone please guide me on what to do next?



Thanks
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677788 is a reply to message #677787] Fri, 11 October 2019 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If your email tool displays that this means that your email is not well-formed and so your package is not correct.
Ask the one who wrote it.

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677789 is a reply to message #677788] Fri, 11 October 2019 12:43 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Michel,

Ok, that will be difficult to do. The programmer did not leave a name nor remarks in the code. Ok, I will dig further and ask around.

Thanks for letting me know.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677790 is a reply to message #677789] Fri, 11 October 2019 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can post the complete raw text that is generated and we can try to see what is wrong in it.

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677791 is a reply to message #677790] Fri, 11 October 2019 13:22 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Ok....will do. Thank you.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677792 is a reply to message #677791] Fri, 11 October 2019 13:51 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
The data I put into the temp test table with 1 record for you was taken from a view which has data that contains chr(10)'s for line breaks.

This is the data copied from the table using SQL Developer . I hope this helps you. Thanks

empl_email = 'joe.rappa@nycha.nyc.gov'

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

Display_Subject = "Please work with Treasury to be in compliance for following Pay Period(s)

"

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

Display_Message = "
Year--> 2019    PP--> 09    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )

Year--> 2019    PP--> 21    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )
"

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





This is what gets generated (snapshot attached also)
Reply-To: do_not_reply@nycha.nyc.gov
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii

IT TESTING !!!  Please complete forms ASAP, Thank you.

--a1b2c3d4e3f2g1
content-type: text/html;


Year--> 2019    PP--> 09    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )

Year--> 2019    PP--> 21    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )

--a1b2c3d4e3f2g1--

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677793 is a reply to message #677792] Fri, 11 October 2019 14:10 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
I am starting to think that the CHR(10) has something to do with the problem.

In the SQL that creates the view, there is a CHR(10) appended to Year and directly at the end. I am not sure if this is fixable unless CHR(10) is removed.

,listagg( CHR(10) || 'Year--> ' ||Pay_year 
         ||'    PP--> '||LPAD(to_char(pay_period),2,'0')
         ||'    Location--> '||Payrl_dist_code  
         ||'    ( '||office_name||' )'
         ||CHR(10)
         )
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677794 is a reply to message #677792] Fri, 11 October 2019 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, what does your email tool receive (still raw text, if you use outlook right-click on the message -> properties -> details -> source)?

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677795 is a reply to message #677793] Fri, 11 October 2019 14:32 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Note: I asked someone here who is familiar with the send email package to take a look at my issue. I'll keep u posted.... if this is too difficult to break down further. Thanks, Michel.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677796 is a reply to message #677795] Fri, 11 October 2019 14:34 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
P.S. Just got your msg... I will see if I can locate source from Outlook.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677797 is a reply to message #677796] Fri, 11 October 2019 14:42 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Unfortunately, the source is grayed out for the email message. Strange

However, I can view the source on Other normal outlook email messages.

Let me work with another co-worker on this for a little while....and I will post an update back here next week. You have a good weekend.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677798 is a reply to message #677796] Fri, 11 October 2019 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah! I see the problem.
There should be (at least) an empty line between:
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
and
--a1b2c3d4e3f2g1

That's all!
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677799 is a reply to message #677798] Fri, 11 October 2019 14:51 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Michel, Are you saying that the code in the package needs an empty line defined?
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677800 is a reply to message #677799] Fri, 11 October 2019 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I say instead of:
Reply-To: do_not_reply@nycha.nyc.gov
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii
...
The code should generate:
Reply-To: do_not_reply@nycha.nyc.gov
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"

--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii
...

Note that your html part is not in html and so the lines will be concatenated and not on several lines.

[Updated on: Fri, 11 October 2019 14:55]

Report message to a moderator

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677802 is a reply to message #677799] Fri, 11 October 2019 14:55 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
oh...I think you mean within my data. A blank line before my p_text. Correct?
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677803 is a reply to message #677802] Fri, 11 October 2019 14:58 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Ok, thanks. I will give it a try... Thank you for your time.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677804 is a reply to message #677802] Fri, 11 October 2019 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rappaj wrote on Fri, 11 October 2019 21:55
oh...I think you mean within my data. A blank line before my p_text. Correct?
This part of the code:
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
                         chr(34) || l_boundary ||  chr(34) || chr(13) ||
                         chr(10);
should be:
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
                         chr(34) || l_boundary ||  chr(34) || chr(13) ||
                         chr(10) || chr(13) || chr(10);
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677805 is a reply to message #677804] Fri, 11 October 2019 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or better, this:
 l_temp   := '--' || l_boundary || chr(13)||chr(10);
should be:
 l_temp   := chr(13)||chr(10) || '--' || l_boundary || chr(13)||chr(10);
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677840 is a reply to message #677805] Tue, 15 October 2019 07:32 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi Michel,

Just got back from a nice relaxing 3 day weekend. Hope you did too.

Thanks for that bit of code change. I think what it will do it that it will do a Carriage Return and Start a new line before printing the text.

I will ask one of the tech guys here to make this change to the package and will let you know.

Thanks for your help and looking over the code. Always appreciated.

[Updated on: Tue, 15 October 2019 07:43]

Report message to a moderator

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677844 is a reply to message #677840] Tue, 15 October 2019 10:24 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi Michel,

Nobody around to help mod the package yet.

Can I place this change in my Procedure as a workaround to see the Output instead of making the change to the package?
I am sure others use the package too to send email...


--> l_temp := chr(13)||chr(10) || '--' || l_boundary || chr(13)||chr(10);


create or replace PROCEDURE PAY_SEND_TREASURY_EMAIL_PROC as 
 begin
  for cur_r in (select empl_email, 
                       n_txt_1 DISPLAY_SUBJECT, 
                       n_txt_2 DISPLAY_MSG
        FROM      TEST_SEND_EMAIL_To_joe 
               ) loop
           
    anc_pkg.p_html_email(
                         p_to       => cur_r.empl_email 
                        ,p_from     => 'do_not_reply@nycha.nyc.gov'
                        ,p_subject  => cur_r.DISPLAY_SUBJECT
                        ,p_text     => 'IT TESTING !!!  Please complete forms ASAP, Thank you.'
                        ,p_html     =>  cur_r.DISPLAY_MSG
                  );
  end loop;
end;

/

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677850 is a reply to message #677844] Tue, 15 October 2019 11:50 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi,

I was able to modify the pkg in test and compile. I saw the extra line added to the output after Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1",
but the extra wording/garbage is still there. Maybe I am better off with just leaving it as is?

Thanks



Reply-To: do_not_reply@nycha.nyc.gov
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"

--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii

IT TESTING !!!  Please complete forms ASAP, Thank you.

--a1b2c3d4e3f2g1
content-type: text/html;

Year--> 2019    PP--> 09    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )
Year--> 2019    PP--> 21    Location--> 0834    ( NextGen Ops 1 Manhattan Planning Unit )

--a1b2c3d4e3f2g1--






This is the code change in the package
 ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    --l_temp   := '--' || l_boundary || chr(13)||chr(10);                 -- Joe Rappa commented 10/15/19
    l_temp := chr(13)||chr(10) || '--' || l_boundary || chr(13)||chr(10); -- Joe Rappa replaced above with this on  10/15/19
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

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

[Updated on: Tue, 15 October 2019 11:55]

Report message to a moderator

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677852 is a reply to message #677850] Tue, 15 October 2019 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I put your data in a ".eml" file, double-clicked on it to open it in Outlook and got the correct result:

/forum/fa/14200/0/
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677853 is a reply to message #677852] Tue, 15 October 2019 12:20 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Yes, that does look good to me. Ok, I will pick everyone brain around here if they have encountered this issue as well.

Thanks for the help. I will update with my findings. Since I used a test DB for this, Maybe it'll be different in Production. I'll have the code changed there too.

Joe
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677858 is a reply to message #677853] Wed, 16 October 2019 10:05 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi Michel,

Trying to understand what these three l_temp variables hardcoded text message and Default are used for. I noticed that while searching the web, that these are all standard hardcoded texts for sending mail.
Can you please tell me if these are mandatory values, and what they are used for?

I am thinking that if I just simply space out the text wording, they will be gone from my email output. But I am sure they are needed there for something.

Thank you

l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1'; 


l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||   
                         chr(34) || l_boundary ||  chr(34) || chr(13) ||
                         chr(10);


--write the text boundary
l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' || 
                  chr(13) || chr(10) || chr(13) || chr(10);


--write the html boundary
l_temp   := l_temp || 'content-type: text/html;' || 
                   

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677859 is a reply to message #677858] Wed, 16 October 2019 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This has nothing to do with Oracle but anyway.

"Content-Type: multipart/alternative; boundary" means that your message is made of several parts, text, html, images, video, sound... and gives the boundary text separating each part. The "alternative" keyword means that only one part will be displayed depending on your email tool configuration.

"'content-type: text/plain; charset=us-ascii' " means that you are starting a part that is pure text.

"'content-type: text/html;' " means you are starting a part that is in html.

So, in your case, your message is made of 2 parts, one in text, the other one in html.
As your "multupart" is of "alternative" kind, if the email tool supports html and its configuration allows it, the HTML part will be displayed (this is my case in my previous post), otherwise the TEXT part will be displayed.

If you just need simple text then keep the "content-type: text/plain" line and remove the other lines, and you don't need boundaries (and must not add them otherwise they will be displayed as message text).

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677860 is a reply to message #677859] Wed, 16 October 2019 10:52 Go to previous messageGo to next message
ThomasG
Messages: 3210
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, shouldn't there be header like

MIME-Version: 1.0

somewhere?

Maybe try some of the standard examples in the Wikipedia Article, if they are rendered correctly.

https://en.wikipedia.org/wiki/MIME
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677861 is a reply to message #677859] Wed, 16 October 2019 10:54 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Thanks for the lesson, Michel. I have some options now to consider.......
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677862 is a reply to message #677861] Wed, 16 October 2019 10:57 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Yes, Thomas. There is Mime code as well...

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);

Thank you too.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677957 is a reply to message #677787] Fri, 25 October 2019 07:32 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi All,

Just wanted to give you an update. The 'boundaries' issue problem was finally fixed yesterday.

Within the SQL that creates my view that I use, I had appended a CHR(10) to put breaks between the data so it can display on it's own line. A coworker suggested I change it to '<BR>' instead and it solved the problem. All those boundaries disappeared from the email. It probably would have helped if I had sent that part to you as well...

Snapshot of fixed email is attached. The wording will be changed....


As always, thanks for all your help with creating this for me.

[Updated on: Fri, 25 October 2019 07:33]

Report message to a moderator

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677958 is a reply to message #677957] Fri, 25 October 2019 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need "<br>" if you say the data are in TEXT/PLAIN and not in HTML.
Thanks for the feedback.

[Updated on: Fri, 25 October 2019 07:46]

Report message to a moderator

Re: In SEND EMAIL code, how to remove Boundaries from the output [message #677959 is a reply to message #677958] Fri, 25 October 2019 07:49 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
This was a nice learning experience for me. I will use it for other SEND EMAIL projects as well.
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #678447 is a reply to message #677959] Wed, 04 December 2019 14:00 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Just letting you all know that Everything is working great with this in PRODUCTION.

Thank you
Re: In SEND EMAIL code, how to remove Boundaries from the output [message #678448 is a reply to message #678447] Wed, 04 December 2019 14:43 Go to previous message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Previous Topic: Run Oracle SQL query multiple times with change in filter condition
Next Topic: delete from table where one of the column is nested table is running long.
Goto Forum:
  


Current Time: Tue Dec 10 00:39:35 CST 2019