Job Scheduling

TASK:
Create an email reminder to send an email every three month

SOLUTION:
1) Add a packaged procedure send_reminder(
p_sgid IN NUMBER,
p_protocol IN VARCHAR2,
p_flow_id IN VARCHAR2
) is

l_body clob := NULL;
l_link clob;
p_username VARCHAR2(200);
l_protocol varchar2(500) default 'http://';
c_base_url VARCHAR2(200);

l_sql_err VARCHAR2(2000);
l_date_format VARCHAR2(20) := 'YYYY-MON-DD';
l_date_time_format VARCHAR2(20) := 'YYYY-MON-DD HH24:MI';

begin
--These two lines are very important since this procedure is sent from inside the application
wwv_flow_api.set_security_group_id(p_security_group_id => p_sgid);
apex_application.g_flow_id := p_flow_id;

/* three months after survey reminder */
-- select a user from the reminder view which survey date was three month ago
for c1 in (select email,date_survey
from REMINDER
where to_char(date_survey,l_date_format) = to_char(sysdate-92,l_date_format)
)

loop

p_username := c1.email;
c_base_url := your_URL;

l_body := '===================================' || utl_tcp.crlf;
l_body := l_body || '= This is an Automated Message, Do Not Reply = ' || utl_tcp.crlf;
l_body := l_body || '===================================' || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body || 'Hello ' || p_username || ',' || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body ||'Thanks for taking the time to participate in the survey.' || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body ||'Since three months past from your previous survey we would like to ask you to retake the survey' ||utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;
l_body := l_body ||'via the following URL: ' || c_base_url || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf;

apex_mail.send (p_to => p_username,
p_cc => 'your email',
p_from => 'your email',
p_body => l_body,
p_subj => 'Reminder');

-- email will be sent without a default waiting period
apex_mail.push_queue;

-- save record into email_send_history table

insert into email_send_history(mesg_txt,mesg_type,log_timestamp)
values ('Mail for survey reminder sent successfully','REMINDER',systimestamp);

commit;
end loop;

exception
when others then
l_sql_err := SQLERRM;
l_sql_err := 'Failed sending mail to '|| p_username|| SQLERRM;
insert into sf_email_send_history(mesg_txt,mesg_type,log_timestamp)
values (l_sql_err,'REMINDER',systimestamp);
commit;

end send_reminder;

2) Create the following packaged procedures:
procedure start_reminder_job(
p_sgid IN NUMBER,
p_flow_id IN VARCHAR2
)
is
l_job_id number;
l_sql_err varchar2(500);
l_next date;
l_protocol VARCHAR2(500) default 'http://';
BEGIN

l_next := sysdate;
if ( owa_util.get_cgi_env('SERVER_PORT') = '443' ) then
l_protocol := 'https://';
end if;
l_protocol := l_protocol || owa_util.get_cgi_env('SERVER_NAME') || ':' ||
owa_util.get_cgi_env('SERVER_PORT') ||
owa_util.get_cgi_env('SCRIPT_NAME') || '/';

DBMS_SCHEDULER.create_job (
job_name => 'your_job_name',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN your_package.send_reminder(p_sgid => ' || p_sgid ||
',p_protocol => ''' || l_protocol ||''',p_flow_id => ''' ||p_flow_id || ''' ); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=1',
end_date => NULL,
enabled => TRUE,
comments => 'Job sends an email in a period of 3 month, daily');

COMMIT;
l_sql_err := 'Job Started successfully ';
insert into email_send_history(mesg_txt,mesg_type,log_timestamp)
values (l_sql_err,'JOB',systimestamp);
commit;
exception
when others then
l_sql_err := 'Email Reminder Job could not be started : ' || SQLERRM || l_protocol ;
insert into email_send_history(mesg_txt,mesg_type,log_timestamp)
values (l_sql_err,'JOB',systimestamp);
commit;
end;


procedure stop_reminder_job
is
l_sql_err varchar2(1000);
begin
/*for c1 in (select job_name
from dba_scheduler_jobs
where job_name='your_job_name' )
loop*/
--dbms_job.remove(c1.job);
DBMS_SCHEDULER.DROP_JOB ( job_name => 'your_job_name');
l_sql_err := 'Job Stopped successfully ';
insert into email_send_history(mesg_txt,mesg_type,log_timestamp) values
(l_sql_err,'JOB',systimestamp);
/*end loop;*/
commit;
exception
when others then
l_sql_err := 'Job could not be stopped : ' || SQLERRM;
insert into email_send_history(mesg_txt,mesg_type,log_timestamp) values
(l_sql_err,'JOB',systimestamp);
commit;
end;


3)Create Start and Stop Reminder Job buttons which dynamically appear on the page.
To accomplish:
Create two Regions: Start job and Stop Job => Modify Region Definition->Conditions
In the "Condition Type" choose "NOT Exists (SQL query returns no rows)" => under "Expression 1" enter a select query "select * from dba_scheduler_jobs where job_name='your_job_name'"

4)Finally, start and stop job processes need to be called from the respective buttons:
On the processes "Source" add the following:
begin
your_package.start_reminder_job(v('WORKSPACE_ID'),:APP_ID);
end;

begin
your_package.stop_reminder_job(v('WORKSPACE_ID'),:APP_ID);
end;

Voila, job is created!

SQL report

I have experienced problems reporting ~400 columns. In particular, when I tried to create a SQL report and later modify its parameters I would receive the following error message:

ORA-20505: Error in DML: p_rowid=4556014883360673, p_alt_rowid=ID, p_rowid2=994324116441574, p_alt_rowid2=SECURITY_GROUP_ID. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "EE3D54593890D86AD05465D77CE37E19" application checksum = "0"

Even though it was not possible to overcome the problem completely, I have found the following solution to create the SQL report anyway.

SOLUTION:
After initial creation of the report, I had to return to the Region-> Region Definitions tab-> Source of the report.
Under the Region Source I have changed to "Use Generic Column Names (parse query at runtime only)" to activate "Maximum number of generic report columns:" and changed it to 400.