24. November 2008

Mehr Sicherheit in Oracle11g: PL/SQL Netzwerk ACL

English title: Oracle11g is more secure: PL/SQL Networking ACLs

Oracle11g wird ja mehr und mehr genutzt. Und eine Erfahrung, die jeder Nutzer von UTL_SMTP, UTL_HTTP und anderen "Netzwerk"-Paketen auf Oracle11g macht ist, dass sie irgendwie nicht mehr funktionieren ... das sieht dann typischerweise so aus:
More and more people are actually working with Oracle11g and everyone who works with the PL/SQL network packages makes the same experience: Calls to UTL_SMTP, UTL_HTTP or the other network related PL/SQL packages don't work anymore - this looks then like the following ...
SQL> select httpuritype('www.oracle.de').getclob() from dual;
ERROR:
ORA-29273: HTTP-Anforderung nicht erfolgreich
ORA-06512: in "SYS.UTL_HTTP", Zeile 1674
ORA-24247: Netzwerkzugriff von Zugriffskontrollliste (ACL) abgelehnt
ORA-06512: in "SYS.HTTPURITYPE", Zeile 34
Am EXECUTE-Privileg liegt es nicht ... das ist vorhanden. Trotzdem gelingt der Netzwerkzugriff nicht. Grund ist ein in Oracle11g neu eingeführtes Sicherheitskonzept: Netzwerkzugriffe, welche durch PL/SQL-Pakete erfolgen, müssen vom DBA separat freigegeben werden.
It's not the missing EXECUTE privilege - this was granted. But the network access is blocked though. The reason is a new security concept for PL/SQL network access in Oracle11g. Such network access needs additional privileges.
Auf den ersten Blick macht das mehr Arbeit - genauer betrachtet ist das meiner Meining nach jedoch eins der besten Sicherheitsfeatures in Oracle11g. Man denke nur an einen Hacker, der durch eine SQL Injection-Lücke in die Datenbank eindringt und feststellt, dass er Zugriff auf das UTL_HTTP-Paket hat ... er kann nun neben der Datenbank auch auf das gesamte Netzwerk (welches von der Datenbank erreicht werden kann) zugreifen. Genau aus diesem Grund existiert auch die Empfehlung, das EXECUTE-Privileg auf diese Netzwerkpakete von PUBLIC zu entfernen (REVOKE EXECUTE on UTL_HTTP from PUBLIC) und es nur den Nutzern zu vergeben, die es wirklich brauchen.
At the first glance this is just more work: but IMHO this is one of the best security features in Oracle11g - ist think about a hacker which gets into your database via a SQL injection vulnerability. As soon as he finds out that he can use UTL_HTTP he gains access not only to the database but also to the network areas the database server is connected to. And exactly this is the reason for the "official" recommendation to revoke exceute privileges on those "network packages" from PUBLIC.
In Oracle11g kann, nein muss der Netzwerkzugriff für die einzelnen Ziele separat und feinganular freigegegeben werden. Und das geht mit dem neuen PL/SQL-Paket DBMS_ACL_ADMIN so:
Recap: In Oracle11g network access privileges can, no must be granted fine-grained. And this is done with the new PL/SQL package DBMS_ACL_ADMIN.
begin
  begin
    dbms_network_acl_admin.drop_acl(
      acl =>         'HTTP_OracleDe.xml'
    );
  exception 
    when others then null; -- ACL does not exist yet
  end;
  -- Privilege to connect to a host
  dbms_network_acl_admin.create_acl(
    acl =>         'HTTP_OracleDe.xml',
    description => 'HTTP-Connects zu www.oracle.de',
    principal =>   'CCZARSKI', -- DB Schema (grantee)
    is_grant =>    true,
    privilege =>   'connect',
    start_date  => null, 
    end_date  =>   null
  );
  -- Privilege to resolve a hostname (DNS lookup)
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl =>         'HTTP_OracleDe.xml',
    principal =>   'CCZARSKI', -- DB Schema (grantee)
    is_grant  =>   true,
    privilege =>   'resolve'
    start_date  => null, 
    end_date  =>   null
  );
  -- Privilege to connect to www.oracle.de
  dbms_network_acl_admin.assign_acl(
    acl =>         'HTTP_OracleDe.xml',
    host =>        'www.oracle.de',
    lower_port =>  80,
    upper_port =>  80
  );
  -- Privilege to connect to www.oracle.com (oracle.de is redirected to oracle.com)
  dbms_network_acl_admin.assign_acl(
    acl =>         'HTTP_OracleDe.xml',
    host =>        'www.oracle.com',
    lower_port =>  80,
    upper_port =>  80
  );
end;
/
    
commit
/
Diese Aufrufe schalten die Server www.oracle.de und www.oracle.com auf Port 80 frei. Wie Ihr an den Aufrufen erkennen könnt (Parameter is_grant, start_date und end_date), können Privilegien für bestimmte Zeiträume vergeben werden und neben "positiven" ACL's können auch "negative" Privilegien vergeben werden; á la "Das ganze Subnet "192.168.10.0", aber nicht den Server "192.168.10.88". Als "grantee" können wie immer auch Rollen und auch PUBLIC eingetragen werden. Und so kann man sich auch ein Skript schreiben, welches den "alten" Oracle10g-Zustand wiederherstellt - wobei ich davon für Produktionssysteme dringend abraten möchte; die ACLs sind ein echter Sicherheitsgewinn und es wäre töricht, sie abzuschalten. Auf einer Entwicklermaschine oder für den Übergang kann das folgende Skript aber ganz hilfreich sein:
The calls grant HTTP access (port 80) to www.oracle.de and www.oracle.com to the database schema or role CCZARSKI. As you can see the parameters is_grant, start_date and end_date allow to restrict network access ("negative" privileges) or to grant a privilege only for a particular timeframe. So a grant might include the whole subnet 192.168.10.0 but not the host 192.168.10.88. Not only database schemas can be used as grantees but also roles and PUBLIC. Having this in mind it's easy to write a script which restores 10g behaviour in 11g: I'd strongly recommand not to run this in a production environment - it makes the system less secure! But for a development server or for a migration period this might be helpful.
begin
  begin
    dbms_network_acl_admin.drop_acl(
      acl =>         'all-network-PUBLIC.xml'
    );
  exception 
    when others then null;  
  end;
  dbms_network_acl_admin.create_acl(
    acl =>         'all-network-PUBLIC.xml',
    description => 'Netzwerk-Connects fuer ALLE',
    principal =>   'PUBLIC',
    is_grant =>    true,
    privilege =>   'connect'
  );
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl =>         'all-network-PUBLIC.xml',
    principal =>   'PUBLIC', 
    is_grant  =>   true,
    privilege =>   'resolve'
  );
  dbms_network_acl_admin.assign_acl(
    acl =>         'all-network-PUBLIC.xml',
    host =>        '*'
  );
end;
/
sho err
    
commit
/
Eine Dictionary View für die ACLs gibt es auch: DBA_NETWORK_ACLS. Ach ja: noch etwas Wichtiges: Wie man an den "Dateinamen" für die ACL erkennen kann, ist hier XML im Spiel. Und tatsächlich: Die ACLs werden als XML in der XMLDB (dem XML DB Repository) gespeichert. Ab Oracle11g gilt also: Wenn man mit den genannten PL/SQL Netzwerk-Paketen arbeiten möchte, muss die XML DB in der Datenbank vorhanden sein.
There is also a dictionary view for the ACLs: DBA_NETWORK_ACLS. And by the way: The ACLs are actually stored in the XML DB Repository - XML DB functionality is being used for the ACL implementation. That means that if you want to use PL/SQL network packages in Oracle11g you must have XML DB enabled in your database.
Mehr Informationen zum Thema findet Ihr in der Oracle-Dokumentation.
More information is contained in the Oracle documentation.

17. November 2008

XML erzeugen in der Datenbank: Das Tutorial wird fortgesetzt

English title: Generating XML from database tables ... cont'd ...

Heute geht es mit dem im vorletzten Posting begonnenen Tutorial zum Thema XML generieren weiter. Versprochen waren ja Themen wie Hierarchien, Zeichensätzen und komplexe XML-Views. Zunächst bauen wie eine Hierarchie ein: Dazu gibt es die Funktion XMLAgg()
This is the second part of the generating XML tutorial which started in the posting a week ago .... Today's topics are XML hierarchys, more complex views and custom XML encodings. First we add a hierarchy; the SQL/XML function XMLAgg() is used for this
select
 xmlserialize(
  content
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
        xmlelement(
         "ename",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement(evalname(e.job)),
         xmlelement("salary", e.sal),
         xmlelement("commission", e.comm),
         XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
        )
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
   indent
 ) as xml
from scott.dept d
/

XML
------------------------------------------------------------------------------
<department>
  <name>ACCOUNTING</name>
  <employee>
    <ename id="7782" hire-date="1981-06-09">
      <name>CLARK</name>
      <MANAGER/>
      <salary>2450</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
  <employee>
    <ename id="7839" hire-date="1981-11-17">
      <name>KING</name>
      <PRESIDENT/>
      <salary>5000</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
  <employee>
    <ename id="7934" hire-date="1982-01-23">
      <name>MILLER</name>
      <CLERK/>
      <salary>1300</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
</department>

<department>
  <name>RESEARCH</name>
  <employee>
    <ename id="7369" hire-date="1980-12-17">
:
:
Diese XMLAgg()-Funktion kann beliebig geschachtelt werden, so dass auch komplexere, hierarchische XML-Dokumente möglich werden. Es leuchtet aber sofort ein, dass diese SQL-Abfragen bzw. die View-Definitionen in der Praxis etwas komplizierter werden, da meistens mehr als zwei Tabellen beteiligt sind. Versucht man, das alles in ein- und dieselbe View-Definition zu packen, dürfte man recht schnell die Übersicht verlieren ...
You can nest the XMLAgg() calls as much as you like; more complex XML documents with more hierarchy levels are possible. But it's obvious that the SQL queries get more and more confusing as you include more and more tables. And in real life there are most often more than two tables involved ...
Es empfiehlt sich eine Art "Bausteinkonzept". Zunächst erzeugen wir eine XML-View auf die Angestellten. Das ist wiederum die letzte Abfrage des vorletzten Postings.
The best approach is to create building blocks. First we create a view for just the employees - the SQL query is the last query in the first part of this tutorial.
create or replace view emp_xml as
select
 empno, 
 deptno,
 xmlelement(
  "employee",
  xmlelement(
   "ename",
   XMLAttributes(
    e.empno as "id",
    e.hiredate as "hire-date"
   ),
   xmlelement("name", e.ename),
   xmlelement(evalname(e.job)),
   xmlelement("salary", e.sal),
   xmlelement("commission", e.comm)
  )
 ) xml
from scott.emp e 
/

SQL> desc emp_xml
 Name                                      Null?    Typ
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 DEPTNO                                             NUMBER(2)
 XML                                                XMLTYPE
Die Informationen, die wir zum Join brauchen, werden als normale, relationale Tabellenspalten "neben" das XML gestellt. Außerdem haben wir die Funktion XMLSerialize() wieder entfernt, da das generierte XML im weiteren nochmals (und zwar als XML) benötigt wird. Die Umwandlung in den CLOB (und damit die Formatierung des XML) sollte immer nur zum Schluß erfolgen. Nun die View für die Abteilungen:
The information required for the joins is in normal relational columns besides the XML content. Furthermore the function XMLSerialize() is removed, since the generated XML will be needed by other views (as XML!). The rendering as a CLOB should be the last step in each case. Now the department view follows ...
create or replace view dept_xml as
select
 d.deptno,  
 xmlelement("department",
  xmlelement("name", d.dname),
  (
   select 
    XMLAgg(xml)
   from emp_xml e 
   where e.deptno = d.deptno
  )
 ) xml
from scott.dept d
/

SQL> desc dept_xml
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------
 DEPTNO                                             NUMBER(2)
 XML                                                XMLTYPE
Die Spalte XML dieser View enthält nun auch die Inhalte der View EMP_XML. Nach diesem Konzept können nun "Bausteine" anhand fachlicher Einheiten gebildet und zu komplexeren Objekten zusammengesetzt werden. Und wenn sich an der XML-Struktur für den employee etwas ändert, braucht man nur die View EMP_XML anzupassen - die Änderung wirkt sich wie immer auch auf alle anderen Views, in denen das XML verwendet wird.
The column XML of this view also contains the content of the EMP_XML view. Following this concept "building blocks" can be created based on business objects. And if the XML structure of EMP_XML changes, this is also reflected in all depending views.
Das Rendering des XML ist nun der letzte Schritt: Hier kann wieder die Funktion XMLSerialize() verwendet werden ...
The actual XML rendering is now the last step - and here the function XMLSerialize() might be used ...
select
 xmlserialize(
  document xmlroot(xml, version '1.0') 
 ) as xml_clob
from dept_xml
/

XML_CLOB
---------------------------------------------------------------------------
<?xml version="1.0"?>
<department>
  <name>ACCOUNTING</name>
  <employee>
    <ename id="7782" hire-date="1981-06-09">
      <name>CLARK</name>
      <MANAGER/>
      <salary>2450</salary>
      <commission/>
    </ename>
  </employee>
  <employee>
    <ename id="7839" hire-date="1981-11-17">
      <name>KING</name>
      <PRESIDENT/>
:
:
Nun (als letztes) zu den Zeichensätzen. Zunächst ist festzuhalten, dass XML in der Datenbank immer im Zeichensatz der Datenbank vorliegt. Ist der Datenbank-Zeichensatz also AL32UTF8, so liegt ein XML-Dokument auch stets in diesem Zeichensatz vor (das ist genauso wie bei einem CLOB). Soll das XML nun einen anderen Zeichensatz haben, so kann es nicht mehr als CLOB oder XMLTYPE vorliegen. Es muss in einen BLOB umgewandelt werden. Ein BLOB ist ohne Zeichensatzinformation; hier sind also beliebige Encodings möglich.
Last but not least we come to the XML encodings. The most important prerequisite is that any char datatype (except for NCHAR or NVARCHAR2) is encoded in the database characterset. This also applies to XMLTYPE. When the database has the characterset AL32UTF8 then each XMLTYPE is encoded as Unicode. If another encoding is needed the document has to be rendered as a BLOB.
select
 xmlserialize(
  document xmlroot(xml, version '1.0') as blob encoding 'windows-1252'
 ) as xml_blob
from dept_xml
/

XML_BLOB
--------------------------------------------------------------------------------
3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D2257494E444F57532D3132
3532223F3E0A3C6465706172746D656E743E0A3C6E616D653E4143434F554E54494E473C2F6E616D
653E0A3C656D706C6F7965653E0A3C656E616D652069643D22373738322220686972652D64617465
:
Guckt man sich das dann "richtig" an, so verbirgt sich hinter den Hexcodes folgender Inhalt.
These hexcodes are XML ...
<?xml version="1.0" encoding="WINDOWS-1252"?>
<department>
<name>ACCOUNTING</name>
:
Alternativ zur Funktion XMLSerialize() können auch die XMLTYPE-Methoden getblobval() und getclobval() verwendet werden. XMLSerialize() ist aber der Standard.
As an alternative to XMLSerialize() the XMLTYPE methods getblobval() or getclobval() might be also used. But XMLSerialize() is standard ...
select
  xmlroot(xml, version '1.0').getblobval(nls_charset_id('WE8MSWIN1252')) as xml_blob
from dept_xml
/

10. November 2008

Betriebssystem-Kommandos mit SQL und PL/SQL ausführen: Neue Version verfügbar

New version 0.6 available: Executing OS commands from the database

Letzte Woche hielt ich einen Vortrag über die Möglichkeiten von Java in der Datenbank auf der DOAG SIG Development. Am intensivsten nutze ich Java für das Paket zur Interaktion mit dem Betriebbsystem. Und dieses habe ich jüngst nochmals vervollständigt - die neue Version 0.6 steht nun zum Download bereit:
  • Es kann nun ein working directory für das Betriebssystem-Kommando angegeben werden
  • Es können eigene Umgebungsvariablen gesetzt werden.
  • Im FILE_PKG gibt es nun Methoden zum Abrufen der root-Verzeichnisse (wichtig für Windows) und des sog. path separator Zeichens.
  • Und einige Fehler sind auch behoben - so braucht FILE_TYPE nun keine Java Schreibrechte mehr, wenn eine Datei nur gelesen werden soll.
Last week I had a presentation about java in the database for the development sig of the German Oracle Users Group (DOAG). This is therefore a good time for an update to my PL/SQL package for operating system interaction - the new version 0.6 is now available for download:
  • Support for working directories when executing shell commands
  • Support for custom environment variables
  • Get environment specific information: Root directories and name separator character
  • FILE_TYPE no longer required write privileges when just reading files
Feedback ist natürlich erwünscht ... und das Tutorial "XML Generieren" wird mit dem nächsten Posting fortgesetzt - versprochen!
Feedback is very appreciated ... and the tutorial "generating XML" will be continued with the next posting - this is promised!

5. November 2008

XML erzeugen in der Datenbank: Ein kleines Tutorial

English title: Generating XML in the database - a brief tutorial

Wie man XML aus der Datenbank heraus generieren kann, hatte ich zwar das eine oder andere Mal in einem Blog-Posting behandelt, jedoch niemals so richtig komplett. Daher widme ich dieses und die nächsten Postings den SQL/XML-Funktionen zum Erzeugen von XML.
Generating XML from table data is a pretty frequent requirement. I had written something about this in the past, but these blog postings were not comprehensive. So this (and the next) blog posting is about XML generation - today in a more comprehensive way.
Wir fangen mal ganz einfach an und erzeugen das einfachste denkbare XML-Dokument mit einem leeren Tag.
We start simple and generate an XML document containing just one empty tag.
SQL> select xmlelement("tag") from dual;

XMLELEMENT("TAG")
--------------------------------------------------
<tag></tag>
Die SQL/XML-Funktionen sind SQL-Funktionen (übrigens Teil von SQL:2003), mit denen aus einer Abfrage heraus beliebig XML generiert werden kann. Für die diversen Elementtypen, die in XML möglich sind, gibt es diverse Funktionen:
Funktion:Verfügbar ab:Zweck:
XMLElement()9.2.0Erzeugt ein XML-Tag
XMLAttributes()9.2.0Erzeugt ein oder mehrere XML-Attribute innerhalb eines Tags
XMLForest()9.2.0Erzeugt mehrere XML-Tags auf einmal
XMLCDATA()10.2.0Erzeugt eine sog. CDATA-Section
XMLComment()10.2.0Erzeugt einen XML-Kommentar
XMLPI()10.2.0Erzeugt eine sog. Processing Instruction
XMLAgg()10.2.0Fasst mehrere XML-Tags zusammen und erzeugt eine Hierarchiestufe
XMLRoot()10.2.0Erzeugt den sog. XML-Prolog
The SQL/XML functions are part of the SQL:2003 standard and allow to construct XML from ordinary SQL queries. For the various components of the XML data model exist different functions:
Function:Availibility:Purpose:
XMLElement()9.2.0Produces a XML-Tag
XMLAttributes()9.2.0Generates one or more XML-Attributes inside a tag
XMLForest()9.2.0Generates multiple tags at once
XMLCDATA()10.2.0Generates a CDATA Section
XMLComment()10.2.0Produces a XML comment
XMLPI()10.2.0Generates a XML processing instruction
XMLAgg()10.2.0Aggregates multiple XML tags to a new hierarchy level
XMLRoot()10.2.0Generates the XML-Prolog
Die Funktionen geben stets XML zurück - aus Sicht der Datenbank ist das dann ein XMLTYPE. Nun einige Beispiele:
The functions return XML - so the returning datatype is XMLTYPE
1. Ein Tag mit Inhalt:
1. Producing a XML tag with contents:
SQL> select xmlelement("tag", 'Inhalt') from dual;

XMLELEMENT("TAG",'INHALT')
-------------------------------------------------------------
<tag>Inhalt</tag>
2. Die Inhalte werden aus einer Tabelle selektiert:
2. The tag content is being selected from a table:
SQL> select xmlelement("ename", ename) from scott.emp;

XMLELEMENT("ENAME",ENAME)
-------------------------------------------------------------
<ename>SMITH</ename>
<ename>ALLEN</ename>
<ename>WARD</ename>
<ename>JONES</ename>
:

14 rows selected.
3. Wir nehmen XML-Attribute hinzu:
3. Add XML attributes:
select 
 xmlelement(
  "ename", 
  XMLAttributes(
   empno as "id", 
   hiredate as "hire-date"
  ),
  ename
) from scott.emp
/

<ename id="7369" hire-date="1980-12-17">SMITH</ename>
<ename id="7499" hire-date="1981-02-20">ALLEN</ename>
<ename id="7521" hire-date="1981-02-22">WARD</ename>
:
4. Wir schachteln XML-Tags ineinander:
4. Nesting XML tags:
select 
 xmlelement(
  "employee",
  xmlelement(
   "name", 
   XMLAttributes(
    empno as "id", 
    hiredate as "hire-date"
   ),
   ename
  )
) from scott.emp
/

<employee><name id="7369" hire-date="1980-12-17">SMITH</name></employee>
<employee><name id="7499" hire-date="1981-02-20">ALLEN</name></employee>
<employee><name id="7521" hire-date="1981-02-22">WARD</name></employee>
:
Einschub: Kann man das ganze auch eingerückt darstellen (pretty print)? Ja, das geht - aber erst ab Oracle11g (11.1.0). Aber Vorsicht: Der zurückgegebene Datentyp ist nun CLOB - XMLSerialize() dient dazu, das XML in einen Text umzuwandeln, wird also genutzt, bevor das generierte XML die Datenbank "verlässt".
BTW: You might want to have pretty printed XML (with indents). This is possible with 11g (11.1.0). The returning data type changes to CLOB when you use XMLSerialize(). This function should be used when the generated XML has to "leave" the database as text.
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     ename
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">SMITH</ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">ALLEN</ename>
</employee>

<employee>
  <ename id="7521" hire-date="1981-02-22">WARD</ename>
</employee>
Technisch hat die Einrückung keine Bedeutung - sie dient lediglich dazu, das XML für den Menschen besser lesbar zu machen. Meistens ist jedoch eine Anwendung oder ein Prozeß der Konsument für das XML - und dieser benötigt kein Pretty Print. Im folgenden sind alle SQL-Abfragen mit Pretty-Printing angegeben; auf einer 10g-Umgebung einfach die XMLSerialize()-Funktion zu Beginn und das INDENT zum Ende entfernen
Pretty printing is just for better "human readability" of the XML document. A pretty printed XML document has the same semantics as a non pretty printed one. So if the XML is being consumed by another application pretty printing is not necessary. The following queries are shown with the pretty printing clause; for a 10g environment just remove the XMLSerialize() function (don't forget the INDENT keyword at the end).
5. Der Inhalt des Tags soll als sog. CDATA-Section generiert werden; das wird vor allem dann genommen, wenn die Texte länger sind und viele XML-Zeichen (<, >, &) beinhalten:
5. Now we want to generate a tag content as CDATA section; this is useful for longer texts with many "special XML characters" (<, >, &):
select
 xmlserialize(
  content
   xmlelement(
    "employee",
    xmlelement(
     "ename",
     XMLAttributes(
      empno as "id",
      hiredate as "hire-date"
     ),
     xmlcdata(ename)
    )
   )
  indent
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17"><![CDATA[SMITH]]></ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20"><![CDATA[ALLEN]]></ename>
</employee>
6a. Mehrere Tags auf einmal erzeugen (mit XMLForest()):
6a. Generate multiple XML tags at one (with XMLForest()):
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlforest(
      ename as "name",
      job as "job",
      sal as "salary",
      comm as "commission"
     )
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <job>SALESMAN</job>
    <salary>1600</salary>
    <commission>300</commission>
  </ename>
</employee>
Man sieht, dass das Tag commission (welches aus der Spalte COMM gespeist wird), mal vorhanden ist und mal nicht. XMLForest() erzeugt ein Tag, wenn Inhalte da sind, wenn nicht, wird keins erzeugt.
The tag commission (which contains the value of the table column COMMN) is present for some XML documents and for not for others. XMLForest() does not generate a tag if the column value is NULL.
6b. Mehrere Tags auf einmal erzeugen (jeweils explizit mit XMLElement()):
6b. Now we do the same with XMLElement():
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement("job", job),
     xmlelement("salary", sal),
     xmlelement("commission", comm)
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
    <commission/>
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <job>SALESMAN</job>
    <salary>1600</salary>
    <commission>300</commission>
  </ename>
</employee>
Und ist das Tag commission immer vorhanden - es wurde ja auch explizit verlangt. Wenn die Tabellenspalte den Inhalt NULL hat, bleibt das XML-Tag leer.
The tag commission is now present in each XML document. For NULL values the tag is empty.
7. Wir fügen einen Kommentar ein:
7. Generating a comment:
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement("job", job),
     xmlelement("salary", sal),
     xmlelement("commission", comm),
     XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
    <commission/>
    <!--Dieses XML-Dokument wurde am 02.11.2008 19:18 generiert.-->
  </ename>
</employee>
8. Nun soll der Inhalt der Tabellenspalte den Namen des XML-Tags bestimmen (dies ist ab Version 10.2.0.3 möglich):
8. Now the column value should determine the XML tag's name (this is available with 10.2.0.3 and higher):
select
 xmlserialize(
  content
   xmlelement(
    "employee",
    xmlelement(
     "ename",
     XMLAttributes(
      empno as "id",
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement(evalname(job)),
     xmlelement("salary", sal),
     xmlelement("commission", comm),
     XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
    )
   )
  indent
 ) as xml
from scott.emp
/

XML
----------------------------------------------------------------------
<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <CLERK/>
    <salary>800</salary>
    <commission/>
    <!--Dieses XML-Dokument wurde am 03.11.2008 11:18 generiert.-->
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <SALESMAN/>
    <salary>1600</salary>
    <commission>300</commission>
    <!--Dieses XML-Dokument wurde am 03.11.2008 11:18 generiert.-->
  </ename>
</employee>

:
Für heute soll's das sein. Beim nächsten Mal geht's dann mit Themen wie XML-Hierarchien, komplexen Views oder XML-Zeichensätzen (Encoding) weiter ...
Okay ... this should be enough for today ... We'll continue in the next blog posting with advanced topics like hierarchies, complex Views or how to produce an XML encoding clause ...

Beliebte Postings