It's been a hard time not able to make my code to work with UTL_SMTP package.
Can some one guide me to change below code and make it work with UTL_SMTP method
I created a mailing application which sends mail to all user on their email id's with an attachment.
Took reference and I built it same exactly like this
https://blogs.oracle.com/apex/post/creating-email-campaign-app-with-oracle-apex
My Code :
declare
l_context apex_exec.t_context;
l_emailsidx pls_integer;
l_namesids pls_integer;
l_region_id number;
begin
-- Get the region id for the CUSTOMERS IR region
select region_id
into l_region_id
from apex_application_page_regions
where application_id = :APP_ID
and page_id = 1
and static_id = 'CUSTOMERS';
l_context := apex_region.open_query_context (
p_page_id => 1,
p_region_id => l_region_id );
-- Get the column positions for EMAIL and NAME columns
l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
-- Loop throught the query of the context
while apex_exec.next_row( l_context ) loop
l_id : = apex_mail.send (
p_to => apex_exec.get_varchar2( l_context, l_emailsidx ),
p_template_static_id => 'NEW_YEAR_2020_PROMOTION_DUBAI_BRANCH',
p_placeholders => '{' ||
' "CUSTOMER":' || apex_json.stringify( apex_exec.get_varchar2( l_context, l_namesids )) ||
' ,"START_DATE":' || apex_json.stringify( :P2_START_DATE ) ||
' ,"END_DATE":' || apex_json.stringify( :P2_END_DATE ) ||
' ,"LOCATION":' || apex_json.stringify( :P2_LOCATION ) ||
' ,"NOTES":' || apex_json.stringify( :P2_NOTES ) ||
' ,"ITEMS":' || apex_json.stringify( :P2_ITEMS ) ||
' ,"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 1 )) ||
'}' );
end loop;
for rec in (
select filename, blob_content, mime_type
from apex_application_files
where flow_id = :APP_ID
and filename = 'apex_logo.png'
)
loop
apex_mail.add_attachment(
p_mail_id => l_id,
p_attachment => rec.blob_content,
p_filename => rec.filename,
p_mime_type => rec.mime_type);
end loop;
apex_mail_queue(
p_smtp_hostname => 'xyz.xmp.it',
p_smtp_portno => 45
)
apex_exec.close( l_context );
exception
when others then
apex_exec.close( l_context );
raise;
end;
Need to convert my above code below like this
UTL_MAIL.send (
sender => '[email protected]',
recipents => '[email protected]',
cc => '[email protected]',
subject => 'Testing',
mime_type => 'text/html',
message => 'ABC TESTING'
);
My below code does not support at my end. It throws network access denied as (ACL). Only UTL_SMTP is supported and mail is sent. But below code need to transform as above UTL_SMTP.send method
l_id : = apex_mail.send (
p_to => apex_exec.get_varchar2( l_context, l_emailsidx ),
p_template_static_id => 'NEW_YEAR_2020_PROMOTION_DUBAI_BRANCH',
p_placeholders => '{' ||
' "CUSTOMER":' || apex_json.stringify( apex_exec.get_varchar2( l_context, l_namesids )) ||
' ,"START_DATE":' || apex_json.stringify( :P2_START_DATE ) ||
' ,"END_DATE":' || apex_json.stringify( :P2_END_DATE ) ||
' ,"LOCATION":' || apex_json.stringify( :P2_LOCATION ) ||
' ,"NOTES":' || apex_json.stringify( :P2_NOTES ) ||
' ,"ITEMS":' || apex_json.stringify( :P2_ITEMS ) ||
' ,"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 1 )) ||
'}' );
end loop;
for rec in (
select filename, blob_content, mime_type
from apex_application_files
where flow_id = :APP_ID
and filename = 'apex_logo.png'
)
loop
apex_mail.add_attachment(
p_mail_id => l_id,
p_attachment => rec.blob_content,
p_filename => rec.filename,
p_mime_type => rec.mime_type);
end loop;
apex_mail_queue(
p_smtp_hostname => 'xyz.xmp.it',
p_smtp_portno => 45
)
Following is the YouTube followed to create the application
https://www.youtube.com/watch?v=aZQ04yjS7rQ
Copyright Notice：Content Author:「kiric8494」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/72646645/how-to-re-design-code-with-utl-smtp-oracle-apex-send-mail