NetBackup IT Analytics System Administrator Guide

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;