I've been playing with CROSS APPLY and a text splitting function. In our case it's named str2tbl(). But it's basically the same thing everyone has written, at least once.
with j as (
select /*+ materialize */ owner, job_name, repeat_interval
from dba_scheduler_jobs
where schedule_type= 'CALENDAR'
and rownum <= 10
)
select *
from j
cross apply (
select column_value
from table(str2tbl(repeat_interval, ';')) t
where ltrim(t.column_value) is not null
) det;
| OWNER | JOB_NAME | REPEAT_INTERVAL | COLUMN_VALUE |
| YOU | JOB1 | FREQ=HOURLY;INTERVAL=2 | FREQ=HOURLY |
| YOU | JOB1 | FREQ=HOURLY;INTERVAL=2 | INTERVAL=2 |
| YOU | JOB2 | freq=DAILY; | freq=DAILY |
| YOU | JOB3 | FREQ=DAILY;BYHOUR=16 | FREQ=DAILY |
| YOU | JOB3 | FREQ=DAILY;BYHOUR=16 | BYHOUR=16 |
And then column_value can be decomposed into key/value pairs, via instr and substr.
with j as (
select /*+ materialize */ owner, job_name, repeat_interval
from dba_scheduler_jobs
where schedule_type= 'CALENDAR'
and rownum <= 10
)
select *
from j
cross apply (
select substr(t.column_value,1,instr(t.column_value,'=')-1) as repeat_interval_key,
substr(t.column_value,instr(t.column_value,'=')+1) as repeat_interval_value
from table(str2tbl(repeat_interval, ';')) t
where ltrim(t.column_value) is not null
) det;
| OWNER | JOB_NAME | REPEAT_INTERVAL | REPEAT_INTERVAL_KEY | REPEAT_INTERVAL_VALUE |
| YOU | JOB1 | FREQ=HOURLY;INTERVAL=2 | FREQ | HOURLY |
| YOU | JOB1 | FREQ=HOURLY;INTERVAL=2 | INTERVAL | 2 |
| YOU | JOB1 | freq=DAILY; | freq | DAILY |
| YOU | JOB2 | FREQ=DAILY;BYHOUR=16 | FREQ | DAILY |
| YOU | JOB2 | FREQ=DAILY;BYHOUR=16 | BYHOUR | 16 |
| YOU | JOB3 | freq=MINUTELY; INTERVAL=15; | freq | MINUTELY |
| YOU | JOB3 | freq=MINUTELY; INTERVAL=15; | INTERVAL | 15 |
| YOU | JOB4 | freq=MINUTELY; INTERVAL=30; | freq | MINUTELY |
| YOU | JOB4 | freq=MINUTELY; INTERVAL=30; | INTERVAL | 30 |
And that in turn can be pivoted.
with j as (
select /*+ materialize */ owner, job_name, upper(repeat_interval) repeat_interval
from dba_scheduler_jobs
where schedule_type= 'CALENDAR'
and rownum <= 10
)
select *
from j
cross apply (
select substr(t.column_value,1,instr(t.column_value,'=')-1) as repeat_interval_key,
substr(t.column_value,instr(t.column_value,'=')+1) as repeat_interval_value
from table(str2tbl(repeat_interval, ';')) t
where ltrim(t.column_value) is not null
) det
pivot (
max(repeat_interval_value) as val
for (repeat_interval_key) in ('FREQ' as freq, 'INTERVAL' as interval, 'BYHOUR' as byhour)
);
| OWNER | JOB_NAME | REPEAT_INTERVAL | FREQ_VAL | INTERVAL_VAL | BYHOUR_VAL |
| YOU | JOB1 | FREQ=MINUTELY; INTERVAL=30; | MINUTELY | 30 |
| YOU | JOB2 | FREQ=MINUTELY; INTERVAL=15; | MINUTELY | 15 |
| YOU | JOB3 | FREQ=DAILY;BYHOUR=16 | DAILY | 16 |
| YOU | JOB4 | FREQ=DAILY; | DAILY |
| YOU | JOB5 | FREQ=HOURLY;INTERVAL=2 | HOURLY | 2 |
A real use would include all of the other BYMINUTE, etc columns.