1 2 Previous Next 25 Replies Latest reply: Apr 7, 2014 6:43 AM by rhisingh RSS

    how to write a pl/sql based on column condition to send a email reminder from a report

    rhisingh

      hay guys how would i send and email lets say as an reminder based on the conditions of a report

       

      I have a table which looks like this

       

      ASSESSMENTS_DONE

       

      USER_ID                   forms_to_fill                     Forms_done                                    outstanding

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

      user@1                                5                                        5                                                   0

      user@3                                4                                        3                                                  1

      user@5                                9                                        7                                                  2

       

      So I am thinking i would  have to set a conditional pl/sql expression on Ouststanding column because this the column i want to reference which will have the logic in it the looked something like this (where 'outstanding' >0 send email )

       

      In administration  under users I have user@3 stored as USER@3 with his email stored as UsEr@3 which is his real email address . And user@5  would have the same set up around the storage of his name . The reason that I picked user@3 and user@5 is because they have outstanding forms to fill

       

      what i would like to know is how would the syntax for sending this email reminder look like being that there has to be a where clause in there . and will the email still go through to the users that are outstanding even if there is a difference in there user name and email because as you can see the user name and email are the same but the email has variance in upper and lower case

       

      Please advise

       

      Thank you  

        • 1. Re: how to write a pl/sql based on column condition to send a email reminder from a report
          Christoph

          Which version of Apex are you on? There is quite a bit of documentation for this out there.

          I'm a bit hesitant about the way you're going about this though. It sounds to me like the email gets sent when the report is viewed. You would have to build in a safeguard to prevent emails from being sent every time the report is viewed.

          You may want to consider putting the email code in a table trigger. The trigger would know whether outstanding > 0 and only then send an email.

          For this you would use the utl_smtp package.

           

          Cheers,

          Christoph

          • 2. Re: how to write a pl/sql based on column condition to send a email reminder from a report
            rhisingh

            Hello Christoph

             

            I didn't know the email would fire every time the report is read thanks for that input man

             

            what if i had to create a button (with the label saying 'Send reminder') in the same region as the report and then create a send mail procedure behind that button . I found this idea here Oracle utl_smtp to send e-mail

            The thing there is where ? will the (where remaining >0 ) clause fit in that syntax and also given that the column "REMAINING" is generated by the with clause will this dbms_smtp code be able to reference that colum

            and also what properties on the button will i have to set in order for that code to fire

             

            I am on Apex 4.2.2

            and Oracle 11.2

             

            Thanks again Christoph for the help

            • 3. Re: how to write a pl/sql based on column condition to send a email reminder from a report
              AndyH

              It might be better to write a package/procedure that sends the emails. The procedure would be responsible for determining who to send emails to e.g. it might run a query similar to the one for your report. This procedure could then be called periodically through APEX jobs or through a button on your report page. The procedure could also be responsible for tracking who it has sent emails to, whether multiple emails for the same 'reason' are allowed, etc.

              • 4. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                rhisingh

                Thanks christoph

                 

                I will try to do this

                 

                Cheers man

                • 5. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                  rhisingh

                  Chris so

                  I think creating a procedure  would be much easier for me I am thinking more on the lines of these steps

                  1) create the procedure -in the body of the procedure i would use that 'with clause statement'   you gave me

                  2) here after the procedure would be stored  some where with a name

                  3) then from here will the apex_job automate this or will i use a button to call the procedure ?

                  • 6. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                    Suniti

                    Hi,

                     

                    You can make the procedure like that and then can schedule that.

                     

                    CREATE OR REPLACE PROCEDURE "SEND_AUTOMATED"
                    AS
                        EMAIL_TO                VARCHAR2(255);
                        EMAIL_FROM              VARCHAR2(255);
                        EMAIL_CC                VARCHAR2(255);
                        EMAIL_BCC               VARCHAR2(255);
                    BEGIN
                    begin
                    SELECT EMAIL_TO,EMAIL_FROM,EMAIL_CC,EMAIL_BCC
                    INTO EMAIL_TO,EMAIL_FROM,EMAIL_CC,EMAIL_BCC  FROM TABLE_MAIL where condition ;
                    EXCEPTION WHEN OTHERS THEN
                    NULL;
                    end;

                    conditions

                    apex_mail.send(
                                p_to => EMAIL_TO,
                                P_FROM => EMAIL_FROM,
                                P_BODY => 'Message',
                                p_body_html => '',
                                P_SUBJ => 'Test mail',
                                p_cc => EMAIL_CC,
                                p_bcc => EMAIL_BCC,
                                p_replyto => ''
                                        ); 

                      APEX_MAIL.PUSH_QUEUE(
                        p_smtp_hostname => ' ',
                        p_smtp_portno => 25
                        );
                    END;

                    Can schedule the process
                    sys.dbms_scheduler.disable(name=>'SEND_AUTOMATED_MAIL', force => TRUE);

                    SYS.DBMS_SCHEDULER.ENABLE(NAME=>'SEND_AUTOMATED_MAIL');

                     

                    Regards,

                    Suniti

                    • 7. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                      rhisingh

                      Hi Suniti thanks for your reply

                       

                      I think that this code will have to be entered in the SQL command line . But then the procedure will have to execute once the button a  pressed . So now the condition of this execution is based on a column in a report on my application   which is page 10  and column heading is called REMAINING which is a number column so i would want this procedure to execute on the condition if that the numbers in that column is >0  . I wanted  to know where the where clause for this will fit in

                       

                      In you code that you sent me >>

                       

                      CREATE OR REPLACE PROCEDURE "SEND_AUTOMATED"
                      AS
                          EMAIL_TO                VARCHAR2(255);
                          EMAIL_FROM              VARCHAR2(255);
                          EMAIL_CC                VARCHAR2(255);
                          EMAIL_BCC               VARCHAR2(255);
                      BEGIN
                      begin
                      SELECT EMAIL_TO,EMAIL_FROM,EMAIL_CC,EMAIL_BCC
                      INTO EMAIL_TO,EMAIL_FROM,EMAIL_CC,EMAIL_BCC  FROM TABLE_MAIL where condition ;        <-- Wiil the where condition fit in here
                      EXCEPTION WHEN OTHERS THEN
                      NULL;
                      end;

                      conditions

                      apex_mail.send(
                                  p_to => EMAIL_TO,
                                  P_FROM => EMAIL_FROM,
                                  P_BODY => 'Message',
                                  p_body_html => '',
                                  P_SUBJ => 'Test mail',
                                  p_cc => EMAIL_CC,
                                  p_bcc => EMAIL_BCC,
                                  p_replyto => ''
                                          );

                        APEX_MAIL.PUSH_QUEUE(
                          p_smtp_hostname => ' ',
                          p_smtp_portno => 25
                          );
                      END;

                      Can schedule the process
                      sys.dbms_scheduler.disable(name=>'SEND_AUTOMATED_MAIL', force => TRUE);

                      SYS.DBMS_SCHEDULER.ENABLE(NAME=>'SEND_AUTOMATED_MAIL');



                      Basically what I am trying to ask here which is confusing to me is that this command has to be entered into the DB but then how would it reference the column in the application ? because the where clause gets its details from the column details in the app   which i think would look something like   (where    f?p=104:10:9520140248896::NO::: , REMAINING >0)   What do you think ? 

                      Thanks man

                      • 8. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                        Christoph

                        Rhisingh,

                        if you choose to go with a scheduled job that executes periodically, you still have to keep track of how often you wish to send mail.

                        For example, if the job executes every hour, the same people may receive the same email every hour, if their remaining number remains > 0.

                         

                         

                        You could use Suniti's code and add a cursor that loops through all the users who have remaning > 0, and send an email to each:

                         

                         

                        If you wish, you could add the code below in a stored procedure and fire it with a button:

                        (BTW: The query below gets the same result as the one we looked at earlier)

                         

                         

                        begin

                          for r1 in (

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

                                          detail.user_id         AS USER_ID

                                   FROM   eskom_rela_tbl_imp detail,

                                          eskom_rela_u_c master

                                   WHERE  master.e_mail = detail.user_id

                                   GROUP  BY detail.user_id)

                          select m.e_mail

                            from q1, eskom_rela_u_c m

                          where q1.user_Id(+) = m.e_mail

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

                           

                            -- send email

                            apex_mail.send(

                                      p_to => r1.e_mail,

                                      P_FROM => EMAIL_FROM,

                                      P_BODY => 'Message',

                                      p_body_html => '',

                                      P_SUBJ => 'Test mail',

                                      p_cc => EMAIL_CC,

                                      p_bcc => EMAIL_BCC,

                                      p_replyto => ''

                                              );

                            APEX_MAIL.PUSH_QUEUE(

                              p_smtp_hostname => ' ',

                              p_smtp_portno => 25

                              );

                             

                          end loop;

                        end;

                        • 9. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                          rhisingh

                          Guys chirso and santi  thank you really so much for the time and effort I really do appreciate it  . I will try out these solutions and let you know how it goes  .Thank you 

                          • 10. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                            rhisingh

                            Hay christoph i hope your well

                             

                            I created a button on that report region and on action i select submit page under condition i selected PL/SQL then put in the code you gave me but on apply changes it gave me a error which was

                             

                            ORA-06550: line 6, column 16: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set (Go to error)

                             

                            So i then opened a SQL> cmd and ran the first half of the code be for the loop and it returned results . could it be some thing to do with APEX perhaps because i know some times it accepts ; and sometimes it doesnt  

                            • 11. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                              rhisingh

                              Hi Christo

                               

                              I sorted out the code I didn't enclose the  => BBC in quotes and my the other error that I picked up was that I have stored the my user names in the administration page in upper case and there email as it is in mixed case eg

                              JOHN@ORACLE.COM    -    user name is the internal work space

                              JoHn@Oracle.com           -    Email address

                               

                              So with the code you gave me to generate the email for remaining the system is  picking up the user name in upper case which is now some thing I don't know how to change because the app_user shows up in lower case in the app I am a bit confused because

                               

                              The administration page is where you add users and there emails then assign them to user groups The user names are all in upper cases and there emails can be stored just how they are  for notifications and so on

                               

                              What Im basically trying to ask is can (app_user) be stored in preserved case instead hot lower case  

                              • 12. Re: how to write a pl/sql based on column condition to send a email reminder from a report
                                Christoph

                                Rhisingh,

                                you can use Oracle's UPPER() or LOWER() functions to force the case. For example:

                                WHERE UPPER(:APP_USER) = UPPER(user_name)

                                 

                                 

                                I assume that the username is the full email address. So the where clause would be:

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

                                This way the upper case strings are compared.



                                1 2 Previous Next