OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Sending Mail on a Condition in Apex 5.1.0.00.45

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sending Mail on a Condition in Apex 5.1.0.00.45 [message #665865]

Wed, 27 September 2017 04:57

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

Report message to a moderator

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

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Sounds like a job for dbms_scheduler not apex.

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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

Report message to a moderator

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

John Watson
Messages: 8961
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.

Report message to a moderator

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

JanCoetzee
Messages: 8
Registered: September 2017

Junior Member

Thanks, will go have a look there.

Report message to a moderator

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

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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

JanCoetzee
Messages: 8
Registered: September 2017

Junior Member

Thanks, going to try that quickly

Report message to a moderator

Previous Topic:	Passing encoded url string containing %3A (colon) truncates everting after the last %3A
Next Topic:	Field replacement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:28:34 CST 2024