DBMS Scheduler Schedule your Jobs from the database

How create job works.

BEGIN
   DBMS_SCHEDULER.create_job
                     (job_name             => 'XX_HR_TRAINING_PLANS',
                      job_type             => 'PLSQL_BLOCK',
                      job_action           => 'BEGIN XXHR_TRAINING_PLANS_HIST; END;',
                      start_date           => '07-Oct-11 05.52.00PM Asia/dubai',
                      repeat_interval      => 'FREQ=SECONDLY;INTERVAL=25',
                     -- end_date             => '15-SEP-08 1.00.00AM US/Pacific',
                      enabled              => TRUE,
                      comments             => 'XX Job Training Plans'
                     );
END;

Create Job can be done from apps user.

Purge Log:

BEGIN
DBMS_SCHEDULER.PURGE_LOG(WHICH_LOG => 'JOB_LOG',JOB_NAME => 'APPS.XX_HR_TRAINING_PLANS');
END;

Drop Job:

begin
 DBMS_SCHEDULER.drop_job('XX_HR_TRAINING_PLANS');
end;
Table to check the scheduled Job entry
select * from USER_SCHEDULER_JOBS or
select * from DBA_SCHEDULER_JOBS

 

How to extract more that one records from xml file

Following is the example where vacancy has two recruiters.

select x.*,x1.*
from hr_api_transactions t,
     xmltable('//PerAllVacanciesEORow'
              passing xmltype(transaction_document)
              columns requisition_id  number        path 'RequisitionId',
                      organization_id number        path 'OrganizationId',
                      name            varchar2(100) path 'Name',
                      creation_date   varchar2(30)  path 'CreationDate'                     
             ) x,
      xmltable('//IrcRecTeamMembersEORow'
              passing xmltype(transaction_document)
              columns PersonId  varchar2(10)        path 'PersonId'                                        
             ) x1
where t.transaction_ref_table = 'PER_ALL_VACANCIES'
and t.transaction_ref_id = 146