Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Sending Mail on a Condition in Apex 5.1.0.00.45 (Apex 5.1.0.00.45)
Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665865] Wed, 27 September 2017 04:57 Go to next message
JanCoetzee
Messages: 8
Registered: September 2017
Junior Member
Hi

Don't know if this is the right area but correct me if I should post it in a different area.

I am very new to Apex and am having huge difficulty in doing the following:

I have a END_DATE column in a table. I need to let the users know 60 days and 30 days before this end date is reached. They want to be notified by email and all of this has to be done through Apex.
The page showing the complete data of the table that contains the END_DATE has been created and is working fine. But now to create the function that checks the END_DATE continuously and sent out the mails when the date falls in the 60 day category and in the 30 day category is flooring me.

I am desperate!

Please can someone help?

Regards
Jan
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665866 is a reply to message #665865] Wed, 27 September 2017 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds like a job for dbms_scheduler not apex.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665869 is a reply to message #665866] Wed, 27 September 2017 14:42 Go to previous messageGo to next message
Littlefoot
Messages: 21229
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I agree. The fact you use Apex doesn't mean that it has to be involved into sending an e-mail. Besides, I truly have no idea how to do it purely in Apex.

As CM has already said, a (daily) job would check whether someone should get mail. If so, you'd use UTL_MAIL or UTL_SMTP and send a mail.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665873 is a reply to message #665869] Thu, 28 September 2017 00:55 Go to previous messageGo to next message
JanCoetzee
Messages: 8
Registered: September 2017
Junior Member
Hi

I agree with both of you. But, and feeling rather stupid here, I am also new at working on Oracle and have no idea how to set it up there either.
I was pulled into this and this is not my area of expertise.
So if you could help me or give me direction as to put it up in Oracle I would be very grateful.

Regards
Jan
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665874 is a reply to message #665873] Thu, 28 September 2017 02:04 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
If you want to use APEX mail facilities, use the APEX_MAIL package
http://docs.oracle.com/database/122/AEAPI/apex_mail.htm#AEAPI341
plenty of examples there.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665875 is a reply to message #665874] Thu, 28 September 2017 02:05 Go to previous messageGo to next message
JanCoetzee
Messages: 8
Registered: September 2017
Junior Member
Thanks, will go have a look there.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665894 is a reply to message #665875] Fri, 29 September 2017 02:58 Go to previous messageGo to next message
JanCoetzee
Messages: 8
Registered: September 2017
Junior Member
I manage to get further in this issue. Now have the following issue within Apex. Using this code: SELECT * FROM PD_SPONSORS WHERE (MOU_END - INTERVAL '1'MONTH) BETWEEN (MOU_START + INTERVAL '11' MONTH)AND SYSDATE; on a button as a SQL Expression but get the following error: "ORA-06550: line 1, column 26: ORA-00936 missing expression.
But the same command when run in Oracle gives the desired result without any issues.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665895 is a reply to message #665894] Fri, 29 September 2017 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 21229
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is this code supposed to do? You said you used that code "on a button" - where exactly? Apex is mostly PL/SQL so a SELECT requires an INTO, maybe that's what's missing.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665896 is a reply to message #665895] Fri, 29 September 2017 03:20 Go to previous messageGo to next message
JanCoetzee
Messages: 8
Registered: September 2017
Junior Member
The SQL is supposed to display only the rows that fall within that range on a new page. But like I said this is all new to me so trying till I get it right. Bit difficult with no background in this.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665897 is a reply to message #665896] Fri, 29 September 2017 03:23 Go to previous messageGo to next message
Littlefoot
Messages: 21229
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't display anything on a button; you'd have to create a report region (either a classic or an interactive report) and put that query into its source.
Re: Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665898 is a reply to message #665896] Fri, 29 September 2017 03:24 Go to previous message
JanCoetzee
Messages: 8
Registered: September 2017
Junior Member
Thanks, going to try that quickly
Previous Topic: Passing encoded url string containing %3A (colon) truncates everting after the last %3A
Next Topic: Field replacement
Goto Forum:
  


Current Time: Sun Nov 19 15:38:02 CST 2017

Total time taken to generate the page: 0.03608 seconds