8 Replies Latest reply: May 28, 2013 12:44 AM by TimMc RSS

    INTERVAL DAY TO SECOND

    TimMc
      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
          swesley_perth
          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
            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
              swesley_perth
              Paul has a good response here
              using interval day to second ah the APEX 4.1
              • 4. Re: INTERVAL DAY TO SECOND
                TimMc
                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
                  swesley_perth
                  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
                    .
                    • 7. Re: INTERVAL DAY TO SECOND
                      TimMc
                      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
                        swesley_perth
                        Two steps forward, one step back...