Skip to Main Content

SQL Developer

ORA-01830 while trying to use date parameter in child report

User_S1J6PFeb 28 2022

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
 (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
  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
  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

This post has been answered by User_2DKLA on Feb 28 2022
Jump to Answer
Post Details
Added on Feb 28 2022