Sample configuration of stored procedures for Oracle

Guidelines for configuring stored procedures

• recommends using Oracle Automatic Memory Management (AMM). For more information, go to http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm.
• Create stored procedures by using a database utility, such as SQL Plus.
• Schedule the sp_runid procedure to run at least 10 minutes before the other scripts.

Example for creating a run identifier

The following example illustrates how to create a job and generate a run identifier. The example also illustrates the job ID when the job completes.

The example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014.

declare 
jobno number; 

BEGIN 
DBMS_JOB.submit (job =>:jobno, 
what => 'sp_runid;', 
next_date => to_date('29-Nov-2014 21:00','DD-MON-YYYY HH24:MI' ), 
interval => 'sysdate+1'); 
commit; 
END; 
/ 

Example for processing email contact data

The following example shows how to schedule a batch job to process contact data. The job runs at 21:10 hours every day.

declare 
jobno number; 

BEGIN 
DBMS_JOB.submit (job =>:jobno, 
what => 'sp_populate_mailing_contacts;', 
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ), 
interval => 'sysdate+1'); 
commit; 
END; 
/ 

Example for processing email response data

The following example shows how to schedule a batch job to process response data. The job runs at 21:10 hours every day.

declare 
jobno number; 

BEGIN 
DBMS_JOB.submit (job =>:jobno, 
what => 'sp_populate_mailing_responses;', 
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ), 
interval => 'sysdate+1'); 
commit; 
END; 
/ 

Example for processing SMS contact data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014.

BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_SMS_contacts;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/

Example for processing SMS response data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014.

BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_SMS_responses;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/

Example for processing WhatsApp contact data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014.

BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_WhtsApp_Contacts;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/

Example for processing WhatsApp response data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014.

BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_WhtsApp_Responses;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/

Example for processing Mobile response data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014.

BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_mobile_Responses;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/