Generate ICAL with PL/SQL - integrate your tables with your Desktop Calendar Applications
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.
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 ...
