How to create a temporary job

Basically, you can create a single scheduler job by the following syntax. This job executes and drops itself automatically.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB 
    (  
      JOB_NAME      =>  'TEMP_JOB',  
      JOB_TYPE      =>  'PLSQL_BLOCK',  
      JOB_ACTION    =>  'BEGIN PRC_TEST; END;',  
      START_DATE    =>  SYSDATE,  
      ENABLED       =>  TRUE,  
      AUTO_DROP     =>  TRUE,  
      COMMENTS      =>  'TEMP JOB'
    );
END;

If your procedure that used in the scheduler job has parameter. You can set it with the below code snippet.

DECLARE

    V_JOB_NAME VARCHAR2(100); 

BEGIN

    --In order to generate a new job number 
    V_JOB_NAME := SYS.DBMS_SCHEDULER.GENERATE_JOB_NAME('TEMP_JOB_');

    --Suppose you have the following procedure that accepts one parameter
    --PRC_EMP(P_NAME => 'EMPLOYEE NAME')
    SYS.DBMS_SCHEDULER.CREATE_JOB(
           job_name            => V_JOB_NAME 
          ,number_of_arguments => 1
          ,job_type            => 'STORED_PROCEDURE'
          ,job_action          => 'PRC_EMP'
          ,start_date          => SYSTIMESTAMP + INTERVAL '2' SECOND
          ,auto_drop           => TRUE
    );

    --In order to set the employee name
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        job_name              => V_JOB_NAME,
        argument_position     => 1,
        argument_value        => 'EMPLOYEE NAME'
    );
    
    SYS.DBMS_SCHEDULER.ENABLE(V_JOB_NAME);
            
END;
Leave a Reply

Your email address will not be published. Required fields are marked *