In SEND EMAIL code, how to remove Boundaries from the output [message #677787] |
Fri, 11 October 2019 12:36 |
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 #677792 is a reply to message #677791] |
Fri, 11 October 2019 13:51 |
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 |
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 #677800 is a reply to message #677799] |
Fri, 11 October 2019 14:54 |
|
Michel Cadot
Messages: 68658 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 #677840 is a reply to message #677805] |
Tue, 15 October 2019 07:32 |
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 |
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 |
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 #677858 is a reply to message #677853] |
Wed, 16 October 2019 10:05 |
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 |
|
Michel Cadot
Messages: 68658 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 #677957 is a reply to message #677787] |
Fri, 25 October 2019 07:32 |
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
|
|
|
|
|
|
|