8 Replies Latest reply: Nov 16, 2012 4:04 AM by 846231 RSS

    Apex Email

    846231
      Hi all,

      I found this sample apex email setup dated Dec 2003. Is this still applicable for apex 4.0 or later?

      http://www.oracle.com/technetwork/developer-tools/apex/howto-workflow-095854.html

      http://www.dba-oracle.com/htmldb/t_html_db_sending_emails.htm

      Is there a counterpart of this for 4.0 or later?


      Thanks a lot,

      Kinz
        • 1. Re: Apex Email
          matthew_morris
          Is there a counterpart of this for 4.0 or later?
          The email package is now called APEX_MAIL. Here is the documentation for it:

          http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/aadm_email.htm#sthref2743
          • 2. Re: Apex Email
            846231
            Thanks Mat,

            How do I send an email which contains body that is a select list or report from a table ( eg. select Name from EMP where Status='Terminated') ? Or similary as an attachment?
            • 3. Re: Apex Email
              matthew_morris
              The mechanics for using APEX_MAIL (including adding attachments) are demonstrated in the link provided (i.e. where it said "See Also: "APEX_MAIL" in Oracle Application Express API Reference). Specifically, that URL is:

              http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_mail.htm#AEAPI341

              Collect whatever you want to send in the email into a variable and then pass that variable to to appropriate APEX_MAIL procedure. The above link has example code.
              • 4. Re: Apex Email
                846231
                Thanks Mat,

                I tried testing mail using this sample code at command line:
                SQL> DECLARE
                  2      l_body    CLOB;
                  3  BEGIN
                  4      l_body := 'Thank you for your interest in the APEX_MAIL
                  5  package.'||utl_tcp.crlf||utl_tcp.crlf;
                  6      l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;
                  7      l_body := l_body ||'  The APEX Dev Team'||utl_tcp.crlf;
                  8      apex_mail.send(
                  9          p_to       => 'imes.laura@abc.com',   -- change to your email
                 address
                 10          p_from     => 'ireve.xatov@abc.com', -- change to a real senders
                email address
                 11          p_body     => l_body,
                 12          p_subj     => 'Test EMAIL from APEX');
                 13  END;
                 14  /
                DECLARE
                *
                ERROR at line 1:
                ORA-20001: This procedure must be invoked from within an application session.
                ORA-06512: at "APEX_040000.WWV_FLOW_MAIL", line 290
                ORA-06512: at "APEX_040000.WWV_FLOW_MAIL", line 325
                ORA-06512: at "APEX_040000.WWV_FLOW_MAIL", line 367
                ORA-06512: at line 8
                
                
                SQL> begin
                  2    APEX_MAIL.PUSH_QUEUE;
                  3  end;
                  4  /
                
                PL/SQL procedure successfully completed.
                Why can not i test it on sqlplus? I want to send mail thru DBMS_scheduler and not in apex page.


                Thanks,
                • 5. Re: Apex Email
                  Jitendra
                  H KinsaKaUy,

                  See the link given below, helps you how to schedule a job.


                  http://www.apex-at-work.com/2009/06/dbmsscheduler-examples.html

                  http://awads.net/wp/2011/02/02/25-unique-ways-to-schedule-a-job-using-the-oracle-scheduler/

                  http://allthingsoracle.com/introduction-to-scheduled-jobs/


                  Regards,
                  Jitendra
                  • 6. Re: Apex Email
                    846231
                    Thanks Jit,

                    I read that already. My question is the above wont run on command line, so it will not also run when set on schedule. :(
                    • 7. Re: Apex Email
                      fac586
                      KinsaKaUy? wrote:
                      Thanks Jit,

                      I read that already. My question is the above wont run on command line, so it will not also run when set on schedule. :(
                      It helps if you read the documentation. It helps more if you read the documentation for the current version rather than searching out obscure references from 8 versions ago.

                      The need to set the security group ID when calling <tt>apex_mail</tt> outside an APEX session is noted on the first page of the mail API docs.
                      • 8. Re: Apex Email
                        846231
                        Thanks fac


                        Just curious....How did you know that is an old program? :)