4 Replies Latest reply: Dec 30, 2012 1:24 PM by Nick B RSS

    apex_mail

    981355
      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
          >
          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
            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
              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
                I'd use the UTL_MAIL package.
                Read through this article: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL