OTN Discussion Forums : apex mail

Alekh

Posts: 66
Registered: 07/27/09

apex mail - mail queue
Posted: Sep 3, 2009 3:00 AM

Reply

each time i have to clear mail queue.
how can i send mail directly with using manage services of apex admin.

alekh

Tobias Arnhold

Posts: 220
Registered: 05/23/07

Re: apex mail - mail queue
Posted: Sep 3, 2009 4:06 AM

in response to: Alekh

Reply

Hi Alekh!

Take a look here: [APEX_MAIL with UTL_TCP under XE database|http://apex-at-work.blogspot.com/2008/09/apexmail-with-utltcp-under-xe-database.html]

Best regards,

Tobias

Alekh

Posts: 66
Registered: 07/27/09

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 3, 2009 4:13 AM

in response to: Tobias Arnhold

Reply

DB 11g/apex3.1

Tobias Arnhold

Posts: 220
Registered: 05/23/07

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 3, 2009 4:32 AM

in response to: Alekh

Reply

It should work under 11g as well. To push the mail queue you need this part in your pl/sql process:

wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'ip',
P_SMTP_PORTNO => 'port');

Alekh

Posts: 66
Registered: 07/27/09

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 12:01 AM

in response to: Tobias Arnhold

Reply

i have written a trigger for email

CREATE OR REPLACE TRIGGER "EMAIL"
AFTER
update on "LEAVE_APPLICATION"
for each row
begin
if :new.status='Approve' then
for c1 in (select FIRST_NAME,LAST_NAME,DESIGNATION,EMAIL from EMPLOYEE_DETAILS
where EMPLOYEE_ID=:new.EMPLOYEE_ID)
loop
APEX_MAIL.SEND(
p_to =>c1.email,
p_from=>'XXX@xxxx.com',
p_body=>
'Leave Details: '||chr(10)||'Details Below are :'||chr(10)||
chr(10)||
'Leave ID:'||:new.LEAVAPPLICATION_ID||chr(10)||
'Emplyee ID:'||:new.employee_id||chr(10)||
'Employee Name:'||:new.employee_name||chr(10)||
'Leave Type:'||:new.LEAVE_TYPE||chr(10)||
'Date From :'||:new.DATE_FROM||chr(10)||
'Date To:'||:new.DATE_TO||chr(10)||
'No of Days:'||:new.no_of_working_days||chr(10)||
chr(10)||
'Status:'||:new.status||chr(10),
p_subj=>'Leave Details :');
end loop;
end;

my question is where should i add this code
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'ip',
P_SMTP_PORTNO => 'port');

Tobias Arnhold

Posts: 220
Registered: 05/23/07

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 12:06 AM

in response to: Alekh

Helpful

Reply

Should be between end loop and end:

CREATE OR REPLACE TRIGGER "EMAIL"
AFTER
update on "LEAVE_APPLICATION"
for each row
begin
if :new.status='Approve' then
for c1 in (select FIRST_NAME,LAST_NAME,DESIGNATION,EMAIL from EMPLOYEE_DETAILS
where EMPLOYEE_ID=:new.EMPLOYEE_ID)
  loop
  APEX_MAIL.SEND(
  p_to =>c1.email,
  p_from=>'XXX@xxxx.com',
  p_body=>
  'Leave Details: '||chr(10)||'Details Below are :'||chr(10)||
  chr(10)||
  'Leave ID:'||:new.LEAVAPPLICATION_ID||chr(10)||
  'Emplyee ID:'||:new.employee_id||chr(10)||
  'Employee Name:'||:new.employee_name||chr(10)||
  'Leave Type:'||:new.LEAVE_TYPE||chr(10)||
  'Date From :'||:new.DATE_FROM||chr(10)||
  'Date To:'||:new.DATE_TO||chr(10)||
  'No of Days:'||:new.no_of_working_days||chr(10)||
  chr(10)||
  'Status:'||:new.status||chr(10),
  p_subj=>'Leave Details :');
end loop;
 
-- push the e-mail queue for immediate delivery
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'ip',
P_SMTP_PORTNO => 'port');
end;

Alekh

Posts: 66
Registered: 07/27/09

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:09 AM

in response to: Tobias Arnhold

Reply

ORA-20505: Error in DML: p_rowid=366, p_alt_rowid=LEAVAPPLICATION_ID, p_rowid2=, p_alt_rowid2=. ORA-04092: cannot COMMIT in a trigger ORA-06512: at "SYS.DBMS_LOCK", line 250 ORA-06512: at "FLOWS_030100.WWV_FLOW_MAIL", line 671 ORA-06512: at "AUTHENTIC.EMAIL", line 23 ORA-04088: error during execution of trigger 'AUTHENTIC.EMAIL'

i am getting this problem

jkallman

Posts: 2,974
Registered: 01/10/01

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:27 AM

in response to: Alekh

Reply

Alekh,

There is a database job, Job Number 4001, which is used to push the e-mail queue every 15 minutes. Is this job not running?

select * from DBA_JOBS where JOB = 4001

Joel

Tobias Arnhold

Posts: 220
Registered: 05/23/07

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:29 AM

in response to: Alekh

Reply

Have you tried executing the script with own values like:

APEX_MAIL.SEND(
  p_to =>'testuser@mail.com',
  p_from=>'newuser@mail.com',
  p_body=>
  'Leave Details: '||chr(10)||'Details Below are :'||chr(10)||
  chr(10)||
  'Leave ID:'|| ' Testdata1'||chr(10)||
  'Emplyee ID:'||' Testdata2'||chr(10)||
  'Employee Name:'||' Testdata3'||chr(10)||
  'Leave Type:'||' Testdata4'||chr(10)||
  'Date From :'||' Testdata5'||chr(10)||
  'Date To:'||' Testdata6'||chr(10)||
  'No of Days:'||' Testdata7'||chr(10)||  chr(10)||
  'Status:'||' Testdata8'||chr(10),
  p_subj=>' Leave Details :');
 
-- push the e-mail queue for immediate delivery
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'ip',
P_SMTP_PORTNO => 'port');

Best regards,

Tobias

Alekh

Posts: 66
Registered: 07/27/09

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:38 AM

in response to: jkallman

Reply

it's running

JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE

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

----------------------
4001 SYS FLOWS_030100 FLOWS_030100 07-SEP-09 09:30:54 07-SEP-09 17:30:55 874.999999999999999999999999999999999999 N sysdate + 8/24 0 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24); NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' (RAW) 0

1 rows selected

Edited by: Alekh on Sep 7, 2009 4:08 PM

jkallman

Posts: 2,974
Registered: 01/10/01

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:43 AM

in response to: Alekh

Reply

Alekh,

Sorry, my mistake. I should have said job 4002 and not 4001. Is job 4002 running?

Joel

Alekh

Posts: 66
Registered: 07/27/09

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:50 AM

in response to: jkallman

Reply

JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE

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

--------

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

4002 SYS FLOWS_030100 FLOWS_030100 07-SEP-09 16:11:01 07-SEP-09 16:21:01 997.000000000000000000000000000000000013 N sysdate + 10/1440 0 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT')); NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' (RAW) 0

1 rows selected

jkallman

Posts: 2,974
Registered: 01/10/01

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 3:56 AM

in response to: Alekh

Reply

Alekh,

So why do you "have to clear the mail queue"? Can you not wait until the job rolls around in the next 15 minutes or less to push the queue?

Joel

Alekh

Posts: 66
Registered: 07/27/09

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 4:00 AM

in response to: jkallman

Reply

i just want to clear the queue instantly...

jkallman

Posts: 2,974
Registered: 01/10/01

Re: apex mail - mail queue-DB 11g/apex3.1
Posted: Sep 7, 2009 4:06 AM

in response to: Alekh

Correct

Reply

Alekh,

Try this. Instead of using APEX_MAIL.PUSH_QUEUE, use:

APEX_MAIL.PUSH_QUEUE_BACKGROUND

This does not have a COMMIT in it, like APEX_MAIL.PUSH_QUEUE, so you may be able to get away with using this in your trigger in the same place in your trigger. This procedure submits a job to push the e-mail queue.

No parameters are necessary, as the SMTP Host and SMTP Port are derived from your system settings (just as they are for APEX_MAIL.PUSH_QUEUE).

Joel