Wrap the export/email logic into a procedure
CREATE OR REPLACE PROCEDURE xxsea_send_downtime_extract_email IS
v_context apex_exec.t_context;
v_export apex_data_export.t_export;
v_mail_id NUMBER;
BEGIN
-- 1. Open a query context against your source table
v_context := apex_exec.open_query_context (
p_location => apex_exec.c_location_local_db,
p_sql_query => 'SELECT * FROM XXAL_DOWNTIME_FORM_HISTORY ORDER BY 1 DESC'
);
-- 2. Export that context as CSV
v_export := apex_data_export.export (
p_context => v_context,
p_format => apex_data_export.c_format_xlsx,
p_file_name => 'Downtime_Incident_Table_Extract'
);
apex_exec.close (v_context);
-- 3. Send the email shell and capture the returned mail_id
v_mail_id := apex_mail.send (
p_to => 'aalam@abc.com',
p_from => 'apexadmin@abc.com',
p_body => 'Please find the attached downtime incident data extract.',
p_subj => 'Automated Downtime Incident Data Extract'
);
-- 4. Attach the exported CSV using that mail_id
apex_mail.add_attachment (
p_mail_id => v_mail_id,
p_attachment => v_export.content_blob,
p_filename => v_export.file_name,
p_mime_type => v_export.mime_type
);
-- 5. Push the mail queue immediately (optional)
apex_mail.push_queue;
EXCEPTION
WHEN OTHERS THEN
apex_exec.close (v_context);
RAISE;
END xxsea_send_downtime_extract_email;
/
Important - set the APEX session context inside the job
APEX_DATA_EXPORT and APEX_MAIL need an APEX session/workspace context to resolve correctly. A DBMS_SCHEDULER job runs as a raw DB session with no APEX context by default, so you need to establish one inside the job action. Wrap the call like this:
CREATE OR REPLACE PROCEDURE xxsea_run_downtime_extract_job IS
l_security_group_id NUMBER;
BEGIN
-- point at the correct workspace before calling APEX APIs
l_security_group_id := apex_util.find_security_group_id(p_workspace => 'SEAMAN');
apex_util.set_security_group_id(p_security_group_id => l_security_group_id);
xxsea_send_downtime_extract_email;
END xxsea_run_downtime_extract_job;
/
Create the scheduler job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_DOWNTIME_EXTRACT_EMAIL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN xxsea_run_downtime_extract_job; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=7; BYMINUTE=0', -- daily at 7:00 AM
enabled => TRUE,
comments => 'Sends daily downtime incident extract via email'
);
END;
/
Verify the job is registered and check its run history
-- Confirm it's scheduled
SELECT job_name, enabled, state, next_run_date
FROM user_scheduler_jobs
WHERE job_name = 'JOB_DOWNTIME_EXTRACT_EMAIL';
-- Check whether it actually ran and if it errored
SELECT log_date, status, additional_info
FROM user_scheduler_job_run_details
WHERE job_name = 'JOB_DOWNTIME_EXTRACT_EMAIL'
ORDER BY log_date DESC;
Test it manually before waiting for the schedule
BEGIN
DBMS_SCHEDULER.RUN_JOB('JOB_DOWNTIME_EXTRACT_EMAIL');
END;
/
To disable/drop it later
BEGIN
DBMS_SCHEDULER.DISABLE('JOB_DOWNTIME_EXTRACT_EMAIL');
-- or to remove entirely:
-- DBMS_SCHEDULER.DROP_JOB('JOB_DOWNTIME_EXTRACT_EMAIL');
END;
/
0 comments:
Post a Comment