Scheduling a report(s) from the database using APEX utility :
Till now using subscription, we can schedule the interactive report only.
Most cases we may get requirement to schedule the multiple reports with custom
definitions.
Here is the example to schedule a report from database using APEX utility.
1. Create a stored procedure as below.
create or replace procedure Employee
as
l_body_html CLOB;
l_body CLOB;
BEGIN
l_body:= 'Employee Report';
l_body_html := l_body_html || '<font color="green"><center><h3>Employee
Report</h3><br><h3>Employee Report</h3></center></font><table border="1"
width="100%" ><tr bgcolor="#CEE3F6"><td align="center">Empno</td><td
align="center">Employee Name</td><td align="center">Job</td><td
align="center">Salary</td></tr>';
for c1 in(Select empno,ename,job from emp)
loop
l_body_html :=l_body_html || '<tr><td>' || c1.empno || '</td>' || '<td>' || c1.ename
|| '</td>' || '<td>' || c1.Job || '</td></tr>';
end loop;
l_body_html :=l_body_html || '</table>';
apex_mail.send(
p_to => 'to@test.com
',
p_from => 'from@test.com
',
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'Employee Report',
p_bcc => 'bcc users@test.com
');
END Employee;
2. Create a job to schedule the report.
Schduler :
BEGIN
dbms_scheduler.create_schedule(
schedule_name => 'Employee_Schedule',
start_date => trunc(sysdate),
repeat_interval => 'freq=Daily;Day='Mon',
comments => 'Runtime: Every Monday ');
END;
Program :
begin
-- Call a procedure
dbms_scheduler.create_program
(program_name=> 'Employee_Program',
program_type=> 'STORED_PROCEDURE',
program_action=> ?Employee? ,
enabled=>true,
comments=>'Procedure to collect session information'
);
end;
Job :
begin
-- Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
(job_name => 'Employee_Job',
program_name=> 'Employee_Program',
schedule_name=>'Employee_Schedule',
enabled=>true,
auto_drop=>false,
comments=>'Job to collect data about session values every 5 minutes');
end;
Below is the screenshot for report :
