Thanks for the question.

Asked: August 04, 2009 - 5:36 pm UTC

Last updated: May 30, 2018 - 6:08 am UTC

Version: Apex 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I can successfully send email from Apex application using apex_mail.send.

When I try to use the same from sqlplus it gives the following Error

BEGIN
apex_mail.send (p_to => 'test@test.com',
p_from => 'test@test.com',
p_body => 'test',
p_subj => 'test Subject',
p_replyto => 'Do Not Reply'
);
END;

ORA-20001: This procedure must be invoked from within an application session.
ORA-06512: at "APEX_030200.WWV_FLOW_MAIL", line 185
ORA-06512: at "APEX_030200.WWV_FLOW_MAIL", line 219
ORA-06512: at line 2

Q1) How to make this apex_mail work from sqlplus. Since this is a ready made mail package which can also send attachments. I would be nice to use it in all cases rather than re-inventing the wheel.

Q2) What are the alternatives

and Tom said...

I'll refer you to the most excellent discussion forum for APEX - just goto otn.oracle.com -> discussion forums. A very active forum for APEX related things exists there.

But in answer to this - support Note 815366.1

....
begin

wwv_flow_api.set_security_group_id;

apex_mail.send(
p_to => 'to_user@their_company.com', -- change email address
p_from => 'your_email@company.com', -- change to your email address
p_body => 'Main email text goes here',
p_subj => 'APEX_MAIL Package - Plain Text message'
);

end;

It is mandatory to set the security group in order to run the Apex API commands outside of the Apex environment.

When the Apex security group is not set, the event will fail with the following error:
ORA-20001: This procedure must be invoked from within an application session.
.........

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you very much

A reader, August 05, 2009 - 4:18 pm UTC

Tom Thanks a lot.

Not able to receive email though set_security_group_id

Amarnath, May 28, 2018 - 7:27 am UTC

Ran below code from custom schema which is created for APEX.
Program ran successfully with output messages displayed(BEGIN,END) , but didn't received mail.
Where as ran same script(with different subject) from APEX application and received mail.
begin
dbms_output.put_line('BEGIN');
wwv_flow_api.set_security_group_id(1817032601053388);
APEX_MAIL.SEND(
p_to => 'xxxx.xxxxxx@gmail.com',
p_from => 'dev.oracle.ebs.noreply@ncp.co.uk',
p_subj => 'Subject (from Pl/Sql)',
p_body => 'Please review the Requests.',
p_body_html => 'Please review the Requests. http://XXXXXXXXXX:XXXX/ords/f?p=105:4:::::: ');
dbms_output.put_line('END');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error:'||substr(sqlerrm,1,300));
end;

Not able to receive email though set_security_group_id

Amarnath, May 28, 2018 - 10:48 am UTC

used set_Security_group_id before APEX_MAIL.SEND like
wwv_flow_api.set_security_group_id(worspace_id);

I received mail 2 hours later of code run(anonymous block).

But whereas when I run the same anonymous block by logging into APEX application, I received the mail immediately.

May 29, 2018 - 1:55 am UTC

Add the following at the end of your call

apex_mail.push_queue();

Scheduled Job created based on package with APEX_MAIL.send failing to send mail

Amarnath, May 29, 2018 - 10:27 am UTC

Hi,

Created a scheduled job based on pl/sql package for every 10 minutes which sends mail using APEX_MAIL.send

Added wwv_flow_api.set_security_group_id(vWorkspaceId)
before calling APEX_MAIL.send and
Added apex_mail.push_queue() after APEX_MAIL.send

The Scheduled job is failing with exception:
ORA-20001: This procedure must be invoked from within an application session.

But when I run the job as Anonymous block from backend, I received mail successfully.
BEGIN
dbms_output.put_line('Start');
dbms_scheduler.run_job('XXX_JOB_REC_SEND_EMAIL',TRUE);
dbms_output.put_line('End');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception:'||substr(sqlerrm,1,300));
END;

Regards
Amar

May 30, 2018 - 6:08 am UTC

You should already have a scheduled job (that was created by Apex):

ORACLE_APEX_MAIL_QUEUE

so I'm not sure why are adding more.

You need to give us a top-to-bottom example, not snippets of code here and there

Scheduled Job created based on package with APEX_MAIL.send failing to send mail

Amarnath, May 29, 2018 - 10:47 am UTC

The scheduled job and package were created under custom schema That custom schema is created for APEX application.

I run the job using anonymous block in custom schema.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thank you very much

Not able to receive email though set_security_group_id

Not able to receive email though set_security_group_id

Scheduled Job created based on package with APEX_MAIL.send failing to send mail

Scheduled Job created based on package with APEX_MAIL.send failing to send mail

More to Explore

PL/SQL demos

PL/SQL docs

11g Apex Email not working from Sqlplus

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thank you very much

Not able to receive email though set_security_group_id

Not able to receive email though set_security_group_id

Scheduled Job created based on package with APEX_MAIL.send failing to send mail

Scheduled Job created based on package with APEX_MAIL.send failing to send mail

More to Explore

PL/SQL demos

PL/SQL docs