How to create an Oracle Batch Job using Oracle SQL Developer tool.

 

In order to configure oracle batch jobs, I used Oracle SQL Developer tool. Below are some easy steps to create an oracle batch job and expecting help for newbies:

• Login with sys user in Oracle SQL Developer console.
• In the left pane, select Connections tab and navigate till Scheduler.
• Expand it by clicking on + sign and select Jobs.
• Right click on Jobs and select New Job(Wizard).
• Create a batch job as explained/shown below (Refer SCREENSHOT 1).

 

                                                           SCREENSHOT 1

• Job Name: OracleBatchJob
• Enable: Checked
• Description: As per user/Blank
• Job Class: You can select class as per your requirement. Here I have used SYS.DEFAULT_JOB_CLASS
• Type of Job: Select PL/SQL Block from drop down list (you can also select chain, stored procedure, Named program or executables) and depending upon types of job, you can provide further inputs (here I have selected PL/SQL Block and added below sql query) :

 

DECLARE

xml_char xmltype;

cursor c_cursor is

select UNIQUEID, ALLOWCHG

from xmltable ( '/xml/ROWSET/ROW'

                passing xml_char

                columns

                    "UNIQUEID" varchar(30) path '@UNIQUEID',

                    "ALLOWCHG" varchar(30) path '@ALLOWCHG'

              );

BEGIN

xml_char := xmltype.createXML('<xml><ROWSET><ROW UNIQUEID="All0" ALLOWCHG="0"/><ROW UNIQUEID="All1" ALLOWCHG="1"/></ROWSET></xml>');

for i in c_cursor loop 

  dbms_output.put_line('UNIQUEID: ' || i.uniqueid || ' ALLOWCHG: '|| i.allowchg);

end loop;

sys.dbms_lock.sleep (60);

END;

 

• When to execute Job: Select Repeating from drop down
• Repeat Interval:     FREQ=MINUTELY
• Start Date:            2013-10-15 11:00:00 IST
• End Date:              null

• Click on Next.
• Select Destination as local, Remote or Multiple. By default Local is selected. I have used Local option here. (SCREENSHOT 2).

 

                                              SCREENSHOT 2

 

• Click on Next and come to Notification page (SCREENSHOT 3). In this page you can configure your mailing details.

                                               SCREENSHOT 3

• Click Next and come to Properties page (SCREENSHOT 4). Keep all default options.

                                           SCREENSHOT 4

• Click on Finish

 

Now we have finished with batch job scheduling.  You can also check if the job is running or not by executing below query.

select * from dba_scheduler_running_jobs where  JOB_NAME =  '<you batch job name>'; and it should give an output as below (Refer SCREENSHOT 5)

 

NOTE: Above details has been provided to the users who seek help in creating oracle batch jobs. I have provided some inputs from internet (websites and blogs) and consolidated it here.