26. Mai 2015

APEX_JSON ohne APEX: JSON parsen mit SQL und PL/SQL

Dies ist das zweite von zwei Blog-Postings zum neuen Package APEX_JSON, mit dem man JSON-Dokumente auch ohne APEX, nur mit PL/SQL, verarbeiten kann. Nachdem das erste Blog-Posting sich mit dem Erzeugen von JSON beschäftigt hat, geht es heute um das Parsen und Auslesen von JSON mit APEX_JSON. Vorher noch wichtiger Hinweis.
Ab Oracle12c, genauer: Ab der Version 12.1.0.2 stellt die Datenbank native SQL-Funktionen zum Parsen von JSON bereit. Diese sind im C-Code des Datenbankkerns implementiert und dürften daher wesentlich effizienter sein als eine PL/SQL-Lösung.
Wenn Ihr könnt, solltet Ihr JSON immer mit den nativen SQL/JSON-Funktionen parsen; in 12c also grundsätzlich diese verwenden. APEX_JSON sollte zum Parsen von JSON nur in 11g-Datenbanken verwendet werden. Weiter unten werden wir noch einen Vergleich durchführen.
Los geht's - als Beispiel nehme ich JSON-Dateien, wie sie von der Twitter-API angeliefert werden; hier ein Ausschnitt.
{
  "id": 578903819884585000,
  "text": "RT @iAdvise_live: #countdown naar #Apexworld @OGh_nl ...
  "geo": {...}
  "lang": "nl",
  "retweet_count": 2,
  "created_at": "Fri Mar 20 13:00:00 +0000 2015",
  "user": {
      "statuses_count": 266,
      "lang": "en",
      "id": 1237892781789,
      "favourites_count": 62,        
      "name": "Jonathan ...",
      "screen_name": "jvanvianen78",
  }
  :
}
Wenn man nun eine Tabelle mit diesen JSON-Dateien hat, kann man diese mit APEX_JSON.PARSE parsen und die Inhalte dann mit GET_VARCHAR2, GET_NUMBER und ähnlichen Calls auslesen. Ein Beispiel könnte dann so aussehen:
Hinweis: Wenn Ihr mit der allerersten APEX-Version 5.0.1 und einer deutschsprachigen Session-Language arbeitet, setzt vorher ein ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' ab.
declare
  l_parsed_json apex_json.t_values;
  l_value       varchar2(4000);
begin
  for i in (select tweet from apextweets) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    dbms_output.put_line(
      'User "'||
      apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      ) ||
      '" tweeted at ' ||
      apex_json.get_varchar2(
        p_path => 'created_at',
        p_values => l_parsed_json
      ) 
    );
  end loop;
end;
/
Das Ergebnis wird in diesem Fall mit DBMS_OUTPUT auf die Konsole geschrieben; natürlich ist auch eine andere Verarbeitung denkbar.
User "jvanvianen78" tweeted at Fri Mar 20 13:00:00 +0000 2015
User "Yvke1983" tweeted at Fri Mar 20 12:58:08 +0000 2015
User "iAdvise_live" tweeted at Fri Mar 20 12:57:35 +0000 2015
User "reynde75" tweeted at Fri Mar 20 11:54:56 +0000 2015
User "Smart4Apex" tweeted at Fri Mar 20 11:42:38 +0000 2015
User "brost" tweeted at Fri Mar 20 11:06:20 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:45:55 +0000 2015
User "crokitta" tweeted at Fri Mar 20 10:44:31 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:27:25 +0000 2015
User "PretiusSoftware" tweeted at Fri Mar 20 09:29:02 +0000 2015
:
Allerdings haben wir hier die gleiche Situation wie im letzten Blog-Posting: Man muss jede Abfragesituation in PL/SQL prozedural kodieren; bei Abfragen wäre es ja interessanter, direkt mit SQL auf die JSON-Dokumente zugreifen zu können. APEX_JSON bietet uns hier einen Trick an: Die Funktion TO_XMLTYPE wandelt das JSON in einen XMLTYPE um ...
select apex_json.to_xmltype(tweet) from tweets_json where rownum = 1
/

APEX_JSON.TO_XMLTYPE(TWEET)
--------------------------------------------------------------------------------
<json>
  <retweeted_status>
    <text>#countdown naar #Apexworld @OGh_nl #iadvise_live @Yvke1983 ...
    <retweeted>false</retweeted>
    <truncated>false</truncated>
    <lang>nl</lang>
    <entities>
      <symbols/>
      <urls/>
      <hashtags>
        <row>
          <text>countdown</text>
          <indices>
            <row>0</row>
            <row>10</row>
:
... und darauf lässt sich nun sehr elegant mit den SQL/XML-Funktionen der Datenbank arbeiten.
select
  screen_name,
  created_at,
  lang 
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30)  path 'user/screen_name',
    created_at  varchar2(100) path 'created_at',
    lang        varchar2(5)   path 'lang'
)
where rownum <= 20
/  

SCREEN_NAME                    CREATED_AT                          LANG
------------------------------ ----------------------------------- ----
jvanvianen78                   Fri Mar 20 13:00:00 +0000 2015      nl
Yvke1983                       Fri Mar 20 12:58:08 +0000 2015      nl
iAdvise_live                   Fri Mar 20 12:57:35 +0000 2015      nl
reynde75                       Fri Mar 20 11:54:56 +0000 2015      und
Smart4Apex                     Fri Mar 20 11:42:38 +0000 2015      nl
brost                          Fri Mar 20 11:06:20 +0000 2015      en
johnnyq72                      Fri Mar 20 10:45:55 +0000 2015      nl
crokitta                       Fri Mar 20 10:44:31 +0000 2015      nl
johnnyq72                      Fri Mar 20 10:27:25 +0000 2015      en
PretiusSoftware                Fri Mar 20 09:29:02 +0000 2015      en
josepcoves                     Mon Mar 23 18:57:22 +0000 2015      en
Fr4ncis                        Mon Mar 23 18:48:29 +0000 2015      en
BIntsifuL                      Mon Mar 23 17:58:38 +0000 2015      en
pauljacobs123                  Mon Mar 23 17:45:28 +0000 2015      en
S3v3n11                        Mon Mar 23 17:20:52 +0000 2015      en
Das ist doch schon sehr bequem - und man kann zusätzliche Attribute einfach durch Anpassen der SQL-Abfrage dazunehmen. Auch in die Hierarchie kann man einsteigen, genau wie bei XML. Nun wollen wir eine etwas anspruchsvollere Aufgabe lösen: "Wieviele Tweets (ohne "Retweets") hat jeder User abgesetzt?"
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30) path 'user/screen_name',
    created_at  varchar2(100) path 'created_at'
)
where not xmlexists(
  '/json/retweeted_status'
  passing apex_json.to_xmltype(tweet)
)
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:01:17:55
Man kann mit den XML-Funktionen also eine ganze Menge erreichen. Würde man die gleiche Aufgabe mit PL/SQL Logik lösen, wäre dieser Code hier nötig (die absteigende Sortierung fehlt sogar noch).
declare
  type t_tabtype is table of number index by varchar2(200);

  l_parsed_json apex_json.t_values;
  l_user        varchar2(4000);
  l_tweetsbyusr t_tabtype;
  l_retweeted   boolean; 
begin
  for i in (select tweet from tweets_json) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    l_retweeted := apex_json.does_exist(
      p_path   => 'retweeted_status',
      p_values => l_parsed_json
    );
    if not l_retweeted then 
      l_user := apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      );
      if l_tweetsbyusr.exists(l_user)  then
        l_tweetsbyusr(l_user) := l_tweetsbyusr(l_user) + 1;
      else 
        l_tweetsbyusr(l_user) := 1;
      end if;
    end if;
  end loop;
  l_user := l_tweetsbyusr.first;
  while l_user is not null loop
    dbms_output.put_line(l_user||':'||l_tweetsbyusr(l_user));
    l_user := l_tweetsbyusr.next(l_user);
  end loop;
end;
/

APEXORADEV:13
AliUK12:1
AljazMali:1
AntonScheffer:2
BIASCareers:2
BIntsifuL:7
BigBen212:1
BlueberryCoder:1
CPiasecki23:1
:

Elapsed: 00:01:06.36
Interessant ist nun aber die Ausführungszeit - für diese Auswertung auf 4.526 JSON-Dateien wurden mit dem XMLTYPE-Ansatz etwa 1 Minute und 17 Sekunden verbraucht, der prozedurale PL/SQL-Ansatz verbrauchte 1 Minute und 6 Sekunden. Halten wir da mal die nativen SQL/JSON-Funktionen, die ab 12.1.0.2 bereitstehen, dagegen.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, json_table(
  tweet,
  '$'
  columns (
    screen_name varchar2(30)  path '$.user.screen_name',
    created_at  varchar2(100) path '$.created_at'
  )
)
where not json_exists(tweet, '$.retweeted_status')
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:00:00:79
Es kommt das gleiche Ergebnis heraus - aber Sub-Second! 0.79 Sekunden, um genau zu sein. Wenn Ihr also auf einer 12c-Datenbank seid, verwendet auf jeden Fall die SQL/JSON-Funktionen zum JSON-Parsong - diese sind wesentlich effizienter. Mit APEX_JSON solltet Ihr nur dann parsen, wenn die nativen SQL/JSON-Funktionen nicht in Frage kommen - das wäre bspw. in einer Oracle11g-Datenbank der Fall.
Das mit APEX 5.0 eingeführte PL/SQL-Paket APEX_JSON eignet sich auch außerhalb von APEX sehr gut, um in PL/SQL mit JSON zu arbeiten. Neben dem Generieren von JSON bietet es auch Möglichkeiten an, JSON zu parsen. Besonders interessant ist die Möglichkeit, ein JSON mit TO_XMLTYPE in einen XMLTYPE zu wandeln, so dass man danach mit den SQL/XML-Funktionen darauf auch komplexere Abfragen durchführen kann.
Die mit Oracle12c eingeführten, nativen SQL/JSON-Funktionen bieten hierfür allerdings eine um Längen bessere Performance, so dass SQL-Funktionen wie JSON_VALUE, JSON_QUERY oder JSON_TABLE zum Parsen von JSON sicherlich zu bevorzugen wären.
This is the second of two blog postings about the new PL/SQL package APEX_JSON, which allows to work with JSON documents in a PL/SQL environment, even outside of APEX. While the first blog posting was about generating JSON with APEX_JSON, this posting will concentrate on the parsing side. We already have a bunch of JSON documents and want to work with their data. Before we begin, here is a very important note.
Beginning with Oracle12c, more specific: 12.1.0.2, the Oracle database provides native SQL/JSON fucntions zu parse and access JSON data. These are implemented within the database kernel and therefore much more efficient than a PL/SQL-based approach.
So, in an Oracle12c environment, always try to work with the nativer SQL/JSON functions instead of programming PL/SQL with APEX_JSON. JSON parsing with APEX_JSON is suited for pre-12c databases, where the native functions are not available. In this bloh posting, you'll also see a comparison between the two approaches.
Here we go: As an example, I'll take some JSON files which I obtained from the Twitter API. Here's a snippet.
{
  "id": 578903819884585000,
  "text": "RT @iAdvise_live: #countdown naar #Apexworld @OGh_nl ...
  "geo": {...}
  "lang": "nl",
  "retweet_count": 2,
  "created_at": "Fri Mar 20 13:00:00 +0000 2015",
  "user": {
      "statuses_count": 266,
      "lang": "en",
      "id": 1237892781789,
      "favourites_count": 62,        
      "name": "Jonathan ...",
      "screen_name": "jvanvianen78",
  }
  :
}
Assumed, we have a table containing instances of these JSON documents, we can start parsing these with APEX_JSON.PARSE. APEX_JSON will generate an in-Memory representation of the JSON data. We can then access the JSON data by calling methods like GET_VARCHAR2 or GET_NUMBER. A first procedure looks like this:
Important: If you are working with the very first APEX 5.0.1 release, and you are using a german or french NLS environment, execute the following call at the beginning of your session: ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'
declare
  l_parsed_json apex_json.t_values;
  l_value       varchar2(4000);
begin
  for i in (select tweet from apextweets) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    dbms_output.put_line(
      'User "'||
      apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      ) ||
      '" tweeted at ' ||
      apex_json.get_varchar2(
        p_path => 'created_at',
        p_values => l_parsed_json
      ) 
    );
  end loop;
end;
/
In this example, the processing results are being written onto the console with DBMS_OUTPUT. Of course, we could also process it otherwise or create a table function which returns structured output. So far, so good.
User "jvanvianen78" tweeted at Fri Mar 20 13:00:00 +0000 2015
User "Yvke1983" tweeted at Fri Mar 20 12:58:08 +0000 2015
User "iAdvise_live" tweeted at Fri Mar 20 12:57:35 +0000 2015
User "reynde75" tweeted at Fri Mar 20 11:54:56 +0000 2015
User "Smart4Apex" tweeted at Fri Mar 20 11:42:38 +0000 2015
User "brost" tweeted at Fri Mar 20 11:06:20 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:45:55 +0000 2015
User "crokitta" tweeted at Fri Mar 20 10:44:31 +0000 2015
User "johnnyq72" tweeted at Fri Mar 20 10:27:25 +0000 2015
User "PretiusSoftware" tweeted at Fri Mar 20 09:29:02 +0000 2015
:
But this is the same situation as described in the first blog posting. We will have to create procedural code for each individual query requirement. It would be much more convenient, to execute a SQL-like query direcly on the JSON data. And APEX_JSON offers a trick: The function TO_XMLTYPE converts JSON to an XMLTYPE instance ...
select apex_json.to_xmltype(tweet) from tweets_json where rownum = 1
/

APEX_JSON.TO_XMLTYPE(TWEET)
--------------------------------------------------------------------------------
<json>
  <retweeted_status>
    <text>#countdown naar #Apexworld @OGh_nl #iadvise_live @Yvke1983 ...
    <retweeted>false</retweeted>
    <truncated>false</truncated>
    <lang>nl</lang>
    <entities>
      <symbols/>
      <urls/>
      <hashtags>
        <row>
          <text>countdown</text>
          <indices>
            <row>0</row>
            <row>10</row>
:
... and as soon as we have XMLTYPE, we can start using the native SQL/XML functions which allow us to author SQL queries directly on XML data.
select
  screen_name,
  created_at,
  lang 
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30)  path 'user/screen_name',
    created_at  varchar2(100) path 'created_at',
    lang        varchar2(5)   path 'lang'
)
where rownum <= 20
/  

SCREEN_NAME                    CREATED_AT                          LANG
------------------------------ ----------------------------------- ----
jvanvianen78                   Fri Mar 20 13:00:00 +0000 2015      nl
Yvke1983                       Fri Mar 20 12:58:08 +0000 2015      nl
iAdvise_live                   Fri Mar 20 12:57:35 +0000 2015      nl
reynde75                       Fri Mar 20 11:54:56 +0000 2015      und
Smart4Apex                     Fri Mar 20 11:42:38 +0000 2015      nl
brost                          Fri Mar 20 11:06:20 +0000 2015      en
johnnyq72                      Fri Mar 20 10:45:55 +0000 2015      nl
crokitta                       Fri Mar 20 10:44:31 +0000 2015      nl
johnnyq72                      Fri Mar 20 10:27:25 +0000 2015      en
PretiusSoftware                Fri Mar 20 09:29:02 +0000 2015      en
josepcoves                     Mon Mar 23 18:57:22 +0000 2015      en
Fr4ncis                        Mon Mar 23 18:48:29 +0000 2015      en
BIntsifuL                      Mon Mar 23 17:58:38 +0000 2015      en
pauljacobs123                  Mon Mar 23 17:45:28 +0000 2015      en
S3v3n11                        Mon Mar 23 17:20:52 +0000 2015      en
This is much better - by changing the SQL query we can access any JSON attribute we want. By nesting XMLTABLE expressions, we can also access hierarchical structures; this has been an XML requirement for years. Using this powerful tool, we can take a more sophisticated challenge: "We want to know, how many tweets each user posted, but without retweets". The query is straightforward.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, xmltable(
  '/json'
  passing apex_json.to_xmltype(tweet)
  columns
    screen_name varchar2(30) path 'user/screen_name',
    created_at  varchar2(100) path 'created_at'
)
where not xmlexists(
  '/json/retweeted_status'
  passing apex_json.to_xmltype(tweet)
)
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:01:17:55
So, the SQL/XML functions take us a long way in parsing and working with JSON. Without those functions, we would have to author an awful lot of procedural code. In the following example, the descending sort is even missing.
declare
  type t_tabtype is table of number index by varchar2(200);

  l_parsed_json apex_json.t_values;
  l_user        varchar2(4000);
  l_tweetsbyusr t_tabtype;
  l_retweeted   boolean; 
begin
  for i in (select tweet from tweets_json) loop
    apex_json.parse(
      p_values => l_parsed_json,
      p_source => i.tweet
    );
    l_retweeted := apex_json.does_exist(
      p_path   => 'retweeted_status',
      p_values => l_parsed_json
    );
    if not l_retweeted then 
      l_user := apex_json.get_varchar2(
        p_path => 'user.screen_name',
        p_values => l_parsed_json
      );
      if l_tweetsbyusr.exists(l_user)  then
        l_tweetsbyusr(l_user) := l_tweetsbyusr(l_user) + 1;
      else 
        l_tweetsbyusr(l_user) := 1;
      end if;
    end if;
  end loop;
  l_user := l_tweetsbyusr.first;
  while l_user is not null loop
    dbms_output.put_line(l_user||':'||l_tweetsbyusr(l_user));
    l_user := l_tweetsbyusr.next(l_user);
  end loop;
end;
/

APEXORADEV:13
AliUK12:1
AljazMali:1
AntonScheffer:2
BIASCareers:2
BIntsifuL:7
BigBen212:1
BlueberryCoder:1
CPiasecki23:1
:

Elapsed: 00:01:06.36
Let's have a look on the exeution time: The table contains 4.526 JSON documents. For these documents, the SQL/XML approach took about 1 minute and 17 seconds, whereas the procedural approach took 1 minute and 6 seconds. We see, the XML overhead is rather small compared to the JSON parsing time. Let's compare this to the native SQL/JSON functions in Oracle12c.
select
  screen_name,
  count(*) anzahl_tweets
from tweets_json, json_table(
  tweet,
  '$'
  columns (
    screen_name varchar2(30)  path '$.user.screen_name',
    created_at  varchar2(100) path '$.created_at'
  )
)
where not json_exists(tweet, '$.retweeted_status')
group by screen_name
order by 2 desc
/  

SCREEN_NAME                    ANZAHL_TWEETS
------------------------------ -------------
orclapexblogs                            186
joelkallman                               62
swesley_perth                             45
flederbine                                39
andre_blu                                 39
:

Elapsed: 00:00:00:79
We get the same result, but in less than a second! 0.79 Seconds, to be precise. So, once more: If you are working in an Oracle12c environment, make sure to use the native SQL/JSON functions when parsing and accessing JSON data. APEX_JSON is suitable when the SQL/JSON functions are not available (like in Oracle 11.2) or not appropriate (you might want to use pure PL/SQL and avoid SQL in specific situations).
Summarized, the new APEX_JSON package introduced with APEX 5.0, is very usable outside of APEX as well. Beyond generating JSON it also allows o parse JSON documents and to access JSON data. After converting JSON to XMLTYPE, we can perform even flexible queries and access any JSON attribute we want - without authoring additional procedural code.
But the native SQL/JSON functions, which are available in Oracle12c and higher, offer much better query performance since these have been implemented directly into the database kernel. So, if possible, JSON parsing is done best with the JSON_VALUE, JSON_QUERY or JSON_TABLE SQL functions.

4. Mai 2015

APEX_JSON ohne APEX: JSON erzeugen mit SQL und PL/SQL

Dieses Blog Posting dreht sich um ein PL/SQL-Paket, welches mit APEX 5.0 eingeführt wurde, welches aber auch für Nicht-APEX-Entwickler sehr interessant ist: APEX_JSON. Dieses Paket enthält JSON-Unterstützung für PL/SQL-Programmierer - JSON lässt sich damit sowohl parsen und auslesen als auch erzeugen. Erstellt man sich damit eigene Funktionen, so kommt die JSON-Unterstützung auch in die SQL-Welt. APEX_JSON ist zwar Teil von APEX 5.0 (und wird auch mit diesem zusammen installiert), die Nutzung erfolgt aber komplett losgelöst von APEX selbst; man kann es auch ohne APEX verwenden. Für den SQL und PL/SQL Entwickler bieten sich damit zwei Anwendungsfelder für APEX_JSON:
  • JSON kann nun auch in Datenbankversionen vor 12.1.0.2 geparst werden. Ab 12.1.0.2 empfehle ich aber dringend den Einsatz der nativen JSON-Unterstützung - die SQL/JSON-Funktionen wie JSON_VALUE, JSON_QUERY oder JSON_TABLE sind direkt im Datenbankkern implementiert und dürften damit effizienter sein, als eine PL/SQL-Lösung. Wer aber noch 11.2 oder 12.1.0.1 einsetzen muss, findet in APEX_JSON eine Alternative.
  • Das native Erzeugen von JSON mit SQL anhand von Tabellendaten geht auch mit 12.1.0.2 noch nicht; dieses Feature ist zwar geplant, aber eben noch nicht da. Hier kann APEX_JSON ebenfalls Abhilfe leisten.
In diesem Blog-Posting werde ich daher zeigen, wie man JSON mit APEX_JSON aus Tabellendaten generieren kann. Wie man JSON mit APEX_JSON ausliest, bleibt dem nächsten Posting vorbehalten. Wir starten (wie immer) einfach: Es soll ein JSON aus der wohlbekannten Tabelle EMP erzeugt werden. Für den Anfang reicht die Spalte ENAME. Der Code mit APEX_JSON sieht in etwa wie folgt aus.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('emp');
  for i in (select ename from emp) loop
    apex_json.open_object();
    apex_json.write('ename', i.ename);
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
Als Ergebnis kommt heraus ...
{
  "emp":[
    { "ename":"SMITH" }
   ,{ "ename":"ALLEN" }
   ,{ "ename":"WARD" }
   ,{ "ename":"JONES" }

:

   ,{ "ename":"FORD" }
   ,{ "ename":"MILLER" }
  ]
}
Zu Beginn wird mit INITIALIZE_CLOB_OUTPUT festlegt, dass das generierte JSON in einen CLOB geschrieben werden soll (alternative steht für den APEX-Entwickler der HTP-Buffer bereit). INITIALIZE_CLOB_OUTPUT nimmt drei Parameter entgegen. Die ersten beiden Parameter benötigt APEX_JSON zum Erzeugen des temporären CLOBs - sie werden analog zu DBMS_LOB.CREATETEMPORARY genutzt. Der dritte Parameter legt fest, welche Einrückung für die JSON-Hierarchie verwendet werden soll.
Natürlich kann man auch komplexere JSON-Hierarchien generieren; man muss die Aufrufe von OPEN_OBJECT und OPEN_ARRAY nur entsprechend schachteln. Das folgende PL/SQL generiert JSON mit einer Hierarchieebene für die Tabellen DEPT und EMP.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('dept');
  for i in (select * from dept) loop
    apex_json.open_object();
    apex_json.write('deptno', i.deptno);
    apex_json.write('dname',  i.dname);
    apex_json.open_array('emp');
    for e in (select * from emp where deptno = i.deptno) loop
      apex_json.open_object();
      apex_json.write('empno', e.empno);
      apex_json.write('ename', e.ename);
      apex_json.write('sal',   e.sal);
      apex_json.close_object;
    end loop;
    apex_json.close_array;
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

{
  "dept":[
    {
      "deptno":10
     ,"dname":"ACCOUNTING"
     ,"emp":[
        {
          "empno":7782
         ,"ename":"CLARK"
         ,"sal":2450
        }
       ,{
          "empno":7839
         ,"ename":"KING"

:

   ,{
      "deptno":40
     ,"dname":"OPERATIONS"
     ,"emp":[
      ]
    }
  ]
}
Achtet als Entwickler darauf, die OPEN_OBJECT und OPEN_ARRAY Aufrufe korrekt zu schachteln und mit CLOSE_OBJECT und CLOSE_ARRAY-Aufrufen zu schließen. APEX_JSON stellt das nicht sicher, wenn also ein CLOSE_OBJECT-Aufruf fehlt, bekommt Ihr eine ungültige JSON-Struktur. APEX_JSON bietet euch die Prozedur CLOSE_ALL an, die man am besten als letztes aufruft; diese schließt alle noch offenen Objekte und Arrays - und euer JSON ist syntaktisch korrekt.
Das bedeutet aber, dass die JSON-Hierarchie immer in einer PL/SQL-Prozedur oder einem anonymen Block "programmiert" werden muss. Die Frage ist, ob man da nicht etwas automatisieren kann - und in der Tat gibt es eine Variante von APEX_JSON.WRITE, die einen Cursor entgegennimmt. Die probieren wir gleich mal aus: Wir bauen eine SQL-Funktion, die einen beliebigen Cursor entgegennimmt und daraus JSON generiert.
create or replace function make_json(
  p_cursor in sys_refcursor,
  p_root   in varchar2 default 'query',
  p_indent in number default 2
) return clob 
is
  l_json   clob;
  l_cursor sys_refcursor := p_cursor;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, p_indent);
  apex_json.open_object();
  apex_json.write(p_root, l_cursor);
  apex_json.close_object;
  return apex_json.get_clob_output;
end make_json;
/
sho err
Verwendet wird die Funktion wie folgt ...
SQL select make_json(cursor(select * from emp)) as json_clob from dual;

JSON_CLOB
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"ENAME":"SMITH"
     ,"JOB":"CLERK"
     ,"MGR":7902
     ,"HIREDATE":"1980-12-17T00:00:00Z"
     ,"SAL":800
     ,"DEPTNO":20
    }
   ,{

:

     ,"SAL":1300
     ,"DEPTNO":10
    }
  ]
}
In diese Funktion könnt Ihr nun jede beliebige Query hineingeben; es wird immer ein JSON mit korrekten Attributnamen generiert. Allerdings ist die Struktur des generierten JSON - ebenso wie das Ergebnis einer SQL-Query - immer flach. Manchmal wird aber JSON mit Hierarchie gebraucht - muss man nun wieder programmieren ...?
Interessanterweise gibt es eine Version von APEX_JSON.WRITE, die einen XMLTYPE entgegennimmt. Ginge es nur darum, das XML so, wie es ist, ins JSON einzubauen, könnte man auch mit der APEX_JSON.WRITE-Variante arbeiten, die einen CLOB entgegennimmt. Wenn es aber eine eigene Variante für XMLTYPE gibt, muss mehr dahinterstecken. Probieren wir das mal aus, indem wir eine kleine Objektstruktur als XMLTYPE modellieren und daraus JSON erzeugen ...
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object('xml');
  apex_json.write(xmltype(
'<r>
  <c1>Test</c1>
  <c2>Test2</c2>
  <c3>
    <object>
      <a1>A1</a1>
      <a2>A2</a2>
    </object>
  </c3>
</r>'));
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

"xml":{
  {"c1":"Test","c2":"Test2","c3":{"object":{"a1":"A1","a2":"A2"}}}
}
APEX_JSON interpretiert den XMLTYPE ganz offensichtlich - und versucht, die XML-Struktur in JSON nachzubilden. Das kann man sich für unsere obige Funktion MAKE_JSON zunutze machen. Angenommen, wir erstellen eine XML-View auf die Tabelle EMP (dazu kann man die seit langem verfügbaren SQL/XML Funktionen hernehmen) ...
create or replace view empxml as 
select 
  empno, 
  xmlelement(
    "EMP",
    xmlelement("ENAME", ename), 
    xmlelement("SAL", ename), 
    xmlelement("MGR_TO", (
      select 
        xmlagg(
          xmlelement("EMP", 
            xmlelement("ENAME", d.ename)
          )
        )
        from emp d 
        where d.mgr = e.empno
      )
    ) 
  ) as xml from emp e
/
Die View lässt sich ganz normal selektieren ...
SQL> select empno, xmlserialize(document xml indent size=2)  xml from empxml e;

     EMPNO XML
---------- ----------------------------------------
      7369 <EMP>
             <ENAME>SMITH</ENAME>
             <SAL>SMITH</SAL>
             <MGR_TO/>
           </EMP>

      7566 <EMP>
             <ENAME>JONES</ENAME>
             <SAL>JONES</SAL>
             <MGR_TO>
               <EMP>
                 <ENAME>SCOTT</ENAME>
               </EMP>
               <EMP>
                 <ENAME>FORD</ENAME>
               </EMP>
             </MGR_TO>
           </EMP>
Gibt man das SELECT auf diese View nun als Cursor in die MAKE_JSON-Funktion, so bekommen wir JSON mit Hierarchie - und das ohne die Hierarchie in PL/SQL explizit zu programmieren ...
SQL> select make_json(cursor(select empno, xml emp from empxml)) from dual;

MAKE_JSON(CURSOR(SELECTEMPNO,XMLEMPFROMEMPXML))
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"EMP":{"ENAME":"SMITH","SAL":"SMITH","MGR_TO":null}
    }
   ,{
      "EMPNO":7566
     ,"EMP":{"ENAME":"JONES","SAL":"JONES","MGR_TO":[{"ENAME":"SCOTT"},{"ENAME": "FORD"}]}
    }
:

}
Was die XML-Struktur angeht, ist APEX_JSON sehr empfindlich: Angenommen, es soll ein Array mit Objekten, die je mehrere Attribute haben, generiert werden. Dann sollte in der generierten XML-Struktur jedes Objekt nochmals von einem XML-Tag umgeben sein - ist das nicht der Fall, so generiert APEX_JSON unter Umständen eine andere Struktur. Hier muss man einfach ein wenig experimentieren.
Das mit APEX 5.0 eingeführte PL/SQL-Paket APEX_JSON eignet sich auch außerhalb von APEX sehr gut, um JSON aus SQL und PL/SQL heraus zu generieren. Die zu erzeugende JSON-Struktur kann mit PL/SQL-Aufrufen "programmiert" werden. Darüber hinaus bietet APEX_JSON die Möglichkeit an, JSON anhand eines Cursors zu erzeugen; alle Attributnamen werden dann aus den Tabellenspalten abgeleitet. Selektiert dieser Cursor XML-Dokumente als XMLTYPE, so leitet APEX_JSON die JSON-Strukturen aus dem XML ab - komplexe, hierarchische JSON-Strukturen lassen sich so auch ohne PL/SQL-Programmierung erzeugen: Es braucht nur eine generische SQL-Funktion, welche einen Cursor entgegennimmt und ein CLOB zurückliefert.
This Blog Posting is about a PL/SQL package, which has recently been introduced with APEX 5.0, and which is very interesting also for non-APEX Developers: APEX_JSON. This package is based upon the very popular PL/JSON package and provides PL/SQL support for parsing and generating JSON. By building own SQL functions, APEX_JSON functionality can be brought to the SQL layer as well (if you are interested in this, read on). SQL and PL/SQL developers can find two very nice use-cases for APEX_JSON - outside of APEX:
  • JSON can now be parsed also in pre-12.1.0.2 databases. In 12.1.0.2 and higher, I'd recommand to use the new native SQL/JSON functions. JSON_VALUE, JSON_QUERY or JSON_TABLE have been implemented at SQL level, within the database kernel, and should therefore be more efficient than a PL/SQL solution. But those who need to implement JSON parsing on 11.2 or 12.1.0.1, can now use APEX_JSON very nicely for their requirements.
  • Generating JSON from SQL query results or table data is still not possible with SQL alone - even with 12.1.0.2. Until this feature arrives, we can do this kind of work with APEX_JSON.
So, in this blog posting, I'll show how JSON can be generated from table data using APEX_JSON - but at the SQL and PL/SQL layer - without APEX. The next posting will then be about parsing JSON with APEX_JSON. Let's start easy (as always): We want to generate JSON from the well known table EMP and for this first example, the ENAME column is sufficient.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('emp');
  for i in (select ename from emp) loop
    apex_json.open_object();
    apex_json.write('ename', i.ename);
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
The result of this PL/SQL block is ...
{
  "emp":[
    { "ename":"SMITH" }
   ,{ "ename":"ALLEN" }
   ,{ "ename":"WARD" }
   ,{ "ename":"JONES" }

:

   ,{ "ename":"FORD" }
   ,{ "ename":"MILLER" }
  ]
}
At the beginning, we initialize the JSON generator with INITIALIZE_CLOB_OUTPUT. So, the JSON output will be written to a (temporary) CLOB. Within APEX, there is also the alternative to write the output to the HTP buffer. INITIALIZE_CLOB_OUTPUT takes three parameters. The first two are being used to construct the internal temporary LOB, usage is the same as in DBMS_LOB.CREATETEMPORARY. The third parameter determines the indentation for the JSON hierarchy.
Of course, APEX_JSON also allows to create more complex JSON hierarchies. It's all about the nesting of OPEN_OBJECT and OPEN_ARRAY calls. So, the following example generates "nested JSON" for the tables DEPT and EMP.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object();
  apex_json.open_array('dept');
  for i in (select * from dept) loop
    apex_json.open_object();
    apex_json.write('deptno', i.deptno);
    apex_json.write('dname',  i.dname);
    apex_json.open_array('emp');
    for e in (select * from emp where deptno = i.deptno) loop
      apex_json.open_object();
      apex_json.write('empno', e.empno);
      apex_json.write('ename', e.ename);
      apex_json.write('sal',   e.sal);
      apex_json.close_object;
    end loop;
    apex_json.close_array;
    apex_json.close_object;
  end loop;
  apex_json.close_array;
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

{
  "dept":[
    {
      "deptno":10
     ,"dname":"ACCOUNTING"
     ,"emp":[
        {
          "empno":7782
         ,"ename":"CLARK"
         ,"sal":2450
        }
       ,{
          "empno":7839
         ,"ename":"KING"

:

   ,{
      "deptno":40
     ,"dname":"OPERATIONS"
     ,"emp":[
      ]
    }
  ]
}
Note, that you as the developer are responsible for the correct nesting of your OPEN_OBJECT and OPEN_ARRAY calls and that you have to call CLOSE_OBJECT and CLOSE_ARRAY repectively. Opening an object or an array without closing it leads to a wrong or invalid JSON structure. APEX_JSON provides the CLOSE_ALL procedure, which is best being called at the end of your procedure; it will prevent generating JSON with objects unclosed.
It seems that the JSON hierarchy always has to be "coded" with PL/SQL calls in a procedure or an anonymous block. In practice, we often need something more "automagically": We want to provide a query - and the generator does the rest. For this purpose, one overloading of APEX_JSON.WRITE takes a cursor as parameter. Let's try this out: We build a generic SQL function, taking a Cursor as its parameter and generating JSON from that cursor with APEX_JSON.
create or replace function make_json(
  p_cursor in sys_refcursor,
  p_root   in varchar2 default 'query',
  p_indent in number default 2
) return clob 
is
  l_json   clob;
  l_cursor sys_refcursor := p_cursor;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, p_indent);
  apex_json.open_object();
  apex_json.write(p_root, l_cursor);
  apex_json.close_object;
  return apex_json.get_clob_output;
end make_json;
/
sho err
This function can be used as follows ...
SQL select make_json(cursor(select * from emp)) as json_clob from dual;

JSON_CLOB
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"ENAME":"SMITH"
     ,"JOB":"CLERK"
     ,"MGR":7902
     ,"HIREDATE":"1980-12-17T00:00:00Z"
     ,"SAL":800
     ,"DEPTNO":20
    }
   ,{

:

     ,"SAL":1300
     ,"DEPTNO":10
    }
  ]
}
Now we have a function which can take any SQL query to generate JSON from its results. And we can use this query in the SQL layer - no PL/SQL needed from here on. With this function you should be able to take out a large amount of PL/SQL "JSON generator" code. This function does it all.
The JSON structure coming out of this function will always be as flat as a SQL query result is. In some cases, more complex JSON with a hierarchy is needed - does that mean we have to write PL/SQL code again ...?
Interestingly, there is another overloading of APEX_JSON.WRITE. This one takes XMLTYPE as its parameter, and its purpose is not to simply embed XML to the JSON output (the CLOB variant does this). This function can do more - and to explore how it works, again, a simple example is needed: Let's model a simple object structure as XMLTYPE and pass this to APEX_JSON.WRITE.
declare
  l_json clob;
begin
  apex_json.initialize_clob_output(DBMS_LOB.CALL, true, 2);
  apex_json.open_object('xml');
  apex_json.write(xmltype(
'<r>
  <c1>Test</c1>
  <c2>Test2</c2>
  <c3>
    <object>
      <a1>A1</a1>
      <a2>A2</a2>
    </object>
  </c3>
</r>'));
  apex_json.close_object;
  dbms_output.put_line(apex_json.get_clob_output);
end;
/

"xml":{
  {"c1":"Test","c2":"Test2","c3":{"object":{"a1":"A1","a2":"A2"}}}
}
APEX_JSON examines the XMLTYPE and tries to rebuild the XML structure in the JSON output. We can use this for the above MAKE_JSON function - the cursor needs to select an XMLTYPE column, then. Thus, we now create a more complex XML structure for the data within the EMP table. We use the SQL/XML functions to generate XML and encapsulate the SQL query in a view.
create or replace view empxml as 
select 
  empno, 
  xmlelement(
    "EMP",
    xmlelement("ENAME", ename), 
    xmlelement("SAL", ename), 
    xmlelement("MGR_TO", (
      select 
        xmlagg(
          xmlelement("EMP", 
            xmlelement("ENAME", d.ename)
          )
        )
        from emp d 
        where d.mgr = e.empno
      )
    ) 
  ) as xml from emp e
/
The view can be selected ...
SQL> select empno, xmlserialize(document xml indent size=2)  xml from empxml e;

     EMPNO XML
---------- ----------------------------------------
      7369 <EMP>
             <ENAME>SMITH</ENAME>
             <SAL>SMITH</SAL>
             <MGR_TO/>
           </EMP>

      7566 <EMP>
             <ENAME>JONES</ENAME>
             <SAL>JONES</SAL>
             <MGR_TO>
               <EMP>
                 <ENAME>SCOTT</ENAME>
               </EMP>
               <EMP>
                 <ENAME>FORD</ENAME>
               </EMP>
             </MGR_TO>
           </EMP>
Passing this query to the MAKE_JSON function leads to a JSON result with a hierarchy according to the structure of the XMLTYPE column XML. We generated JSON with a hierarchy, but we did not hard-code it with PL/SQL calls.
SQL> select make_json(cursor(select empno, xml emp from empxml)) from dual;

MAKE_JSON(CURSOR(SELECTEMPNO,XMLEMPFROMEMPXML))
--------------------------------------------------------------------------------
{
  "query":  [
    {
      "EMPNO":7369
     ,"EMP":{"ENAME":"SMITH","SAL":"SMITH","MGR_TO":null}
    }
   ,{
      "EMPNO":7566
     ,"EMP":{"ENAME":"JONES","SAL":"JONES","MGR_TO":[{"ENAME":"SCOTT"},{"ENAME": "FORD"}]}
    }
:

}
APEX_JSON seems to be a but picky regarding the XML structure; for instance, objects with multiple attributes should be encapsulated with an XML tag; if this is missing, you'll not get the right JSON structure. You might need to experiment a bit ...
Summarized, I think that the new APEX_JSON package introduced with APEX 5.0, is very usable outside of APEX as well. The SQL and PL/SQL programmer finds a nice solution for their "Generating JSON" needs. The option to generate JSON from a cursor allows very generic solutions - one PL/SQL procedure is able to generate JSON from multiple tables - always with correct attributes. Bringing XMLTYPE into the game even allows generic solutions with complex hierarchys. A simple SQL function taking a cursor and returning a CLOB brings all this functionality to the SQL layer.

2. April 2015

DOAG DevCamp - und weitere Veranstaltungen im Frühjahr 2015

This blog posting is about some upcoming events in Germany and in german language - and therefore in german only. Makes sense, doesn't it?
Auch wenn ich dieses Jahr wegen anderer Termine nicht teilnehmen kann, so möchte ich dennoch eine Lanze für das DOAG DevCamp, welches am 29. und 30. April in Frankfurt stattfindet, brechen. Dieses Mal steht die Veranstaltung unter dem allgemeinen Thema Upcycling Software - welches uns im Umfeld von Unternehmensdatenbanken und den Systemen drumherum immer wieder betrifft.
Auf dem ersten DevCamp letztes Jahr in München war ich dabei - und von der Art und Weise der Veranstaltung wirklich angetan. Es gibt im Vorfeld keine feste Agenda, zu Beginn kommen alle Teilnehmer zusammen und erstellen die Agenda gemeinsam. Das Format ist unglaublich flexibel und spontan - und auch ohne vorbereitete Vorträge entstehen Diskussionen, man bekommt eine Menge neuer Ideen, Gedanken und Anregungen.
Insofern: Wer noch kein Barcamp besucht hat, dem möchte ich das hiermit wärmstens empfehlen - probiert es einfach mal aus. Und wer schonmal auf einem war ... der weiss ja Bescheid.
Ich selbst werde, wie schon gesagt, dieses Mal nicht dabei sein - dafür findet Ihr mich in April, Mai und Juni auf folgenden DOAG Veranstaltungen.
  • DOAG BI 2015 in München:
    Hier bin ich mit zwei Vorträgen vertreten. Einmal zum Thema D3.js im Unternehmenseinsatz (auf den freue ich mich besonders) und einmal zum Thema Geodaten mit SQL - und ohne Karte.
  • DOAG Oracle Spatial & Geodata Day:
    Hier trifft sich die Oracle Geodaten-Community - zum ersten Mal nach etwas längerer Zeit. An diesem Tag geht es um Geodaten, die Oracle Datenbank und was man damit machen kann. Themen wie Routing mit der Datenbank, Adressvalidierung mit einem Geocoder und OpenStreetMap-Daten mit Oracle machen doch neugierig, oder?
  • DOAG APEX Connect 2015:
    Und natürlich bin ich beim Highlight des Jahres für alle APEX-Entwickler und solche, die es werden wollen, mit dabei. Die APEX Connect ist die erste reine APEX-Konferenz im deutschsprachigen Raum: Zwei Tage lang nur APEX und sonst nix.

17. März 2015

String-Operationen auf CLOBs - richtig schnell machen!

Making string operations on a CLOB really fast
Dieses Blog Posting behandelt das Thema CLOB, Stringoperationen und PL/SQL - es geht um verschiedene Varianten, wie man mit einer PL/SQL Prozedur, per Zeichenverkettung, einen CLOB zusammenbauen kann. Das kommt in der Praxis ja gar nicht so selten vor. Interessant ist, dass die Performance je nach gewähltem Ansatz wirklich völlig verschieden sein kann. In meinem Beispiel braucht - bei gleichen Datenmengen - die langsamste Methode über 4 Minuten, wogegen die schnellste in 0.2 Sekunden fertig ist.
Zunächst zur Aufgabe: Die etwa 920.000 Zeilen der Demotabelle SALES im Schema SH ...
SQL> select * from sh.sales 

PROD_ID CUST_ID TIME_ID             CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
------- ------- ------------------- ---------- -------- ------------- -----------
     13     987 10.01.1998 00:00:00          3      999             1        1232
     13    1660 10.01.1998 00:00:00          3      999             1        1232
     13    1762 10.01.1998 00:00:00          3      999             1        1232
     13    1843 10.01.1998 00:00:00          3      999             1        1232
     13    1948 10.01.1998 00:00:00          3      999             1        1232
     13    2273 10.01.1998 00:00:00          3      999             1        1232
      :       :                   :          :        :             :           : 
... sollen, semikolon-separiert, in einen CLOB geladen werden.
13;987;10.01.1998 00:00:00;3;999;1;1232,16
13;1660;10.01.1998 00:00:00;3;999;1;1232,16
13;1762;10.01.1998 00:00:00;3;999;1;1232,16
13;1843;10.01.1998 00:00:00;3;999;1;1232,16
13;1948;10.01.1998 00:00:00;3;999;1;1232,16
:
Damit die Tests nicht so lange dauern, beschränke ich mich bei meinen Tests auf die ersten 10.000 Zeilen. Der erste Versuch ist der naivste, ganz einfach "herunterprogrammiert": Ein impliziter Cursor wird geöffnet und in der Cursor-Loop wird der CLOB Schritt für Schritt zusammenkonkateniert. Man kann das mit einen CLOB tatsächlich genauso machen wie mit einem VARCHAR2.
declare
  l_clob clob := '';
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
   l_clob := l_clob || to_char(i.PROD_ID) || ';' || 
                       to_char(i.CUST_ID) || ';' ||  
                       to_char(i.TIME_ID) || ';' ||  
                       to_char(i.CHANNEL_ID) || ';' || 
                       to_char(i.PROMO_ID) || ';' || 
                       to_char(i.QUANTITY_SOLD) || ';' || 
                       to_char(i.AMOUNT_SOLD)|| chr(10);
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
Da wir schon im Vorfeld wissen, dass das Ergebnis größer als 32.767 Byte sein wird, ist die Variable l_clob nicht vom Typ VARCHAR2, sondern CLOB. Trotzdem kann man in PL/SQL, ganz normal, mit dem || zur Zeichenverkettung arbeiten. Allerdings läuft diese Prozedur lange - allein für 10.000 Zeilen braucht sie etwa 4 Minuten. Über die Laufzeit für alle 920.000 Zeilen möchte ich gar nicht nachdenken.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:04:23.60
Anhand der Data Dictionary-View V$TEMPORARY_LOBS kann man feststellen, dass, während die Prozedur läuft, temporäre LOBs entstehen. Ein temporärer LOB ist ein LOB-Objekt, was nicht in einer Tabelle liegt, sondern nur transient existiert - beispielsweise in einer PL/SQL-Variable. Ein LOB ist immer persistent - in einer Tabelle - oder temporär.
SQL> select * from v$temporary_lobs

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
        20          0            0             0
        38          0            0             0
       272          6            0             0
Tatsächlich läuft obiger Code nicht optimal. Vor allem das Anhängen der einzelnen Tabellenspalten an den CLOB sorgt dafür, dass sehr viele Konvertierungen von VARCHAR2 nach CLOB stattfinden, und dass sehr viele LOB-Operationen erfolgen. Generell ist das Anhängen eines Strings an einen CLOB wesentlich teurer als an einen VARCHAR2 - hier der Beweis: Zunächst 30.000 Zeichenverkettungen auf einem CLOB ...
declare
  v_result clob;
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.56
... dann 30.000 Zeichenverkettungen mit einem VARCHAR2 ...
declare
  v_result varchar2(32000);
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.01
Das scheint zumindest etwa der Faktor 60 zu sein; wenn nicht sogar noch mehr. Der nächste Schritt wäre also, dafür zu sorgen, dass unser Code weniger LOB-Verkettungen und mehr VARCHAR2-Verkettungen macht. Das ist auch ganz einfach: Eine Tabellenzeile wird zuerst in eine Variable vom Typ VARCHAR2 geladen und erst dann an den CLOB angehängt ...
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    l_clob := l_clob || l_str;
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
Das Ergebnis kann sich sehen lassen ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:01.61
Damit sind wir von 4 Minuten auf 2 Sekunden herunter - das ist stark. Die ganze Datenmenge von 920.000 Zeilen wird dann also etwa 3 Minuten brauchen. Man kann sogar schon nachdenken, hier aufzuhören ... aber wir sind noch nicht fertig.
Wieder schauen wir in die Dictionary View V$TEMPORARY_LOBS, und diesmal zeigt sie nur noch zwei temporäre LOBs an (vorher waren es sechs). Wenn man aber genauer nachdenkt, so müsste einer eigentlich ausreichen. Es braucht einen einzigen temporären LOB (für das Ergebnis); an den wird alles angehängt. Der PL/SQL Code braucht aber zwei. Nun, im PL/SQL-Code haben wir die Syntax für VARCHAR2 verwendet und es der Engine überlassen, das korrekt auf den CLOB anzuwenden. Machen wir die Arbeit doch mal selbst: Alle Zeichenoperationen, die auf dem CLOB stattfinden, sind nun mit dem Paket DBMS_LOB realisiert. Außerdem legen wir unseren temporären LOB (ebenfalls mit DBMS_LOB) selbst an.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Wieder gibt es eine Verbesserung. Der Mehraufwand für die "Übersetzung" der Stringoperationen auf das manchmal umständliche DBMS_LOB hat sich gelohnt: Nochmals Faktor 4.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.35
Kann man noch mehr rausholen? Ja, es geht noch was: Wir haben festgestellt, dass eine Zeichenverkettung für einen LOB wesentlich teurer ist als für ein VARCHAR2. Derzeit bauen wir eine Tabellenzeile als VARCHAR2 zusammen und hängen sie an den CLOB an. Wir haben also soviele LOB-Operationen, wie es Tabellenzeilen gibt. Nun könnte man aber doch mehrere Tabellenzeilen zu einem VARCHAR2 zusammenfassen und die LOB-Operation erst dann machen, wenn der VARCHAR2 "Puffer" mit 32.000 Bytes "voll" ist. In Code sieht das so aus:
declare
  l_clob  clob := '';
  l_str   varchar2(32000) := '';
  l_block varchar2(32000) := '';
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    if length(l_block) + length(l_str) > 32000 then
      dbms_lob.writeappend(l_clob, length(l_block), l_block);
      l_block := '';
    end if;
    l_block := l_block || l_str;
  end loop;
  dbms_lob.writeappend(l_clob, length(l_block), l_block);
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Das war Mehraufwand ... aber es hat sich tatsächlich nochmal gelohnt.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.13
Und damit wäre ich am Optimierungsende angekommen. Es ist schon erstaunlich, was man mit geschicktem Coding herausholen kann. Bei dieser Prozedur traue ich mich nun, die ROWNUM-Beschränkung herauszuholen und den CLOB für die ganze Tabelle generieren zu lassen - der 39MB große CLOB ist in 6 Sekunden erzeugt. Es zeigt sich deutlich, dass, beim Arbeiten mit LOBs, am PL/SQL Paket DBMS_LOB kein Weg vorbeiführt.
Viel Spaß beim Ausprobieren ...
This blog posting is about the CLOB datatype, string operations and PL/SQL. I will elaborate about four different methods to "build" a CLOB from table data with PL/SQL. For 10000 rows, the execution time will vary from 4 Minutes to 0.2 seconds. If you want to know, why the DBMS_LOB package is so important and how to make string operations on CLOB variables really fast, read on.
The exercise: We want to build a CLOB, containing semicolon-separated data, from the well-known demo table SALES within the schema SH. This table contains about 920000 rows.
SQL> select * from sh.sales 

PROD_ID CUST_ID TIME_ID             CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
------- ------- ------------------- ---------- -------- ------------- -----------
     13     987 10.01.1998 00:00:00          3      999             1        1232
     13    1660 10.01.1998 00:00:00          3      999             1        1232
     13    1762 10.01.1998 00:00:00          3      999             1        1232
     13    1843 10.01.1998 00:00:00          3      999             1        1232
     13    1948 10.01.1998 00:00:00          3      999             1        1232
     13    2273 10.01.1998 00:00:00          3      999             1        1232
      :       :                   :          :        :             :           : 
Our result will look like this:
13;987;10.01.1998 00:00:00;3;999;1;1232,16
13;1660;10.01.1998 00:00:00;3;999;1;1232,16
13;1762;10.01.1998 00:00:00;3;999;1;1232,16
13;1843;10.01.1998 00:00:00;3;999;1;1232,16
13;1948;10.01.1998 00:00:00;3;999;1;1232,16
:
To save some time, I will start with focusing on the first 10000 rows. We'll run our procedure for the full dataset, when we have found the most efficient approach. Our first approach: We'll loop using an implicit cursor and use PL/SQL string concatenation operators in order to build the CLOB (as we would do for VARCHAR2). Pretty simple.
declare
  l_clob clob := '';
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
   l_clob := l_clob || to_char(i.PROD_ID) || ';' || 
                       to_char(i.CUST_ID) || ';' ||  
                       to_char(i.TIME_ID) || ';' ||  
                       to_char(i.CHANNEL_ID) || ';' || 
                       to_char(i.PROMO_ID) || ';' || 
                       to_char(i.QUANTITY_SOLD) || ';' || 
                       to_char(i.AMOUNT_SOLD)|| chr(10);
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
We already know that our result will be larger than 32767 bytes. So we cannot use a VARCHAR2, we must use CLOB as datatype for the variable l_clob. But we can use "normal" PL/SQL string concatenation syntax, though. For 10000 rows, this procedure takes about 4 Minutes - which is long. I don't even want to think about the time needed for the whole table ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:04:23.60
While the procedure runs, it's worthful to have a look into the V$TEMPORARY_LOBS data dictionary view. You'll see that your database session creates some temporary lobs.
SQL> select * from v$temporary_lobs

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
        20          0            0             0
        38          0            0             0
       272          6            0             0
Let's get this straight: This code is far away from being optimal. All these string concatenation operations on the CLOB variable lead to (too) many temporary lobs being created. And string concatenations on CLOBs are, in general, far more expensive then on VARCHAR2s. Try it out: Here are 30000 string concatenations on a CLOB ...
declare
  v_result clob;
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
... and here is the same on a VARCHAR2:
declare
  v_result varchar2(32000);
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
That is at least factor 50 to 60. So, our next step must be to eliminate string concatenations on the CLOB. This is more easy than you might think: We can build a VARCHAR2 for a complete table row and finally append this (full row) to the CLOB.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    l_clob := l_clob || l_str;
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
The result is nothing less than impressive ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61
We are down from 4 Minutes to 2 seconds; just with a very tiny code adjustment. For the complete dataset of 920000 rows, we now have to expect an execution time of about 3 minutes. But we are not finished here - the story continues ...
Again, we have a look into V$TEMPORARY_LOBS, while the procedure is running. It shows us that two temporary LOBs are in use. But we really need only one temporary LOB and nothing more. Why? Because our result (which is a CLOB) must be hold as temporary LOB; all other temporary content can be handled as VARCHAR2. Since we used plain PL/SQL string operations, we left it up to the engine, how these operations are to be exeucuted on the CLOB. Thus, in our third approach, we'll do the job ourselves: All string operations are now done with the procedures and functions of the DBMS_LOB package. And ... we'll also create the temporary LOB explicitly and ourselves.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Using this obscure DBMS_LOB package seems to pay off: Performance improvement by factor 4.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
That's all ...? No, we can get even better: We have learned, that a string operation on a CLOB is far more expensive than on a VARCHAR2. Now, we build each table row as VARCHAR2 and append it to the CLOB. So we have as many CLOB concatenations as there are table rows. But we could also aggregate multiple table rows into the VARCHAR2, up to its limit of 32000 bytes. When the VARCHAR buffer is full, we append it to the CLOB and start again. Translated to code, this looks as follows ...
declare
  l_clob  clob := '';
  l_str   varchar2(32000) := '';
  l_block varchar2(32000) := '';
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    if length(l_block) + length(l_str) > 32000 then
      dbms_lob.writeappend(l_clob, length(l_block), l_block);
      l_block := '';
    end if;
    l_block := l_block || l_str;
  end loop;
  dbms_lob.writeappend(l_clob, length(l_block), l_block);
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
It's faster - again.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
And this is the end of the optimization. These differences are stunning: We started with 4 minutes and came down to 0.2 seconds; which is a factor of 1200. Now, I'm totally relaxed when running the code for whole table of 920000 rows - the CLOB of 39MB is being build in just 6 seconds. As so often, the first and "most easy" approach isn't the best: When it's about working with CLOBs, there is now way around the (kind of obscure) DBMS_LOB package. Getting familiar with it, is (as we see) absolutely worth the effort ...
As always - habe a lot of fun while playing with this ...

Beliebte Postings