3 Replies Latest reply: Jul 5, 2014 5:47 AM by fac586 RSS

    Is it possible to use values extracted by a for loop in a HTML body to send a email using the Apex_send_mail API

    rhisingh

      Hi Guys I am trying to create a procedure that sends emails generated buy a loop which compares the values of two similar columns in two tables and extracts the first name of the user that its supposed to send the mail to from the Apex_workspace_apex_users .





        The first table is the E_PERFO_TBL_IMP which is described as                 


      user_id          assessed                 vendor

      -------------------------------------------------------------

      User@1                  1                     IBM

      User@1                  1                     Oracle

      User@1                  1                     sap

      user@2                  1                     Oracle

      user@2                  1                     hp








      The secound table is the E_PERF_2_COMP which is described as

       

      user_id                       ASMENTS_2_COMP                              vendors

      --------------------------------------------------------------------------------------------------------------

      User@1                    3                    IBM,ORACLE,SAP

      user@2                    4                    Oracle,Sap,Hp,IBM



      So a user would log into a application and be shown the vendors that he must do assessments on . If you look at table E_PERF_2_COMP it says that user@1 has 3 assessments to do he would then use a form in the application to assess these 3 vendors and every vendor that he assess is then inserted into the E_PERFO_TBL_IMP on this table i have a trigger set up so that when the user his submit on the page on the application on insert it inserts 1 into the assessed column . In the loop below the ASMENTS_2_COMP column in the E_PERF_2_COMP table is compared to the assessed  column in the  E_PERFO_TBL_IMP . If the loop picks up a number greater then 0 after comparing ASMENTS_2_COMP and assessed  it should send that user an email because he has remaining assessments to do. I also had to do a join to the Apex_workspace_apex_users table because the users first_name was a requirement  in this mail that had to be sent . The producer Im trying to create to do this is below . The error im getting on compile is .What is it that i seem to be doing wrong here ?  .Thanks guys  


      Compilation failed  (11:05:56)
      PL/SQL: ORA-00904: "Q1"."FIRSTNAME": invalid identifierCompilation failed,line 13 (11:05:56)
      PL/SQL: SQL Statement ignoredCompilation failed,line 23 (11:05:56)
      PLS-00364: loop index variable 'R1' use is invalidCompilation failed,line 23 (11:05:56)
      PL/SQL: Statement ignored

       







      E_PERFO_TBL_IMP




      create or replace procedure "NEW_RMD_TEST1"

      is

          v_message_rmd_html varchar2(1000);

          v_return_date varchar2(24);

      first_name varchar2(20);

         

      begin

          v_return_date := to_char((sysdate + 14 ),'DD MON YYYY');

         

         

         

      for r1 in (

        with q1 as (SELECT detail.user_id  , names.first_name , Count(detail.assessed) AS ASSESSED

           FROM E_PERFO_TBL_IMP detail, Apex_workspace_apex_users names, E_PERF_2_COMP master                

             WHERE  master.e_mail = detail.user_id

                and lower (master.e_mail) = lower (names.email)

           GROUP  BY  detail.user_id, names.FIRST_NAME)

       

      select m.e_mail, q1.firstname

          from q1, E_PERF_2_COMP m

        where lower(q1.user_Id(+)) = lower(m.e_mail)

          and ASMENTS_2_COMP - nvl(q1.assessed,0) > 0 ) loop

       

       

          v_message_rmd_html := '<p>Good day ' || r1.first_name ||' </p>

      <p>We have noticed that you have not yet completed all your assessments .<br>

      <p>You are hereby kindly reminded to complete this before  '||v_return_date||'.</p>

      ;

       

       

      -- send email

          apex_mail.send(

                    p_to => r1.email,

                    P_FROM => 'no_reply@mail',

                    P_BODY => v_message_rmd_html,

                    p_body_html => v_message_rmd_html,

                    P_SUBJ => 'Please complete your survey performance assessments ',

                    p_cc => '',

                    p_bcc => '',

                    p_replyto => 'no_reply@mail.com '

                            );

          APEX_MAIL.PUSH_QUEUE(

            p_smtp_hostname => 'localhost',

            p_smtp_portno => 25

            );

          

        end loop;

      end;

        • 1. Re: Is it possible to use values extracted by a for loop in a HTML body to send a email using the Apex_send_mail API
          fac586

          rhisingh wrote:

           

          create or replace procedure "NEW_RMD_TEST1"

          is

              v_message_rmd_html varchar2(1000);

              v_return_date varchar2(24);

          first_name varchar2(20);

           

          begin

              v_return_date := to_char((sysdate + 14 ),'DD MON YYYY');

           

           

           

          for r1 in (

            with q1 as (SELECT detail.user_id  , names.first_name , Count(detail.assessed) AS ASSESSED

              FROM E_PERFO_TBL_IMP detail, Apex_workspace_apex_users names, E_PERF_2_COMP master               

                WHERE  master.e_mail = detail.user_id

                    and lower (master.e_mail) = lower (names.email)

              GROUP  BY  detail.user_id, names.FIRST_NAME)

           

          select m.e_mail, q1.firstname

              from q1, E_PERF_2_COMP m

            where lower(q1.user_Id(+)) = lower(m.e_mail)

              and ASMENTS_2_COMP - nvl(q1.assessed,0) > 0 ) loop

           

           

              v_message_rmd_html := '<p>Good day ' || r1.first_name ||' </p>

          <p>We have noticed that you have not yet completed all your assessments .<br>

          <p>You are hereby kindly reminded to complete this before  '||v_return_date||'.</p>

          ;

           

           

          -- send email

              apex_mail.send(

                        p_to => r1.email,

                        P_FROM => 'no_reply@mail',

                        P_BODY => v_message_rmd_html,

                        p_body_html => v_message_rmd_html,

                        P_SUBJ => 'Please complete your survey performance assessments ',

                        p_cc => '',

                        p_bcc => '',

                        p_replyto => 'no_reply@mail.com '

                                );

              APEX_MAIL.PUSH_QUEUE(

                p_smtp_hostname => 'localhost',

                p_smtp_portno => 25

                );

             

            end loop;

          end;


          You return the column as first_name in the subquery, but reference it as firstname in the main query.

           

          A problem of this nature really shouldn't require recourse to the forum to solve...

          • 2. Re: Is it possible to use values extracted by a for loop in a HTML body to send a email using the Apex_send_mail API
            rhisingh

            Hi Fac thank for that yes that was really stupid of me . The mails  work now but the first name is not being returned it just coming as 

             

            Good day

            We have noticed that you have not yet completed all your assessments

            You are hereby kindly reminded to complete this before 

             

            Thanks man . take care

            • 3. Re: Is it possible to use values extracted by a for loop in a HTML body to send a email using the Apex_send_mail API
              fac586

              rhisingh wrote:

               

              Hi Fac thank for that yes that was really stupid of me . The mails  work now but the first name is not being returned it just coming as

               

              Good day

              We have noticed that you have not yet completed all your assessments

              You are hereby kindly reminded to complete this before

              The first name is sourced from the Q1 subquery. You're performing an outer join between E_PERF_2_COMP and Q1, so clearly either there is a row in E_PERF_2_COMP with no matching row in Q1, or the join predicate is not correct.

               

              Without access to your database, table definitions or data it's not possible to provide specific help. If you need further assistance then reproduce the problem on apex.oracle.com.