Find the order of tasks from the schedule table?

Question

I am trying to write a batch job schedule based on a table stored in an Oracle database.

I really don't know where to start.

There are only two corresponding columns in the table:

JOBSTREAM | FOLLOW_JOBSTREAM 

JOBSTREAM is the name of the job thread that will be launched. FOLLOW_JOBSTREAM is the name of the workflow that must be completed before the workflow starts. In some cases, a single JOBSTREAM entry has multiple FOLLOW_JOBSTREAM entries.

For instance:

 JS001 JS028 JS001A JS039 JS001B JS001A JS002 JS001A JS002 JS001B 

Is there any way to sort them using PL / SQL?

Thanks!

Answer 1

First we need a complete list of tasks from both columns with hierarchical links:

  select all_jobs.job_id as jobstream, job_depends.follow_jobstream as parent_jobstream from ( select jobstream job_id from job_list union select follow_jobstream job_id from job_list ) all_jobs, job_list job_depends where job_depends.jobstream (+) = all_jobs.job_id 

Then determine the depth of each job.

 with full_job_list as ( select all_jobs.job_id as jobstream, job_depends.follow_jobstream as parent_jobstream from ( select jobstream job_id from job_list union select follow_jobstream job_id from job_list ) all_jobs, job_list job_depends where job_depends.jobstream (+) = all_jobs.job_id ) select CONNECT_BY_ROOT jobstream as jobstream, level as lvl, CONNECT_BY_ROOT parent_jobstream as preceding_jobstream, sys_connect_by_path(jobstream, '<-') as path_illustration from full_job_list connect by prior parent_jobstream = jobstream 

And finally, sort tasks by depth. Work at the same depth can work in any order.

 with full_job_list as ( select all_jobs.job_id as jobstream, job_depends.follow_jobstream as parent_jobstream from ( select jobstream job_id from job_list union select follow_jobstream job_id from job_list ) all_jobs, job_list job_depends where job_depends.jobstream (+) = all_jobs.job_id ) select jobstream, depth, preceding_jobstream from ( select distinct jobstream, ( last_value(lvl ignore nulls) over ( partition by jobstream order by lvl rows between unbounded preceding and unbounded following ) ) as depth, ( last_value(preceding_jobstream ignore nulls) over ( partition by jobstream order by lvl rows between unbounded preceding and unbounded following ) ) as preceding_jobstream from ( select CONNECT_BY_ROOT jobstream as jobstream, level as lvl, CONNECT_BY_ROOT parent_jobstream as preceding_jobstream from full_job_list connect by prior parent_jobstream = jobstream ) ) order by depth, jobstream 

SQL Fiddle

Refresh Fixed to get a complete list of tasks and add previous tasks.

Update2 Fixed a bug with the direction of the path.

Answer 2

Hierarchical queries can help you in such cases:

 SELECT JOBSTREAM, FOLLOW_JOBSTREAM FROM <jobstreams> START WITH FOLLOW_JOBSTREAM NOT IN (SELECT JOBSTREAM FROM jobstreams ) CONNECT BY PRIOR JOBSTREAM = FOLLOW_JOBSTREAM 

SQL Fiddle

Answer 3

There is an official Oracle method for combining multiple tasks into a schedule.

DBMS_Scheduler allows you to define task chains with dependencies based on the exit condition of the previous steps in the chain. This is probably much more flexible and complex than the home method, so I would look at its migration in the future.