Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Sending Emails based on query (APEX/ Oracle 10g)
Sending Emails based on query [message #557783] Fri, 15 June 2012 14:00 Go to next message
trulysick
Messages: 5
Registered: June 2012
Location: Michigan
Junior Member
Ok I am building a job that runs daily and sends out email based on rows in table where send_date = Trunc(sysdate)....

I've got the job all set up and that runs, but I am working on the procedure it is calling..

Basically what I need is something that will pull out information from the row where send_date = TRUNC(sysdate) then send emails to right places/people based on this.

We have email.util and email.pkg all set up then we also have a process in the page called send_emails. All of these are pulling data from different tables and combining them to send the emails... I am unsure of how exactly to get my procedure to run. Do I call these other functions, etc from with my procedure? Or should I copy over the send_emails process which does call the email.util???

I'm so lost, but this is what I have so far:
This is my procedure that the job uses:
Code is right here


I'm unsure if anything in the email.util needs to be changed or can I just slice up the send_emails process and use it in my procedure and how??

any help is much much appreciated!


[EDITED by LF: applied [spoiler] tags]

[Updated on: Fri, 15 June 2012 15:39] by Moderator

Report message to a moderator

Re: Sending Emails based on query [message #557785 is a reply to message #557783] Fri, 15 June 2012 14:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What exactly do you expect from us here?

Re: Sending Emails based on query [message #557788 is a reply to message #557783] Fri, 15 June 2012 14:21 Go to previous messageGo to next message
trulysick
Messages: 5
Registered: June 2012
Location: Michigan
Junior Member
I just need to know how to get my procedure to send out emails based on that query....
Do I just stick that whole process into my Procedure?

I know it's a LOT of code up there, but I figured it may be needed
Re: Sending Emails based on query [message #557789 is a reply to message #557788] Fri, 15 June 2012 14:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
In general, you can either copy and paste your whole process to send the mail into your loop within your
send_postdated_email procedure or you can create a procedure from your whole process, then reference that
procedure within your send_postdated_email procedure. I would prefer the latter. Either way, you will
then need to reference the variables from your loop as x.type_id, x.lvl_id, x.title, x.start_date, and
x.end_date and pass then to the procedure in the loop. So, your procedure would need to accept those
parameters and substitute them in your process, something like:

create or replace procedure send_mail
  (l_type_id in tec_notif.type_id%type,
   l_lvl_id  in tec_notif.lvl_id%type,
   l_title   in tec_notif.title%type,
   l_start   in tec_notif.start_date%type,
   l_end     in tec_notif.end_date%type)
as
  -- other variables
begin
  -- send_mail process
end;
/
show errors
create or replace Procedure SEND_POSTDATED_EMAIL
is 
BEGIN
  for x in 
    (SELECT type_id, lvl_id, title, start_date, end_date
     FROM   tec_notif
     WHERE  send_date = TRUNC(sysdate))
  loop
    send_mail (x.type_id, x.lvl_id, x.title, x.start_date, x.end_date);
  end loop;
end; 
/
show errors

Re: Sending Emails based on query [message #557791 is a reply to message #557789] Fri, 15 June 2012 14:57 Go to previous messageGo to next message
trulysick
Messages: 5
Registered: June 2012
Location: Michigan
Junior Member
Ok, cool I'll give that a shot on Monday since the workday is over.

Thanks for the help! I was really hoping I would be able to pass something... I'm not big on procedural stuff i'm a Java person myself.
Re: Sending Emails based on query [message #557797 is a reply to message #557791] Fri, 15 June 2012 15:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is too much code for me so my answer might not help at all, but - as you use Apex, perhaps you could/should have a look at APEX_MAIL package to send an email from an Oracle Application Express application.
Re: Sending Emails based on query [message #558009 is a reply to message #557797] Mon, 18 June 2012 13:40 Go to previous messageGo to next message
trulysick
Messages: 5
Registered: June 2012
Location: Michigan
Junior Member
OK so I tried doing this :
create or replace procedure send_postdated_email
IS

  l_notif_id            NUMBER;
   l_notif_seq           NUMBER;
   l_notif_type          NUMBER;
   l_lvl_id              NUMBER;
   l_start               DATE;
   l_end                 DATE;
   l_send_date           DATE;
   l_notif_title         VARCHAR2(500);
   l_resend              VARCHAR2(500);
   l_its_ids             VARCHAR2(100);
   l_its_div             NUMBER;
   --
   l_http_host           VARCHAR2(500);
   l_email_recipients    wwv_flow_global.vc_arr2;
   l_count_str           VARCHAR2(255);
   l_user_str            VARCHAR2(4000);
   --
   l_email_body          VARCHAR2(4000);
   l_parsed_body         VARCHAR2(4000);
   l_email_subj          VARCHAR2(500);
   l_parsed_subj         VARCHAR2(500);
   --
   l_toname              VARCHAR2(500);
   l_frmname             VARCHAR2(255) := 'LCC-TEC@email.lcc.edu';
   l_em_status           VARCHAR2(255);
   --
   l_emed_user_cnt       NUMBER := 0;
   l_distro_names        VARCHAR2(500);
   l_its_send_msg        VARCHAR2(1000);
   



BEGIN
 for x in (SELECT type_id, lvl_id, title, start_date, end_date
           FROM tec_notif
           WHERE send_date = TRUNC(sysdate))
       loop
         
          --send mail
          
        
           
         BEGIN
           l_http_host := OWA_UTIL.get_cgi_env ('HTTP_HOST');
           --
           BEGIN
             SELECT its_id INTO l_its_div
             FROM   tec_its_contacts
             WHERE  LOWER(its_contact_name) = 'its-div';
             EXCEPTION
               WHEN NO_DATA_FOUND THEN
                 l_its_div := 1;
           END;
           --
           IF INSTR(':'|| :P2_ITS_IDS ||':', ':'|| l_its_div ||':') > 0 THEN
             l_its_ids := '1';
           ELSE
             l_its_ids := :P2_ITS_IDS;
           END IF;
           -- have to get b/c page items are turned into display text after notification is created (do not want users to change static attributes)
           SELECT type_id, lvl_id, title, start_date, end_date, send_date
           INTO   l_notif_type, l_lvl_id, l_notif_title, l_start, l_end, l_send_date
           FROM   tec_notif
           WHERE  notif_id = :P2_NOTIF_ID;
           --  
           l_email_recipients := tec_email_util.get_email_recipients(l_notif_type,:P2_IMP_SVCS,l_lvl_id,l_its_ids,:P2_NO_SEND); 
         -- get 'who' will be emailed
           -- get the email body and subject line templates
           SELECT body, subject
           INTO   l_email_body, l_email_subj
           FROM   tec_email_config
           WHERE  outage_type_id = :P2_NOTIF_TYPE_ID;
           --
         
         
         
         
          IF l_its_ids IS NOT NULL THEN
             FOR c_its_ids IN ( SELECT its.its_contact_name,its.its_id
                                FROM   tec_its_contacts its
                                WHERE  INSTR(':'|| l_its_ids ||':', ':'|| its.its_id ||':') > 0
                                ORDER BY its.its_id
                              ) LOOP
                 l_distro_names := l_distro_names || ', ' || c_its_ids.its_contact_name;
             END LOOP;
             l_distro_names := substr(l_distro_names, INSTR(l_distro_names, ',', 1) + 1);
         --    l_its_send_msg := '<br><br>The following distribution lists would have been emailed: ' || l_distro_names;
           END IF;
           --
           l_parsed_body := tec_email_util.parse_keywords(l_email_body,:P2_NOTIF_ID,:P2_NOTIF_SEQ,:P2_SUB_ID,l_lvl_id,:P2_STATUS_ID,l_notif_type,:P2_INFO_TYPE,'&APP_ID.',l_notif_title,:P2_IMP_SVCS,:P2_EMAIL_BODY,l_start,l_end,'SEND');
           l_parsed_subj := tec_email_util.parse_keywords(l_email_subj,:P2_NOTIF_ID,:P2_NOTIF_SEQ,:P2_SUB_ID,l_lvl_id,:P2_STATUS_ID,l_notif_type,:P2_INFO_TYPE,'&APP_ID.',l_notif_title,:P2_IMP_SVCS,NULL,l_start,l_end,'SEND');
           
           
           -- if test instance, email to user and concatenate who would actually receive emails to the body
           IF INSTR(':'|| lower(l_http_host) ||':', 'horizon') > 0 THEN -- test instance
             l_resend := :P2_RESEND_NOTE;
         
         	IF LENGTH(l_distro_names) > 0 THEN
         		l_its_send_msg := '<br><br>The following distribution lists would have been emailed: ' || l_distro_names;    
         	END IF;
         	--
         	IF :P2_NO_SEND IS NULL THEN
         		l_emed_user_cnt := l_email_recipients.count;
         	END IF;
         	l_count_str := '<br><br>*This was sent from the test instance of TEC. In Production this would been sent to - ' || l_emed_user_cnt || ' people*';
         	l_parsed_body := l_parsed_body || l_count_str || l_its_send_msg;
         	l_toname := LOWER(:APP_USER) || '@email.lcc.edu';
         	
         	IF :P2_IS_NEW_RECORD = 'TRUE' THEN
         	
         
         		-- send the email to the person using the test application
         		lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		
         	
         	ELSE	
         		IF l_resend = 'Yes' THEN
         			-- send the email to the person using the test application
         			lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		END IF;
         	END IF;
         	
         	
           ELSE   -- WE ARE IN PRODUCTION
             l_resend := :P2_RESEND_NOTE;
             
              --
              IF LENGTH(l_distro_names) > 0 THEN
                l_its_send_msg := '<br><br>This message was sent to the following distribution list(s): ' || l_distro_names;    
              END IF;
              l_parsed_body := l_parsed_body || l_its_send_msg;
              --
         	 IF :P2_IS_NEW_RECORD = 'TRUE' THEN
         		FOR i in 1..l_email_recipients.count
         		LOOP
         		
         			l_toname := TO_CHAR(l_email_recipients(i));
         			lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		
         		END LOOP;
         	ELSE	
         		IF l_resend = 'Yes' THEN
         			FOR i in 1..l_email_recipients.count
         			LOOP
         		
         				l_toname := TO_CHAR(l_email_recipients(i));
         				lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		
         			END LOOP;
         		END IF;
             END IF;
         	
            END IF;
            
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                            
           -- 
          END LOOP;  
         END;


But when I force run the job it say this procedure is in an invalid state.... i'm getting errors on the :P2 stuff which are calls to page items(this is an apex app).. which i'm pretty sure i do need. Or do I have to use calls to the database only??
Re: Sending Emails based on query [message #558011 is a reply to message #558009] Mon, 18 June 2012 14:32 Go to previous messageGo to next message
trulysick
Messages: 5
Registered: June 2012
Location: Michigan
Junior Member
The above send mail process is a page process in apex... will this even work in a procedure thats called by a daily job?
Re: Sending Emails based on query [message #558349 is a reply to message #558011] Thu, 21 June 2012 03:20 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Yes. We have a project here that uses APEX_MAIL outside an Apex context. I believe it involves setting the Apex security through the Apex API.

MHE
Previous Topic: How to create wallet in APEX 4.1
Next Topic: Calling URL using Database without specifying a browser
Goto Forum:
  


Current Time: Thu Mar 28 06:26:33 CDT 2024