This discussion is archived
8 Replies Latest reply: May 27, 2013 10:44 PM by TimMc RSS

INTERVAL DAY TO SECOND

TimMc Newbie
Currently Being Moderated
I have one page with an interactive report (Page 10) and another with a form to update each entry (Page 20).

The table that these pages refer to have a column called MY_INTERVAL_COL of type INTERVAL DAYS TO SECONDS.

I can successfully display the contents of MY_INTERVAL_COL by extracting DAYS, MINUTES, and SECONDS on Page 10:

TO_CHAR(EXTRACT(DAY FROM MY_INTERVAL_COL)) AS MY_DAYS,
TO_CHAR(EXTRACT(HOUR FROM MY_INTERVAL_COL)) AS MY_HOURS,
TO_CHAR(EXTRACT(MINUTE FROM MY_INTERVAL_COL)) AS MY_MINUTES,

However, the Automated Row Fetch process appears to ignore columns of type INTERVAL DAYS TO SECONDS on Page 20.

:P20_INTERVAL (database column MY_INTERVAL_COL is used)
:P20_DAYS (trying to convert :P20_INTERVAL using TO_CHAR(EXTRACT(DAY...
:P20_HOURS (and so on...)
:P20_MINUTES

Does Automated Row Fetch ignore columns of type INTERVAL DAYS TO SECONDS?

Oracle 11gR2 used. Apex 4.1

Tim.
  • 1. Re: INTERVAL DAY TO SECOND
    scott.wesley Guru
    Currently Being Moderated
    That's an interesting question.

    Would you be able to supply the exact source details of P20_DAYS?

    It may be a simple case of the interval being implicitly converted to a string, then unable to emplicitly recognise it as an interval convert it back when calculating days.

    Scott
  • 2. Re: INTERVAL DAY TO SECOND
    TimMc Newbie
    Currently Being Moderated
    P20_DAYS is configured as follows:

    Text Field
    Source Used: Always, replacing any existing value in session state
    Source Type: PL/SQL Expression or Function
    Source value or expression: TO_CHAR(EXTRACT(DAY FROM :P20_INTERVAL))
    Runtime Error: ORA-06550: line 1, column 51: PLS-00306: wrong number or types of arguments in call to ' SYS$EXTRACT_FROM' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

    P20_INTERVAL is configured as follows:

    Text Field
    Source Used: Always, replacing any existing value in session state
    Source Type: Database Column
    Source value or expression: MY_INTERVAL_COL

    I'm not sure why MY_INTERVAL_COL isn't being cast to a string. It appears to be null when I edit a record on Page 20. I've tried TO_CHAR(:P20_INTERVAL) in the Post Calculation Computation field but it's still blank.

    I can accept user input for P20_DAYS and use a computation to convert the text into an INTERVAL, and APEX even displays the string representation of it if there's a validation error. It just won't cast to a string automatically when displaying the item with a value fetched using Automated Row Fetch.

    Tim.
  • 3. Re: INTERVAL DAY TO SECOND
    scott.wesley Guru
    Currently Being Moderated
    Paul has a good response here
    using interval day to second ah the APEX 4.1
  • 4. Re: INTERVAL DAY TO SECOND
    TimMc Newbie
    Currently Being Moderated
    Thanks Scott. I read through Paul's posts earlier, and decided to post as a last resort, to get to this point:

    1. The interactive report successfully displays INTERVAL DAY TO SECOND in three columns by using TO_CHAR(EXTRACT(DAY FROM MY_INTERVAL_COL)) AS DAYS, TO_CHAR(EXTRACT(HOUR FROM MY_INTERVAL_COL)) AS HOURS, TO_CHAR(EXTRACT(MINUTE FROM MY_INTERVAL_COL)) AS MINUTES

    3. The form successfully saves as INTERVAL DAY TO SECOND by using NUMTODSINTERVAL(:P20_DAYS, 'DAY')

    The part that I haven't figured out/found is:

    2. Display the INTERVAL DAY TO SECOND in three Text Fields as Days, Hours and Minutes (using Automated Row Fetch by default, and possibly by using TO_CHAR(EXTRACT()) like the interactive report to display each field as a number).


    The issue seems to be that the Database Column MY_INTERVAL_COL appears to be NULL despite being a valid column...


    Tim.

    Edited by: TimMc on 28/05/2013 13:37
  • 5. Re: INTERVAL DAY TO SECOND
    scott.wesley Guru
    Currently Being Moderated
    The report is doing all the work in the SQL, while the form will be attempting to convert a (non supported) value already retrieved from the DB

    Might I suggest sourcing from a key-preserved view, instead of direct from the table?
  • 6. Re: INTERVAL DAY TO SECOND
    TimMc Newbie
    Currently Being Moderated
    .
  • 7. Re: INTERVAL DAY TO SECOND
    TimMc Newbie
    Currently Being Moderated
    Thanks Scott.

    I considered using virtual columns and have now implemented the form using a key-preserved view. Both approaches bring up the correct values for the Days, Hours, Minutes form fields.

    I'm now trying to figure out whether I can remove P20_DAYS, P20_HOURS and P20_MINUTES after submitting the form so that Automatic Row Processing won't attempt to update those columns...

    Tim.
  • 8. Re: INTERVAL DAY TO SECOND
    scott.wesley Guru
    Currently Being Moderated
    Two steps forward, one step back...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points