21. Dezember 2007

Kurz vor Weihnachen noch ein Tipp ...

English title: Just before Christmas one tip for application developers ...

Heute (kurz vor Weihnachten) noch das für den Anwendungsentwickler recht wichtiges Kommando der Oracle-Datenbank. Auf der Entwicklungsumgebung sollte man es (als SYS) ruhig absetzen:
Today (just before christmas) I have an important Oracle command for application developers: I'd recommand to issue this on a development environment as SYS
SQL> shutdown immediate
In der Produktionsumgebung vielleicht eher nicht ... für den Anwendungsentwickler ist es allerdings - gerade jetzt - eine hervorragende Gelegenheit, in der kurzen "staden Zeit" zu entspannen und neue Ideen zu sammeln. In diesem Sinne wünsche ich euch und euren Lieben ...

Frohe Weihnachten und einen guten Rusch ins neue Jahr 2008

... und dann geht's hier auch weiter ...
It's not a good idea for the production environment ... but for the application developer this is now a good opportunity to "step back" for a short while, relax and start again with many creative ideas in the next year. And with this in mind I wish every reader ...

Merry Christmas and a happy new year 2008 for you and your family

... and in 2008 I'll continue with the next post ...

17. Dezember 2007

Relationale Daten als XML per FTP abrufen: Und das nur mit der Datenbank

English title: Get relational data as XML via FTP: Just with the database!

Heute befassen wir uns nochmal mit dem Thema XML. Vor einiger Zeit habe ich ja schon ein wenig über die Methoden zum Generieren von XML geschrieben - heute wenden wir das ganze praktisch an. Wir erzeugen mit den Inhalten der Tabellen DEPT und EMP für jedes Department ein XML-Dokument (mit den SQL/XML Funktionen) und stellen diese direkt als FTP-Download bereit. Wie im letzten Post beschrieben, kann man Pointer (REF) auf Zeilen einer Tabelle OF XMLTYPE erzeugen und mit diesen Pointern Einträge im virtuellen Dateisystem anlegen.
Heute machen wir das wieder genauso, nur wird heute keine TABLE OF XMLTYPE erstellt, sondern eine VIEW OF XMLTYPE.
Today we'll focus again on XML: Some time ago I wrote something about the different methods for generating XML in the Oracle database (The post is here, but it's not translated so far). Now we'll use it in practice: Based on the well-known tables EMP and DEPT we'll create an XML document for each Department using the SQL/XML functions. These "virtual" XML documents will then be made available for download via FTP. As described in the previous post one can create pointers (REF's) on rows in a table OF XMLTYPE and with these pointers entries in the virtual filesystem (the "XML DB Repository") can be created.
Today we'll do the same with just one little difference: We'll not create a TABLE OF XMLTYPE but a VIEW OF XMLTYPE
create view V_DEPT_XML of XMLTYPE
with object oid (extract(sys_nc_rowinfo$, '/department/@id').getnumberval())
as
select 
 XMLElement("department",
  XMLAttributes(d.deptno as "id"),
  XMLElement("name", d.dname),
  XMLElement("location", d.loc),
  XMLElement("employees",
   (
    select 
     XMLAgg(
      XMLElement("employee", 
       XMLAttributes(e.empno as "id"),
       XMLElement("name", e.ename),
       XMLElement("hiredate", e.hiredate),
       XMLElement("payment-info",
        XMLForest(e.sal as "salary", e.comm as "commission")
       )
      )
     )
     from emp e where e.deptno = d.deptno
   )
  )
 )
from dept d
/
Von besonderer Bedeutung ist die zweite Zeile WITH OBJECT OID .... Da dies eine View und keine Tabelle ist, kann auch die Objekt-ID nicht automatisch generiert werden - vielmehr muss man der Datenbank sagen, wie ein "Objekt" (hier: ein XML-Dokument) identifiziert werden soll bzw. wie die Objekt-ID aus dem XML-Dokument abgeleitet werden soll. In diesem Beispiel wird die Objekt-ID aus der DEPTNO abgeleitet.
Nun schauen wir wiederum im Data Dictionary nach den Spalten der View
The second line WITH OBJECT OID ... in the above code is very important. Since this is a view and not a table, the object id cannot be generated automatically - the user has to tell the database how the objects (the generated XML documents) are identified and therefore how to derive the object id from the XML content. In this example the object id is derived from the DEPTNO column.
Now we look into the data dictionary to get information about the view columns.
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='V_DEPT_XML'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
SYS_NC_ROWINFO$                XMLTYPE                        NO  NO
SYS_NC_OID$                    RAW                            NO  YES

2 Zeilen ausgewählt.
Die View hat zwei Spalten: Die Spalte SYS_NC_ROWINFO$ enthält das generierte XML Dokument, die Spalte SYS_NC_OID$ die Objekt-ID. Das entspricht in etwa dem Aufbau der TABLE OF XMLTYPE (letzter Post), diese hatte nur eine zusätzliche, versteckte Spalte mit den eigentlichen XML-Daten - da wir nun eine View haben, wird diese nicht benötigt. Nun kann man sich die Pointer (REF) ausgeben lassen.
This view has two columns: SYS_NC_ROWINFO$ is for the generated XML document, SYS_NC_OID contains the (generated) object id. This is similar to a TABLE OF XMLTYPE (previous post); the table had another additional column for the XML data itself - since this is a view such a column is not needed. Now we can get the pointers (REF) to the virtual XML documents.
SQL> select ref(e) from v_dept_xml e;

REF(E)
--------------------------------------------------------------------------------
00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C10B0000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C1150000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C11F0000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C1290000000000000000000000000000000000000000
Nun erzeugen wir mit folgendem PL/SQL-Code die Einträge im virtuellen Dateisystem:
Now we'll create the virtual files with the following PL/SQL code:
declare
  v_res boolean;
begin
  for i in (
    select 
      ref(e) as reference,
      extractvalue(object_value, '/department/@id') as id
    from v_dept_xml e
  ) loop
    v_res := dbms_xdb.createresource(
      abspath => '/public/department-'||i.id||'.xml',  
      data    => i.reference,                    
      sticky  => true                     
    );
  end loop;
end;
/
sho err
  
commit
/
Anschließend sieht es im Ordner /public des virtuellen Dateisystems in etwa so aus (wenn der FTP-Zugriff noch nicht funktioniert, spielt als SYS das Skript $ORACLE_HOME/rdbms/admin/catxdbdbca.sql ein):
After running this code the folder /public in the virtual filesystem (if FTP does not work, run the script $ORACLE_HOME/rdbms/admin/catxdbdbca.sql as SYS) looks like the following
220- vmware.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 vmware.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-10.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-20.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-30.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-40.xml
226 ASCII Transfer Complete
ftp: 292 bytes received in 0,00Seconds 292000,00Kbytes/sec.
ftp>
Laden Sie nun eine Datei herunter und öffnen Sie diese mit einem Editor - sie sehen das von der View erzeugte XML-Dokument. Und jetzt kommt's: Ändern Sie mal mit einem SQL UPDATE die Tabellen EMP bzw. DEPT (COMMIT nicht vergessen) und laden Sie das XML-Dokument nochmal herunter - Sie bekommen sofort aktuelle Daten per FTP ... und das alles mit den Bordmitteln der Datenbank!
Download a file, open it using a text editor and you'll see the XML document generated by the view. And this is the trick: Issue a SQL UPDATE command for the table EMP or DEPT and download the same file again (don't forget the COMMIT. You'll always get the most current data via FTP ... and all this without additional tools or software - just with the database!

16. Dezember 2007

English ... German ... Deutsch .. . Englisch

In der Vergangenheit habe ich immer häufiger festgestellt, dass auch Nachfrage nach den Posts in Englisch besteht. Daher habe ich mir vorgenommen, künftig eine übersetzte Version anzubieten: Ihr seht die Posts (soweit übersetzt) in Englisch, wenn Ihr an die URL ein lang=en anhängt. Probiert es aus: Englisch - Deutsch
During the last months I recogized some demand for english versions of my posts. So I plan to meet this requirement and provide translated versions. You get the english versions (if available) by appending "lang=en" to the Blog or Post URL ("lang=de" gets you the german versions, of course). Try it: English - German

7. Dezember 2007

TABLE OF XMLTYPE ... oder "normale" Spalte: Was ist der Unterschied ...?

English title: TABLE OF XMLTYPE ... or "normal" column: What's the difference?

This post is about a "very special" Oracle XML feature: Most people know that there are two ways to create a table for XML documents
Heute geht es wieder mal um das Thema XML und dabei um eine "Spezialität" der objektrelationalen Features in Oracle. Schaut man sich in der Dokumentation um, dann kann man feststellen, dass man eine XML-Tabelle auf zweierlei Art und Weise anlegen kann:
  1. CREATE TABLE XMLTAB1 (xml_document XMLTYPE);
  2. CREATE TABLE XMLTAB2 OF XMLTYPE;
Beyond the fact that the latter variant allows only one column: What's the difference ...?
Mal abgesehen davon, dass man bei der zweiten Variante keine weiteren Spalten mehr angeben kann: Was ist eigentlich der Unterschied ...?
The documentation of the object-relational features states that the first table stores the XML documents as column objects, the second table as row objects. But what are the consequences ...? To find out more we dig into the oracle data dictionary:
Wenn man in die Dokumentation zu den objektrelationalen Features, dann stellt man fest, dass die XML-Dokumente im ersten Fall als Column Objects und im zweiten Fall als Row Objects gespeichert werden. Aber was bedeutet das nun im Detail ...? Dazu sehen wir uns ein wenig im Data Dictionary um:
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='XMLTAB1'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
XML_DOCUMENT                   XMLTYPE                        YES NO
SYS_NC00002$                   CLOB                           NO  YES
The table XMLTAB1 ("column objects") contains XML_DOCUMENT as specified in the CREATE TABLE statement. But this is a virtual (derived) column. In addition there is the hidden column SYS_NC00002$ of type CLOB. When there are no storage parameters for XML, Oracle chooses the text-based storage, as explained more detailed in the post XML Speicherungsformen (german). Therefore this hidden column SYS_NC00002$ does actually store the XML text. The column XML_DOKUMENT is just derived (BTW: Here we can see that Virtual Columns in Oracle11g are not really a "new feature": the Oracle database did use them in previous versions; but Oracle11g exposes them externally.)
Die Tabelle XMLTAB1 mit den "Column Objects" enthält die im CREATE TABLE-Kommando angegebene Spalte XML_DOCUMENT als virtuelle, also "abgeleitete" Spalte und zusätzlich die versteckte (Hidden) Spalte SYS_NC00002$ vom Typ CLOB. Wie bereits im Post über die Speicherungsformen geschrieben, wird ein XMLTYPE als CLOB gespeichert, wenn man bei Tabellenerstellung nix anderes angibt. Und diese versteckte Spalte SYS_NC00002$ nimmt das eigentliche XML-Dokument auf. Die sichtbare Spalte XML_DOKUMENT ist dagegen eine daraus abgeleitete virtual Column. (Übrigens: Daran ist erkennbar, dass Virtual Columns in Oracle11g kein wirklich neues Feature ist - es wurde lediglich für den Anwender nutzbar gemacht. Virtuelle Spalten gab's auch schon vorher.)
But what about the table OF XMLTYPE ("row objects")?
Aber wie sieht es nun mit der Tabelle OF XMLTYPE, also den "Row Objects" aus?
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='XMLTAB2'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
SYS_NC_OID$                    RAW                            NO  YES
SYS_NC_ROWINFO$                XMLTYPE                        YES NO
XMLDATA                        CLOB                           NO  YES
Again, there is a hidden column (XMLDATA), which contains the actual XML data, and a derived (virtual) column. The virtual column is now named SYS_NC_ROWINFO$, as we did not specify a column name during table creation (CREATE TABLE ... OF XMLTYPE). Compared with the first table there is an additional column: SYS_NC_OID$. This column contains a unique object id for each XML document. This object id allows to create a pointer for each XML document.
Wiederum haben wir eine versteckte Spalte (XMLDATA), welche die eigentlichen XML-Daten aufnimmt und eine abgeleitete (virtuelle) Spalte, die hier SYS_NC_ROWINFO$ heißt (bei Tabellenerstellung haben wir hier ja keinen Spaltennamen angegeben). Soweit ist es wie bei der ersten Tabelle. Neu ist die Spalte SYS_NC_OID$, welche eine eindeutige Object-ID für jedes XML-Dokument enthält. Diese Object ID macht es nun möglich, dass man einen Pointer auf das XML-Dokument erzeugen kann:
SQL> insert into xmltab2 values ('<xml-dokument>Ein Text</xml-dokument>');

1 row created.

SQL> select ref(e) from XMLTAB2 e;

REF(E)
--------------------------------------------------------------------------------------
000028020940B3A963EF38DC29E040A50A86F8248140B3A963EF37DC29E040A50A86F824810141DA550000
This reference pointer can now be used to create an entry for the particular XML document in Oracle XML DB's virtual file system (the XML DB Repository). The following example illustrates this using the FTP access (in most cases this must be configured with the script $ORACLE_HOME/rdbms/admin/catxdbdbca.sql):
Und eine solche Referenz könnte man nun nutzen, um das eingefügte XML-Dokument im virtuellen Dateisystem der XML Datenbank erscheinen zu lassen. Im folgenden sei dies anhand des FTP-Zugriffs (ggfs. einrichten mit dem Skript $ORACLE_HOME/rdbms/admin/catxdbdbca.sql) erklärt:
declare
  v_ref ref xmltype;
  v_res boolean;
begin
  select ref(e) into v_ref from xmltab2 e;
  v_res := dbms_xdb.createresource(
    abspath => '/public/testdatei.xml',  -- Pfad, unter dem die virtuelle Datei erzeugt werden soll
    data    => v_ref,                    -- Pointer auf die Tabellenzeile
    sticky  => true                      -- "Klebrig"; beim Löschen der Tabellenzeile verschwindet
                                         -- auch die virtuelle Datei
  );
end;
/
sho err
  
commit
/
Now we browse the virtual file system with a command line FTP client
Nun schauen wir uns das virtuelle Dateisystem mit FTP an
ftp> open host 2100
Connected to host.mydomain.com.
220- host
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 host FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   2 SCOTT    oracle         0 DEC 12 13:03 testdatei.xml
226 ASCII Transfer Complete
ftp> get testdatei.xml
226 ASCII Transfer Complete
ftp: 37 bytes received in 0,05Seconds 1298,24Kbytes/sec.
After "downloading" the file testdatei.xml can be opened using a normal text editor and it contains the XML text which was inserted in the table. Deleting the file via the FTP DELETE command leads the table row also to be deleted. Changing the XML content with a SQL UPDATE command leads the file content to be updated accordingly.
Access to the virtual filesystem is not only possible with FTP but also with HTTP and WebDAV - so this feature is very feasible for data integration scenarios: The next post will show not only how to create a XML view on top of some relational tables, but also how to expose these virtual XML documents as files in the virtual filesystem - clients can get the data via FTP or HTTP.
Schaut man in die Datei hinein, so findet man den eben in die Tabelle eingefügten XML-Text wieder. Löscht man die Datei via FTP DELETE-Kommando, dann verschwindet sie auch aus der Tabelle. Ändert man die Tabellenzeile, so ist die virtuelle Datei sofort mitgeändert.
Wie man beim Einspielen des o.g. Skripts $ORACLE_HOME/rdbms/admin/catxdbdbca.sql feststellt, steht neben FTP auch HTTP zur Verfügung - und da gelten genau die gleichen Bedingungen.
Dieses Feature lässt sich sehr gut zum Datenaustausch nutzen - beim nächsten Mal werde ich zeigen, wie man es zuammen mit XML-Views auf relationale Tabellen einsetzen kann. Man erzeugt dann nicht nur eine XML-Sicht über die relationalen Tabellen, sondern macht die Daten auch gleich per FTP oder HTTP verfügbar. Aber dies ... später ...

5. Dezember 2007

Umfrage zum Thema XML in der Datenbank ...

Zum Thema XML in der Oracle-Datenbank habe ich ja schon den einen oder anderen Post in diesem Blog geschrieben ... nun würde ich gerne ein Bild über die tatsächlichen XML-Themen, die so auf eurem Schreibtisch liegen, bekommen. Stimmt einfach ab: Habt Ihr mit XML in der Datenbank zu tun? Und wenn ja: Was ist die Anforderung ...?

4. Dezember 2007

Emails aus einem Postfach abrufen ... wieder mal mit SQL!

English title: Retrieving mails from an IMAP server - again: with SQL!

Emails aus der Datenbank heraus zu versenden, ist keine allzu kompilizierte Aufgabe - es wird tagtäglich mit Hilfe der bekannten PL/SQL-Pakete wie UTL_SMTP oder UTL_MAIL gemacht. Eine interessante Aufgabe wäre dagegen das Abrufen von Emails aus einem Postfach. Wozu kann sowas gut sein - man könnte bspw. Prozesse durch Emails antriggern - die Datenbank holt die Mails ab und starten dann PL/SQL-Logik ...
It is no problem to send emails from the database - with the provided PL/SQL packages UTL_SMTP or UTL_MAIL this is done day by day without problems. But the other way around is an interesting challenge: to retrieve mails from an mail account using SQL or PL/SQL. What's this good for? This is obvious: The process of retrieving emails could be triggered by a database event - some PL/SQL collects the email and processes them further.
Doch wie macht man das? Schließlich gibt es kein PL/SQL-Pakete zum Abrufen von Emails. Die Lösung lautet Java in der Datenbank. Die Java Mail API ist in der Java Welt sehr bekannt und kann nicht nur Mails senden, sondern auch aus Postfächern (POP3 und IMAP) abrufen. Also schreiben wir eine Java Stored Procedure: Diese verbindet sich auf den Mailserver, ruft die Mails ab und gibt Sie als Array zurück. Das Array wird dann auf ein PL/SQL VARRAY abgebildet, so dass die Java Stored Procedure wie eine Table Function verwendet werden kann. Doch genug der Vorrede - hier kommt der Code (dieses Codebeispiel ist nur mit IMAP-Mailservern getestet - eim Umschreiben auf POP3-Postfäche sollte aber nicht so kompliziert sein)
But how to do this? There is no PL/SQL package for retrieving Mails - with pure PL/SQL this simply cannot be done. The solution is (as in many other cases) java in the database. The java mail API is very well known (in the java programmers' world) and it can not only send mail but also retrieve it via POP3 as well as via the IMAP protocol. So we'll create a java stored procedure: This procedure connects to the mail server, retrieves the mail headers and returns them as an array - this array must then be converted to a PL/SQL VARRAY. This java stored procedure can finally be accessed like a table function. But this is enough talking - here's the code (this example is for IMAP email servers - changing it to POP3 should be an easy task)
set define off

drop procedure get_mail_headers
/
drop type mail_header_ct
/
drop type mail_header_t
/

create or replace type mail_header_t as object(
  subject       varchar2(4000),
  sender        varchar2(100),
  sender_email  varchar2(100),
  sent_date     date,
  deleted       char(1),
  read          char(1),
  recent        char(1),
  answered      char(1)
)
/

create or replace type mail_header_ct as table of mail_header_t
/


create or replace java source named "GetMailHeaders" as 
import java.sql.Connection;
import java.sql.DriverManager;

import java.util.Properties;
import java.util.Vector;

import javax.mail.Folder;
import javax.mail.FetchProfile;
import javax.mail.Message;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.Flags;
import javax.mail.internet.InternetAddress;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;


class getMails {

  public static ARRAY getMails(String host, int port, String user, String pass, int maxHeaders) 
  throws Exception {
    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    ArrayDescriptor aDescr = ArrayDescriptor.createDescriptor("MAIL_HEADER_CT", con);
    StructDescriptor rDescr = StructDescriptor.createDescriptor("MAIL_HEADER_T", con);
    Object[] mailHeader = new Object[8];
    STRUCT oraMail = null;
    Vector vMails = new Vector();
      
    Properties props = new Properties();
    props.setProperty("mail.store.protocol", "imap");
    Session s = Session.getDefaultInstance(props);
    Store store = s.getStore();
    store.connect(host, port, user, pass);
    Folder folder = store.getFolder("INBOX");
    folder.open(Folder.READ_ONLY);
    Message message[] = folder.getMessages();
    FetchProfile fp = new FetchProfile();
    fp.add(FetchProfile.Item.ENVELOPE);
    fp.add(FetchProfile.Item.FLAGS);
    folder.fetch(message, fp);
  
    String sPersonal = null;

    for (int i=message.length-1; i>=0 && ((i>=(message.length - maxHeaders)) || maxHeaders == -1);i--) {
      mailHeader[0] = new String(message[i].getSubject());
      sPersonal = ((InternetAddress)(message[i].getFrom()[0])).getPersonal();
      if (sPersonal == null) {
        mailHeader[1] = new String("");
      } else {
        mailHeader[1] = new String(sPersonal);
      }
      mailHeader[2] = new String(((InternetAddress)(message[i].getFrom()[0])).getAddress());
      mailHeader[3] = new java.sql.Timestamp(message[i].getSentDate().getTime());
      mailHeader[4] = (message[i].isSet(Flags.Flag.DELETED)?"Y":"N"); 
      mailHeader[5] = (message[i].isSet(Flags.Flag.SEEN)?"Y":"N"); 
      mailHeader[6] = (message[i].isSet(Flags.Flag.RECENT)?"Y":"N"); 
      mailHeader[7] = (message[i].isSet(Flags.Flag.ANSWERED)?"Y":"N"); 
      oraMail = new STRUCT(rDescr, con, mailHeader);
      vMails.add(oraMail);
    }
    folder.close(false);
    store.close();
    return new ARRAY(aDescr, con, vMails.toArray());
  }
}
/

alter java source "GetMailHeaders" compile
/
sho err
Hier ist der PL/SQL Wrapper, welcher die Java-Klasse auf eine PL/SQL Table Function abbildet.
This is the "PL/SQL wrapper" which makes the java method available to PL/SQL.
create or replace function get_mail_headers(
  p_mailhost in varchar2,
  p_mailport in number,
  p_username in varchar2,
  p_password in varchar2,
  p_max_mails in number 
) return MAIL_HEADER_CT
is language java name 'getMails.getMails(
  java.lang.String, 
  int, 
  java.lang.String, 
  java.lang.String, 
  int
) return oracle.sql.ARRAY';
/
Die Funktion GET_MAIL_HEADERS hat folgende Parameter:
  1. p_mailhost: Der Mailserver-Hostname oder die IP-Adresse
  2. p_mailport: Der TCP/IP-Port des Mailservers (normalerweise 143 für IMAP-Postfächer)
  3. p_username: Username zum Login in den Mailserver
  4. p_password: Passwort zum Login in den Mailserver
  5. p_max_mails: Maximal abzurufende Mails, "-1" ruft alle ab.
Und wie immer, wenn man mit Java Stored Procedures Netzwerkverbindungen nach "draußen" öffnen möchte, benötigt man Privilegien: Der folgende Code muss als SYS abgesetzt werden und räumt dem User SCOTT das Privileg ein, Netzwerkverbindungen zu mailserver.mydomain.com zu öffnen.
The function GET_MAIL_HEADERS is called with the following parameters:
  1. p_mailhost: The Mailserver hostname or its IP address
  2. p_mailport: TCP/IP port of the mailserver (in most cases "143" for IMAP servers)
  3. p_username: mailserver username
  4. p_password: mailserver password
  5. p_max_mails: maximum number of mails to be retrieved; "-1" gets all
And ... as always when opening network connections with java ... some privileges are needed. The following call grants the privilege to connect to mailserver.mydomain.com and must be issued as SYS.
begin
  dbms_java.grant_permission( 
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.net.SocketPermission',
    permission_name   => 'mailserver.mydomain.com', 
    permission_action => 'connect,resolve' 
  );
end;
/

commit
/
Ausprobieren ist dann ganz einfach ...
Testing is very simple ...
select sender, subject from table(
  get_mail_headers(
    'mailserver.domain.com', 
    143, 
    'carsten.czarski',
    'ganzgeheim', 
    -1
  )
)
/

SENDER                                  SUBJECT
--------------------------------------  ----------------------------------------
Max.Muster@maildomain.com               Hallo Carsten
:                                       :
Und was bringt das? Sowie die Mail-Header als (virtuelle) Tabelle mit SQL abrufbar sind, kann man jedes beliebige Web-Framework nutzen, um sie als Tabelle auf eine Webseite zu bringen - Ein Beispiel für die Darstellung mit Application Express findet sich in der Application Express-Community - einfach mal reinschauen.
And what's this good for ...? As the mail headers are available as a virtual table (via the table function) one can use every web framework to get them onto a web page in tabular format ("report"). A (german) example describing how this is used with Oracle Application Express can be found in the german Application Express community.

Beliebte Postings