Sample .sql file (setup_ora_job.sql) to set up an automatic job
DECLARE
jobName user_scheduler_jobs.job_name%TYPE := NULL;
BEGIN
----------------------------------------------------------------------
-- Move new clients whose server name ends with 'ORA' into the 'database' host group
-- Frequency: Every day at 5am (Portal Time)
----------------------------------------------------------------------
jobName := dba_package.getSchedulerJobName('moveOracleClients');
IF (jobName IS NOT NULL AND LOWER(jobName) <> LOWER('moveOracleClients')) THEN
DBMS_OUTPUT.PUT_LINE('setupInactivePolicyClients exists with default name
'|| jobName ||' hence will be removed and recreated.');
DBMS_SCHEDULER.DROP_JOB(job_name => jobName);
jobName := NULL;
END IF;
IF jobName IS NULL THEN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'moveOracleClients',
job_type => 'PLSQL_BLOCK',
job_action => 'server_mgmt_pkg.moveOrCopyClients(''/Aptare'',
''/Aptare/database'',''*ORA'', 1);', -- What to run
start_date => SYSDATE + (5/24),
-- First run is 5am server time
repeat_interval => 'TRUNC(SYSDATE+1,''DD'') + (5/24)',
-- Next run is 5am each subsequent day
enabled => TRUE);
ELSE
DBMS_OUTPUT.PUT_LINE('setupInactivePolicyClients exists and will be
altered with updated version.');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => jobName,
attribute => 'job_type',
value => 'PLSQL_BLOCK'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => jobName,
attribute => 'job_action',
value => 'server_mgmt_pkg.moveOrCopyClients(''/Aptare'',
''/Aptare/database'',''*ORA'', 1);'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => jobName,
attribute => 'repeat_interval',
value => 'TRUNC(SYSDATE+1,''DD'') + (5/24)'
);
END IF;
DBMS_OUTPUT.put_line('moveOracleClients job set to run at 5am every day');
END;
/
quit;
