Tabellendaten als XML im Browser: einfach so!

English title: Accessing table data as XML with a browser - no coding required

Vor einiger Zeit hatte ich ja mal ein kleines Tutorial zum Thema XML erzeugen mit den SQL/XML-Funktionen veröffentlicht. Damit ist es möglich, Tabellendaten in beliebiger Art und Weise als XML aufzubereiten.

Some time ago I published a tutorial about generating XML using the SQL/XML functions. With this functions you can construct XML from table data. The functions are very powerful - you can construct any XML structure you want.

Aber wusstet Ihr schon, dass Ihr XML auch völlig ohne Code generieren könnt ...?

But did you know that you can generate simple XML without any coding ...?

Seit Oracle9i gibt es das DB URI Servlet und die DB URI Types. Und das lässt sich ganz einfach nutzen. Zuerst solltet Ihr prüfen, ob der HTTP-Listener eurer Datenbank aktiv ist. Das geschieht ganz einfach mit einem lsnrctl status.

Since Oracle9i there is the DBURI Servlet and the DBURI type. The usage of this is very simple. But beforehand you should check whether the HTTP listener of your database is actually running. This is done with lsnrctl status.

$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 27-MAY-2009 19:57:37

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.140)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                27-MAY-2009 19:49:19
Uptime                    0 days 0 hr. 8 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/u01/app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /oracle/u01/app/oracle/diag/tnslsnr/vmware/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.140)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmware.de.oracle.com)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.doag.org" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
Service "orclXDB.doag.org" has 1 instance(s).
  Instance "orcl", status READY, has 3 handler(s) for this service...
Service "orcl_XPT.doag.org" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
The command completed successfully

Wenn die rote Zeile bei euch nicht vorhanden ist, müsst Ihr den HTTP-Listener erst aktivieren. Das geht bei 11g-Datenbanken mit der Prozedur DBMS_XDB.SETHTTPPORT oder auch bei älteren Datenbanken mit dem SQL-Skript $ORACLE_HOME/rdbms/admin/catxdbdbca.sql. Nehmt einen TCP/IP-Port eurer Wahl; mit dem Port "0" wird der HTTP-Listener abgeschaltet.

If the red line is missing in your environment the HTTP listener is deactivated (which is the default). You can activate it with the procedure DBMS_XDB.SETHTTPPORT in Oracle11g and the $ORACLE_HOME/rdbms/admin/catxdbdbca.sql in older releases. Just choose an TCP/IP port; zero ("0") deactivates the listener.

Nun könnt Ihr Tabellendaten als XML abrufen. Startet einen Browser und gebt mal folgende URL ein, umdie Daten der EMP-Tabelle zu betrachten.

Now you can start viewing table data - start your browser and enter the following URL.

http://[database-host]:[http-port]/oradb/SCOTT/EMP

Ihr werdet zunächst aufgefordert, euch einzuloggen; nehmt einen Datenbankuser, der die Tabelle EMP lesen kann (naheliegenderweise SCOTT). Daraufhin solltet Ihr in etwa folgendes Ergebnis sehen.

First you get prompted for username and password. Enter the credentials of a user which is capable to select the EMP table (I'd suggest the SCOTT user). You should see the following result then.

Die XML-Struktur ist in diesem Fall vorgegeben; sie kann nicht beeinflusst werden. Aber im Gegenzug generiert die Datenbank ohne viel Aufwand XML aus den Tabellendaten. Wenn Ihr eigene XML-Strukturen benötigt, sind wir wieder bei den eingangs erwähnten SQL/XML-Funktionen. Und in manchen Fällen reicht diese einfache XML-Struktur vielleicht sogar aus ...

The structure of the XML document cannot be changed - it is always that simple. If you need other XML structures you have to code a view with the above mentioned SQL/XML functions. But there might be cases in which this simple structure is sufficient.

Ihr könnt die Ansicht auch mit Kriterien beschränken; dazu wird XPath-Syntax verwendet. Aber Achtung; das DB URI Servlet unterstützt nicht die komplette XPath-Syntax, nur ein Subset kann verwendet werden. Die XML-Funktionen in der Datenbank wie EXTRACTVALUE oder EXISTSNODE unterstützen XPath dagegen vollständig. So zeigt die folgende URL nur eine Zeile aus der EMP-Tabelle an.

You can apply filter criteria to the URI; use XPath notation for this. But keep in mind that this particular DBURI Servlet does not support 100% of XPath. The XML functions in the database(like EXISTSNODE or EXTRACTVALUE) at the other hand do fully support XPath.

http://[host]:[port]/oradb/SCOTT/EMP/ROW[EMPNO=7839]

Auch andere Kriterien sind möglich; wenn mehrere Zeilen selektiert werden, ist jedoch ein zusätzlicher Parameter rowsettag nötig, sonst gibt es eine Fehlermeldung.

Other criteria is also possible - but use the rowsettag parameter when you expect more then one row - an error is raised otherwise.

http://[host]:[port]/oradb/SCOTT/EMP/ROW[JOB="CLERK"]?rowsettag=QUERY

Mit der Funktion SYS_DBURIGEN könnt Ihr euch URLs generieren lassen. Damit wird es möglich, auf eine bestimmte Tabellenspalte in einer bestimmten Zeile mit genau einer Zeichenkette, eben der URI zu verweisen.

The SYS_DBURIGEN function allows to generate such URI. An URI is (expressed otherwise) a pointer to a single value in the database - as the following example illustrates.

SQL> select sys_dburigen(ENAME, SAL) from partner.emp where empno=7844;

SYS_DBURIGEN(ENAME,SAL)(URL, SPARE)
--------------------------------------------------------------------------------
DBURITYPE('/PARTNER/EMP/ROW[ENAME=''TURNER'']/SAL', NULL)

Setzt man nun Hostname und Port und das Schlüsselwort oradb davor, so kann man sich das Ergebnis im Browser ansehen ... aber auch in SQL können diese URI verwendet werden. Dazu gibt es den Objekttypen DBURITYPE.

Prefix this with the hostname, the TCP/IP-Port of the HTTP listener and the oradb keyword and you can (again) bnavigate to this value with the browser. But the DBURIs can also be used in SQL queries - the DBURITYPE object type is there for this.

SQL> select dburitype('/PARTNER/EMP/ROW[ENAME=''TURNER'']/SAL').getxml() from dual;

DBURITYPE('/PARTNER/EMP/ROW[ENAME=''TURNER'']/SAL').GETXML()
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<SAL>1501</SAL>

Probiert es mal aus ... insbesondere die Variante über den Browser kann sehr interessant sein. Wenn man nun noch berücksichtigt, dass Kommandozeilenwerkzeugen wie wget es erlauben, diese Funktionalität in Batchläufen zu nutzen, dann wird es sehr einfach, XML von der Datenbank in andere Systeme zu übertragen. Einfacher geht es nun wirklich nicht ...

Give it a try. The simple browser access might be of particluar interest if you need to provide XML data from the database in a short timeframe. Consider the wget commandline utility which allows to use this feature in batch processes. Transferring XML data between the database and other systems is then a very simple task.

Mehr Informationen zu den DB URIs findet Ihr im XML DB Handbuch. Darin enthalten sind auch Informationen über zusätzliche HTTP-Parameter, die Ihr verwenden könnt - so kann transform eine XSLT-Transformation auslösen ... schaut einfach mal rein.

More information is contained in the XML DB documentation. This also contains additional parameters which can be applied to the HTTP URL's (e.g. transform for Stylesheet (XSLT)-transformations).

Mehr zu Flashback Query (Abfragen in die Vergangenheit)

English title: More about Flashback Query (Queries into the past)

Mit dem Thema Flashback Query hatte ich ja bereits im vorletzten Blog-Posting begonnen. Dort könnt Ihr lesen, wie Ihr mit der AS OF-Klausel eine Abfrage zu einem Zeitpunkt in der Vergangenheit ausführen könnt.

The last but one posting contained some information about the Flashback Query feature which allows to execute a SQL query "in the past".

Heute stelle ich einige weiterführende Konzepte vor. Zunächst die Flashback Versions Query, auf die in einem der Kommentare zu Recht schon verwiesen wurde:

Today I'll continue on this topic - there are some additional features available beyond the "pure" flashback query. First there is the Flasback Versions Query which was pointed out by one of the commantators. So I'll pick this up:

SELECT empno, ename, versions_operation, versions_xid, versions_starttime FROM emp versions between timestamp minvalue and maxvalue ORDER BY empno, versions_starttime

     EMPNO ENAME      V VERSIONS_XID     VERSIONS_STARTTIME
---------- ---------- - ---------------- ---------------------
      7369 SMITH      D 14001D007C170000 07.05.09 10:09:53
      7369 SMITH      I 0C0004001B180000 07.05.09 11:14:18
      7369 SMITH
      7499 ALLEN      D 14001D007C170000 07.05.09 10:09:53
      7499 ALLEN      I 0C0004001B180000 07.05.09 11:14:18
      7499 ALLEN
      7521 WARD       D 14001D007C170000 07.05.09 10:09:53
      7521 WARD       I 0C0004001B180000 07.05.09 11:14:18
      7521 WARD
      7566 JONES      D 14001D007C170000 07.05.09 10:09:53
         : :          : :                :

Man kann also nicht nur die Daten zum Zeitpunkt X, sondern auch die Operationen, welche die Daten seitdem verändert haben, ansehen. Die Klausel VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE besagt, dass alle in den Undo-Segmenten vorhandenen Versionen selektiert werden sollen. Wieviel das tatsächlich ist, hängt von der Größe des Undo-Tablespace und von der Transaktionslast auf dem System ab. Die Spalte VERSIONS_OPERATION gibt wieder, ob die Daten eingefügt ("I"), geändert ("U") oder gelöscht ("D") wurden. Spalten wie VERSIONS_XID, VERSIONS_SCN und VERSIONS_STARTTIME enthalten Details zur jeweiligen Datenbanktransaktion. Damit müsste es ja eigentlich möglich sein, das Undo-SQL zu konstruieren ... und auch das ist kein Problem; die View FLASHBACK_TRANSACTION_QUERY enthält genau diese Information.

The Flashback Versions Query shows not only the data as it was in the past but also information about all transactions which modified the data from the past until the current version. The VERSIONS BETWEEN TIMESTAMP MINVALUE and MAXVALUE clause leads the database to show all undo information which is available. The actual amount of information depends (as explained in the previous but one posting) on the undo tablespace size and the trancation load on the system. The VERSIONS_OPERATION column contains a flag indicating the kinf of XML which modified the row: "I" is for SQL INSERT, "U" for UPDATE and "D" was a DELETE operation. The other columns (VERSIONS_SCN, VERSIONS_STARTTIME etc) contain detailed information about the transaction. With this information it's basically possible to construct some kind of UNDO SQL - and this is also available: The FLASHBACK_TRANSACTION_QUERY view contains the desired information.

select commit_scn, operation, row_id, undo_sql from flashback_transaction_query where table_name = 'EMP'

COMMIT_SCN OPERATION                        ROW_ID
---------- -------------------------------- -------------------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------------------------------------
      4711 INSERT                           AAApb3AAEAAANxgAAA
delete from "PARTNER"."EMP" where ROWID = 'AAApb3AAEAAANxgAAA';

      4711 INSERT                           AAApb3AAEAAANxgAAB
delete from "PARTNER"."EMP" where ROWID = 'AAApb3AAEAAANxgAAB';

      4711 INSERT                           AAApb3AAEAAANxgAAC
delete from "PARTNER"."EMP" where ROWID = 'AAApb3AAEAAANxgAAC';

      4711 INSERT                           AAApb3AAEAAANxgAAD
delete from "PARTNER"."EMP" where ROWID = 'AAApb3AAEAAANxgAAD';

      4711 DELETE                           AAApb3AAEAAANxgAAb
insert into "PARTNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7934','MILLER','CLERK','7782',TO_DATE('23.01.82', 'DD.MM.RR'),'1301',NULL,'10');

      4711 DELETE                           AAApb3AAEAAANxgAAa
insert into "PARTNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7902','FORD','ANALYST','7566',TO_DATE('03.12.81', 'DD.MM.RR'),'3000',NULL,'20');

      4711 DELETE                           AAApb3AAEAAANxgAAZ
insert into "PARTNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7900','JAMES','CLERK','7698',TO_DATE('03.12.81', 'DD.MM.RR'),'951',NULL,'30');

      4711 DELETE                           AAApb3AAEAAANxgAAY
insert into "PARTNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7876','ADAMS','CLERK','7788',TO_DATE('12.01.83', 'DD.MM.RR'),'1101',NULL,'20');

Nun ist das Abfragen der Daten in der Vergangenheit mit der AS OF-Klausel sehr bequem, aber es kommt doch recht häufig vor, dass man ein SQL-Kommando vielleicht gar nicht ändern kann oder nicht möchte. Auch hierfür gibt es eine Lösung: Das Paket DBMS_FLASHBACK. Nach Aufruf der Prozedur ENABLE_AT_TIME zielen alle SQL-Abfragen auf den gesetzten Zeitpunkt; man spart sich also die AS OF-Klausel.

Flashback Query usage with the AS OF clause is very convenient, but there are situations in which a query cannot be modified. For those cases there is the PL/SQL package DBMS_FLASHBACK. The procedure ENABLE_AT_TIME enables Flashback Query for all subsequent SQL queries. The AS OF clause is not necessary then.

begin dbms_flashback.enable_at_time( query_time => systimestamp - interval '15' minute ); end;

Passend dazu gibt es natürlich auch die Prozedur DISABLE. Viel Spaß beim Ausprobieren.

Disabling Flashback Query is (of course) also possible: With the procedure DISABLE. Have fun!