29. Mai 2012

Tabellen- oder andere Metadaten als XML ausgeben ...

Get table (or object) metadata ... as XML

Jüngst erreichte mich die Frage, wie man eine Tabellendefinition als XML ausgeben kann - für eine gegebene Tabelle (bspw. EMP) wird also eine Ausgabe in etwa wie folgt benötigt ...

<table schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

Und sowas ist einfacher, als man denkt: Denn man muss gerade nicht selbst das Data Dictionary durcharbeiten, es gibt fertige Funktionen dafür. Das PL/SQL-Package der Wahl ist DBMS_METADATA. Das kennt nicht nur die GET_DDL-Funktion zum Erzeugen von SQL-Skripts, sondern auch die Funktionen GET_XML und GET_SXML. Beide Varianten erzeugen ein XML-Format ...

  • GET_XML generiert ein recht umfangreiches XML, welches auch wirklich alle Informationen zum Datenbankobjekt enthält. Allerdings sind viele Details intern kodiert - die Datentypen der Tabellenspalten sind bspw. nicht als Typ-Bezeichnungen, sondern als Oracle-Interne Type-IDs enthalten. Dieses Format ist vor allem für die Datenbank selbst gedacht.
    <?xml version="1.0"?>
    <ROWSET><ROW>
      <TABLE_T>
     <VERS_MAJOR>1</VERS_MAJOR>
     <VERS_MINOR>3 </VERS_MINOR>
     <OBJ_NUM>78581</OBJ_NUM>
     <SCHEMA_OBJ>
      <OBJ_NUM>78581</OBJ_NUM>
      <DATAOBJ_NUM>78581</DATAOBJ_NUM>
      <OWNER_NUM>96</OWNER_NUM>
      :
    
  • GET_SXML generiert ein XML-Dokument, welches wie eine XML-Repräsentation eines DDL-Skripts aussieht und dementsprechend die Informationen enthält, die man auch in einem CREATE TABLE Kommando verwendet. Mit einem solchen XML kann man weiterarbeiten ...
    <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
     <SCHEMA>TESTIT</SCHEMA>
     <NAME>EMP</NAME>
     <RELATIONAL_TABLE>
        <COL_LIST>
           <COL_LIST_ITEM>
              <NAME>EMPNO</NAME>
              <DATATYPE>NUMBER</DATATYPE>
              <PRECISION>4</PRECISION>
              <SCALE>0</SCALE>
              <NOT_NULL></NOT_NULL>
           </COL_LIST_ITEM>
       :
    

DBMS_METADATA macht also schon fast die ganze Arbeit. Übrigens arbeitet DBMS_METADATA intern immer mit XML. Auch wenn man mit GET_DDL ein SQL-Skript anfordert, wird zunächst ein XML generiert (GET_XML) und danach wird dieses XML in ein SQL-Skript umgewandelt. GET_DDL macht das alles auf einmal. Wenn man das Generieren der SQL-Skripte genau konfigurieren möchte und mit den Aufrufen von OPEN, ADD_TRANSFORM_PARAM, ADD_REMAP_PARAM, FETCH und CLOSE des Pakets DBMS_METADATA arbeitet, dann legt man mit ADD_TRANSFORM fest, ob man die Metadaten als XML, SXML oder als DDL-Skript haben möchte.

Nun haben wir mit DBMS_METADATA.GET_SXML schon ein XML-Dokument für unsere Tabelle; allerdings sieht das XML-Format noch nicht so aus, wie es oben gefordert wurde. Aber in der Welt von XML und der Oracle-Datenbank ist das ein Kinderspiel: Ein XML in ein anderes XML transformieren - dazu sind XSLT-Stylesheets gut geeignet. Und die Funktion XMLTRANSFORM macht eine solche XSLT-Transformation direkt in der Datenbank. Also zunächst das Stylesheet ...

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:ku="http://xmlns.oracle.com/ku" version="1.0">
<xsl:output method="xml"/>
 <xsl:template match="/">
  <xsl:for-each select="/ku:TABLE">
  <table>
   <xsl:attribute name="schema">
    <xsl:value-of select="ku:SCHEMA/text()"/>
   </xsl:attribute>
   <xsl:attribute name="name">
    <xsl:value-of select="ku:NAME/text()"/>
   </xsl:attribute>
   <xsl:for-each select="ku:RELATIONAL_TABLE/ku:COL_LIST/ku:COL_LIST_ITEM">
    <column>
     <xsl:variable name="current-column">
      <xsl:value-of select="ku:NAME"/>
     </xsl:variable> 
     <xsl:attribute name="name">
      <xsl:value-of select="ku:NAME"/>
     </xsl:attribute>
     <xsl:attribute name="type">
      <xsl:choose>
       <xsl:when test="ku:DATATYPE = 'VARCHAR2'">
        <xsl:value-of select="concat(ku:DATATYPE,'(', ku:LENGTH, ')')"/>
       </xsl:when>
       <xsl:otherwise>
        <xsl:choose>
         <xsl:when test="ku:DATATYPE = 'NUMBER'">
          <xsl:choose>
           <xsl:when test="ku:PRECISION">
            <xsl:value-of select="concat(ku:DATATYPE,'(', ku:PRECISION, ',', ku:SCALE, ')')"/>
           </xsl:when>
           <xsl:otherwise>
            <xsl:value-of select="ku:DATATYPE"/>
           </xsl:otherwise>
          </xsl:choose>
         </xsl:when>
         <xsl:otherwise>
          <xsl:value-of select="ku:DATATYPE"/>
         </xsl:otherwise>
        </xsl:choose>
       </xsl:otherwise>
      </xsl:choose>
     </xsl:attribute>
     <xsl:for-each select="../../ku:PRIMARY_KEY_CONSTRAINT_LIST/ku:PRIMARY_KEY_CONSTRAINT_LIST_ITEM/ku:COL_LIST/ku:COL_LIST_ITEM">
      <xsl:choose>
       <xsl:when test="ku:NAME/text() = $current-column">
        <xsl:attribute name="primary-key">
         <xsl:value-of select="position()"/>
        </xsl:attribute>
       </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </column>
   </xsl:for-each>
  </table>
   </xsl:for-each>
 </xsl:template>
</xsl:stylesheet> 

Dieses XSLT könnte man nun in eine Tabelle speichern. Man kann nun mit BFILE, UTL_FILE oder dem XML DB Repository arbeiten. Letzteres ist ein sehr einfacher und eleganter Weg, Dateien in die Datenbank zu bringen. Schaltet zunächst den FTP-Port der Datenbank mit DBMS_XDB.SETFTPPORT(2100) frei. Danach bringt Ihr das Stylesheet so in die Datenbank.

D:\>ftp -n
ftp> open sccloud030 2100
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.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> put table.xsl
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 2041 bytes sent in 0,00Seconds 2041000,00Kbytes/sec.
ftp>

Nun ist alles da: Mit DBMS_METADATA.GET_SXML holen wir die Metadaten der Tabelle EMP als XML (Oracle "SXML") und danach wandeln wir dieses mit dem XSLT-Stylesheet in ein anderes XML um. Achtet auf die Anwendung der XDBURITYPE-Funktion - mit der holen wir das Stylesheet aus dem XML DB Repository (wohin wir es per FTP hochgeladen hatten) wieder heraus.

select 
 xmltransform(
  xmltype(dbms_metadata.get_sxml('TABLE','EMP')), 
  xdburitype('/public/table.xsl').getxml()
) from dual;

XMLTRANSFORM(XMLTYPE(DBMS_METADATA.GET_SXML('TABLE','EMP')),XDBURITYPE('/PUBLIC/
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<table xmlns:ku="http://xmlns.oracle.com/ku" schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

Voilá! Mit dem "richtigen" XSLT kann man sich jede beliebige XML-Struktur erzeugen; sogar das Generieren von Code wäre denkbar, denn mit XSLT können auch Textdateien erzeugt werden. XML-Dokumente lassen sich auch recht gut vergleichen; allerdings muss man das nicht selbst machen; man kann das Paket DBMS_METADATA_DIFF verwenden. Viel Spaß beim Ausprobieren und hier noch ein paar Links zum Thema:

Some time ago I got a question about how to retrieve table metadata as XML - the colleague needed the definition of a table in the Oracle database (say: EMP) as follows ...

<table schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

And this is much simpler as most people think. It is not necessary to walk through the data dictionary and to generate XML using the appropriate SQL funktions or string concatenation. We have ready-to-use functionality for that in DBMS_METADATA. This does not only contain GET_DDL to generate SQL scripts, but also the functions GET_XML and GET_SXML. Both functions generate metadata in XML format.

  • GET_XML produces an XML document containing all information about the database object. This XML is complex, opaque and contains binary and instance-specific information. So you'll find the information that a table column has the data type "2" instead of VARCHAR2. So this XML format is only for Oracle internal use.
    <?xml version="1.0"?>
    <ROWSET><ROW>
      <TABLE_T>
     <VERS_MAJOR>1</VERS_MAJOR>
     <VERS_MINOR>3 </VERS_MINOR>
     <OBJ_NUM>78581</OBJ_NUM>
     <SCHEMA_OBJ>
      <OBJ_NUM>78581</OBJ_NUM>
      <DATAOBJ_NUM>78581</DATAOBJ_NUM>
      <OWNER_NUM>96</OWNER_NUM>
      :
    
  • GET_SXML generates an XML document which looks like an XML representation of a DDL statement. The tag names and structure corresponds to the names and structure used in DDL statements. So this format is suitable to work on with ...
    <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
     <SCHEMA>TESTIT</SCHEMA>
     <NAME>EMP</NAME>
     <RELATIONAL_TABLE>
        <COL_LIST>
           <COL_LIST_ITEM>
              <NAME>EMPNO</NAME>
              <DATATYPE>NUMBER</DATATYPE>
              <PRECISION>4</PRECISION>
              <SCALE>0</SCALE>
              <NOT_NULL></NOT_NULL>
           </COL_LIST_ITEM>
       :
    

DBMS_METADATA does most of the work for us. BTW: It works the same way internally: Metadata is firstly being retrieved as XML and then converted to a SQL script. When GET_DDL is being used, the package does it all at once. Those who customize DBMS_METADATA by using calls to OPEN, ADD_TRANSFORM_PARAM, ADD_REMAP_PARAM, FETCH and CLOSE, may use ADD_TRANSFORM to generate their metadata as DDL, XML or SXML.

So we have an XML document representing our table, but this is not the XML format we need. The tag names and structure introduced at the beginning is different from Oracle's SXML format. But in the XML worlds this is not really a problem. We can transform XML documents into another tag structure by using XSLT Stylesheets. And the Oracle database provides the SQL function XMLTRANSFORM to execute such a transformation directly in the database. So here is the XSLT Stylesheet ...

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:ku="http://xmlns.oracle.com/ku" version="1.0">
<xsl:output method="xml"/>
 <xsl:template match="/">
  <xsl:for-each select="/ku:TABLE">
  <table>
   <xsl:attribute name="schema">
    <xsl:value-of select="ku:SCHEMA/text()"/>
   </xsl:attribute>
   <xsl:attribute name="name">
    <xsl:value-of select="ku:NAME/text()"/>
   </xsl:attribute>
   <xsl:for-each select="ku:RELATIONAL_TABLE/ku:COL_LIST/ku:COL_LIST_ITEM">
    <column>
     <xsl:variable name="current-column">
      <xsl:value-of select="ku:NAME"/>
     </xsl:variable> 
     <xsl:attribute name="name">
      <xsl:value-of select="ku:NAME"/>
     </xsl:attribute>
     <xsl:attribute name="type">
      <xsl:choose>
       <xsl:when test="ku:DATATYPE = 'VARCHAR2'">
        <xsl:value-of select="concat(ku:DATATYPE,'(', ku:LENGTH, ')')"/>
       </xsl:when>
       <xsl:otherwise>
        <xsl:choose>
         <xsl:when test="ku:DATATYPE = 'NUMBER'">
          <xsl:choose>
           <xsl:when test="ku:PRECISION">
            <xsl:value-of select="concat(ku:DATATYPE,'(', ku:PRECISION, ',', ku:SCALE, ')')"/>
           </xsl:when>
           <xsl:otherwise>
            <xsl:value-of select="ku:DATATYPE"/>
           </xsl:otherwise>
          </xsl:choose>
         </xsl:when>
         <xsl:otherwise>
          <xsl:value-of select="ku:DATATYPE"/>
         </xsl:otherwise>
        </xsl:choose>
       </xsl:otherwise>
      </xsl:choose>
     </xsl:attribute>
     <xsl:for-each select="../../ku:PRIMARY_KEY_CONSTRAINT_LIST/ku:PRIMARY_KEY_CONSTRAINT_LIST_ITEM/ku:COL_LIST/ku:COL_LIST_ITEM">
      <xsl:choose>
       <xsl:when test="ku:NAME/text() = $current-column">
        <xsl:attribute name="primary-key">
         <xsl:value-of select="position()"/>
        </xsl:attribute>
       </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </column>
   </xsl:for-each>
  </table>
   </xsl:for-each>
 </xsl:template>
</xsl:stylesheet> 

Before using XMLTRANSFORM we need to bring the Stylesheet into the database. There are many approaches for this - using UTL_FILE, BFILE, SQL*Loader and others. An easy and elegant approach is to use the XML DB repository and the FTP protocol server. First enable it by executing DBMS_XDB.SETFTPPORT(2100) (as SYS). Afterwards you can use a plain FTP client to upload the XSLT file into your database.

D:\>ftp -n
ftp> open sccloud030 2100
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.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> put table.xsl
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 2041 bytes sent in 0,00Seconds 2041000,00Kbytes/sec.
ftp>

Now all components are present. The table metadata is being retrieved in "SXML" format using DBMS_METADATA.GET_SXML and transformed to another XML format with our stylesheet and the XMLTRANSFORM function. Note the usage of the XDBURITYPE function: This grabs the stylesheet from the XML DB repository, where we uploaded it with the FTP client.

select 
 xmltransform(
  xmltype(dbms_metadata.get_sxml('TABLE','EMP')), 
  xdburitype('/public/table.xsl').getxml()
) from dual;

XMLTRANSFORM(XMLTYPE(DBMS_METADATA.GET_SXML('TABLE','EMP')),XDBURITYPE('/PUBLIC/
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<table xmlns:ku="http://xmlns.oracle.com/ku" schema="TESTIT" name="EMP">
  <column name="EMPNO" type="NUMBER(4,0)" primary-key="1"/>
  <column name="ENAME" type="VARCHAR2(10)"/>
  <column name="JOB" type="VARCHAR2(9)"/>
  <column name="MGR" type="NUMBER(4,0)"/>
  <column name="HIREDATE" type="DATE"/>
  <column name="SAL" type="NUMBER(7,2)"/>
  <column name="COMM" type="NUMBER(7,2)"/>
  <column name="DEPTNO" type="NUMBER(2,0)"/>
</table>

That's it. The target XML structure can be changed by changing the stylesheet. It's even possible to generate code using stylesheets since they can render plain text as well. The SXML format is also very useful in order to detect differences between database objects - but there's also a ready-to-use package for this: DBMS_METADATA_DIFF.

Have fun trying this out.

Keine Kommentare:

Beliebte Postings