Forum Stats

  • 3,838,328 Users
  • 2,262,355 Discussions


ics Outlook Calendar export

User_UNRG6 Member Posts: 3
edited Feb 1, 2021 10:04PM in SQL & PL/SQL


I tried to automatically generate a .ics file to send it to our employees, but there is something wrong with the format.

My code PL/SQL code:




      TO_CHAR(TO_DATE((SELECT available_date FROM zb_dates_quick WHERE id = 1), 'DD.MM.YY'), 'YYYY-MM-DD')

     FROM dual) 

    || 'T' ||


    (SELECT start_time FROM zb_timeslots_quick WHERE id = 1) || ':00'

  ) as ICSFormat

FROM dual;

Can anybody help me with writing this to work, always when I sent it than apex displays that something ends before it is able to process. I don't know what is wrong about this format, so is it false to transfer it to a string? I think the isc format need this format: 2021-01-27T13:59:43


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,230 Red Diamond
    edited Feb 1, 2021 10:43PM


    If this problem is about how to use some Apex feature, then you should post the question in the Apex Space: APEX — oracle-tech

    If this really is a SQL problem (e.g., how to manipulate strings, which you just happen to be doing in an Apex application) then post little sample data (CREATE TABLE and INSERT statements) for all tables involved, and the exact output you want from that sample data.

    I don't see any obvious errors in the SQL you posted, depending on your data. There are other ways to get the same results. For example:

    SELECT REGEXP_REPLACE ( d.available_date
                          , '(\d\d)\.(\d\d)\.(\d\d)'
    		      , '20\3-\2-\1T'
        || t.start_time
        || ':00' AS icsformat
    FROM       zb_dates_quick     d
    CROSS JOIN zb_timeslots_quick t
    WHERE = 1
    AND = 1

    Date information belongs in DATE columns, not VARCHAR2 columns. If you're stuck with the data in a string column, then you might find it simpler to treat the data as a string, rather than convert the string to a DATE, and then convert it back to a string.

    The dual table is needed in SQL when you're not actually dealing with any table. In this case, you're dealing with two real tables, so there's no need to use dual.

    Again, the code you posted should work. Of course, without any sample data, I can't actually test anything.