Forum Stats

  • 3,853,805 Users
  • 2,264,277 Discussions
  • 7,905,450 Comments

Discussions

Categories

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

User_S1J6P
User_S1J6P Member Posts: 34 Red Ribbon
in SQL Developer

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

Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • User_2DKLA
    User_2DKLA Member Posts: 49 Blue Ribbon
    Answer ✓

    Hi,

    If I read your SQL correctly, DT in the parent report is a TIMESTAMP(3).

    In that case, the bind value passed to the child report by SQL Developer would be a TIMESTAMP too—or possibly a DATE; you might trace the session to find out which. In any case, it is most likely not a VARCHAR2.

    Therefore, in the child report, you must not subject the :DT bind value to the TO_TIMESTAMP function, as this causes a double conversion: first an implicit conversion from date/timestamp to character, followed by an explicit conversion back to timestamp. Due to NLS settings in your session (which might be changed without you noticing it immediately, BTW), this pattern could frequently result in unexpected conversion exceptions, e.g. ORA-01830.

    Solution: in the child report, just cast the :DT bind variable into the desired timestamp type, e.g.

    CAST(:DT as TIMESTAMP(3))

    Hope this helps.

    Regards,

    User_S1J6P
    · Share on TwitterShare on Facebook
  • User_2DKLA
    User_2DKLA Member Posts: 49 Blue Ribbon
    Answer ✓

    If it reads as "FROM_TIME"=12, actually that means that NULL is passed.

    My guess is, if the parent report is a Chart, you can no longer use just any column from the SQL query in the parent as a bind value in the child report—as you would if the parent report was a Table.

    In practice, the following works:

    • Passing the value of the X axis: the DT column in your example
    • Passing values of bind variables used in the SQL query of the parent report

    (Unsure whether values on the Y axis may be passed or not.)

    So, as far as I understand, you'll probably have to work around that, possibly be recomputing the values of FROM_TIME and TO_TIME in the SQL query of the child report.

    Regards,

    User_S1J6P
    · Share on TwitterShare on Facebook

Answers

  • User_2DKLA
    User_2DKLA Member Posts: 49 Blue Ribbon
    Answer ✓

    Hi,

    If I read your SQL correctly, DT in the parent report is a TIMESTAMP(3).

    In that case, the bind value passed to the child report by SQL Developer would be a TIMESTAMP too—or possibly a DATE; you might trace the session to find out which. In any case, it is most likely not a VARCHAR2.

    Therefore, in the child report, you must not subject the :DT bind value to the TO_TIMESTAMP function, as this causes a double conversion: first an implicit conversion from date/timestamp to character, followed by an explicit conversion back to timestamp. Due to NLS settings in your session (which might be changed without you noticing it immediately, BTW), this pattern could frequently result in unexpected conversion exceptions, e.g. ORA-01830.

    Solution: in the child report, just cast the :DT bind variable into the desired timestamp type, e.g.

    CAST(:DT as TIMESTAMP(3))

    Hope this helps.

    Regards,

    User_S1J6P
    · Share on TwitterShare on Facebook
  • User_S1J6P
    User_S1J6P Member Posts: 34 Red Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16827255#Comment_16827255

    Thanks. It does help. I guess I was just wrongly assuming that DATE/TIMESTAMP parameters only get passed in as VARCHAR2.

    If I can be little bit greedy, can you help me understand why I seem to unable to use FROM_TIME and TO_TIME column values in the output of sql for Master Report as parameters in the Child Report? For some reason, when I try to use FROM_TIME and TO_TIME in the sql for Child report, it reports the value of 12 instead of the date range that I am expecting. What am I missing?

    Thanks in advance

    · Share on TwitterShare on Facebook
  • User_2DKLA
    User_2DKLA Member Posts: 49 Blue Ribbon
    Answer ✓

    If it reads as "FROM_TIME"=12, actually that means that NULL is passed.

    My guess is, if the parent report is a Chart, you can no longer use just any column from the SQL query in the parent as a bind value in the child report—as you would if the parent report was a Table.

    In practice, the following works:

    • Passing the value of the X axis: the DT column in your example
    • Passing values of bind variables used in the SQL query of the parent report

    (Unsure whether values on the Y axis may be passed or not.)

    So, as far as I understand, you'll probably have to work around that, possibly be recomputing the values of FROM_TIME and TO_TIME in the SQL query of the child report.

    Regards,

    User_S1J6P
    · Share on TwitterShare on Facebook