31. Januar 2011

Neue Version des Betriebssystem-Packages: 1.0.0RC1

New version of operating system package: 1.0.0RC1
Heute habe ich eine neue Version meines Betriebssystem-Packages für PL/SQL veröffentlicht. Und langsam wird es ja auch mal Zeit, die "Nuller"-Versionen hinter sich zu lassen. Für diejenigen, die es noch nicht kennen: In diesem Paket habe ich Funktionen zusammengestellt, die den Zugriff auf das Dateisystem und die Shell vereinfachen. Zwar kann man mit UTL_FILE problemlos Dateien schreiben und lesen, FILE_PKG kann darüber hinaus jedoch Verzeichnisse auslesen - und zwar mit einem SELECT; wie bei einer Tabelle. OS_COMMAND erlaubt das Ausführen von Betriebssystem-Kommandos - und zwar mit vollem Zugriff auf stdin, stdout, stderr und auf den Return Code. Einfach mal ausprobieren ...
Today I released a new version of my operanting system package for PL/SQL. It now time to say goodbye to the "zero" version numbers and introduce version 1. I'll start with a "release candidate". For those not familiar with the package: It allows easy access to the filesystem, to files or to the operating system shell. Reading from or writing to files is possible with UTL_FILE but FILE_PKG allows to get directory listings with a SQL query - the directory is being exposed like a database table! OS_COMMAND allows executing shell commands - with full access to stdin, stdout, stderr and the Return Code. Just give it a try ...
Und das ist im Release 1.0 RC1:
And that is new to Release 1.0 RC1:
  • Die Prozedur FILE_PKG.SET_FS_ENCODING erlaubt es, für Dateinamen mit einem anderen als dem Datenbankzeichensatz zu arbeiten. Das ist hilfreich bei Dateinamen mit Umlauten.
  • Die EXEC-Prozeduren im Package OS_COMMAND können die Ausgaben von "stdout" und "stderr" nun getrennt voneinander verarbeiten.
  • The new procedure FILE_PKG.SET_FS_ENCODING allows to work with different encodings for file names. If filenames contain special characters (umlauts) this is useful.
  • The EXEC procedures in OS_COMMAND are now able to handle "stdout" and "stderr" separately.
Und schließlich habe ich intern ein wenig optimiert. Es ist manchmal immer wieder beeindruckend, was das Erzeugen von Java-Objekten kostet. Im konkreten Fall habe ich den Code so umgestellt, dass einige interne Objekte nicht mehr jedesmal neu, sondern nur einmal erstellt und danach wiederverwendet werden. Das Ergebnis kann sich sehen lassen. Hier ist ein Directory-Listing mit der vorherigen Version 0.9.2.
And last but not least I did some internal optimizations. I saw that I recreated some internal java object for each call - and decided to create them only once and to reuse them afterwards. When I tested the effect I was stunned ... here is a directory listing with the previous version 0.9.2.
SQL> select count(*), sum(file_size) from table(file_pkg.get_file_list(file_pkg.get_file('/usr/bin')))

  COUNT(*) SUM(FILE_SIZE)
---------- --------------
      1287       76976156

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.62
Und hier die neue Version 1.0.0RC1 (gleiche Maschine, gleiche Datenbank, nur anderes Schema):
And this is the listing with new version 1.0.0RC1 (same machine, same database, just other schema):
SQL> select count(*), sum(file_size) from table(file_pkg.get_file_list(file_pkg.get_file('/usr/bin')))

  COUNT(*) SUM(FILE_SIZE)
---------- --------------
      1287       76976156

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.35
Es lohnt sich also auf jeden Fall, die neue Version auszuprobieren. Über Feedback freue ich mich (wie immer).
Just try it out - if you have feedback for the package, let me know ...

11. Januar 2011

XML-Dokumente und SQL: EXTRACTVALUE oder XMLTABLE?

Working with XML documents: EXTRACTVALUE or XMLTABLE?
In der Vergangenheit hatte ich ja schon einige Blog-Postings zum Umgang mit XML-Dokumenten geschrieben. Es ist ja in der Oracle-Datenbank schon seit der Version 9i sehr einfach, Informationen mit den Funktionen EXTRACT und EXTRACTVALUE aus XML-Dokumenten zu extrahieren. Allerdings hat sich die SQL-Standardisierung in den letzten Jahren fortentwickelt, so dass diese und andere bekannte ab der Datenbankversion 11.2 als deprecated, also "veraltet" gekennzeichnet sind. Sie funktionieren immer noch, werden wohl auch noch eine sehr lange Zeit weiter funktionieren; man sollte allerdings (vor allem in neuen Projekten) allmählich auf die neue Syntax mit den Funktionen XMLQUERY und XMLTABLE umsteigen. Dazu ein Beispiel:
In the past I already had some Blog postings about working with XML documents and SQL. Since Oracle9i it is very easy to extract information out of XML documents using the SQL functions EXTRACT and EXTRACTVALUE. But the SQL standard moved forward during the last years and it turned out that the SQL standard contains other functions for that purpose. With the database version 11.2 Oracle marked some of the old and well-known SQL functions for XML as deprecated. They still work and they will work in the future as well - but since future optimizations and enhancements will concentrate on the new, standard-compliant functions I'd recommend to start using the new functions XMLQUERY and XMLTABLE. This blog posting is about XMLTABLE. We start with an example:
create table xmltest (
  id number(10),
  xml xmltype
)
/

insert into xmltest values (
  1, 
  '<emps cnt="2">
    <emp ename="FORD" sal="3000"/>
    <emp ename="MILLER" sal="5000"/>
   </emps>'
);
insert into xmltest values (
  2, 
  '<emps cnt="3">
    <emp ename="KING" sal="10000"/>
    <emp ename="CLARK" sal="4000"/>
    <emp ename="MEYER" sal="7500"/>
   </emps>'
);

commit
/
Nun ein paar Beispiele - wenn es darum geht, aus jeder Zeile das XML-Attribut cnt des Tags emps auszulesen, geht das schon seit Oracle9i wie folgt.
Now we'll do some queries to extract information from the XML documents. In the first example we need the attribute cnt of the XML tag emps in each table row. The "old" and well known syntax is as follows.
select 
  id,
  extractvalue(xml, '/emps/@cnt') emp_cnt
from xmltest
/

        ID    EMP_CNT
---------- ----------
         1          2
         2          3
Das funktioniert, verwendet man die objektrelationale Speicherung oder ab Oracle11g die neue Varinate Binary XML, so wird auch optimiert ausgeführt. Dennoch entspricht es nicht dem SQL-Standard - und da man (wie immer) davon ausgehen kann, dass künftige Optimierungen sich auf die standardisierte Syntax beziehen werden, ist es sinnvoller, die folgende Syntax zu verwenden.
This works well - and if you use the object relational or the Binary XML storage options the query is being optimized for fast execution. But the standard-compliant functions look different - so you might formulate the same query in future projects as follows.
select 
  t.id,
  x.emp_cnt
from 
  xmltest t, 
  xmltable(
    'for $i in /emps return $i'
    passing xml
    columns "EMP_CNT" number path '/emps/@cnt'
  ) x
/

        ID    EMP_CNT
---------- ----------
         1          2
         2          3
Ergebnis und auch die Ausführungsweise sind gleich. Allerdings hat die neue Syntax einen Vorteil; denn es ist möglich, den Datentypen der neuen Spalte vorzugeben. Da wir in diesem Beispiel die textbasierte Speicherung der XML-Dokumente nutzen (weder objektrelational noch Binary XML), werden grundsätzlich alle extrahierten Informationen als VARCHAR2(4000) zurückgegeben - Ihr könnt das prüfen, indem Ihr mit dem SELECT eine View erzeugt. Die neue Syntax erlaubt in der COLUMNS-Klausel jedoch, die Datentypen genau vorzugeben - früher wären hierfür diverse TO_NUMBER-Aufrufe nötig gewesen.
The query returns the same result and should use the same execution plan (depending on the chosen XML storage option). But there is one advantage: The COLUMNS clause allows to set the datatype of the returned column - so you don't need TO_NUMBER or TO_DATE calls any more. This is particularly useful when the XML is stored with the CLOB (textbased) approach - any extracted information is a VARCHAR2(4000) for the database (it has no information about the datatype). So when you use the "old" functions EXTRACT or EXTRACTVALUE the results are always returned as VARCHAR2(4000). The COLUMNS clause of the XMLTABLE function allows to set the desired datatype in a very elegant manner.
Die XML-Dokumente in diesem Beispiel enthalten eine 1:n-Beziehung: das XML-Tag emp kommt mehrfach vor. Auch das war in der Vergangenheit kein Problem - mit dem Funktionskonstrukt TABLE(XMLSEQUENCE(EXTRACT(...))) kann man Informationen problemlos auslesen.
As in reality: The XML documents in this example contain a one-to-many relationship; the XML tag emps contains multiple emp tags. That is no problem: Since Oracle9i we can use the syntax construct TABLE(XMLSEQUENCE(EXTRACT(...))) to extract each and every piece of information.
select 
  id,
  extractvalue(xml, '/emps/@cnt') emp_cnt,
  extractvalue(value(e), '/emp/@ename') ename,
  extractvalue(value(e), '/emp/@sal') sal
from xmltest,
  table(xmlsequence(extract(xml, '/emps/emp'))) e
/

        ID    EMP_CNT ENAME                        SAL
---------- ---------- -------------------- -----------
         1          2 FORD                        3000
         1          2 MILLER                      5000
         2          3 KING                       10000
         2          3 CLARK                       4000
         2          3 MEYER                       7500
Auch hier ist die standardisierte Funktion XMLTABLE anwendbar - und (betrachtet man es genau) auch besser lesbar.
The standardized function XMLTABLE is also better suited for this - and (to be honest) it has a much better readability.
select 
  t.id,
  x.emp_cnt,
  e.ename,
  e.sal
from 
  xmltest t, 
  xmltable(
    'for $i in /emps
     return $i'
    passing xml
    columns "EMP_CNT" number path '/emps/@cnt'
  ) x,
  xmltable(
    'for $i in /emps/emp return $i'
    passing xml
    columns "ENAME"   varchar2(20) path '/emp/@ename',
            "SAL"     number       path '/emp/@sal'
  ) e
/
Ich persönlich hatte mich so gut mit der "alten" Syntax angefreundet, dass ich mich wirklich "zwingen" muss, auf die neue umzusteigen. Allerdings finde ich gerade die COLUMNS-Klausel mit der Möglichkeit, die Datentypen vorzugeben, sehr elegant. Das for $i in ... return $i ist XQuery - und man könnte nun noch seitenweise darüber schreiben - aber für dieses Mal möchte ich es damit belassen.
In the past I got very familar with EXTRACT and EXTRACTVALUE and I really have to "force" myself to work with the new functions. But the COLUMNS clause alone is worth the efforts. The for $i in ... return $i syntax inside the XMLTABLE function is XQuery; we could now move on and do another 10 blog postings on that topic ... but not today.

Beliebte Postings