Hello,
I am using SQL Developer 20.4 and am trying to create a Master-Detail Report based on ASH data. The master report is a graph and child report is just a table of data. However, child report fails with ORA-01830.
Below are the SQL statements that I am using
Master Report:
select dt, SESSION_STATE, count(*)/count(distinct inst_id||session_id||session_serial#) num_sessions, min(sample_time) FROM_TIME, max(sample_time) TO_TIME
from
(select sample_time, inst_id, session_id, session_serial#, trunc(sample_time, 'HH24') + (trunc(to_number(to_char(sample_time, 'MI'))/5)*5)/(24*60) dt
, CASE WHEN session_state = 'ON CPU' THEN 'CPU'
WHEN session_state = 'WAITING' THEN wait_class
END SESSION_STATE
from GV$ACTIVE_SESSION_HISTORY
where session_type = 'FOREGROUND'
and sample_time between sysdate - (:num_mins/(24*60)) and sysdate
)
group by dt, SESSION_STATE
order by dt
Child Report:
select sample_time, inst_id, session_id, session_serial#, program
, CASE WHEN session_state = 'ON CPU' THEN 'CPU'
WHEN session_state = 'WAITING' THEN event
END as event
from GV$ACTIVE_SESSION_HISTORY
where session_type = 'FOREGROUND'
and sample_time between to_timestamp(:DT, 'YYYY-MM-DD HH24:MI:SS.FF') and to_timestamp(:DT, 'YYYY-MM-DD HH24:MI:SS.FF')+(5/(24*60))
and wait_class = :SESSION_STATE
When I checked the "Logging" window for the sql that gets generated for the child report then it does show the DT parameter having "2022-02-28 12:30:00.0" value.
The sql for child report works fine when I try to run it from worksheet.
I am at a loss as to what do I need to change in order to fix this?
Thanks in advance