4. November 2014

XML-Strukturen analysieren - mit SQL!

Analyze XML documents ... with SQL!
In diesem Blog Posting schreibe ich etwas über die "Analyse" von XML-Dokumenten mit SQL-Mitteln. Das Extrahieren einzelner Inhalte aus XML mit SQL-Funktionen wie XMLTABLE (oder früher: EXTRACTVALUE) habe ich ja schon oft behandelt. Um damit arbeiten zu können, muss man jedoch die Namen der XML-Tags, für die man sich interessiert, deren Position und ggfs. auch deren Namespace kennen. De-jure gibt es zu jedem XML-Dokument eine Dokumentation oder gar ein XML Schema, aus dem man diese Informationen entnehmen kann, de-facto jedoch ...
Daher gibt es heute einige Beispiele für SQL-Abfragen in XML-Dokumenten, die keine Inhalte extrahieren, sondern Informationen über den Aufbau. Vielleicht kann der eine oder andere von euch das gelegentlich gebrauchen.
Wenn die SQL-Funktionen XMLQUERY oder XMLTABLE (ich selbst verwende eigentlich nur XMLTABLE) verwendet werden, so wird zur Selektion innerhalb des XML-Dokumentes XPath oder XQuery genutzt. Neben der reinen Navigation im XML bietet diese Abfragesprache allerdings auch Funktionen an, mit denen man den Namen des Tags (name()), den Namespace (namespace-uri()) oder andere Dinge erfahren kann. Damit bauen wir ein einfaches Beispiel: Zu einem gegebenen XML-Dokument soll die SQL-Abfrage alle Tags auflisten.
select tag from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)

TAG
----------
doc
tag1
tag1a
tag2
tag2

5 Zeilen ausgewählt.
Die XMLTABLE-Funktion erhält neben dem XML-Dokument (welcher hier als Literal direkt in die Abfrage eingebaut wurde) den XPath-Ausdruck //* übergeben - dieser selektiert alle Tags auf allen Hierarchieebenen. In der COLUMNS-Klausel, welche die Informationen auf die Ergebnisspalten der SELECT-Abfrage abbildet, wird dann die XPath-Funktion name() verwendet; im Ergebnis wird für jedes vorkommende XML-Tag eine Zeile generiert, die dessen Namen enthält. Wie man an tag2 erkennen kann, wird wirklich für jedes Tag eine Zeile gebildet - wenn man nur eindeutige Namen haben möchte, kann man dies mit einem SELECT DISTINCT erreichen; auch das Zählen der Vorkommen ist so natürlich überhaupt kein Problem mehr.
select tag, count(*) from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)
group by tag
 
TAG          COUNT(*)
---------- ----------
doc                 1
tag1                1
tag2                2
tag1a               1

4 Zeilen ausgewählt.
Auch Namespace-Informationen (Blog Posting) lassen sich auf diese Weise extrahieren. Die folgende SQL-Abfrage zeigt die Anwendung der XPath-Funktionen local-name() und namespace-uri().
select 
  fulltag, tag, nsuri
from xmltable(
  '//*'
  passing xmltype(
   '<doc xmlns="http://mein-namespace/a" xmlns:ns1="http://mein-namespace/b">
     <ns1:tag>Text</ns1:tag>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'local-name()',
    fulltag varchar2(10) path 'name()',
    nsuri   varchar2(30) path 'namespace-uri()'
)

FULLTAG    TAG        NSURI
---------- ---------- ------------------------------
doc        doc        http://mein-namespace/a
ns1:tag    tag        http://mein-namespace/b

2 Zeilen ausgewählt.
Das ist insbesondere hilfreich, wenn man die Namespaces nicht genau kennt - die Deklaration derselben erfolgt zwar meist zu Beginn des XML-Dokumentes, das muss aber nicht so sein; es ist nach dem XML-Standard durchaus möglich, dass mitten im Dokument ein Tag mit einer neuen Namespace-Deklaration auftaucht.
Als nächstes wollen wir Informationen über den Kontext eines Tags herausfinden. XPath erlaubt nicht nur die fast immer verwendete Navigation zu den Child-Elementen, sondern unter anderem auch die Navigation zu den Elternelementen. Die folgende SQL-Abfrage zeigt, wie das geht. Hier wird aber nicht mehr mit reinem XPath gearbeitet; vielmehr packen wir unsere Aufgabe in eine XQuery-Abfrage.
select 
  tagname, 
  parentname,
  count(*) occurrences
from xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xmltype(
    '<root-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag/>
     </root-tag>')
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname
/

TAGNAME         PARENTNAME      OCCURRENCES
--------------- --------------- -----------
root-tag                                  1
level1-tag      root-tag                  3
level2-tag      level1-tag                2
Wie man sehen kann, wird im XQuery Ausdruck für jedes gefundene XML-Tag ein "neues" XML-Dokument zusammengebaut, und zwar von der Struktur "<r><t>{tag-name}</t><p>{parent-tag-name}</p></r>". Dieses wird dann an die COLUMNS-Klausel weitergegeben; welche logischerweise innerhalb der Tags <r>, <t> and <p> navigiert und deren Inhalte auf die relationalen Ergebnisspalten abbildet. Der XQuery-Ausdruck selbst selektiert für jedes Element dessen Namen, dann navigiert er mit dem Ausdruck parent::* zum Elternknoten und selektiert dessen Namen. Wir erhalten zu jedem XML-Tag sein Parent-Tag. Und da wir in der Oracle-Datenbank sind, wissen wir sofort, was nun zu tun ist ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(
      '<root-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag/>
       </root-tag>')
    columns
      tagname    varchar2(15) path '/r/t',
      parentname varchar2(15) path '/r/p'
  )
  group by tagname, parentname
)
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
--------------------------------------------------------------
  root-tag (1)
    level1-tag (3)
      level2-tag (2)

3 Zeilen ausgewählt.
Das ist doch gar nicht übel, oder ...? Eine SQL-Abfrage gibt uns einen Überblick über die Struktur im XML-Dokument. Das wollen wir nun an einem größeren XML-Dokument testen. Am besten nehmen wir eins, was die Datenbank bereits hat. Und zwar speichert die XML DB die Konfiguration der Protokollserver (FTP, HTTP, WebDAV) in einem XML-Dokument ab. Man kann es mit der PL/SQL-Funktion DBMS_XDB.CFG_GET abrufen.
SQL> select dbms_xdb.cfg_get().getclobval() from dual

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-
Nun wenden wir die obige SQL-Abfrage auf dieses XML-Dokument an.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
  )
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
------------------------------------------------------------
  xdbconfig (1)
    sysconfig (1)
      acl-cache-size (1)
      acl-evaluation-method (1)
:
      persistent-sessions (1)
      protocolconfig (1)
        common (1)
          extension-mappings (1)
:
            mime-mappings (1)
              mime-mapping (51)
                extension (51)
                mime-type (51)
:
                  servlet-schema (2)
              servlet-mappings (1)
                servlet-mapping (6)
                  servlet-name (6)
                  servlet-pattern (6)
            welcome-file-list (1)
              welcome-file (2)
      resource-view-cache-size (1)
      xdbcore-loadableunit-size (1)
      xdbcore-log-level (1)
      xdbcore-logfile-path (1)
      xdbcore-xobmem-bound (1)

92 Zeilen ausgewählt.
Mit anderen hierarchischen Funktionen kann nun auch problemlos der komplette Pfad zu den XML-Tags ausgegeben werden; und man sieht sehr schön, wo ein bestimmtes XML-Tag steht und wie man es selektieren muss. Diese SQL-Abfrage können wir nun nochmals erweitern und damit gezielt nach bestimmten Tags suchen: Die folgende Abfrage zeigt alle Tags namens session-timeout mitsamt deren vollständigen Navigationspfad im XML-Dokument an. Der komplette Pfad wurde mit der SQL-Funktion SYS_CONNECT_BY_PATH generiert - das hat mit XML nichts zu tun; diese Funktion kann in jeder hierarchischen Abfrage genutzt werden.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
), hierarchie as (
  select 
    tagname,
    sys_connect_by_path(tagname, '/') as pfad
  from tags
  start with parentname is null
  connect by prior tagname = parentname
)
select pfad 
from hierarchie
where tagname = 'session-timeout'
/

PFAD
--------------------------------------------------------------------------------
/xdbconfig/sysconfig/protocolconfig/common/session-timeout
/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout
/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout

3 Zeilen ausgewählt.
Das Ergebnis kann direkt verwendet werden, wie die Probe zeigt ...
select timeout
from xmltable (
  xmlnamespaces (DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'), 
  '/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout'
  passing dbms_xdb.cfg_get()
  columns timeout path 'text()'
);

TIMEOUT
-----------------------------------------------------
6000

1 Zeile wurde ausgewählt.
Wie haben wir den Namespace herausgefunden ...? Ach ja ...
select distinct nsuri
from xmltable(
  '//*'
  passing dbms_xdb.cfg_get()
  columns
    nsuri   varchar2(50) path 'namespace-uri()'
)
/ 

NSURI
--------------------------------------------------
http://xmlns.oracle.com/xdb/xdbconfig.xsd

1 Zeile wurde ausgewählt.
Damit soll es genug sein. Beachtet bitte, dass diese Art der XML-Abfragen von der Datenbank nicht besonders optimiert werden können; es findet stets ein XML-Parsing statt. Auf sehr großen Dokumentbeständen oder großen Einzeldokumenten können diese Abfragen also durchaus länger dauern. Aber ansonsten steht der Analyse von XML - mit SQL nichts im Wege.
This blog posting will be about "analysis" of XML documents - in the database, with the SQL language. I have blogged several times about how to extract information from XML documents using the SQL functions XMLTABLE or EXTRACTVALUE (in earlier database versions). But to work with these functions, one must know, where the information of interest is located within the XML document. One must know the name of the XML tag, its position within the hierarchy, its namespace and so on. Of course, there is always good and comprehensive documentation (or an XML Schema) which contains this kind of information ... but I'll proceed anyway ...
So, today you'll see SQL queries on XML documents, which don't extract actual content, but information about the XML structure. Perhaps this is useful for somebody - sometime.
Within the SQL functions XMLQUERY or XMLTABLE (I hardly use XMLQUERY myself), XML tags are being selected. For this selection, either the XQuery or the more simple XPath language can be used. Beyond the pure navigational features, XPath also contains some functions to obtain information about the XML structure: name() retrieves the name of the current XML tag, namespace-uri() gets the full XML namespace information, and there are some more. With this in mind, we can build our first example. A SQL query is supposed to list the names of all XML tags.
select tag from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)

TAG
----------
doc
tag1
tag1a
tag2
tag2

5 rows selected.
For clarity, we pass the XML document directly into the XMLTABLE function as a literal. The XPath expression //* selects all XML tags in all hierarchy levels. This "intermediate XML result" will then go the the COLUMNS clause which maps it to the relational result set of the SQL query. In this case, the COLUMNS clause applies the name() function on each selected node. You'll see a row for each XML tag - including duplicates. To get only distinct tag names, we can utilize SQL features (SELECT DISTINCT); and counting the XML tags is also a very easy task now (these are the things I really like in the Oracle database - combine functionality and do awesome things.)
select tag, count(*) from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)
group by tag
 
TAG          COUNT(*)
---------- ----------
doc                 1
tag1                1
tag2                2
tag1a               1

4 rows selected.
We can also extract XML namespace information (blog posting about that). The following example illustrates the usage of local-name() and namespace-uri().
select 
  fulltag, tag, nsuri
from xmltable(
  '//*'
  passing xmltype(
   '<doc xmlns="http://mein-namespace/a" xmlns:ns1="http://mein-namespace/b">
     <ns1:tag>Text</ns1:tag>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'local-name()',
    fulltag varchar2(10) path 'name()',
    nsuri   varchar2(30) path 'namespace-uri()'
)

FULLTAG    TAG        NSURI
---------- ---------- ------------------------------
doc        doc        http://mein-namespace/a
ns1:tag    tag        http://mein-namespace/b

2 rows selected.
That is very useful, when you don't know all the used namespaces within your XML document. In most cases these are being declared at the beginning, but this is not mandatory. A namespace declaration can occur at any position in your XML document, so perhaps there is an XML tag from an unknown namespace - at the very end of the XML document ... you can't be sure. Of course, in pratice we always have an XML schema or a documentation, so this is all theoretical ...
Next, we'll extract information about the context of an XML tag. XPath does not only allow to navigate from a parent to a child, but also from a child to a parent - or from a tag to its sibling. Whereas the child axis is the default, there are other axes to navigate along. The following SQL query will use the parent axis in order to retrieve the name of each XML tag's parent. Since this is a bit more complex, we don't use pure XPath any more. We'll utilize XQuery syntax instead.
select 
  tagname, 
  parentname,
  count(*) occurrences
from xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xmltype(
    '<root-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag/>
     </root-tag>')
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname
/

TAGNAME         PARENTNAME      OCCURRENCES
--------------- --------------- -----------
root-tag                                  1
level1-tag      root-tag                  3
level2-tag      level1-tag                2

3 rows selected.
I don't want to provide a full explanation of XQuery here (there are plenty of tutorials in the web), but you might see how it works. The XQuery expression "builds" another XML document (for each XML tag) with the simple structure "<r><t>{tag-name}</t><p>{parent-tag-name}</p></r>". This is being passed to the COLUMNS clause. The XPath expressions in the COLUMNS clause now selects items from the "intermediate" XML document, so they navigate within the <r>, <t> and <p> tags. Looking at this queries' results and knowing that we are within the Oracle database, we should know, what the next step is ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(
      '<root-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag/>
       </root-tag>')
    columns
      tagname    varchar2(15) path '/r/t',
      parentname varchar2(15) path '/r/p'
  )
  group by tagname, parentname
)
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
--------------------------------------------------------------
  root-tag (1)
    level1-tag (3)
      level2-tag (2)

3 rows selected.
Another nice instance of combining technology: A SQL query gives an overview on the structure of an XML document. Other technologies would require you to install tools or to author complex procedural code - and here we are doing all this with query language. Let's test this on more complex XML document - I'll take one which is already present in an Oracle database: The configuration of the XML DB protocol servers (FTP, HTTP, WebDAV) is being stored as XML within the database. We can retrieve it with the PL/SQL function DBMS_XDB.CFG_GET.
SQL> select dbms_xdb.cfg_get().getclobval() from dual

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-
    :
Now, let's apply the above query on that document ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
  )
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
------------------------------------------------------------
  xdbconfig (1)
    sysconfig (1)
      acl-cache-size (1)
      acl-evaluation-method (1)
:
      persistent-sessions (1)
      protocolconfig (1)
        common (1)
          extension-mappings (1)
:
            mime-mappings (1)
              mime-mapping (51)
                extension (51)
                mime-type (51)
:
                  servlet-schema (2)
              servlet-mappings (1)
                servlet-mapping (6)
                  servlet-name (6)
                  servlet-pattern (6)
            welcome-file-list (1)
              welcome-file (2)
      resource-view-cache-size (1)
      xdbcore-loadableunit-size (1)
      xdbcore-log-level (1)
      xdbcore-logfile-path (1)
      xdbcore-xobmem-bound (1)

92 rows selected.
We now get an overview on the hierarchy of the XML DB protocol server configuration; without a tool - just with SQL. Using the SYS_CONNECT_BY_PATH function, we can let the database even generate a full path to each XML tag. And with a WHERE clause we can look for specific tags of interest. The following query shows this: We are interested in the full path to a tag named session-timeout.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
), hierarchie as (
  select 
    tagname,
    sys_connect_by_path(tagname, '/') as pfad
  from tags
  start with parentname is null
  connect by prior tagname = parentname
)
select path
from hierarchie
where tagname = 'session-timeout'
/

PATH
--------------------------------------------------------------------------------
/xdbconfig/sysconfig/protocolconfig/common/session-timeout
/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout
/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout

3 rows selected.
You can actually use this result, as the following test shows you.
select timeout
from xmltable (
  xmlnamespaces (DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'), 
  '/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout'
  passing dbms_xdb.cfg_get()
  columns timeout path 'text()'
);

TIMEOUT
-----------------------------------------------------
6000

1 row selected.
And how did we find out the namespace (which is used in the XMLNAMESPACES clause) ...?
select distinct nsuri
from xmltable(
  '//*'
  passing dbms_xdb.cfg_get()
  columns
    nsuri   varchar2(50) path 'namespace-uri()'
)
/ 

NSURI
--------------------------------------------------
http://xmlns.oracle.com/xdb/xdbconfig.xsd

1 row selected.
OK folks, enough for today. When playing with these features, keep in mind, that the database does not optimize these kinds of XML query for performance - so they might take longer for large XML documents or large document sets. But having this in mind, nothing should prevent you from analyzing your XML documents - with SQL!

10. Oktober 2014

PL/SQL in Oracle 12.1: Index By Tables, SQL Abfragen und TABLE()

PL/SQL in 12.1: INDEX BY tables, SQL Queries and the TABLE() function
Eins der neuen PL/SQL Features in der Oracle Version 12.1 ist, dass - im Gegensatz zu 11 - noch mehr reine PL/SQL Typen in SQL-Queries oder DML-Anweidungen genutzt werden können. Reine PL/SQL-Typen sind RECORD-, aber auch BOOLEAN- oder "PL/SQL Index-By-Table"-Datentypen. Letztere werden auch als assoziative Arrays bezeichnet. Ab der Version 12.1 ist es nun möglich, eine solche PL/SQL Index-By-Table in SQL-Queries mit der Funktion TABLE() zu verwenden. zumindest die Dokumentation. Wie das genau funktioniert, beschreibt dieses Blog-Posting.
Zunächst der Setup: Ich brauche eine PL/SQL-Funktion, die eine Index-By Table erstellt und zurückgibt - außerdem muss der Type, der als Index-By-Table definiert ist, in einem Package deklariert werden (sonst kann man ihn gar nicht als Funktionsparameter verwenden). Die Dokumentation beschreibt bereits die eine oder andere Einschränkung. So muss u.a. das assoziative Array als INDEX BY PLS_INTEGER definiert sein.
create or replace package mypkg is
  type t_assoc_array is table of varchar2(20) index by pls_integer;
  function testit (p_amount in number) return t_assoc_array;
end mypkg;
/
sho err

create or replace package body mypkg is
  function testit (p_amount in number) return t_assoc_array is
    l_array t_assoc_array;
  begin
    for i in 1..p_amount loop
      l_array(i) := 'VALUE_'||i;
    end loop;
    return l_array;
  end testit;
end mypkg;
/
sho err
Nun könnte man versuchen, die Funktion aus einer SQL-Query zu verwenden ...
select * from table(mypkg.testit(20))
/

FEHLER in Zeile 1:
ORA-00902: Ungültiger Datentyp
Das funktioniert nicht. Und es gibt auch eine passende Einschränkung in der Dokumentation dazu: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL. Doch wie kann man dieses Feature dann überhaupt nutzen?
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array := mypkg.testit(20);
  for i in (
    -- Nutzung des PL/SQL Arrays in einer SQL Query - nun aber im PL/SQL Kontext
    select * from table(l_array)
  ) loop
    dbms_output.put_line(i.column_value);
  end loop;
end;
/

VALUE_1
VALUE_2
VALUE_3
VALUE_4
VALUE_5
VALUE_6
:

PL/SQL-Prozedur erfolgreich abgeschlossen.
Wenn man also eine SQL-Query innerhalb einer PL/SQL Prozedur, -Funktion oder eines anonymen Blocks ausführt, so kann man darin etwaige PL/SQL Index By Tables verwenden (TABLE()-Operator nutzen). Das kann sehr nützlich sein, wenn man die Index-By-Table mit einer Datenbanktabelle joinen möchte.
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array(1) := 7839;
  l_array(2) := 7844;
  for i in (
    select ename, sal from emp e join table(l_array) a on e.empno = a.column_value
  ) loop
    dbms_output.put_line('ENAME: '||i.ename||' - SAL: '||i.sal);
  end loop;
end;
/

ENAME: KING - SAL: 5000
ENAME: TURNER - SAL: 1500

PL/SQL-Prozedur erfolgreich abgeschlossen.
Zusammenfassend kann man sagen, dass PL/SQL und SQL in 12.1 nochmal ein wenig besser zusammenarbeiten. PL/SQL Index-by-Tables können in SQL Queries verwendet werden, solange man sich im PL/SQL Kontext befindet. Für eine "top-level" SQL-Query sind sie aber weiterhin nicht erreichbar. So bleibt auch in 12.1. eine gewisse Grenze zwischen SQL und PL/SQL - aber die Richtung stimmt.
One of the new things in Oracle12c is, that more (compared to Oracle11g) pure PL/SQL types are accessible from SQL queries or DML statements. Pure PL/SQL types are record-, boolean or "INDEX BY" types. The latter are also called associative arrays. So, beginning with 12.1, we can access PL/SQL INDEX BY tables with a SQL query - according to the documentation. This blog posting describes how it works (and what does not work).
First, the setup. We need (of course) a PL/SQL function returing a PL/SQL INDEX BY table. And in order to create such a function, we need to have a package specification with the INDEX BY table definition. To make it easy, we put the function into the package as well. Reading the documentation carefully, we observe the first restriction for this feature: The INDEX BY table must be declared as INDEX BY PLS_INTEGER.
create or replace package mypkg is
  type t_assoc_array is table of varchar2(20) index by pls_integer;
  function testit (p_amount in number) return t_assoc_array;
end mypkg;
/
sho err

create or replace package body mypkg is
  function testit (p_amount in number) return t_assoc_array is
    l_array t_assoc_array;
  begin
    for i in 1..p_amount loop
      l_array(i) := 'VALUE_'||i;
    end loop;
    return l_array;
  end testit;
end mypkg;
/
sho err
Now we do the first test: Use the INDEX BY table within a SQL query ...
select * from table(mypkg.testit(20))
/

ERROR at line 1:
ORA-00902: invalid datatype
Does not work. Bummer. OK, read the documentation again. Aah, there is the other restriction: A PL/SQL function cannot return a value of a PL/SQL-only type to SQL. But what's the value of that new feature then ...? And how can it be used?
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array := mypkg.testit(20);
  for i in (
    -- Nutzung des PL/SQL Arrays in einer SQL Query - nun aber im PL/SQL Kontext
    select * from table(l_array)
  ) loop
    dbms_output.put_line(i.column_value);
  end loop;
end;
/

VALUE_1
VALUE_2
VALUE_3
VALUE_4
VALUE_5
VALUE_6
:

PL/SQL procedure successfully completed.
So, PL/SQL INDEX BY tables are accessible for a SQL statement within a PL/SQL procedure, function or anonymous block (use the TABLE() operator). This can be very useful: think about joining your PL/SQL INDEX BY table to a database table, as follows ...
declare 
  l_array mypkg.t_assoc_array;
begin
  l_array(1) := 7839;
  l_array(2) := 7844;
  for i in (
    select ename, sal from emp e join table(l_array) a on e.empno = a.column_value
  ) loop
    dbms_output.put_line('ENAME: '||i.ename||' - SAL: '||i.sal);
  end loop;
end;
/

ENAME: KING - SAL: 5000
ENAME: TURNER - SAL: 1500

PL/SQL procedure successfully completed.
In Oracle 12.1, PL/SQL and SQL work more closely together. We can use INDEX BY tables within SQL queries now, as long as this query is being executed within a PL/SQL context. So INDEX BY tables are still PL/SQL, not SQL data types. We still have some barriers for PL/SQL data types in SQL - but the development direction is good.

26. September 2014

XML Namespaces, XMLTABLE und die Oracle Datenbank

Dealing with XML namespaces in the Oracle database
Heute geht es um einen Aspekt beim Umgang mit XML in der Oracle-Datenbank, der recht häufig zu Fragen und scheinbar unverständlichem Verhalten der Datenbank führt: die XML Namespaces. Dazu (wie immer) ein einführendes Beispiel.
create table xml_tab (
  id  number,
  xml xmltype
)
/

insert into xml_tab values (
  1, 
  '<document xmlns="a">
     <blog>SQL und PL/SQL</blog>
     <thema>Oracle Datenbank</thema>
   </document>'
);

commit
/
Aus diesem XML-Dokument sollen nun, mit der SQL-Funktion XMLTABLE, die Inhalte extrahiert werden - wie das geht, findet man recht schnell heraus:
select 
  blog, 
  thema
from xml_tab t, xmltable(
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

No rows selected.
Wie man sieht, funktioniert das nicht - man überprüft die Angaben der XML-Tags noch einmal und stellt fest, dass alles richtig ist. Der Grund für das Verhalten liegt an der Namespace Deklaration im XML-Dokument - und zwar hier.
<document xmlns="a">...</document>
XML kennt das Konzept der Namespaces: Ein XML-Tag wird nicht nur durch den Namen selbst bestimmt, in diesem Fall also document, sondern auch durch den Namespace. Damit wird es möglich, in ein- und demselben XML-Dokument mehrere Tags des scheinbar gleichen Namens zu haben - durch unterschiedliche Namespaces ist es aber nicht mehr dasselbe Tag. Namespaces werden in der Praxis meist mit mit URLs (bspw. http://www.meinefirma.de/xmlnamespace1) benannt - damit sie global möglichst eindeutig sind (die Webadressen müssen natürlich nicht tatsächlich funktionieren - es sind nur Namen). Man kann aber auch, wie oben, jede beliebige Zeichenkette hernehmen. Gerade wenn der Namespace mit einer längeren URL benannt ist, wäre es aber recht umständlich, die gesamte URL zu jedem XML-Tag dazuzuschreiben - die ohnehin schon sehr großen XML-Dokumente würden noch größer und schwieriger zu verarbeiten. Daher gibt es zusätzlich ...
  • Namespace-Präfixe
  • Default Namespace
Beide werden mit dem Attribut xmlns definiert. Im Beispiel oben wird der Namespace "a" als Default-Namespace deklariert. Alle XML-Tags ohne ein Namespace-Präfix werden also dem Namespace "a" zugeordnet. Alternativ könnte man auch einen Namespace-Präfix deklarieren - dann sieht das XML-Dokument so aus.
insert into xml_tab values (
  2, 
  '<pr:document xmlns:pr="a">
     <pr:blog>SQL und PL/SQL</pr:blog>
     <pr:thema>Oracle Datenbank</pr:thema>
   </pr:document>'
);
Inhaltlich sind beide XML-Dokumente exakt identisch - ein XML-Parser macht keinen Unterschied. Die XML-Tags gehören in beiden Fällen zum Namespace "a", nur ist das einmal der Default-Namspace, im zweiten Fall werden explizite Präfixe verwendet.
Und weil das alles noch nicht genug ist, gibt es auch XML-Dokumente, in denen das Attribut xmlns fehlt oder in denen es XML-Tags ohne Präfix, aber keinen Default-Namespace gibt. Diese Tags haben dann keinen Namespace, manche sprechen auch vom Null-Namespace. Ein XML-Tag ohne Namespace ist nach dem XML-Standard ein anderes Tag als eines mit Namespace. Und das alles kann in ein- und demselben XML-Dokument auch gemischt werden.
Diese Dinge müssen beim Formulieren der SQL-Abfrage mit XMLTABLE berücksichtigt werden; im Eingangsbeispiel gehören alle XML-Tags zum Namespace "a", die XMLTABLE-Abfrage enthält aber keinerlei Namespace-Definition. Also hat sie nach XML-Tags ohne Namespace gesucht, davon (natürlich) keine gefunden, daher das No rows selected.. Die Abhilfe kann so aussehen ...
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces('a' as "p"),
  '/p:document'
  passing xml
  columns
    blog  varchar2(30) path 'p:blog',
    thema varchar2(30) path 'p:thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
... oder so:
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a'),
  '/document'
  passing xml
  columns
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
Man sieht, dass die XMLNAMESPACES-Klausel für die XMLTABLE-Funktion die gleiche Bedeutung hat, wie das Attribut xmlns im XML-Dokument. In beiden Fällen wird der Namespace auf ein Präfix abgebildet oder als Default festgelegt. In den folgenden XQuery oder XPath Ausdrücken muss dann der deklarierte Präfix verwendet werden. Es muss aber keinesfalls der gleiche Präfix wie im Dokument verwendet werden - der Präfix oder die Tatsache, dass ein Namespace als Default deklariert ist, ist völlig bedeutungslos. Wichtig ist allein der Name des Namespace, also das "a". Und natürlich kann man sich nun auch XML-Dokument mit mehreren Namespaces vorstellen ...
insert into xml_tab values (
  3, 
  '<ns1:document xmlns:ns1="a" xmlns:ns2="b" xmlns:ns3="c">
     <ns2:blog>SQL und PL/SQL</ns2:blog>
     <ns3:thema>Oracle Datenbank</ns3:thema>
   </ns1:document>'
);
Nun gehört jedes XML-Tag tatsächlich zu einem anderen Namespace. Dieses Dokument ist auch für einen XML-Parser inhaltlich ein anderes Dokument als die ersten beiden. Es werden drei Namespaces deklariert: a, b und c. Während a als Default-Namespace deklariert wird (also kein Präfix für das XML-Tag), erhalten b und c die Präfixe ns1 und ns2. In der XMLTABLE-Abfrage muss das berücksichtigen.
select 
  blog, 
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a', 'b' as "p1", 'c' as "p2"),
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'p1:blog',
    thema varchar2(30) path 'p2:thema'
) x
/
Natürlich muss man in der XMLTABLE-Abfrage nicht mit einem Default-Namespace arbeiten, man kann auch alle drei auf Präfixe abbilden. Wichtig ist nur, dass die eigentlichen Namen der Namespaces, nämlich a, b und c, korrekt angesprochen werden. Achtet man darauf, so sind auch XML-Dokumente mit Namespaces kein Problem mehr.
This blog posting is about XML namespaces, and how to deal with them when processing XML with SQL functions. This often leads to confusion and questions - the following sections will try to shed some light into this. We'll start with a simple example ...
create table xml_tab (
  id  number,
  xml xmltype
)
/

insert into xml_tab values (
  1, 
  '<document xmlns="a">
     <blog>SQL und PL/SQL</blog>
     <thema>Oracle Datenbank</thema>
   </document>'
);

commit
/
From this XML document, we want to extract data using the XMLTABLE function. After reading the documentation, the SQL query is authored rather quickly.
select 
  blog, 
  thema
from xml_tab t, xmltable(
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

No rows selected.
But, although all tag names are correct, it does not work. The reason is the XML namespace declaration at the beginning of the document.
<document xmlns="a">...</document>
XML has the concept of namespaces. An XML tag is being identified not only by its name (here: document), but also by its namespace. Using this, XML allows tags having the same name, but different semantics. By considering both name and namespace, an XML engine is able to differentiate between the tags. In practice, namespaces are being named with URLs like http://mycompany.com/myproject/mynamespace (these URLs don't have to exist physically - it's just a name). Instead of a URL, we can also use any character string for a namespace, like in the example above: "a" (which is not likely to be globally unqiue, of course). If a URL, or a long string is being used for a namespace, we seem to have a problem: We need to add this string to each and every XML Tag, don't we ...?
  • Namespace prefixes map long namespaces to short prefixes
  • The default namespace is being used for tags without a prefix
Both are declared with the xmlns attribute: xmlns="a" declares "a" as the default namespace wheres xmlns:pr="a" maps "a" to the namespace prefix "pr". In our example, a default namespace is being used. As an alternative, we can declare a namespace prefix. Then we need to add it to the XML tags - like in the following XML document.
insert into xml_tab values (
  2, 
  '<pr:document xmlns:pr="a">
     <pr:blog>SQL und PL/SQL</pr:blog>
     <pr:thema>Oracle Datenbank</pr:thema>
   </pr:document>'
);
Both XML documents have exactly the same semantics - for an XML parser there is no difference. And that's not all: XML tags can also have no namespace at all. Some refer to this as the null or empty namespace. This is the case, when the xmlns attribute is either not present or when the XML document has no default namespace, but contains XML tags without a namespace prefix. Therefore we can have XML tags with, and without a namespace and - of course - mutliple different namespaces - all within the same XML document.
We need to take care about this when authoring XMLTABLE queries. In the above example, all XML tags were part of the "a" namespace. But the XMLTABLE query did not contain a namespace declaration - so it looked for tags without a namespace. Since the XML document does not contain these, the query result is correct. To get the query working - we need to add the XMLNAMESPACES clause. A working solution can look like this ...
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces('a' as "p"),
  '/p:document'
  passing xml
  columns
    blog  varchar2(30) path 'p:blog',
    thema varchar2(30) path 'p:thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
... or like this ...
select
  blog,
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a'),
  '/document'
  passing xml
  columns
    blog  varchar2(30) path 'blog',
    thema varchar2(30) path 'thema'
) x
/

BLOG                           THEMA
------------------------------ ------------------------------
SQL und PL/SQL                 Oracle Datenbank
SQL und PL/SQL                 Oracle Datenbank

2 rows selected.
The XMLNAMESPACES clause within an XMLTABLE query has the same meaning as the xmlns attribute within an XML document: a namespace is mapped to a prefix or declared as default. The prefixes and defaults within a query are independent from the prefixes and defaults in the document - only the namespaces themselves (here "a") are important. Having this in mind, we can now also imagine documents with tags from multiple namespaces.
insert into xml_tab values (
  3, 
  '<ns1:document xmlns:ns1="a" xmlns:ns2="b" xmlns:ns3="c">
     <ns2:blog>SQL und PL/SQL</ns2:blog>
     <ns3:thema>Oracle Datenbank</ns3:thema>
   </ns1:document>'
);
This XML document is semantically different to the previous ones, since each XML tag belongs to another namespace: a, b and c. a is being declared as the default (XML tag has no prefix); b and c are mapped to prefixes ns1 and ns2. A working XMLTABLE query can look as follows.
select 
  blog, 
  thema
from xml_tab t, xmltable(
  xmlnamespaces(default 'a', 'b' as "p1", 'c' as "p2"),
  '/document'
  passing xml
  columns 
    blog  varchar2(30) path 'p1:blog',
    thema varchar2(30) path 'p2:thema'
) x
/
As already said, the prefixes used in the XMLTABLE query can be different from the prefixes in the document: the namespace itself is important - not the prefix. We do also not need to use a default namespace - another possibile solution would be to have three different namespace prefixes. Taking care about this, XML documents with namespaces are no problem at all.

4. September 2014

node.js und die Oracle-Datenbank: Erste Versuche ...

node.js and the Oracle database: First experiments
In diesem Blog-Posting möchte ich ein wenig von meinen ersten Gehversuchen mit der Oracle-Datenbank und node.js berichten. node.js selbst möchte ich jedoch nicht im Detail vorstellen, dazu gibt es ausreichend Information im Internet. Auch zum Thema Installation und Setup einer node.js-Umgebung sowie zur Installation zusätzlicher Pakete mit "npm" sind zahlreiche Tutorials verfügbar. Hier geht es vor allem um node.js und die Oracle-Datenbank.
Bekanntlich kann man node.js recht gut mit "JavaScript auf dem Server" bezeichnen. Basis ist die JavaScript-Engine des Chrome-Browsers, die dann JavaScript-Programme per Kommandozeile startet und ausführt. Wie andere Programmiersprachen bietet auch node.js mittlerweile eine recht umfangreiche und erweiterbare Funktionsbibliothek mit, die von der sehr aktiven Community ständig weiterentwickelt wird. Einige Wichtige Eigenschaften von node.js seien aber vorab genannt: Node.js ist Asynchron, event-getrieben und nicht-blockierend. Was das genau bedeutet, sehen wir noch.
Möchte man mit node.js auf eine Oracle-Datenbank zugreifen, stellt sich (wie immer) die Frage nach einem Treiber. Von Oracle gibt es derzeit (noch) keinen node.js Treiber, so dass wir auf das Angebot der Community zurückgreifen müssen - und siehe da: Es existiert ein Oracle-Treiber namens node-oracle von Joe Ferner, den man von GitHub herunterladen kann.
Dem Download liegt ein README bei, welches die Installation in eine bestehende node.js Umgebung beschreibt. Auf Unix/Linux-Systemen gelang mir das auf Anhieb und ohne weitere Probleme. Danach kann man sein erstes node.js-Programm schreiben. Ich greife natürlich auf die Oracle-Datenbank zu, selektiere erst mal etwas und gebe es aus. Das sieht dann so aus.
var oracle = require('oracle');

var connectData = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com", 
  user:     "scott",
  password: "tiger"
}

oracle.connect(connectData, function(err, connection) {
  if (err) {
   console.log("Error connecting to db:", err); return;
  }

  connection.execute("select ename, job from emp ", [],  function(err, results) {
    connection.close();
    console.log(results);
  });
});

console.log("done");
Der Code sieht etwas anders aus, als man es von prozeduralen Programmiersprachen gewohnt ist: node.js arbeitet bei Funktionen, die "I/O bound" sind (Netzwerk, Dateisystem) asynchron. Die Funktion oracle.connect ist I/O bound und bekommt neben den Verbindungsdaten zur Oracle-Datenbank auch eine Callback-Funktion übergeben - diese wird aufgerufen, wenn die Verbindung hergestellt wurde. node.js ist nicht-blockierend: oracle.connect wird asynchron ausgeführt, das Skript arbeitet währenddessen weiter. Demnach muss der Code, der von oracle.connect abhängt, in eine Callback-Funktion gepackt werden.
Startet man das Programm, so bekommt man folgendes Ergebnis.
$ node ora.js
done
[ { ENAME: 'SMITH', JOB: 'CLERK' },
  { ENAME: 'ALLEN', JOB: 'SALESMAN' },
  { ENAME: 'WARD', JOB: 'SALESMAN' },
  { ENAME: 'JONES', JOB: 'MANAGER' },
  { ENAME: 'MARTIN', JOB: 'SALESMAN' },
  { ENAME: 'BLAKE', JOB: 'MANAGER' },
  { ENAME: 'CLARK', JOB: 'MANAGER' },
  { ENAME: 'SCOTT', JOB: 'ANALYST' },
  { ENAME: 'KING', JOB: 'PRESIDENT' },
  { ENAME: 'TURNER', JOB: 'SALESMAN' },
  { ENAME: 'ADAMS', JOB: 'CLERK' },
  { ENAME: 'JAMES', JOB: 'CLERK' },
  { ENAME: 'FORD', JOB: 'ANALYST' },
  { ENAME: 'MILLER', JOB: 'CLERK' } ]
$
Interessant ist, dass das "done", welches im Skript ja erst am Ende ausgegeben wird, dennoch zuerst ausgegeben wurde. Das hängt eben mit der bereits erwähnten asynchronen Natur eines node.js-Programms zusammen. Wenn man sich den Code genau ansieht, so sieht man, dass auf der obersten Ebene nur 2 Statements vorhanden sind: oracle.connect und console.log. Während oracle.connect asynchron abgearbeitet wird, arbeitet node.js mit dem zweiten Statement weiter. Als erstes wird demnach "done" ausgegeben. Erst danach ist der Verbindungsaufbau zu Oracle fertig, node.js ruft die Callback-Funktion auf und führt das eigentliche SELECT durch.
Als nächstes soll ein node.js Skript verwendet werden, um Dateien in die Datenbank hochzuladen. Mit dem Aufruf soll ein Parameter übergeben werden, der auf ein Verzeichnis zeigt - das node.js Skript soll dann alle Textdateien in die Tabelle DOCUMENT_TABLE in der Datenbank speichern. Zuerst also die Tabelle anlegen ...
create table document_table (filename varchar2(200), document clob);
Dann kommt das node.js Skript - es arbeitet eigentlich wie vorhin. Allerdings wird nun, sobald die Verbindung zur Datenbank hergestellt wurde, das angegebene Directory durchgearbeitet und alle gefundenen Dateien werden mit oracle.execute in die Datenbank geladen. Die Arbeit mit den Callback-Funktionen kann, wie man sehen kann, zu stark geschachteltem Code führen. Node.js bietet hier durchaus Ansätze, das soll aber nicht Thema dieses Postings sein.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "orcl",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

ora.connect(oradb, function(err, connection) {
  if (err) {throw err;}
  connection.setAutoCommit(false);
  var fileCount = 0;
  fs.readdir(dirname, function (err, files) {
    if (err) {throw err;}
    files.forEach(function (file) {
      fileCount ++;
      fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;} else {
              console.log('"'+file+'" saved.');
              fileCount--;
              if (fileCount == 0) {
                connection.close();
                console.log("done");
              }
            }
        });
      });
    });
  });
});
Nun wurden alle Dateien über genau eine einzige Verbindung an die Oracle-Datenbank gespeichert. Die Dateien werden also sequenziell verarbeitet. Wenn nun auf Datenbankseite mehr Ressourcen bereitstehen, dann würde man auch gerne mehr Datenbankverbindungen nutzen - schreiben wir das Skript also ein wenig um - so dass für jede Datei nun eine neue Verbindung geöffnet wird. Der folgende Code arbeitet sich also zuerst durch die Dateien des angegebenen Verzeichnisses und versucht dann, für jede Datei eine Datenbankverbindung zu öffnen und die Datei an die Datenbank zu senden.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
Wenn man dieses Skript startet, passiert erstmal gar nichts ... und nach einer Weile bricht es typischerweise mit einem Fehler ab. Wird mit einer Datenbank gearbeitet, die große Ressourcen bereitstellt, so kann das Skript tatsächlich auch erfolgreich laufen - effizient arbeitet es aber in keinem Fall.
[oracle@sccloud033 node]$ node loadFiles.js po

/home/oracle/node/loadFiles.js:19
          if (err) {throw err;}
                          ^
Error: ORA-12516: TNS:listener could not find available handler with matching protocol stack
Noch klarer wird das ganze, wenn man (während das Skript läuft), die Oracle Data Dictionary View V$SESSION beobachtet.
SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       28
SCOTT                                 154
SYS                                     1

-- nach einer Weile ...

SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       26
SCOTT                                 366
SYS                                     1

Um dieses Verhalten zu verstehen, muss man wieder an die Tatsache denken, dass node.js asynchron arbeitet. Wenn im Verzeichnis 500 Dateien liegen, sind arbeitet sich das Skript schnell durch diese 500 Dateien durch und versucht, für jede Datei asynchron eine Datenbankverbindung zu öffnen (wie gelernt: Während das läuft arbeitet das Skript weiter). Das aber dauert (im Vergleich zum Lesen des Verzeichnisses) recht lange, so das das Verzeichnis durchgearbeitet ist, noch bevor die erste Datenbankverbindung steht - als Folge entsteht der Bedarf nach 500 Datenbankverbindungen - Oracle kommt gar nicht mehr hinterher.
Es braucht einen Connection-Pool, der die verwendeten Datenbankverbindungen reguliert. Node.js bietet keine Connection-Pools für Datenbankverbindungen an, aber zum Glück hat die Oracle-Datenbank die Lösung. Denn seit der Oracle-Version 11.1 gibt es den Database Resident Connection Pool, der für solche Umgebungen, die keine eigenen Connection-Pools bereitstellen können, geschaffen wurde. Man dachte damals vor allem an PHP - für node.js eignet sich der Pool genauso gut. Aber erst muss der Pool auf Datenbankseite gestartet werden. Das geschieht mit dem PL/SQL-Paket DBMS_CONNECTION_POOL, mit dem auch Einstellungen am Pool vorgenommen werden können. Hier muss allerdings mit DBA-Privilegien gearbeitet werden. Verwendet man Oracle12c, so muss der Connection-Pool in der Container-Datenbank, nicht in der Pluggable Database gestartet und eingerichtet werden.
SQL> exec dbms_connection_pool.start_pool;

PL/SQL-Prozedur erfolgreich abgeschlossen.
Dieser Connection Pool ist nicht mit der Shared Server Architektur vergleichbar. Die Datenbank verwaltet nun selbstständig einen Pool von Dedicated Server-Verbindungen. Wie bei jedem Connection Pool kann man die minimale und maximale Anzahl der gehaltenen Verbindungen einstellen. Wenn ein Client eine Verbindung braucht, wird der Dedicated Server Prozess nicht neu erzeugt, er wird aus dem Pool genommen - und beim Schließen der Verbindung wird er an den Pool zurückgegeben. Wenn gerade keine Verbindung frei ist, wartet der Client solange bis eine frei ist. Das ist genau das, was wir für node.js brauchen. Zuerst stellen wir noch die minimale und maximale Anzahl Verbindungen ein.
begin
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE', '10');
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE', '20');
end;
/
Einstellungen überprüfen ...
SQL> SELECT * FROM DBA_CPOOL_INFO;

CONNECTION_POOL
--------------------------------------------------------------------------------
STATUS              MINSIZE    MAXSIZE   INCRSIZE SESSION_CACHED_CURSORS
---------------- ---------- ---------- ---------- ----------------------
INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION
------------------ -------------- --------------- --------------------
 NUM_CBROK MAXCONN_CBROK
---------- -------------
SYS_DEFAULT_CONNECTION_POOL
ACTIVE                   10         20          2                     20
               300            120          500000                86400
         1         40000

Um den Pool nun mit unserem node.js Programm zu nutzen, müssen nur die Verbindungsdaten angepasst werden. Übrigens: Das :POOLED kann man an jeden Oracle-Connection-String anhängen - der Database Resident Connection Pool lässt sich also auch mit Java, .NET oder SQL*Plus nutzen.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com:POOLED",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
Nun wird das Programm durchlaufen - als DBA kann man die View V$CPOOL_STATS und so die Auslastung der Datenbank beobachten ...
SQL> SELECT num_open_servers, num_busy_servers, num_auth_servers, num_requests
  2  FROM V$CPOOL_STATS

NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_AUTH_SERVERS NUM_REQUESTS
---------------- ---------------- ---------------- ------------
              15                9                1          799

Der Schlüssel zu skalierbaren node.js Skripten mit der Oracle-Datenbank liegt also tatsächlich in diesem etwas unscheinbaren Connection Pool. Ein node.js Skript sollte ihn auf jeden Fall verwenden - genau wie PHP.
Vorerst soll das genug sein - ich finde node.js nicht uninteressant; man kann es dank der Funktionsbibliothek sehr schön als Skripting Sprache einsetzen: In einem anderen Beispiel habe ich XML-Dateien nach JSON konvertiert und in die Datenbank geladen; im Vergleich zu diesen Skripten waren das nur wenige Codezeilen mehr ...
In this blog posting, I'd like write something about my first experiences with the Oracle database and node.js. This will not be a tutorial on how to install and configure node.js, and how to install new packages - there are plenty tutorials of this kind available in the internet. This posting will focus on how to deal with the Oracle database in node.js programs.
I like to describe node.js as "javascript on the server". It is based on Chrome's JavaScript Engine: a node.js script is being placed in a normal file and then executed by node.js - typically launched from the command line. The programming language is Javascript, node.js is extensible, provides a package manager (npm) and has a vibrant community which drives and develops the function library. Compared to other programming languages, there is a huge difference: Node.js is asynchronous, event driven and non-blocking for I/O bound functions; I'll go into more details during the blog posting.
To connect to the Oracle database, a program uses a driver - in the Java World this is JDBC, in the .NET World this is ODP.NET and so on. For node.js Oracle does not (yet) provide a driver - so we have to look what the community provides: And there is the module node-oracle by Joe Ferner, which is available from GitHub.
The download contains a README which pretty well describes the installation of the node-oracle module into an existing node.js environment. On my linux system this went smooth and without problems. After that we can author the first node.js program which does something with the Oracle database. So, let's select the EMP table and print out the results.
var oracle = require('oracle');

var connectData = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com", 
  user:     "scott",
  password: "tiger"
}

oracle.connect(connectData, function(err, connection) {
  if (err) {
   console.log("Error connecting to db:", err); return;
  }

  connection.execute("select ename, job from emp ", [],  function(err, results) {
    connection.close();
    console.log(results);
  });
});

console.log("done");
As said earlier, node.js is event driven. The oracle.connect function, which opens the connection to the database, is I/O bound (network communication to the Oracle database). So node.js executes it asynchonously. During its execution, node.js does not block and continues working with the next statement. Upon finish, the oracle.connect will execute the callback function which has been provided as a parameter (in the example this is an anonymous function). So, node.js code looks different from Java, C or PHP code. Due to the asynchronous and non-blocking architecture, developers work with callback functions.
Upon execution, the script generates the following output
$ node ora.js
done
[ { ENAME: 'SMITH', JOB: 'CLERK' },
  { ENAME: 'ALLEN', JOB: 'SALESMAN' },
  { ENAME: 'WARD', JOB: 'SALESMAN' },
  { ENAME: 'JONES', JOB: 'MANAGER' },
  { ENAME: 'MARTIN', JOB: 'SALESMAN' },
  { ENAME: 'BLAKE', JOB: 'MANAGER' },
  { ENAME: 'CLARK', JOB: 'MANAGER' },
  { ENAME: 'SCOTT', JOB: 'ANALYST' },
  { ENAME: 'KING', JOB: 'PRESIDENT' },
  { ENAME: 'TURNER', JOB: 'SALESMAN' },
  { ENAME: 'ADAMS', JOB: 'CLERK' },
  { ENAME: 'JAMES', JOB: 'CLERK' },
  { ENAME: 'FORD', JOB: 'ANALYST' },
  { ENAME: 'MILLER', JOB: 'CLERK' } ]
$
console.log("done") is the very last statement within the script - but in the output it seems that it has been executed first. That is due to the non-blocking characteristics of node.js. If you examine the script carefully, you'll notice that at the top-level there are only 2 statements. The first is oracle.connect, the second is console.log. The other lines are callback functions. So the script starts by invoking oracle.connect, the database connection is being established - and while this is being done, node.js continues executing the console.log statement. So we first see "done" and then the actual data from the database.
The next node.js script is supposed to load files into the database. I want to provide a directory - the node.js script loads all text files within that directory into a given database table, DOCUMENT_TABLE.
create table document_table (filename varchar2(200), document clob);
The node.js script is based on the previous one: The nature of the task is a bit more complex - and so is the script. The filesystem calls for reading a directory and reading a file are I/O bound as well - and so they have the same characteristics as the Oracle calls. We need to provide a callback in all the cases. For that reason we get this highly nested code. There are approaches in the node.js community to keep the code more simple, but this is another topic.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "orcl",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

ora.connect(oradb, function(err, connection) {
  if (err) {throw err;}
  connection.setAutoCommit(false);
  var fileCount = 0;
  fs.readdir(dirname, function (err, files) {
    if (err) {throw err;}
    files.forEach(function (file) {
      fileCount ++;
      fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;} else {
              console.log('"'+file+'" saved.');
              fileCount--;
              if (fileCount == 0) {
                connection.close();
                console.log("done");
              }
            }
        });
      });
    });
  });
});
The script opens the connection to the Oracle database, walks through the directory, reads each file and performs a SQL INSERT on the Oracle database. So far, so good. Here all the work is being done within one database connection. So on the database side, we utilize one CPU. Some database systems have more available resources, so what to do if we want to have this more scalable ...?
We need more database connections - so we change the order within the script. We first walk though the directory, read each file, then open a database connection in order to perform the INSERT operation. The code now looks like this.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
In the "traditional" world, this would work without any problems - perhaps it would be slower, because all these database connections are being opened and closed - but it would work. But the node.js script shows up nothing ... and after a while it stops with an error message (note that the script might run successful on a database with huge resources). What happens here ...?
[oracle@sccloud033 node]$ node loadFiles.js po

/home/oracle/node/loadFiles.js:19
          if (err) {throw err;}
                          ^
Error: ORA-12516: TNS:listener could not find available handler with matching protocol stack
Things become clear as we start monitoring the V$SESSION view ...
SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       28
SCOTT                                 154
SYS                                     1

-- after a while ...

SQL> select username, count(*) from v$session group by username

USERNAME                         COUNT(*)
------------------------------ ----------
                                       26
SCOTT                                 366
SYS                                     1

Again, node.js is event-driven. Creating an Oracle connection is a very expensive task - while the first one is running, the script continues in walking through the directory, reading files and spawning new Oracle connections. Before the first file has been stored into the database, node.js has walked completely though the directory and requested a database connection for each file. And as soon as the database limits have been reached, the script stops.
We need a connection pool. But node.js does not provide a connection pool for database connections. Luckily, the database does. Oracle 11.1 introduced the Database Resident Connection Pool which has been developed for exactly these cases. This connection pool (which is not the Shared Server Architecture) is a pool of Dedicated Server Connections. When a client establishes a database connection, no new background process will be created, it will be taken from the pool. And when the client closes the connection, the server process is not being destroyed, it is being placed back into the pool. And as all connection pools, one can configure settings like the minimum or maximum amount of connections or other settings.
The database-resident connection pool is being administered with the PL/SQL package DBMS_CONNECTION_POOL. You need DBA privileges in order to work with it and if you are using Oracle12c and the Multitenant Architecture, you need to work in the Container Database. Now, start the Connection Pool with default settings.
SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.
We can adjust pool settings ...
begin
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE', '10');
  DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE', '20');
end;
/
... and check the existing configuration.
SQL> SELECT * FROM DBA_CPOOL_INFO;

CONNECTION_POOL
--------------------------------------------------------------------------------
STATUS              MINSIZE    MAXSIZE   INCRSIZE SESSION_CACHED_CURSORS
---------------- ---------- ---------- ---------- ----------------------
INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION
------------------ -------------- --------------- --------------------
 NUM_CBROK MAXCONN_CBROK
---------- -------------
SYS_DEFAULT_CONNECTION_POOL
ACTIVE                   10         20          2                     20
               300            120          500000                86400
         1         40000

To actually use the connection pool in our node.js script, we just need to make a tiny change within the database connection parameters. Note that this works in SQL*Plus, Java or any other Oracle client as well.
var fs = require("fs");
var ora = require("oracle");

var oradb = {
  hostname: "sccloud034.de.oracle.com",
  port:     1521,
  database: "pdb01.de.oracle.com:POOLED",
  user:     "scott",
  password: "tiger"
}

var dirname = process.argv[2];

fs.readdir(dirname, function (err, files) {
  if (err) {throw err;}
  files.forEach(function (file) {
    fs.readFile(dirname+"/"+file, 'utf-8', function (err, data) {
      ora.connect(oradb, function(err, connection) {
        if (err) {throw err;}
        connection.execute(
          "insert into document_table values (:1, :2)",
          [file, data],
          function (err, results) {
            if (err) {throw err;}
            console.log('"'+file+'" saved.');
            connection.close();
        });
      });
    });
  });
});
While the node.js script is running, you might monitor the Oracle View V$CPOOL_STATS ... there you can see, how many server processes are being actually used.
SQL> SELECT num_open_servers, num_busy_servers, num_auth_servers, num_requests
  2  FROM V$CPOOL_STATS

NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_AUTH_SERVERS NUM_REQUESTS
---------------- ---------------- ---------------- ------------
              15                9                1          799

The key to scalable node.js scripts working with the Oracle database is a mainly unkown database feature: Database Resident Connection Pool. Every node.js program working on the Oracle database should use it.
I did some further experiments with node.js - but for today this should be enough information. I think, node.js can be nicely used as a scripting language. For instance, converting a folder of XML documents to JSON and uploading this to the database is just a minor change within the above script and will lead to only a few more lines of code ...

Beliebte Postings