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 ...

15. April 2013

PL/SQL Email Client Package: Version 1.0 verfügbar

PL/SQL Email Client package: version 1.0 is available
Nach einer sehr langen Zeit (um genau zu sein: nach 2 1/2 Jahren), konnte ich endlich eine neue Version meines Projektes PL/SQL Email Client online stellen. Neu in Version 1.0 sind
  • Eine GET_RECEIVEDATE -Funktion, die den Zeitstempel, zu dem die Mail empfangen wurde, zurückgibt (nicht das Sendedatum)
  • Das Package MAIL_FILTER zum Setzen von Suchfiltern auf dem Mailserver. Damit ruft das Package nicht mehr zwingend alle Emails aus einem Folder ab, sondern nur die, die den gesetzten Filterkriterien genügen. Das Filtern wird vom Mailserver gemacht; nicht von der Datenbank.
  • Und schließlich sind noch einige interne Optimierungen enthalten: So instanziiert der Java-Code nun weniger interne Objekte.
Am besten gleich mal ausprobieren - und so ruft Ihr mit PL/SQL alle ungelesenen Emails aus eurem Postfach ab.
begin
  mail_client.connect_server(
    p_hostname => 'mailserver.mycompany.de',
    p_port     => '993',
    p_protocol => mail_client.protocol_imap,
    p_userid   => '****',
    p_passwd   => '****',
    p_ssl => true
  );
  mail_client.open_inbox;
  mail_filter.clear_filters;
  mail_filter.add_seen_filter(false);
end;
/

PL/SQL procedure successfully completed.

select msg_number, subject, sender, sent_date, message_size from table(mail_client.get_mail_headers_p())e ;

MSG_NUMBER
----------
SUBJECT
--------------------------------------------------------------------------------
SENDER
--------------------------------------------------------------------------------
SENT_DATE           MESSAGE_SIZE
------------------- ------------
         6
Testmail
Carsten Czarski
15.04.2013 14:09:31         6790

begin
  mail_client.close_folder;
  mail_client.disconnect_server;
end;
/

1 row selected.

PL/SQL procedure successfully completed.
Den ersten "Feature-Request" für Version 1.1 habe ich schon - es wird also nicht mehr 2 1/2 Jahre dauern.
Eines noch: Ich bekomme immer wieder Emails, die mir zeigen, dass dieses Package tatsächlich verwendet wird. Das würde ich gerne mal sammeln. Also: Wenn jemand das Package MAIL_CLIENT (produktiv) nutzt und dazu ein wenig als Kommentar zu diesem Posting schreibt, freue ich sehr mich darüber ... und vielen Dank schon mal im voraus.
After a long period of time (2 and a haf years, to be exact), I finally managed to release version 1.0 of my PL/SQL Email Client project. And these are the new features.
  • The GET_RECEIVEDATE function returns the timestamp at which the message has been received (not sent).
  • The MAIL_FILTER package allows to set server-side filters. The package will then retrieve not necessarily all emails from within a folder, but only these which suffice the filter set by the programmer. Filtering is done by the email server.
  • Beyond this, I did some internal optimizations: The java code now instantiates less objects.
So ... just try it out: The following SQL script retrieves all unread messages from your Inbox folder. You just need to replace the connection details for your mail server - and make sure that your database is able to reach the mail server via the network.
begin
  mail_client.connect_server(
    p_hostname => 'mailserver.mycompany.de',
    p_port     => '993',
    p_protocol => mail_client.protocol_imap,
    p_userid   => '****',
    p_passwd   => '****',
    p_ssl => true
  );
  mail_client.open_inbox;
  mail_filter.clear_filters;
  mail_filter.add_seen_filter(false);
end;
/

PL/SQL procedure successfully completed.

select msg_number, subject, sender, sent_date, message_size from table(mail_client.get_mail_headers_p())e ;

MSG_NUMBER
----------
SUBJECT
--------------------------------------------------------------------------------
SENDER
--------------------------------------------------------------------------------
SENT_DATE           MESSAGE_SIZE
------------------- ------------
         6
Testmail
Carsten Czarski
15.04.2013 14:09:31         6790

1 row selected.

begin
  mail_client.close_folder;
  mail_client.disconnect_server;
end;
/

PL/SQL procedure successfully completed.
I already have the next "feature request" - but the next version will be there sooner than 2 1/2 years ...
One more thing: In the past, I got some emails telling me about the usage of the mail client package or asking some questions. I'd like to collect this information here: So ... if you are using the MAIL_CLIENT package (in production?) and don't mind, I'd be very happy if you wrote a few words as a commect to this blog posting - thanks in advance.

Beliebte Postings