PL/SQL: Sample code for submitting a DBMS_SCHEDULER jobs

 In this post I will provide a sample simple code to submit a dbms_scheduler job.

• Create a procedure. You can create a procedure inside a package also if needed.

 --create table   
 CREATE TABLE xxaj_debug (  
              ,id         NUMBER  
              ,msg        VARCHAR2(4000)  
              ,call_stack VARCHAR2(4000)  
              ,date_stamp DATE  
             );  
 --create sequence   
 CREATE SEQUENCE xxaj_seq  
  START WITH 1  
  INCREMENT BY 1; 
  
 --create procedure   
 CREATE OR REPLACE PROCEDURE xxaj_proc (msg VARCHAR2)  
 IS  
  PRAGMA autonomous_transaction;  
 BEGIN  
   INSERT INTO xxaj_debug  
   VALUES ( xxaj_seq.NEXTVAL  
           ,msg  
           ,dbms_utility.format_call_stack  
           ,SYSDATE);  
   COMMIT;  
 END xxaj_proc;   

You can execute the below code to submit the above procedure as a dbms job.

            Parameters

                job_name      : you can give any name. Give something meaningful so that you can filter with 

                                        the  name when trouble shooting

                job_type        : in this case it is a stored procedure

                job_action     : in this case we are executing a stored procedure, so specify the procedure                                                  name

                enabled         : we want the job to run straight away, so set to true  

 BEGIN  
   dbms_scheduler.create_job (job_name   => 'XXAJ_JOB_'||TO_CHAR(SYSDATE, 'DDMMHH24MI')  
                             ,job_type   => 'STORED_PROCEDURE'  
                             ,job_action => 'xxaj_proc'  
                             ,enabled    => TRUE);  
 END;   

There are lot more options on the job submission. I will create a separate blog with different variances. 

You can also refer the below oracle site for the details.

Refer:Scheduler (DBMS_SCHEDULER) in Oracle Database 10g Onward

Feel free to point out if anything is missing/wrong in this blog.