This discussion is archived
4 Replies Latest reply: Dec 30, 2012 11:24 AM by Nick B RSS

apex_mail

981355 Newbie
Currently Being Moderated
Hi ,
I have to send a birthday wishes to all customers of my clint.So I create one dbms_job and one package.But it is not working.I pasted everything in below.Pleas guide me.


create or replace procedure test as

v_subject VARCHAR2(100);
v_sender VARCHAR2(100);
v_recipient VARCHAR2(100);
v_body VARCHAR2(4000);

BEGIN

v_subject := 'wishes';
v_sender := 'ravikumar.sukhavasi@gmail.com';
v_recipient := 'ravi@kramah.com';
v_body := 'many more happy returns of the day' || chr(10) || 'from TEST';


APEX_MAIL.SEND(
P_TO => v_recipient,
P_FROM => v_sender,
P_SUBJ => v_subject,
P_BODY => v_body);
END;


begin
dbms_scheduler.create_job(
job_name => 'WISHES_JOB_SCHEDULE',
job_type => 'STORED_PROCEDURE',
job_action => 'TEST',
start_date => '22-DEC-12 11.31.34.143000 AM +05:30',
repeat_interval => 'freq=DAILY;byhour=0;interval=1',
end_date => '21-DEC-16 11.31.34.143000 AM +05:30',
enabled => TRUE,
comments => 'test job Shedule.');
end;

I got below errors in my dabase alert log file.

ORA-12012: error on auto execute of job 98802
ORA-20001: This procedure must be invoked from within an application session.
ORA-06512: at "APEX_040100.WWV_FLOW_MAIL", line 230
ORA-06512: at "APEX_040100.WWV_FLOW_MAIL", line 267
ORA-06512: at "APEX_040100.WWV_FLOW_MAIL", line 307
ORA-06512: at "TRAVEL_GENERIC.TEST", line 12


thanks in advance..
  • 1. Re: apex_mail
    Earl Lewis Journeyer
    Currently Being Moderated
    >
    I got below errors in my dabase alert log file.

    ORA-12012: error on auto execute of job 98802
    ORA-20001: This procedure must be invoked from within an application session.
    ORA-06512: at "APEX_040100.WWV_FLOW_MAIL", line 230
    ORA-06512: at "APEX_040100.WWV_FLOW_MAIL", line 267
    ORA-06512: at "APEX_040100.WWV_FLOW_MAIL", line 307
    ORA-06512: at "TRAVEL_GENERIC.TEST", line 12
    I believe your answer is in your error message. It would appear that Apex_mail isn't available to the job scheduler. While I've never tried to do that it seems rather straightforward. There are lots of messaging packages out there that use UTL_SMTP for sending email. I would try to find/install one of those and see how that goes.

    Earl
  • 2. Re: apex_mail
    Nick B Journeyer
    Currently Being Moderated
    Another option is to manually create an APEX session before you send your e-mails.
    More information on creating an APEX session in PL/SQL can be found here:
    http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html
  • 3. Re: apex_mail
    981355 Newbie
    Currently Being Moderated
    Hi Nick B ,

    Manually I create an APEX session and i observed the session in apex_workspace_sessions view.But again i got a error like

    ORA-12012: error on auto execute of job 98690
    ORA-06512: at "SYS.OWA_UTIL", line 356
    ORA-06512: at "SYS.HTP", line 1368
    ORA-06512: at "SYS.HTP", line 1443
    ORA-06512: at "SYS.HTP", line 1735
    ORA-06512: at "IES.BIRTHDAY_WISHES", line 18
    ORA-20001: This procedure must be invoked from within an application session.

    Actually am a new guy for apex.is there any other way to send a mails ?

    Thanks in advance..
  • 4. Re: apex_mail
    Nick B Journeyer
    Currently Being Moderated
    I'd use the UTL_MAIL package.
    Read through this article: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

Legend

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