Friday, September 18, 2009

Parallel PL/SQL execution

CREATE procedure refresh_employees
authid definer
is
begin
execute immediate 'truncate table vb_emp_no';
insert into vb_emp_no
select distinct employee_number from remote_view@db_link;
commit;
execute immediate 'truncate table employees';

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P1_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P1''); end;',
comments => 'Thread 1 to refresh employees',
enabled => true,
auto_drop => true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P2_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P2''); end;',
comments => 'Thread 2 to refresh employees',
enabled => true,
auto_drop => true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P3_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P3''); end;',
comments => 'Thread 3 to refresh employees',
enabled => true,
auto_drop = true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P4_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P4''); end;',
comments => 'Thread 4 to refresh employees,
enabled => true,
auto_drop => true);

end;
/

No comments:

Post a Comment