Sample configuration of stored procedures for Oracle

Use the following guidelines when you configure stored procedures for the Oracle database.

Guidelines for configuring stored procedures

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

Important: The SMS feature is not a part of the default Reports offering, and you must buy a license for the feature separately. However, the delta placement takes place regardless of whether you bought the SMS feature.

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

Important: The WhatsApp feature is not a part of the default Reports offering, and you must buy a license for the feature separately. However, the delta placement takes place regardless of whether you bought the WhatsApp feature.

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;
/