1 2 Previous Next 22 Replies Latest reply: Jan 23, 2013 6:28 AM by Bhavesh J Shah RSS

    sending an email

    erics44
      Hi
      I am new to apex.

      I have joined a company a taken control of an apex application. There is a send email option on a few of the pages.

      we have recently changed exchange servers and ever since the email sending hasnt worked.

      I have changed the server name in the sql script that supports the application.

      Any help would be greatly appreciated.

      Thanks in advance
        • 1. Re: sending an email
          Udo
          Hi,

          does your script use the APEX_MAIL-API or other means to send email?
          If it uses APEX_MAIL you could monitor your mail queue to see what happens to the mails. To do that, login to the APEX internal administration (workspace internal) and go to Home>Manage Service>Mail Queue
          It may be helpful to see the email-part of your script anyway to determine possible other reasons for the failure.

          Do you have any log on your Exchange server where you could monitor if the database server you run APEX on actually reaches it? Perhaps you need some additional firewall rule to allow the host of the database server to connect to your new Exchange environment.

          -Udo
          • 2. Re: sending an email
            erics44
            thanks for the reply

            it does seem to use apex_mail

            the leaver notification process code is pasted below

            how do i get into the internal administration?

            PROCEDURE send_leaver_notification IS
            l_body VARCHAR2(32000);
            l_to VARCHAR2(32000);
            temp VARCHAR2(32000);

            CURSOR leavers IS SELECT employee_id,
                                          first_name || ' ' || last_name employee_name,
                                          end_date,
                                          last_pay_date,
                                          leaving_status
                                FROM employees
                                WHERE end_date <= SYSDATE
                                AND leaver_notification_sent IS NULL ;

            l_attachment_cursor sys_refcursor;

            BEGIN

            FOR r IN leavers
            LOOP


                 l_body:='This email has been generated automatically to notify you that the named person below is leaving Attenda Limited.

            This IS confidential information, DO NOT discuss OR divulge this information UNTIL formal notification has been given BY the Line Manager.

                 1) You must complete the tasks AS listed IN the attached spreadsheet
                 2) Print OFF the complete worksheet (specific TO your area)
                 3) SIGN it
                 4) RETURN TO HR

            This IS a ISO27001 AUDIT requirement AND must be completed immediately you receive this notification

            FULL DETAILS

            Employee Name: ' || r.employee_name || chr(10) ||
            'Last Employment Date (date leaving the building): ' || r.end_date || CHR(10) ||
            'Last Pay Date (end of notice period: ' || r.last_pay_date ||CHR(10) ||
            'Leaving Status: ' || r.leaving_status || CHR(10) || CHR(10) ||
            'Many Thanks
            Attenda Human Resources';

                 SELECT value
                 INTO l_to
                 FROM lookups
                 WHERE TYPE='New Starter/Leaver Notification';

                 SELECT value
                 INTO temp
                 FROM lookups
                 WHERE TYPE='Finance Notification';

                 l_to := l_to || ',' || temp;

                 OPEN l_attachment_cursor FOR
                 SELECT doc,r.employee_name || '.xls',mime_type FROM documents WHERE doc_type='Leaver Spread Sheet';

                 Att_Hr_Pkg.send_email(p_from=> 'attendahumanresources@attenda.net',
                 p_to => l_to,
                 p_subj => 'Leaver Notification ' || r.employee_name ,
                 p_body_text=>l_body,
                 p_attachments => l_attachment_cursor);



                 UPDATE EMPLOYEES
                 SET LEAVER_NOTIFICATION_SENT= 'Y',
                 employment_type ='X'
                 WHERE EMPLOYEE_ID =r.employee_id;
            END LOOP;

            APEX_MAIL.PUSH_QUEUE;

            END send_leaver_notification;
            • 3. Re: sending an email
              Udo
              Hi Eric,

              you script uses APEX_MAIL, but the actual mail send process seems to be stored in the package Att_Hr_Pkg.
              The internal administration can be reached by logging in as "ADMIN" (with APEX Admin password) to the workspace "INTERNAL" or by calling <your_base_url>/apex/apex_admin and logging in as ADMIN there.

              In the internal administration, you also could check if the right SMTP server is configured for APEX. This is done in Home>Manage Service>Instance Settings in the Email section. If you haven't been there before, this might already be the solution for your problem.

              -Udo
              • 4. Re: sending an email
                erics44
                hi thanks again for the reply

                this system was set up when the work was outsiourced to india

                we dont have an admin password to go into the apex_admin area

                is there a way of setting one up do you know?
                • 5. Re: sending an email
                  Udo
                  Hi,

                  you can download the apex installation package that corresponds to the APEX-version you currently use, unpack it on any machine with a database client (e.g. SQL*Plus) on it and connect to the database instance of your APEX as sys and run apxchpwd.sql , for example in sqlplus like that:
                  @apxchpwd
                  You'll be prompted to enter your new APEX Admin password.

                  -Udo
                  • 6. Re: sending an email
                    erics44
                    hi mate

                    thanks again for the reply

                    http://www.oracle.com/technetwork/developer-tools/apex/3-098191.html

                    is this the download you mean?
                    • 7. Re: sending an email
                      Udo
                      Exactly, if you're currently using APEX 3.1.x

                      -Udo
                      • 8. Re: sending an email
                        erics44
                        hi mate thanks again

                        i cant see how to open it

                        im probably sounding stupid now, but how do i get on it once it is unpacked?
                        • 9. Re: sending an email
                          Udo
                          Hi Eric,

                          once you downloaded the file, it's a simple zip archive (e.g., http://download.oracle.com/otn/java/appexpress/apex_3.1.2.zip - Downloadlink appears after you accepted the OTN License Agreement). It contains a directory "apex" and inside that directory you'll find apxchpwd.sql
                          The script does not have any further dependencies on other scripts, so you could copy that file to any other workstation with access to a SQL client programm.

                          If you don't get it working, I could also post the content.

                          -Udo
                          • 10. Re: sending an email
                            erics44
                            yep, im with you now

                            i have the content of the file

                            i have pasted it into the sql plus worksheet in the oracle clent and it kind of just runs all commands without waiting for me to enter the password

                            it just immediately returns all below


                            Enter a password for the ADMIN user []
                            Session altered.

                            ...changing password for ADMIN
                            Enter value for passwd: Enter value for passwd:
                            PL/SQL procedure successfully completed.
                            • 11. Re: sending an email
                              Udo
                              It seems the input-directive is not supported by the sqlplus worksheet when executing the script as pasted version. Do you have access to the command line version of sqlplus, where you could run the script as described above? (connect as sys and execute the script with @apxchpwd.sql). Possibly, the @apxchpwd.sql also works in the worksheet. You could give the absolute path to the script, if it is not found by the worksheet, like that
                              @D:\Temp\apex\apxchpwd.sql
                              -Udo
                              • 12. Re: sending an email
                                erics44
                                it was looking so good mate

                                entered the new password and pressed enter and then it stuck on the line

                                ...changing the password for admin user

                                by the way, once it works so i log into apex_admin as user ADMIN?
                                • 13. Re: sending an email
                                  Udo
                                  entered the new password and pressed enter and then it stuck on the line

                                  ...changing the password for admin user
                                  That's unusual. Perhaps you just press return/enter once more? If that doesn't help, you may have a lock situation caused by the previous session. Try to terminate it so your transaction may continue.
                                  by the way, once it works so i log into apex_admin as user ADMIN?
                                  That's right.
                                  • 14. Re: sending an email
                                    erics44
                                    nope cant get it to work

                                    the only way to get it to stop processing is to press control and break and then it just completely stops

                                    thanks for all your help anyway, i have learned a lot
                                    1 2 Previous Next