1 2 Previous Next 25 Replies Latest reply: Apr 7, 2014 6:43 AM by rhisingh Go to original post RSS
      • 15. Re: how to write a pl/sql based on column condition to send a email reminder from a report
        rhisingh

        Hay Christoph

         

        Once again thanks so much for your time and effort i really do appreciate it and i really would have never known what to do with out your help  . This email error is driving me crazy

        I tried the code again but the invalid recipient  error comes up

         

        I opened up SQL and ran the first part of the code

         

        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 upper(q1.user_Id(+)) = upper(m.e_mail)

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

         

        which returned some results in upper case

         

        Then ran the email part of the code in application express SQL work shop  but i used my personal email as the to recipient

         

        -- send email

            apex_mail.send(

                      p_to => 'rhi.singh@gmail.com',

                      P_FROM => 'Servey@rhisingh',

                      P_BODY => 'Message',

                      p_body_html => 'Please complete',

                      P_SUBJ => 'Test mail',

                      p_cc => '',

                      p_bcc => '',

                      p_replyto => 'rhi.singh@gmail.com'

                              );

            APEX_MAIL.PUSH_QUEUE(

              p_smtp_hostname => 'localhost',

              p_smtp_portno => 25

              );

         

        when i pressed run it executed successfully .Then i went to the internal work space and checked the maill queue the invalid recipient error showed up there . So I'm just trying to figure out what could be the problem now 

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

          You're troubleshooting it correctly. At this point, however, I'm not sure whether I can be of much help. Have you set up the Access Control List (ACL) on the Apex database?

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

            Hay christoph

             

            Yes i have set up the ACL      ---- 

             

            SQL>select acl, principal from dba_network_acl_privileges;

             

            ACL                                                               PRINCIPAL

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

            /sys/acls/local-access-users.xml            APEX_030200

            /sys/acls/local-access-users.xml           APEX_040200

            /sys/acls/local-access-users.xml           APEX_040200

            /sys/acls/www.xml                                      APEX_040200

             

            SQL> select host, lower_port, upper_port, acl from dba_network_acls;

             

            HOST                                      LOWER_PORT              UPPER_PORT          ACL

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

            localhost                                                                                                                  /sys/acls/local-access-user

            *                                                                                                                                 /sys/acls/www.xml

            stbeehive.oracle.com             25                                        25                             /sys/acls/local-access-user

             

            Can you possibly see anything wrong here

             

            Once again Christoph thank you so much for your time and effort it really appreciated ;

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

              I don't see an entry for an email server in dba_networks_acls.

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

                OK how would i go about doing that  the code below is what i used to create the dba_network_acls for stbeehive.oracle.com

                 

                BEGIN

                  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => '/sys/acls/www.xml',

                                                    description => 'WWW ACL',

                                                    principal   => 'APEX_040200',

                                                    is_grant    => true,

                                                    privilege   => 'connect');

                 

                  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => '/sys/acls/www.xml',

                                                       principal => 'APEX_040200',

                                                       is_grant  => true,

                                                       privilege => 'resolve');

                 

                  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => '/sys/acls/www.xml',

                                                    host => 'stbeehive.oracle.com');

                END;

                 

                But this wasnt working at all so i had to download a free smtp_server  then the email of the report started working . But the email of the button that we discussed still inst working  

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

                  Hay christoph

                   

                  I had a though  i don't if this is possible but when the user of my app  presses submit on the page in the insert table of that page i used a trigger which was a before insert ( new.user := (APP_USER) which is the result of the upper case error when trying to general the email reminder  .

                  Is there some way to perhaps save the users email address in the insert table like the way i used app_user eg (new.user :=(APP_USER.email) ) something like this

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

                    Hay christoph

                     

                    I think I might no whats giving me the error you know the part in the code >> p_to=>'send_to_email' has to be enclosed in single quotes other wise the invalid email address error comes up so when the SQL query of the code runs it returns those values  into r1.email which doesn't get enclosed into single quotes . So the question is how do i get the values returned from the sql code to be automatically assigned single quotes 

                     

                    Please let me know what you think    

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

                      Rhising,

                      sorry for being away for so long.

                      If you're using variables (like r1.email) you do not use quotes.

                      Have you been able to make any progress on this?

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

                        Hay Christoph

                        Good to hear from you man I trust you have been well  .

                         

                        There was a 550 invalid recipient error showing up in the manage mail queue so checking up on Google it said that this error most of the time occurs on the recipients end which I'm thinking is most likely begin blocked because of the free email emulator I use  to test the send mail function .So now i just have to wait till I deploy this application in the required  environment inside of where the email is supposed to go to hopefully this error will go away

                        there's this document i came across on these SMTP error codes found here http://www.answersthatwork.com/Download_Area/ATW_Library/Networking/Network__3-SMTP_Server_Status_Codes_and_SMTP_Error_C…   If you might want to look at them

                         

                         

                        Christoph  you have been a great help you me and i really do appreciate your time and effort .Thank you ever so kindly .GOD BLESS       

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

                          Rhisingh,

                          i'm glad to help. I hope you're getting the last kinks worked out.

                           

                          Cheers,

                          Christoph

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

                            Hay chirstoph

                             

                            Great to hear from you again . Yes well I am seem to be getting  things done thank GOD and great guys here on apex OTN Im just haveing issues with the serve firewall but that's on the customers end it seems to be blocking every port i tried 80 , 5986 ,  8080 still blocked I'm going over there today hopefully they can change the security on the server .

                            Keep in touch chirstoph take care buddy      

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

                              Sounds like you're close to getting this resolved.

                              If you don't mind, please mark the thread as answered so the contributors can receive OTN credit.

                               

                              Thanks,

                              Christoph

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

                                Hay man I cant seem to find any option to mark as answered . ?

                                1 2 Previous Next