Thanks for the question, Tushar.
Asked: December 09, 2020 - 5:31 pm UTC
Last updated: December 11, 2020 - 3:56 am UTC
Version: 5.1
Viewed 1000+ times
You Asked
Hi,
How can I delete the data from apex_mail_queue using dbms_scheduler?
I am trying this for creation of job-
begin
dbms_scheduler.create_job (
job_name => 'xyz',
job_type => 'STORED_PROCEDURE',
job_action => 'rescheduler',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
enabled => TRUE,
auto_drop => FALSE);
END;
and for preocedure-
CREATE OR REPLACE PROCEDURE rescheduler
AS
BEGIN
insert into dummy_mail
select * from apex_mail_queue;
delete from apex_mail_queue;
END;
Data inserted in the dummy_mail table but not deleted from apex_mail_queue.
Please help me regarding this.
and Connor said...
The APEX_MAIL_QUEUE is a view which sits on top of the physical mail table wwv_flow_mail_queue, and that view ensures that you only delete email for *your* workspace. Thus in your scheduler job, if no workspace is set, it will not delete anything.
So you'll need to do something like
apex_application_install.set_workspace_id (your_workspace_id);
apex_util.set_security_group_id(p_security_group_id => apex_application_install.get_workspace_id);
delete apex_mail_queue;
to set your workspace first, and then you can delete emails for *that* workspace.