Configure Data Scheduler Using Oracle Database Cloud Service-based
Cloud Scheduler Package
The CLOUD-SCHEDULER package (present in Oracle DBCS) is a wrapper that exactly mirrors the standard DBMS_SCHEDULER package in an Oracle database. The scheduled job is a REST web service that performs data synchronization. The APEX_WEB_SERVICE package contains
functions to invoke and parse responses from web services.
A stored procedure that invokes the secured REST web service is as follows.
create or replace procedure "RESTWS_REQUEST"
is
l_clob clob;
l_buffer varchar2(32767);
begin
l_clob := apex_web_service.make_rest_request(
p_url =>
'https://java-trialxxxx.java.us1.oraclecloudapps.com/ScheduledRESTWebServiceEndpoi
nt/jersey/myRESTWebService',
p_http_method => 'GET',
p_username => 'AdminAccountGoesHere',
p_password => 'AdminaccountpasswdGoesHere'
);
l_buffer := dbms_lob.substr(l_clob, 32000, 1 );
dbms_output.put_line(l_buffer);
end;
Once the stored procedure is compiled and executed successfully, it can be scheduled
or run ad hoc using CLOUD_SCHEDULER.
A sample SQL code to invoke a CLOUD_SCHEDULER package that schedules the
above stored procedure with a defined frequency is as follows:
BEGIN
CLOUD_SCHEDULER.CREATE_JOB (
job_name => 'RESTSchedule1',
job_type => 'STORED_PROCEDURE',
job_action => 'RESTWS_REQUEST',
start_date => sysdate,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
end_date => sysdate+2,
enabled => TRUE,
comments => 'Test scheduler every 5 mins');
END;
Note: job_action corresponds to the name of the store procedure in
the sample above.
1) You can use the standard USER_SCHEDULER_% views to determine the status
and number of jobs you submitted:
Select * from user_scheduler_jobs;
2) To view results or log of each instance of an executed job:
Select * from USER_SCHEDULER_JOB_LOG;
3) Once a job is created or scheduled, CLOUD_SCHEDULER allows you to run that job immediately on an ad hoc request using
CLOUD_SCHEDULER.RUN_JOB (RESTWS_REQUEST).
4) You can disable the job in the scheduler by using cloud scheduler's DISABLE procedure using
CLOUD_SCHEDULER.disable (RESTWS_REQUEST).
