Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Modal Dialog issue on using File Browse

GrrshRJan 24 2018 — edited Jan 24 2018

Hello,

I am getting an error "Application 2900 Dialog page 12 cannot be rendered successfully. Ensure the page template in use on page 12 is template type "Dialog page", with appropriate JavaScript dialog initialization, dialog closure and dialog cancel code defined" on using a File Browse in the Modal Dialog Page.

If i close the error page and check the table, it is saving the file in the table.

I am currently using APEX 5

When i do not add any file using File Browse, the Form works perfectly fine and successfully submits the form.

We are manually closing the Modal Dialog page after processing (There is no Branching or Close Dialog DA on Button)

Please help.

Comments

odie_63

I have to display output which is grouped based on month and its corresponding array values as json_ojbect.
Then you have to actually aggregate JSON objects per month in a JSON array : use aggregate function JSON_ARRAYAGG.
Final step is to consolidate all months in a single object, that's an aggregation too : use JSON_OBJECTAGG.
For example (just showing the last query step) :

SELECT json_objectagg(
     cal_mon
     value json_arrayagg(
         json_object(
          'date' value to_char(cal_dates,'DD-MON-YYYY')
         , 'val' value nvl(appointment_id,0)
         )
         order by cal_dates -- if necessary
        ) 
    ) Final_output
FROM get_json
GROUP BY cal_mon

You may also need to add some RETURNING CLOB clause here and there if the output is too large.

Paulzip

Maybe something like this (might need tweaking).


with
  cal_data as (
     select     first_date + level - 1   as cal_dates
     from       (select next_day(trunc(sysdate, 'MONTH') - 15, 'SUNDAY')  as first_date
                      , next_day(last_day(sysdate) - 1, 'SATURDAY')       as last_date
                 from   dual)
     connect by level <= last_date + 1 - first_date
  )
, data as (
    select c.*, a.*, to_number(to_char(cal_dates, 'dd')) day_of_week, to_char(cal_dates, 'fmMonth') month_name, to_number(to_char(cal_dates, 'MM')) month_num
    from   cal_data  c
    left join patient_appointments a on a.appointment_date >= c.cal_dates and a.appointment_date < c.cal_dates + 1
  ) 
select json_objectagg(
         month_name
       , json_arrayagg(
           json_object (
             'date' value day_of_week
           , 'val'  value nvl(appointment_id, 0)
           )
           order by day_of_week 
         )
       ) 
from data
group by month_num, month_name 
order by month_num
;

{
  "May" : [
    {
      "date" : 22,
      "val" : 0
    },
    {
      "date" : 23,
      "val" : 0
    },
    {
      "date" : 24,
      "val" : 0
    },
    {
      "date" : 25,
      "val" : 0
    },
    {
      "date" : 26,
      "val" : 0
    },
    {
      "date" : 27,
      "val" : 0
    },
    {
      "date" : 28,
      "val" : 0
    },
    {
      "date" : 29,
      "val" : 0
    },
    {
      "date" : 30,
      "val" : 0
    },
    {
      "date" : 31,
      "val" : 0
    }
  ],
  "June" : [
    {
      "date" : 1,
      "val" : 0
    },
    {
      "date" : 2,
      "val" : 0
    },
    {
      "date" : 3,
      "val" : 0
    },
    {
      "date" : 4,
      "val" : 0
    },
    {
      "date" : 5,
      "val" : 0
    },
    {
      "date" : 6,
      "val" : 0
    },
    {
      "date" : 7,
      "val" : 1
    },
    {
      "date" : 8,
      "val" : 0
    },
    {
      "date" : 9,
      "val" : 2
    },
    {
      "date" : 10,
      "val" : 3
    },
    {
      "date" : 11,
      "val" : 0
    },
    {
      "date" : 12,
      "val" : 0
    },
    {
      "date" : 13,
      "val" : 0
    },
    {
      "date" : 14,
      "val" : 0
    },
    {
      "date" : 15,
      "val" : 0
    },
    {
      "date" : 16,
      "val" : 0
    },
    {
      "date" : 17,
      "val" : 0
    },
    {
      "date" : 18,
      "val" : 0
    },
    {
      "date" : 19,
      "val" : 0
    },
    {
      "date" : 20,
      "val" : 0
    },
    {
      "date" : 21,
      "val" : 0
    },
    {
      "date" : 22,
      "val" : 0
    },
    {
      "date" : 23,
      "val" : 0
    },
    {
      "date" : 24,
      "val" : 0
    },
    {
      "date" : 25,
      "val" : 0
    },
    {
      "date" : 26,
      "val" : 0
    },
    {
      "date" : 27,
      "val" : 0
    },
    {
      "date" : 28,
      "val" : 0
    },
    {
      "date" : 29,
      "val" : 0
    },
    {
      "date" : 30,
      "val" : 0
    }
  ],
  "July" : [
    {
      "date" : 1,
      "val" : 0
    },
    {
      "date" : 2,
      "val" : 0
    }
  ]
}

 
Solomon Yakobson
Answer
WITH CAL_DATA AS (
                  SELECT  FIRST_DATE + LEVEL -1 AS CAL_DATES
                    FROM  (
                           SELECT  NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY') AS FIRST_DATE,
                                   NEXT_DAY(LAST_DAY(SYSDATE) - 1,'SATURDAY') AS LAST_DATE
                              FROM DUAL
                          )
                    CONNECT BY LEVEL <= LAST_DATE + 1 - FIRST_DATE
                 ),
     GET_JSON AS (
                  SELECT  TO_CHAR(C.CAL_DATES,'MON') CAL_MON,
                          C.CAL_DATES,
                          A.*
                    FROM      CAL_DATA C
                          LEFT JOIN
                              PATIENT_APPOINTMENTS A
                            ON (TRUNC(C.CAL_DATES) = TRUNC(A.APPOINTMENT_DATE))
                 )
SELECT  JSON_SERIALIZE(
                       JSON_OBJECT(
                                   KEY CAL_MON
                                   VALUE JSON_ARRAYAGG(
                                                    JSON_OBJECT(
                                                                KEY 'date'
                                                                VALUE TO_CHAR(CAL_DATES,'DD-MON-YYYY'),
                                                                KEY 'val'
                                                                VALUE NVL(APPOINTMENT_ID,0)
                                                               )
                                                    ORDER BY CAL_DATES
                                                   )
                                  )
                       RETURNING CLOB
                       PRETTY
                      ) Final_output
  FROM  GET_JSON
  GROUP BY CAL_MON
  ORDER BY CAL_MON
/

FINAL_OUTPUT
------------------------------------------------------------------------------------------
{
  "JUL" :
  [
    {
      "date" : "01-JUL-2022",
      "val" : 0
    },
    {
      "date" : "02-JUL-2022",
      "val" : 0
    }
  ]
}
{
  "JUN" :
  [
    {
      "date" : "01-JUN-2022",
      "val" : 0
    },
    {
      "date" : "02-JUN-2022",
      "val" : 0
    },
    {
      "date" : "03-JUN-2022",
      "val" : 0
    },
    {
      "date" : "04-JUN-2022",
      "val" : 0
    },
    {
      "date" : "05-JUN-2022",
      "val" : 0
    },
    {
      "date" : "06-JUN-2022",
      "val" : 0
    },
    {
      "date" : "07-JUN-2022",
      "val" : 1
    },
    {
      "date" : "08-JUN-2022",
      "val" : 0
    },
    {
      "date" : "09-JUN-2022",
      "val" : 2
    },
    {
      "date" : "10-JUN-2022",
      "val" : 3
    },
    {
      "date" : "11-JUN-2022",
      "val" : 0
    },
    {
      "date" : "12-JUN-2022",
      "val" : 0
    },
    {
      "date" : "13-JUN-2022",
      "val" : 0
    },
    {
      "date" : "14-JUN-2022",
      "val" : 0
    },
    {
      "date" : "15-JUN-2022",
      "val" : 0
    },
    {
      "date" : "16-JUN-2022",
      "val" : 0
    },
    {
      "date" : "17-JUN-2022",
      "val" : 0
    },
    {
      "date" : "18-JUN-2022",
      "val" : 0
    },
    {
      "date" : "19-JUN-2022",
      "val" : 0
    },
    {
      "date" : "20-JUN-2022",
      "val" : 0
    },
    {
      "date" : "21-JUN-2022",
      "val" : 0
    },
    {
      "date" : "22-JUN-2022",
      "val" : 0
    },
    {
      "date" : "23-JUN-2022",
      "val" : 0
    },
    {
      "date" : "24-JUN-2022",
      "val" : 0
    },
    {
      "date" : "25-JUN-2022",
      "val" : 0
    },
    {
      "date" : "26-JUN-2022",
      "val" : 0
    },
    {
      "date" : "27-JUN-2022",
      "val" : 0
    },
    {
      "date" : "28-JUN-2022",
      "val" : 0
    },
    {
      "date" : "29-JUN-2022",
      "val" : 0
    },
    {
      "date" : "30-JUN-2022",
      "val" : 0
    }
  ]
}
{
  "MAY" :
  [
    {
      "date" : "22-MAY-2022",
      "val" : 0
    },
    {
      "date" : "23-MAY-2022",
      "val" : 0
    },
    {
      "date" : "24-MAY-2022",
      "val" : 0
    },
    {
      "date" : "25-MAY-2022",
      "val" : 0
    },
    {
      "date" : "26-MAY-2022",
      "val" : 0
    },
    {
      "date" : "27-MAY-2022",
      "val" : 0
    },
    {
      "date" : "28-MAY-2022",
      "val" : 0
    },
    {
      "date" : "29-MAY-2022",
      "val" : 0
    },
    {
      "date" : "30-MAY-2022",
      "val" : 0
    },
    {
      "date" : "31-MAY-2022",
      "val" : 0
    }
  ]
}

SQL>

SY.

Marked as Answer by user525840 · Jun 7 2022
user525840

Thank you everyone for quick turnaround.
Using the suggested query, I can see the expected output. I will now pass it to the Oracle JET web component to use this json data instead of static json file.
I will create a procedure returning clob output from this SQL and use that in array data provider in front end.
Thank you.

Solomon Yakobson

Keep in mind NEXT_DAY is NLS dependent so if your stored procedure will be executed by, say, client in Israel:

SQL> create or replace
  2    procedure p1
  3      is
  4      begin
  5          dbms_output.put_line(NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY'));
  6  end;
  7  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1;
22-MAY-22

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_language=hebrew;

Session altered.

SQL> exec p1;
BEGIN p1; END;

                                                                          *
ERROR at line 1:
ORA-01846: not a valid day of the week
ORA-06512: at "SY47755.P1", line 4
ORA-06512: at line 1

SQL>

You could use 'IW' format instead but it is simpler to use pre-defined date (any Sunday):

TRUNC(SYSDATE,'MONTH') - 15 + 1 + MOD(ABS(TRUNC(SYSDATE,'MONTH') - 15 - DATE '2022-05-29') - 1,7)

Similarly you can get next Saturday (or any day).
SY.

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 21 2018
Added on Jan 24 2018
1 comment
134 views