30. April 2013

ICAL mit PL/SQL erzeugen: Oracle-Tabellen mit Desktop-Kalenderanwendungen integrieren ...

Generate ICAL with PL/SQL - integrate your tables with your Desktop Calendar Applications
Bereits vor einiger Zeit hatte ich in der deutschsprachigen APEX und PL/SQL Community einen Tipp zum Thema APEX-Kalender mit Thunderbird oder Microsoft Outlook integrieren veröffentlicht.
Nun, dieser Tipp hatte mit APEX eigentlich gar nix zu tun - er funktioniert mit jeder beliebigen Tabelle, welche in einem Kalender anzeigbare Daten enthält - auch völlig ohne APEX. Die einzige Voraussetzung ist eine DATE- oder TIMESTAMP-Spalte. Und wie das geht, zeigt dieses Blog-Posting. Wir fangen mit der Tabelle an.
drop table tipp_kalender
/

create table tipp_kalender(
  id              number(10),
  bezeichnung     varchar2(200),
  ort             varchar2(200),
  organisator     varchar2(200),
  org_email       varchar2(200),
  beschreibung    varchar2(4000),
  datum_beginn    date,
  datum_ende      date,
  constraint pk_tippkalender primary key (id)
)
/

insert into tipp_kalender values (1, 'Cloud Computing mit APEX', 'Düsseldorf', 'Max Mustermann', 'max.mustermann@firma.de', 'Eine Beschreibung', to_date('20110503','YYYYMMDD'), to_date('20110503','YYYYMMDD'));
insert into tipp_kalender values (2, 'Cloud Computing mit APEX', 'Hamburg', 'Max Mustermann', 'max.mustermann@firma.de', 'Eine Beschreibung', to_date('20110510','YYYYMMDD'), to_date('20110510','YYYYMMDD'));
insert into tipp_kalender values (3, 'Cloud Computing mit APEX', 'München', 'Max Mustermann', 'max.mustermann@firma.de', 'Eine Beschreibung', to_date('20110518','YYYYMMDD'), to_date('20110518','YYYYMMDD'));
insert into tipp_kalender values (4, 'DOAG SIG Development', 'Kassel', 'DOAG', 'max.mustermann@firma.de', 'DOAG Veranstaltung', to_date('20110609','YYYYMMDD'), to_date('20110609','YYYYMMDD'));
insert into tipp_kalender values (5, 'DOAG SIG Database', 'Hannover', 'DOAG', 'max.mustermann@firma.de', 'DOAG Veranstaltung', to_date('20110519','YYYYMMDD'), to_date('20110519','YYYYMMDD'));
insert into tipp_kalender values (6, 'DOAG Konferenz 2011', 'Nürnberg', 'DOAG', 'max.mustermann@firma.de', 'DOAG Veranstaltung', to_date('20111115','YYYYMMDD'), to_date('20111117','YYYYMMDD'));

commit
/
Aus diesen Daten muss nun ein Format erzeugt werden, welches die Desktop-Kalender wie MS Outlook oder Thunderbird verstehen. ICAL bietet sich hier an.
BEGIN:VCALENDAR
VERSION:2.0
PRODID:http://www.example.com/calendarapplication/
METHOD:PUBLISH
BEGIN:VEVENT
UID:461092315540@example.com
ORGANIZER;CN="Alice Balder, Example Inc.":MAILTO:alice@example.com
SUMMARY:Eine Kurzinfo
DESCRIPTION:Beschreibung des Termines
CLASS:PUBLIC
DTSTART:20060910T220000Z
DTEND:20060919T215900Z
DTSTAMP:20060812T125900Z
END:VEVENT
END:VCALENDAR
Der folgende PL/SQL-Code generiert - anhand der Tabellendaten - das ICAL-Format.
create or replace procedure generate_ical is
begin
  /*
   * Schritt 1: HTTP-Headerinformationen setzen 
   */
  owa_util.mime_header('text/calendar', false);
  htp.p('Content-Disposition: inline; filename=apexcommunity-events.ics');
  owa_util.http_header_close;
  
  /*
   * Schritt 2: iCalendar-Format - Kopfdaten
   */
  htp.p('BEGIN:VCALENDAR');
  htp.p('VERSION:2.0');
  htp.p('PRODID:http://www.oracle.com/webfolder/global/de/community/tipps/kalender-integrieren/index.html');
  htp.p('METHOD:PUBLISH');
  
  /*
   * Schritt 3: Ereignisdaten per PL/SQL Schleife
   */
  for i in ( select * from tipp_kalender ) loop
    htp.p('BEGIN:VEVENT');
    htp.p('UID:EVENT_'||i.id||'_APEXCOMMUNITY@meinefirma.de');
    htp.p('ORGANIZER;CN='||i.organisator||':MAILTO:'||i.org_email);
    htp.p('SUMMARY:'||i.bezeichnung);
    htp.p('LOCATION:'||i.ort);
    htp.p('DESCRIPTION:'||replace(i.beschreibung,chr(10), '\n'));
    htp.p('CLASS:PUBLIC');
    htp.p('DTSTART:'||to_char(i.datum_beginn, 'YYYYMMDD'));
    htp.p('DTEND:'||to_char(i.datum_ende + 1, 'YYYYMMDD'));
    htp.p('DTSTAMP:'||to_char(sysdate, 'YYYYMMDD')||'T000000Z');
    htp.p('END:VEVENT');
  end loop;
  
  /*
   * Schritt 4: iCalendar-Format: Abschluß
   */
  htp.p('END:VCALENDAR');
end;
/
sho err
Der PL/SQL-Code arbeitet mit den Packages HTP, HTF und OWA_UTIL - die Inhalte werden also per HTTP bereitgestellt. Dabei kann APEX seine Vorteile natrülich ausspielen, denn alle APEX-Seiten werden mit PL/SQL in der Datenbank generiert und per HTTP zum Browser ausgeliefert - die ganze nötige Infrastruktur ist also schon da. Ohne APEX kann man sich aber auch helfen: Mit dem PL/SQL Embedded Gateway (DBMS_EPG) weiterhelfen. Das sieht dann wie folgt aus (im folegenden nehmen wir an, Tabelle und PL/SQL-Prozedur zum Erzeugen des ICAL liegen im Schema SCOTT):
begin
  dbms_epg.drop_dad(
     dad_name => 'SHOW_ICAL_DAD'
  );
end;
/
sho err

begin
  dbms_epg.create_dad(
    dad_name => 'SHOW_ICAL_DAD',
    path     => '/ical/*'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'SHOW_ICAL_DAD', 
    attr_name => 'database-username', 
    attr_value => upper('SCOTT')
  );
  DBMS_EPG.AUTHORIZE_DAD(
    dad_name => 'SHOW_ICAL_DAD',
    user => upper('SCOTT')
  );
end;
/
Nun sollte noch überprüft werden, ob die HTTP-Protokollserver aktiviert ist. Das geschieht am einfachsten auf dem Datenbankserver mit einem lsnrctl status.
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-APR-2013 15:50:16

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
:
Listener Log File         /opt/oracle/diag/tnslsnr/sccloud030/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
:
Wenn die hier fett markierte Zeile mit (PORT=8080) fehlt, solltet Ihr folgendes prüfen:
  • Ist die XML DB in der Datenbank vorhanden (Dictionary View DBA_REGISTRY) ...?
    SQL> select comp_name, version from dba_registry
    
    COMP_NAME                                VERSION
    ---------------------------------------- --------------------
    Oracle Application Express               4.2.1.00.08
    OWB                                      11.2.0.2.0
    :
    Oracle Multimedia                        11.2.0.2.0
    Oracle XML Database                      11.2.0.2.0
    Oracle Text                              11.2.0.2.0
    :
    
  • Enthalt der Datenbankparameter DISPATCHERS wenigstens den Inhalt (PROTOCOL=TCP)(SERVICE={oracle-SID}XDB) ...?
    SQL> sho parameter dispatchers
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------------------------
    dispatchers                          string      (PROTOCOL=TCP)(SERVICE=orclXDB)
    
  • Ist der HTTP-Port mit DBMS_XDB.SETHTTPPORT gesetzt worden ...?
    SQL> exec dbms_xdb.sethttpport(8080);
    
    PL/SQL procedure successfully completed.
    
Wenn das alles passiert ist, könnt Ihr den Setup in eurem Kalender testen - die folgenden Screenshots wurden mit Mozilla Thunderbird gemacht - in Microsoft Outlook funktioniert es ganz ähnlich ...
http://{datenbank-hostname}:8080/ical/generate_ical
Einrichten des Kalender als Netzwerk-Kalender im "ICS-Format"
Betrachten des Kalenders
Der APEX-Community-Tipp enthält darüber hinaus noch ausführliche Beschreibungen zum Einbinden in Thunderbird oder Outlook.
Some time ago, the german APEX and PL/SQL Community published, how to integrate APEX calendar regions with desktop applications like Mozilla Thunderbird or Microsoft Outlook (non german readers might try Google Translate - or go on reading this blog posting).
Well, this howto does not depend on APEX - it can be used within any Oracle database on any table. The only (obvious) requirement is, that the table contains a DATE or TIMESTAMP column. And this blog posting shows, how the contents of such an arbitrary table can be displayed within a desktop calendar application - we'll start with creating the table.
drop table tipp_kalender
/

create table tipp_kalender(
  id              number(10),
  bezeichnung     varchar2(200),
  ort             varchar2(200),
  organisator     varchar2(200),
  org_email       varchar2(200),
  beschreibung    varchar2(4000),
  datum_beginn    date,
  datum_ende      date,
  constraint pk_tippkalender primary key (id)
)
/

insert into tipp_kalender values (1, 'Cloud Computing mit APEX', 'Düsseldorf', 'Max Mustermann', 'max.mustermann@firma.de', 'Eine Beschreibung', to_date('20110503','YYYYMMDD'), to_date('20110503','YYYYMMDD'));
insert into tipp_kalender values (2, 'Cloud Computing mit APEX', 'Hamburg', 'Max Mustermann', 'max.mustermann@firma.de', 'Eine Beschreibung', to_date('20110510','YYYYMMDD'), to_date('20110510','YYYYMMDD'));
insert into tipp_kalender values (3, 'Cloud Computing mit APEX', 'München', 'Max Mustermann', 'max.mustermann@firma.de', 'Eine Beschreibung', to_date('20110518','YYYYMMDD'), to_date('20110518','YYYYMMDD'));
insert into tipp_kalender values (4, 'DOAG SIG Development', 'Kassel', 'DOAG', 'max.mustermann@firma.de', 'DOAG Veranstaltung', to_date('20110609','YYYYMMDD'), to_date('20110609','YYYYMMDD'));
insert into tipp_kalender values (5, 'DOAG SIG Database', 'Hannover', 'DOAG', 'max.mustermann@firma.de', 'DOAG Veranstaltung', to_date('20110519','YYYYMMDD'), to_date('20110519','YYYYMMDD'));
insert into tipp_kalender values (6, 'DOAG Konferenz 2011', 'Nürnberg', 'DOAG', 'max.mustermann@firma.de', 'DOAG Veranstaltung', to_date('20111115','YYYYMMDD'), to_date('20111117','YYYYMMDD'));

commit
/
Based on this data, we now want to generate a data format which a desktop application is able to understand. ICAL is most appropriate here - it's pretty easy and understood by most calendar applications. Below is a sample ...
BEGIN:VCALENDAR
VERSION:2.0
PRODID:http://www.example.com/calendarapplication/
METHOD:PUBLISH
BEGIN:VEVENT
UID:461092315540@example.com
ORGANIZER;CN="Alice Balder, Example Inc.":MAILTO:alice@example.com
SUMMARY:Eine Kurzinfo
DESCRIPTION:Beschreibung des Termines
CLASS:PUBLIC
DTSTART:20060910T220000Z
DTEND:20060919T215900Z
DTSTAMP:20060812T125900Z
END:VEVENT
END:VCALENDAR
The following PL/SQL code generated ICAL data based on the table's contents.
create or replace procedure generate_ical is
begin
  /*
   * Step 1: Set the HTTP header 
   */
  owa_util.mime_header('text/calendar', false);
  htp.p('Content-Disposition: inline; filename=apexcommunity-events.ics');
  owa_util.http_header_close;
  
  /*
   * Step 2: iCalendar format - header data
   */
  htp.p('BEGIN:VCALENDAR');
  htp.p('VERSION:2.0');
  htp.p('PRODID:http://www.oracle.com/webfolder/global/de/community/tipps/kalender-integrieren/index.html');
  htp.p('METHOD:PUBLISH');
  
  /*
   * Step 3: iCalendar format - event data per PL/SQL loop
   */
  for i in ( select * from tipp_kalender ) loop
    htp.p('BEGIN:VEVENT');
    htp.p('UID:EVENT_'||i.id||'_APEXCOMMUNITY@meinefirma.de');
    htp.p('ORGANIZER;CN='||i.organisator||':MAILTO:'||i.org_email);
    htp.p('SUMMARY:'||i.bezeichnung);
    htp.p('LOCATION:'||i.ort);
    htp.p('DESCRIPTION:'||replace(i.beschreibung,chr(10), '\n'));
    htp.p('CLASS:PUBLIC');
    htp.p('DTSTART:'||to_char(i.datum_beginn, 'YYYYMMDD'));
    htp.p('DTEND:'||to_char(i.datum_ende + 1, 'YYYYMMDD'));
    htp.p('DTSTAMP:'||to_char(sysdate, 'YYYYMMDD')||'T000000Z');
    htp.p('END:VEVENT');
  end loop;
  
  /*
   * Step 4: iCalendar format: footer data
   */
  htp.p('END:VCALENDAR');
end;
/
sho err
This PL/SQL code works with the packages HTP, HTF and OWA_UTIL - so the output is to be consumed over the HTTP protocol. In an APEX installation all we need is an Application Process in order to publich the procedure. Without APEX we need to do some more work - since we cannot just point the Thunderbird or MS Outlook application to the database. We need to provide an HTTP endpoint, and for this we have the PL/SQL embedded gateway (there is another blog posting about this). So, the next steps are about configuring the embedded gateway to enable an HTTP endpoint for our "database ICAL service" ... let's assume, we did all the work within the schema SCOTT.
begin
  dbms_epg.drop_dad(
     dad_name => 'SHOW_ICAL_DAD'
  );
end;
/
sho err

begin
  dbms_epg.create_dad(
    dad_name => 'SHOW_ICAL_DAD',
    path     => '/ical/*'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'SHOW_ICAL_DAD', 
    attr_name => 'database-username', 
    attr_value => upper('SCOTT')
  );
  DBMS_EPG.AUTHORIZE_DAD(
    dad_name => 'SHOW_ICAL_DAD',
    user => upper('SCOTT')
  );
end;
/
Next, we should check, whether the HTTP protocol server is enabled - the most easy way is to execute a lsnrctl status.
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-APR-2013 15:50:16

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
:
Listener Log File         /opt/oracle/diag/tnslsnr/sccloud030/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
:
If the bold line is present, you are fine. If the HTTP-Port is a different value, you are also fine. If the line is missing, you should do the following 3 checks:
  • Is XML DB installed in the data dictionary of the database...? Use the dictionary view DBA_REGISTRY to check this.
    SQL> select comp_name, version from dba_registry
    
    COMP_NAME                                VERSION
    ---------------------------------------- --------------------
    Oracle Application Express               4.2.1.00.08
    OWB                                      11.2.0.2.0
    :
    Oracle Multimedia                        11.2.0.2.0
    Oracle XML Database                      11.2.0.2.0
    Oracle Text                              11.2.0.2.0
    :
    
  • Is the database parameter DISPATCHERS configured correctly ...? It must contain at least (PROTOCOL=TCP)(SERVICE={oracle-SID}XDB)
    SQL> sho parameter dispatchers
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------------------------
    dispatchers                          string      (PROTOCOL=TCP)(SERVICE=orclXDB)
    
  • Did you set the HTTP port by calling DBMS_XDB.SETHTTPPORT ...?
    SQL> exec dbms_xdb.sethttpport(8080);
    
    PL/SQL procedure successfully completed.
    
When the HTTP endpoint is running, you might check your setup within Mozilla thunderbird or Microsoft Outlook; the following screenshots have been created with Thunderbird. Just create a new calendar, choose a Network calendar and use the following URL as the service location ...
http://{datenbank-hostname}:8080/ical/generate_ical
Setup a network calendar within Mozilla Thunderbird.
View the calendar contents (the table data) within Thunderbird.
The document within the german APEX and PL/SQL community also contains comprehensive setup guides for Outlook and Thunderbird - but in German language. So you might use a translation tool here ...

Beliebte Postings