11. Juni 2016

Warum have ich soviele "temporary LOBs"? Wissenswertes.

Ja, auf diesem Blog gibt es neue Postings. Nachdem es hier in den letzten drei Monaten etwas ruhiger war, kommt heute wieder ein neues Posting - auch künftig geht es hier regelmäßig mit Themen rund um SQL, PL/SQL und die Oracle-Datenbank weiter.

Heute geht es um temporäre LOBs (Large Objects). Größere Texte werden bekanntlich als CLOB, größere Binärobjekte als BLOB abgespeichert. Da ein solches Objekt sehr groß werden kann, repräsentiert ein CLOB oder BLOB die Daten nicht direkt, es ist vielmehr ein Pointer auf dieselben. Wenn man einen LOB in eine Tabelle speichert, so hängt die tatsächliche Speicherung von der Größe und der STORAGE IN ROW Eigenschaft der Tabelle ab.

  • Ist der LOB größer als 4000 Byte, so wird er immer separat im LOB-Segment gespeichert, die Tabellenspalte selbst erhält nur den Pointer
  • Unter 4000 Byte hängt es von der Eigenschaft STORAGE IN ROW ab: Wurde ENABLE STORAGE IN ROW angegeben (was der Default ist), so wird der LOB (wie ein VARCHAR2 oder RAW) in der Tabelle selbst abgelegt. Wird dagegen DISABLE STORAGE IN ROW angegeben, so werden auch kleine LOBs in die Tabelle abgelegt.

So weit - so gut. Das Thema heute lautet aber temporäre LOBs. Das sind solche, die gerade nicht in einer Tabelle abgespeichert werden, sondern nur transient in PL/SQL Programmen verwendet werden. Tatsächlich gespeichert werden diese temporären LOBs (da sie ja größer werden können) im temporary tablespace. Temporäre LOBs können explizit mit der PL/SQL-Prozedur DBMS_LOB.CREATETEMPORARY erzeugt werden - das geht wie folgt:

declare
  l_tclob clob;
begin
  dbms_lob.createtemporary(
    lob_loc   => l_tclob,
    cache     => true,
    dur       => dbms_lob.session
  );

  -- append data to this temp CLOB up to 4 GB
  dbms_lob.writeappend( l_tclob, ... );

  dbms_lob.freetemporary(
    lob_loc   => l_tclob
  );
end;

Der in diesem Beispiel erzeugte temporäre LOB wird in der SGA gecacht (cache => true). Würde man cache auf false setzen, so erfolgt jeder Schreib- und Lesezugriff auf den LOB direkt (physikalisch) im Datafile des temporary tablespace - dazu hatte ich letztes Jahr bereits ein Blog-Posting (String-Operationen auf CLOBs - richtig schnell machen). Ein temporärer LOB wird in PL/SQL gemeinsam mit seiner Variablen zerstört: So existiert ein temporärer LOB in einer Package-Variablen während der ganzen Session, in einer lokalen Variable wird er nach Ablauf des Blocks, der Funktion oder Procedure zerstört. Der Parameter dur von createtemporary ist lediglich ein Hint, wann der temporäre LOB zerstört und der Platz freigegeben werden soll - das ist besonders interessant, wenn temporäre LOBs nicht aus PL/SQL heraus (bspw. aus Java innerhalb oder außerhalb der Datenbank) erzeugt werden. Mit DBMS_LOB.FREETEMPORARY kann man einen temporären LOB auch manuell zerstören.

Mit der Dictionary View V$TEMPORARY_LOBS kann man prüfen, ob in einer Datenbanksession gerade temporäre LOBs existieren. Die folgende Query zeigt: Nix da.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

Temporäre LOBs werden aber nicht nur explizit generiert, sie entstehen auch oft implizit - das folgende Beispiel ist hier ganz interessant: In PL/SQL Prozeduren kann ein CLOB genauso wie ein VARCHAR2 verwendet werden - und de-jure funktioniert es bis 32 Kilobyte auch genauso. Prüfen wir das mal nach: Ein PL/SQL Block fügt 32.767 mal ein Leerzeichen an einen CLOB an - eigentlich dürfte kein temporärer LOB entstehen ...

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;
begin
    for i in 1..32767 loop
        l_clob := l_clob || ' ';
    end loop;

    select cache_lobs, nocache_lobs 
      into l_ctemplobs, l_nctemplobs
      from v$temporary_lobs;
 
    dbms_output.put_line('Cached temp lobs:     '|| l_ctemplobs);
    dbms_output.put_line('Non-Cached temp lobs: '|| l_nctemplobs);
end;

Als Ergebnis kommt heraus:

Cached temp lobs:     0
Non-Cached temp lobs: 0

Wie erwartet. Gehen wir auf 40.000 hoch ...

Cached temp lobs:     0
Non-Cached temp lobs: 0

Interessant - immer noch kein temporärer LOB. Man kann das jetzt noch weiter treiben; bis 131.072 ( 4 mal 32.768 ) Zeichen entsteht kein temporärer LOB und PL/SQL arbeitet allein im Hauptspeicher. Sobald der CLOB aber größer als 131.072 Zeichen wird, wird sich die Ausgabe ändern - und nicht nur das: ab hier wird die Performance auch spürbar schlechter - ein temporärer LOB ist eben doch etwas anderes als ein VARCHAR2. Das zeigt der folgende Code ...

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;

    l_time       pls_integer;
begin
    for i in 1..10 loop
        l_time := dbms_utility.get_time;
        for j in 1..32767 loop
            l_clob := l_clob || ' ';
        end loop;

        dbms_output.put_line( i || '. 32k chunk: ' || ( dbms_utility.get_time - l_time ) || ' hs');
      
        select cache_lobs, nocache_lobs into l_ctemplobs, l_nctemplobs
        from v$temporary_lobs;
       
        dbms_output.put_line('  .. Cached temp lobs:     '|| l_ctemplobs);
    end loop;   
end;

1. 32k chunk:  10 hs
.. Cached temp lobs:     0
2. 32k chunk:  11 hs
.. Cached temp lobs:     0
3. 32k chunk:  10 hs
.. Cached temp lobs:     0
4. 32k chunk:  10 hs
.. Cached temp lobs:     0
5. 32k chunk:  83 hs
.. Cached temp lobs:     1
6. 32k chunk: 133 hs
.. Cached temp lobs:     1
7. 32k chunk: 176 hs
.. Cached temp lobs:     1
8. 32k chunk: 187 hs
.. Cached temp lobs:     1
9. 32k chunk: 191 hs

Wenn der PL/SQL-Block abgelaufen ist, werden die temporären LOBs zerstört; das zeigt die Kontrolle in V$TEMPORARY_LOBS. Da die PL/SQL-Variablen nach Ablauf des PL/SQL Blocks zerstört werden, werden auch die temporären LOBs zerstört. Gerade das - und die duration verdienen nun eine eigene Betrachtung: Bauen wir eine PL/SQL-Funktion, die einen temporären LOB zurückgibt - diese rufen wir dann auf und prüfen dann V$TEMPORARY_LOBS.

create or replace function get_templob return clob
is
    l_clob clob;
begin
    dbms_lob.createtemporary( l_clob, true, dbms_lob.call );
    return l_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
begin
         
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
end;
/

Das Ergebnis ...

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

Interessant, nicht wahr? Obwohl der temporäre LOB in einer lokalen Variable der Funktion GET_TEMPLOB gehalten wird, hat sich die Anzahl der temporären LOBs mit jedem Aufruf erhöht. Nach Abschluß des anonymen Blocks werden jedoch alle zerstört.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

Der Grund für das Ansteigen während der Laufzeit des PL/SQL Blocks ist, dass der temporäre CLOB im anonymen Block nicht einer Variable zugewiesen, sondern unmittelbar mit DBMS_LOB.GETLENGTH weiterverarbeitet wird. Dazu muss der PL/SQL-Compiler intern eine Art "anonyme Variable" erzeugen, den temporären LOB dorthin übernehmen und an DBMS_LOB.GETLENGTH weitergeben. Das Verhalten ist ein anderes, wenn wir den temporären LOB explizit in eine eigene Variable vom Typ CLOB übernehmen.

declare
    l_cloblength number := 0;
    l_clob       clob;
begin
         
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;

end;
/

Nun kommt ein anderes Ergebnis - jeder Aufruf überschreibt die Variable l_clob, so dass der damit verbundene temporäre LOB ebenfalls überschrieben (und der "alte" damit zerstört) wird.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

Packt man den Aufruf in eine SELECT-Abfrage, so ändert sich das Bild nochmals - denn in diesem Fall findet jedes mal ein neuer Top-Level-Call statt.

declare
    l_cloblength number := 0;
begin
         
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;

end;
/

Nach Abschluß werden alle lokalen PL/SQL Variablen zerstört, so dass kein temporärer LOB mehr übrig bleibt.

Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0

Um einen temporären LOB für die Dauer der Session "überleben" zu lassen, braucht es zunächst eine Variable, die während der ganzen Session gültig ist - wie eine Package Variable. Aber eine Warnung bereits vorab: Das ist typischerweise der Weg zu Memory oder "temporary lob" leaks.

create or replace package clobholder is
  g_clob clob;
end clobholder;
/

create or replace function get_templob return clob
is
begin
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
    l_clob       clob;
    
begin

    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;

end;
/

Nun arbeitet der Code mit einer CLOB-Variablen in einem Package - diese ist während der ganzen Session gültig. Probiert es aus!

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

Obgleich mit einer SQL-Query ein neuer Top-Level Call getätigt wurde, bleiben die temporären LOBs erhalten. Im Gegensatz zu vorhin sind sie nun auch nach Abschluß des PL/SQL-Blocks noch da.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          4            0             0          3

Und wenn man den anonymen Block nun immer wieder ausführt, erhöht sich die Anzahl der temporären LOBs stetig - wir haben ein temporary LOB leak programmiert.

Cached temp lobs: 5
Cached temp lobs: 6
Cached temp lobs: 7
Cached temp lobs: 8

Doch warum ...? Schließlich arbeiten wir doch immer auf der gleichen globalen Package-Variable; und dennoch erhöhen sich die temporären LOBs ständig. Der Grund ist, dass wir auf der gleichen Variablen (clobholder.g_clob) immer wieder dbms_lob.createtemporary aufrufen. Doch auch temporäre LOBs auf Package-Variablen können automatisch zerstört werden, dazu braucht es aber eine Zuweisung zu dieser Variablen mit :=. Wir ändern den Code der Funktion GET_TEMPLOB ganz leicht um ...

create or replace function get_templob return clob
is
begin
    clobholder.g_clob := null;
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

Die explizite Zuweisung vor dem erneuten createtemporary sorgt dafür, dass ein etwaiger bereits existierender temporärer LOB zerstört und der Speicherplatz freigegeben wird. Das Problem ist behoben.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

Nach Abschluß des PL/SQL-Blocks bleibt genau der eine temporäre LOB übrig.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          1            0             0          3

Allgemein kann man sagen, dass temporäre LOBs, besonders wenn sie in globalen Package Variablen gehalten werden, immer mit DBMS_LOB.FREETEMPORARY explizit freigegeben werden sollten. Wer eine Package-Funktion schreibt, die einen solchen temporären LOB erzeugt und zurückgibt, sollte auch eine Prozedur schreiben, die das entsprechende FREETEMPORARY ausführt. Der folgende Code geht auf Nummer Sicher.

declare
    l_cloblength number := 0;
    l_clob clob;
begin

    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;

end;
/

Man sieht, temporäre LOBs können sich ganz interesant verhalten. PL/SQL nimmt uns einige Arbeit ab und zerstört die temporären LOBs, sobald die LOB-Variablen zerstört werden oder explizit ein anderer Wert zugewiesen wird. Gerade mit temporären LOBs, die in Package Variablen gehalten werden, muss man aber aufpassen - hier können immer noch Leaks entstehen, so dass eine Datenbanksession ständig neue temporary LOBs erzeugt. Solche LOBs sollten immer nach Gebrauch mit DBMS_LOB.FREETEMPORARY explizit freigegeben werden.

YES, I'll continue to publish postings on this blog. Since my last posting in March it got a bit quiet here, but here I am again: a new posting about SQL, PL/SQL and the Oracle database.

Today it's about temporary LOBs (LOB = Large Objects). Larger texts or binary values are best stored as CLOB or BLOB. Since such an object can grow pretty large, it does not contain the data directly - instead it's a pointer. When such a LOB is stored in a table, actual data storage depends on LOB size and the table columns storage attributes.

  • LOBs larger than 4.000 byte are always stored in the separate LOB segment; the table column itself only contains the pointer.
  • For LOBs smaller than 4.000 bytes, it depends on the STORAGE IN ROW clause of the table column. ENABLE STORAGE IN ROW stores these LOBs directly in the table row - like a VARCHAR2 column. DISABLE STORAGE IN ROW lets the database store these LOBs in the LOB segment like their larger pendants.

So far - so good. But the topic today is temporary LOBs. These are LOBs which are used in variables within PL/SQL or Java programs and not being stored in a table (the variables and their scope will become important). Temporary LOB data is being stored in the Temporary Tablespace. A temporary LOB is created using DBMS_LOB.CREATETEMPORARY and destroyed with DBMS_LOB.FREETEMPORARY:

declare
  l_tclob clob;
begin
  dbms_lob.createtemporary(
    lob_loc   => l_tclob,
    cache     => true,
    dur       => dbms_lob.session
  );

  -- append data to this temp CLOB up to 4 GB
  dbms_lob.writeappend( l_tclob, ... );

  dbms_lob.freetemporary(
    lob_loc   => l_tclob
  );
end;

The temporary LOB in the above example will be cached in the buffer cache (cache => true). Setting this cache to false would lead to physical I/O access for every read or write operation on that LOB (last year, I published a posting about that: String-Operationen auf CLOBs - richtig schnell machen).

In PL/SQL, a temporary LOB is bound to its variable. When the variable is being destroyed or a new value is being assigned to it, the temporary LOB is being destroyed. So a temporary LOB being hold by a local variable in a function will die after the function as ended. It its being hold by a global package variable, it will be alive until the session ends. The dur parameter in createtemporary is only a hint and of particular interest when the temporary LOB is being created outside of PL/SQL (e.g. Java in the Database).

The dictionary view V$TEMPORARY_LOBS shows how many temporary LOBs have been allocated in that session.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

Not all temporary LOBs are being created explicitly. Sometimes you'll get implicit temporary LOBs; the following example shows this. Within PL/SQL, you cam work with a CLOB just like with VARCHAR2; and de-jure it works the same up to 32.767 bytes. So let's check this: A PL/SQL block adds 32.767 space characters to a CLOB - so we should see no temporary LOB here.

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;
begin
    for i in 1..32767 loop
        l_clob := l_clob || ' ';
    end loop;

    select cache_lobs, nocache_lobs 
      into l_ctemplobs, l_nctemplobs
      from v$temporary_lobs;
 
    dbms_output.put_line('Cached temp lobs:     '|| l_ctemplobs);
    dbms_output.put_line('Non-Cached temp lobs: '|| l_nctemplobs);
end;

The result ...

Cached temp lobs:     0
Non-Cached temp lobs: 0

... as expected. Let's move up to 40.000 ...

Cached temp lobs:     0
Non-Cached temp lobs: 0

... still no temporary LOB. Further tests show that you'll get a temporary LOB when going beyond 131.072 space characters. Smaller CLOBs are completely handled in memory - just like VARCHAR2 variables. From 131.072 and higher, PL/SQL will work with a temporary LOB segment. You'll also notice that when looking the the time consumed ... to see that effect, we need to change the code a bit.

declare
    l_clob       clob;
    l_ctemplobs  number;
    l_nctemplobs number;

    l_time       pls_integer;
begin
    for i in 1..10 loop
        l_time := dbms_utility.get_time;
        for j in 1..32767 loop
            l_clob := l_clob || ' ';
        end loop;

        dbms_output.put_line( i || '. 32k chunk: ' || ( dbms_utility.get_time - l_time ) || ' hs');
      
        select cache_lobs, nocache_lobs into l_ctemplobs, l_nctemplobs
        from v$temporary_lobs;
       
        dbms_output.put_line('  .. Cached temp lobs:     '|| l_ctemplobs);
    end loop;   
end;

1. 32k chunk:  10 hs
.. Cached temp lobs:     0
2. 32k chunk:  11 hs
.. Cached temp lobs:     0
3. 32k chunk:  10 hs
.. Cached temp lobs:     0
4. 32k chunk:  10 hs
.. Cached temp lobs:     0
5. 32k chunk:  83 hs
.. Cached temp lobs:     1
6. 32k chunk: 133 hs
.. Cached temp lobs:     1
7. 32k chunk: 176 hs
.. Cached temp lobs:     1
8. 32k chunk: 187 hs
.. Cached temp lobs:     1
9. 32k chunk: 191 hs

After the PL/SQL block has finished, the temporary LOB is being destroyed; that can be checked in the V$TEMPORARY_LOBS dictionary view. So let's have a deeper look at the lifetime of a temporary LOB. For that we build a PL/SQL function returning a temporary LOB - then we'll call that function. We'll monitor the amount of existing temporary LOBs in V$TEMPORARY_LOBS.

create or replace function get_templob return clob
is
    l_clob clob;
begin
    dbms_lob.createtemporary( l_clob, true, dbms_lob.call );
    return l_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
begin
         
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
    l_cloblength := dbms_lob.getlength( get_templob );
    print_templobs;
end;
/

Results ...

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

Interesting, isn't it ...? Although the temporary LOB is bound to a local variable within the GET_TEMPLOB function, we'll get a new one with each call. After the block has finished, however, all temporary LOBs are being destroyed.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          0            0             0          3

The temporary LOB returned by GET_TENPLOB is not being assigned to a variable, instead the code directly passes it to DBNS_LOB.GETLENGTH. The PL/SQL compiler builds - kind of - an "anonymous variable" for it; assigns the returned temporary LOB to it and passes it then to DBMS_LOB.GETLENGTH. So we have four "anonymous variables" containing four temporary LOBs which are alive until the block has finished. Behaviour changes when we explicitly assign the result of GET_TEMPLOB to a CLOB variable - as follows:

declare
    l_cloblength number := 0;
    l_clob       clob;
begin
         
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;
    l_clob := get_templob; l_cloblength := dbms_lob.getlength( l_clob );
    print_templobs;

end;
/

Each assignment to the l_clob variable overwrites the existing value, which leads to the temporary LOB being destroyed.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

We can also embed our call into a SQL query. Then we will have a new top level call.

declare
    l_cloblength number := 0;
begin
         
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;
    select dbms_lob.getlength( get_templob ) into l_cloblength from dual;
    print_templobs;

end;
/

After the Query has finished, all local variables are being freed and all temporary LOBs destroyed.

Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0
Cached temp lobs: 0

To have a temporary LOB survive until the end of the session, we need to have it bound to a variable which lives for the length of the session - like a package variable. But note the warning: Ba careful with this, otherwise you'll get "temporary LOB" leaks and massive temporary tablespace consumption.

create or replace package clobholder is
  g_clob clob;
end clobholder;
/

create or replace function get_templob return clob
is
begin
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

create or replace procedure print_templobs is
    l_templobs   number;
begin
    select cache_lobs
      into l_templobs
      from v$temporary_lobs;
   
    dbms_output.put_line( 'Cached temp lobs: ' || l_templobs );
end;
/

declare
    l_cloblength number := 0;
    l_clob       clob;
    
begin

    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;
    select get_templob  into l_clob from dual;
    print_templobs;

end;
/

Now our code works with a global CLOB variable in a package. Try it out.

Cached temp lobs: 1
Cached temp lobs: 2
Cached temp lobs: 3
Cached temp lobs: 4

We still perform a SQL query (and therefore a top-level-call) in our anonymous block. But the temporary LOBs survive. And unlike in the previous example, they are still present after the block has finiahed.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          4            0             0          3

Running the block again leads to even more temporary LOBs in our session - we have created a temporary LOB leak.

Cached temp lobs: 5
Cached temp lobs: 6
Cached temp lobs: 7
Cached temp lobs: 8

But why - we only work with one global package variable. The reason is that, with each call to GET_TEMPLOB are are executing CREATETEMPORARY on that variable. But we don't do an explicit assignment, so the already existing temporary LOB is not being destroyed. The solution to that problem is pretty easy: to have the temporary LOB of a variable being freed, assign something to that variable with :=- as follows:

create or replace function get_templob return clob
is
begin
    clobholder.g_clob := null;
    dbms_lob.createtemporary( clobholder.g_clob, true, dbms_lob.session );
    return clobholder.g_clob;
end get_templob;
/

With the assignment of NULL to the clobholder.g_clob variable, an existing temporary LOB is being destroyed and memory is being freed.

Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1
Cached temp lobs: 1

After the block has finished, exactly one temporary LOB remains.

SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS     CON_ID
---------- ---------- ------------ ------------- ----------
       264          1            0             0          3

Take special care for temporary LOBs in global package variables - it's generally best practice to always free them explicitly with DBMS_LOB.FREETEMPORARY. If you are authoring a function which creates and returns a temporary LOB based on a global package variable, make sure to create a to free it up as well. The following code plays it safe.

declare
    l_cloblength number := 0;
    l_clob clob;
begin

    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;
    select get_templob  into l_clob from dual;
    dbms_lob.freetemporary( clobholder.g_clob );
    print_templobs;

end;
/

You see, temporary LOBs can be very interesting. PL/SQL does a lot of work for us and frees up temporary LOBs automatically when their variable lifetime ends or new values are being assiged. Developers should take particular care for temporary LOBs when they are hold by global package variables. It's always best practice to destroy temporary LOBs explicitly using DBMS_LOB.FREETEMPORARY. werden.

15. März 2016

APEX Connect 2016: Early Bird endet heute am 15. März

In diesem Blog Posting möchte ich nochmals auf die APEX Connect hinweisen: Am 15. März endet der Early Bird Tarif für die APEX-Konferenz im deutschsprachigen Raum. Drei Tage lang (vom 26. bis 28. April) stehen APEX und PL/SQL im Mittelpunkt. Nach dem großen Erfolg im letzten Jahr ist die Agenda wiederum hochkarätig und mit Top-Sprechern aus der deutschen und internationalen APEX-Community besetzt. So kann man sich jetzt schon auf die Keynote von Mike Hichwa, dem "Vater" und APEX-Entwicker der ersten Stunde. Am besten also noch heute anmelden.

Ich hoffe, wir sehen uns dort.

1. März 2016

Veränderung ...

Dieses Blog-Posting behandelt mal kein technisches SQL oder PL/SQL Thema, sondern ist eines in eigener Sache.

Das Beste zuerst: Ich bin extrem glücklich, ab dem 1. März 2016 (also ab heute) Teil des Oracle Application Express Entwicklerteams zu sein.

Das ganze fällt mit meinem "15. Oracle-Geburtstag" zusammen. Am 1. März 2001 habe ich im Presales bei Oracle Deutschland angefangen und arbeite nun also 15 Jahre mit der deutschsprachigen Entwicklercommunity zu Themen wie APEX, aber auch XML DB, JSON, Spatial, SQL-Funktionen, PL/SQL und andere Themen - rund um die Datenbank - zusammen. Dieses breite Themenspektrum und die Zusammenarbeit mit den Kunden, Partnern und der Community in Deutschland war sehr bereichernd und hat viel Spaß gemacht.

Fast ebenso lange arbeite ich schon mit dem APEX-Entwicklerteam zusammen - für das Release 4.2 hatte ich die Gelegenheit, den Sample Geolocation Showcase zu erstellen, welcher ja Teil der Packaged Applications ist. Nun werde ich noch tiefer in Application Express einsteigen und (hoffentlich) die eine oder andere Idee und Vorstellung für euch umsetzen können - das wird auf jeden Fall herausfordernd, spannend und ich freue mich sehr drauf. Von der Bildfläche verschwinden werde ich nicht, auf Konferenzen wie der DOAG APEX Connect oder der DOAG Jahreskonferenz im November, aber auch auf anderen Veranstaltungen könnt Ihr mich treffen - ich freue mich auf die Diskussionen und Gespräche.

This blog posting is not about some SQL or PL/SQL topic, but about myself.

First (and best) things first: I'm happy and excited to join the Oracle Application Express Development Team as of March 1st, 2016 (as of today).

This happens at my 15th Oracle anniversary - at March 1st, 2001, I started in the presales organization of Oracle Germany. In the last 15 years, I worked on database-centric development and on topics like SQL, PL/SQL, XML, Spatial, JSON and (of course) Application Express. This broad spectrum and working together with the vibrant community in Germany was a lot of fun.

I'm working with APEX and the APEX team since the first public release, HTML DB 1.5. For APEX 4.2 I had the opportunity to add the Sample Geolocation Showcase to the collection of packaged applications in APEX and now my focus will be on further developing and improving Application Express. Having the chance to bring some of my ideas directly into the APEX product is very exciting and cool.

I won't be totally off the grid, I still plan to attend conferences like DOAG APEX Connect, the annual DOAG conference or others. I'm looking forward to meeting you there and to talk and discuss with you.

19. Februar 2016

"gzip" und "gunzip" - in der Datenbank: UTL_COMPRESS

Heute möchte ich auf ein PL/SQL Paket hinweisen, was es schon sehr, sehr lange in der Datenbank gibt, was aber ein wenig in Vergessenheit geraten ist: UTL_COMPRESS. Die Funktionen LZ_COMPRESS und LZ_UNCOMPRESS sind dabei die Datenbank-Äquivalente zu gzip und gunzip auf dem Betriebssystem.

Probiert das doch gleich einmal aus. Ihr könnt in eurer Datenbank sehr einfach an einen BLOB kommen, indem Ihr die XMLDB-Konfiguration abruft. Ihr bekommt ein XML-Dokument zurück. Das folgende Beispiel ruft zuerst als CLOB ab, dann als BLOB (UTL_COMPRESS arbeitet nur mit BLOBs).

select xmlserialize(document dbms_xdb.cfg_get() as clob) XML_CLOB from dual;

XML_CLOB
--------------------------------------------------------------------------------
<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>

select xmlserialize(document dbms_xdb.cfg_get() as blob) XML_BLOB from dual;

XML_BLOB
--------------------------------------------------------------------------------
3C786462636F6E66696720786D6C6E733D22687474703A2F2F786D6C6E732E6F7261636C652E636F
6D2F7864622F786462636F6E6669672E7873642220786D6C6E733A7873693D22687474703A2F2F77
77772E77332E6F72672F323030312F584D4C536368656D612D696E7374616E636522207873693A73
6368656D614C6F636174696F6E3D22687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F78
64622F786462636F6E6669672E78736420687474703A2F2F786D6C6E732E6F7261636C652E636F6D

Die Größe des BLOBs (das brauchen wir gleich noch) ...

select dbms_lob.getlength(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) original_len from dual;

ORIGINAL_LEN
------------
       13513

Jetzt komprimieren wir mal ...

select utl_compress.lz_compress(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) from dual;

1F8B0800000000000003E55BDD73DB36127FCF5FA1E974EEE16E28CA76ECF3E554CE246D33D79BB4
75E36426F7E481C095841824680094E8FCF5B70049F143944DD282CF9DF38BC5FDC0FEB0BB582E41
709E850B2AE2255B4DB288C7EA87EFD65A276F7CDF5E4D852494C3948AC847417F273CCD54F85DAE
F126536CA7B5DD6EA7DB33545BF9A7B3D989FFE5D70FD7740D11F158AC348929A096626F94257E10
946826E2014627FDE105AF2693B9BA5739C95CE135CA7B11C93CB282E0E47CEED7AF2B094A109FA7

select dbms_lob.getlength(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) zipped_len from dual;

ZIPPED_LEN
----------
      1874

Nun schreiben wir den komprimierten BLOB ins Filesystem - dazu könnt Ihr bspw. mein Package für das Filesystem und Betriebsystem-Kommandos hernehmen. Auf Unix-Ebene kann die Datei dann mit gunzip ausgepackt werden.

select file_pkg.get_file('/home/oracle/xdbconfig.xml.gz').write_to_file(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) bytes_written from dual;

BYTES_WRITTEN
-------------
         1874

Auf Unix-Ebene kann die Datei dann mit gunzip ausgepackt werden. Man sieht dann die XML-Inhalte des urspränglichen BLOBs ...

$ gunzip xdbconfig.xml.gz
$ more xdbconfig.xml
<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>
  :

Und der umgekehrte Weg funktioniert genauso. Insbesondere wenn es darum geht, BLOBs oder CLOBs über das Netzwerk zum Datenbankserver zu übertragen, kann dieses Paket eine wertvolle Hilfe sein. Wenn man LOB-Inhalte vor dem Absenden an die Datenbank mit gzip verpackt und in der Datenbank mit UTL_COMPRESS.LZ_UMCOMPRESS wieder auspackt, so lässt sich die meist kostbare Netzwerk-Bandbreite gleich viel besser ausnutzen.

Auch APEX-Entwickler sollten sich UTL_COMPRESS ansehen, sobald sie mit Datei-Uploads arbeiten. Zwar ist APEX seit der Version 5.0 in der Lage, ZIP-Archive zu verarbeiten, mit GZIP-Dateien kann APEX_ZIP aber nicht umgehen. Genau hier kann man sehr schön mit dem UTL_COMPRESS Paket arbeiten. Es ist schnell eingesetzt und kann in manchen Situationen ein echter Quick Win sein. Schaut's euch an.

This blog posting will be about a PL/SQL package which is part of the database for a very long time, but which is also forgotten by most Oracle users: UTL_COMPRESS. Its funktions LZ_COMPRESS and LZ_UNCOMPRESS are the equivalents to the OS utilities gzip and gunzip.

It's very easy to try these functions out. First, you need a BLOB to compress. If you don't have some BLOB values ready in a table, you can easily generate one by retrieving the XML configuration of your database. This is actually an XML document.

select xmlserialize(document dbms_xdb.cfg_get() as clob) XML_CLOB from dual;

XML_CLOB
--------------------------------------------------------------------------------
<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>

select xmlserialize(document dbms_xdb.cfg_get() as blob) XML_BLOB from dual;

XML_BLOB
--------------------------------------------------------------------------------
3C786462636F6E66696720786D6C6E733D22687474703A2F2F786D6C6E732E6F7261636C652E636F
6D2F7864622F786462636F6E6669672E7873642220786D6C6E733A7873693D22687474703A2F2F77
77772E77332E6F72672F323030312F584D4C536368656D612D696E7374616E636522207873693A73
6368656D614C6F636174696F6E3D22687474703A2F2F786D6C6E732E6F7261636C652E636F6D2F78
64622F786462636F6E6669672E78736420687474703A2F2F786D6C6E732E6F7261636C652E636F6D

Determine the BLOB size (we'll need this later on) ...

select dbms_lob.getlength(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) original_len from dual;

ORIGINAL_LEN
------------
       13513

OK - let's compress.

select utl_compress.lz_compress(
  xmlserialize(document dbms_xdb.cfg_get() as blob)
) from dual;

1F8B0800000000000003E55BDD73DB36127FCF5FA1E974EEE16E28CA76ECF3E554CE246D33D79BB4
75E36426F7E481C095841824680094E8FCF5B70049F143944DD282CF9DF38BC5FDC0FEB0BB582E41
709E850B2AE2255B4DB288C7EA87EFD65A276F7CDF5E4D852494C3948AC847417F273CCD54F85DAE
F126536CA7B5DD6EA7DB33545BF9A7B3D989FFE5D70FD7740D11F158AC348929A096626F94257E10
946826E2014627FDE105AF2693B9BA5739C95CE135CA7B11C93CB282E0E47CEED7AF2B094A109FA7

select dbms_lob.getlength(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) zipped_len from dual;

ZIPPED_LEN
----------
      1874

Then we'll write the compressed BLOB to the file system - you might use my Package for file system and OS commands for this, but working with UTL_FILE or similar is also OK.

select file_pkg.get_file('/home/oracle/xdbconfig.xml.gz').write_to_file(
  utl_compress.lz_compress(
    xmlserialize(document dbms_xdb.cfg_get() as blob)
  )
) bytes_written from dual;

BYTES_WRITTEN
-------------
         1874

Now you can inflate the file with the gunzip utility - and having done this, you can see the original XML contents.

$ gunzip xdbconfig.xml.gz
$ more xdbconfig.xml
<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>
  :

The other way around does, of course, also work. So you can upload a gzip-compressed LOB to your database and before storing it into your table, you run the UTL_COMPRESS.LZ_UNCOMPRESS function on it. This might be a very nice approach to better utilize the network bandwith to your database.

UTL_COMPRESS is also interesting for APEX developers - when building APEX applications, you sometimes have to build file uploads. APEX can deal with ZIP archives since the APEX_ZIP package has been introduced with APEX 5.0. But APEX_ZIP cannot handle GZIP files - to process these, UTL_COMPRESS comes to your rescue. I think, the UTL_COMPRESS package is a real quick win, using it is extemely easy and you get an immediate effect.

22. Januar 2016

APEX ohne APEX: APEX PL/SQL API für Alle!

Das erste Blog-Posting des Jahres 2016 steht unter dem Motto APEX ohne APEX. Was das bedeuten soll? Nun, mit APEX werden eine ganze Reihe PL/SQL Pakete in die Datenbank installiert - das ist logisch, da APEX in PL/SQL geschrieben ist. Die meisten der PL/SQL Pakete sind nur im Zusammenspiel mit APEX sinnvoll - einige andere aber (und genau um die geht es heute), lassen sich auch ohne APEX, aus ganz normalen PL/SQL-Prozeduren, sinnvoll nutzen - und genau um die soll es in diesem Blog Posting gehen. Ich werde nicht alle im Detail erklären; zu einigen gibt es auch schon fertige Howtos, auf die ich einfach verweisen werde.

JSON-Parsing: APEX_JSON (seit APEX 5.0)

Ab dem Release 12.1.0.2 bringt die Datenbank SQL/JSON-Funktionen mit, um JSON mit SQL parsen und verarbeiten zu können. Nutzer einer 11g-Datenbank haben diese Möglichkeit nicht. Und die Möglichkeit, JSON zu generieren oder mit PL/SQL zu verarbeiten, ist in 12.1.0.2 ebenfalls noch nicht vorhanden (die SQL/JSON-Funktionen stehen nur auf SQL-Ebene bereit).

Eine Lösung ist das Paket APEX_JSON, welches ab APEX 5.0 enthalten ist. Es kann auch außerhalb von APEX, in "gewöhnlichen PL/SQL-Prozeduren" problemlos genutzt werden. Zu diesem Thema gab es im letzten Jahr bereits zwei Blog-Postings, auf die ich hier nur verweisen möchte.

ZIP-Archive: APEX_ZIP

Das Paket APEX_ZIP erlaubt es, ZIP-Archive, die als BLOB vorliegen, auszupacken oder neue zu erstellen. Das folgende Beispiel listet alle Dateien, die in einem ZIP-Archiv vorhanden sind. Das Zipfile selbst liegt als BLOB in einer Tabelle.

declare
  l_zipfile_list apex_zip.t_files;
  l_zip_archive  blob;
begin
  select filecontent into l_zip_archive
  from zip_archives where filename = 'p22298106_503_Generic.zip';
 
  l_zipfile_list := apex_zip.get_files(
    p_zipped_blob => l_zip_archive
  );

  dbms_output.put_line('ZIP Archive contains: ' || l_zipfile_list.count || ' files.');
  for f in l_zipfile_list.first..l_zipfile_list.last loop
    dbms_output.put_line(l_zipfile_list(f));
  end loop;
end;
/

ZIP Archive contains: 12312 files.
patch/images/lowerboxL.gif
patch/images/FNDSUBSM.gif
patch/images/wwv_quick_picks2.gif
patch/images/dotted_dbl_line.gif
patch/images/wcenter.gif
patch/images/bottomredright.gif
patch/images/pobcol.gif
:

Analog dazu lassen sich mit APEX_ZIP auch neue ZIP-Archive erstellen. Dazu dienen die Prozeduren ADD_FILE und FINISH.

LDAP-Zugriffe ganz einfach: APEX_LDAP

Zum Zugriff auf einen LDAP-Server gibt es bereits seit langer Zeit das Paket DBMS_LDAP bereit. Allerdings ist der Umgang mit diesem Paket eher umständlich, so dass das APEX-Entwicklerteam für die wichtigsten Aufgaben das Paket APEX_LDAP bereitgestellt hat. Ab APEX 5.0 ist vor allem die Funktion SEARCH interessant.

select dn, name, val from table(
  apex_ldap.search(
    p_host            => 'ldap.mycompany.com'
   ,p_port            => 389
   ,p_search_base     => 'dc=domain,dc=tld'
   ,p_search_filter   => 'cn=Czarski*'
   ,p_attribute_names => 'cn,title,ou,city'
  )
)
/

DN                                            NAME            VAL
--------------------------------------------- --------------- -------------------------
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CZARSKI,CARSTEN
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN,CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CCZARSKI_DE
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN.CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    title           Senior Leitende/R System
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    ou              Bu St-Dbtec
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    city            Munich
:

Setzt man die Möglichkeiten der SQL PIVOT Klausel geschickt ein, so kann man folgendes Ergebnis erzielen.

select cn, org, title,  city
from (
  select * from table(
    apex_ldap.search(
      p_host            => 'ldap.mycompany.com'
     ,p_port            => 389
     ,p_search_base     => 'dc=domain,dc=tld'
     ,p_search_filter   => 'cn=Czarski*' 
     ,p_attribute_names => 'cn,title,ou,city'
    )
  )
) pivot (
  listagg(val,','||chr(10)) within group (order by val) for name in (
    'cn'    as cn,
    'title' as title,
    'ou'    as org,
    'city'  as city
  )
)
/

CN                   ORG                  TITLE                                    CITY
-------------------- -------------------- ---------------------------------------- ----------
CARSTEN,             Bu St-Dbtec          Senior Leitende/R Systemberater/In       Munich
CARSTEN,CZARSKI,
CARSTEN.CZARSKI,
CARSTEN_CZARSKI,
CCZARSKI,
CCZARSKI_DE,
CZARSKI,
CZARSKI,CARSTEN

1 Zeile wurde ausgewählt.

Neben der Funktion SEARCH stehen noch einige Standard-Funktionen bereit, die vor allem für den APEX-Entwickler interessant sind. So führt AUTHENTICATE eine LDAP-Authentifizierung durch; das lässt sich zur Validierung eines LDAP-Passworts nutzen. Die Funkionen MEMBER_OF bzw. MEMBER_OF2 geben ein Array bzw. eine Liste der LDAP-Gruppen zurück, denen ein LDAP-Username zugeordnet ist.

Escaping mit APEX_ESCAPE

Mit APEX_ESCAPE können verschiedenste Sonderzeichen maskiert werden. APEX-Entwickler benötigen das am häufigsten, um HTML-Sonderzeiten wie <, > und & zu maskieren, damit diese auf der Webseite als solche dargestellt und nicht interpretiert werden (das ist insbesondere wichtig beim Schutz vor XSS-Schwachstellen).

Aber APEX_ESCAPE bietet auch andere hilfreiche Funktionen an. So maskiert die Funktion JSON JSON-spezifische Sonderzeichen wie " oder '.

SQL> select apex_escape.json('Text: "Zu Maskieren", ''Zu Maskieren''') escaped from dual;

ESCAPED
------------------------------------------------
Text: \"Zu Maskieren\", \u0027Zu Maskieren\u0027

Analog dazu stehen Funktionen für Reguläre Ausdrücke (REGEXP), LDAP Distinguished Names (LDAP_DN und LDAP-Suchausdrücke (LDAP_SEARCH_FILTER) bereit.

REST-Dienste aufrufen mit APEX_WEB_SERVICE

Das Paket APEX_WEB_SERVICE erlaubt das Konsumieren von REST- und SOAP-Webservices mit PL/SQL. Die Funktion MAKE_REST_REQUEST erlaubt das Angeben einer URL (Webservice Endpoint), einer HTTP-Methode und ggfs. zusätzlicher Parameter zur Authentifizierung oder zum Setzen von HTTP-Headern.

select apex_web_service.make_rest_request(
  p_url       => 'http://sql-plsql-de.blogspot.com/feeds/posts/default?alt=json',
  http_method => 'GET'
) from dual;

APEX_WEB_SERVICE.MAKE_REST_REQUEST('HTTP://SQL-PLSQL-DE.BLOGSPOT.COM/FEEDS/POSTS/
--------------------------------------------------------------------------------
{"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom
","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$blogger":"
http://schemas.google.com/blogger/2008","xmlns$georss":"http://www.georss.org/ge
orss","xmlns$gd":"http://schemas.google.com/g/2005","xmlns$thr":"http://purl.org
:

Hier ist jedoch schon etwas Vorsicht geboten: Seit Oracle11g braucht ein Datenbankuser, der einen HTTP-Request machen möchte, vorher eine Netzwerk-ACL, die vom DBA mit dem Paket DBMS_NETWORK_ACL_ADMIN eingerichtet wird. APEX_WEB_SERVICE führt die HTTP-Requests mit den Rechten des APEX-Engine Users (APEX_050000) durch. Sobald diesem User also eine ACL zum Zugriff auf eine Netzwerkressource eingeräumt wurde, können alle anderen Datenbankuser diese Netzwerkressource ansprechen.

Kleinere Helfer im Paket APEX_UTIL

APEX_UTIL ist der "Gemischtwarenladen" der APEX PL/SQL API. Hier finden sich viele verschiedene Funktionen, die teilweise auch rein historisch und aus Gründen der Rückwärtskompatibilität vorhanden sind. Die meisten Funktionen in APEX_UTIL haben einen sehr konkreten APEX-Bezug und deren Verwendung ist außerhalb einer APEX-Applikation nicht sinnvoll. Einige wenige sind aber dabei, mit denen man auch als PL/SQL Entwickler etwas anfangen kann ...

  • GET_HASH errechnet einen Hashwert aus einer gegebenen Liste von VARCHAR2-Werten. Im APEX-Umfeld wird das oft gebraucht, wenn man Änderungen an Tabellendaten feststellen möchte (Lost Update Detection), aber auch in einer PL/SQL Stored Procedure kann das nützlich sein.
    SQL> select apex_util.get_hash(apex_t_varchar2('WERT 1','WERT 2')) as HASH from dual; 
    
    HASH
    --------------------------------------------------------------------------------------
    Ohp8_wWM0lC8rR7Wmz8tzp_sLrSCjqRj5mTo6XMBVqrphnsv2C5Ec9inJHeOqydJLM-z394dOLp8zIjcI0h-zQ
    
  • STRING_TO_TABLE wandelt eine Werteliste, die als separierte Liste als VARCHAR2 Datentyp vorliegt, in ein PL/SQL Assoziatives Array um.
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array := apex_util.string_to_table('Wert 1#Wert 2#Wert 3#', '#');
      for i in l_array.first..l_array.last loop
        dbms_output.put_line(l_array(i));
      end loop;
    end;
    
    Wert 1
    Wert 2
    Wert 3
    
  • TABLE_TO_STRING geht den umgekehrten Weg und wandelt eine Werteliste, die als PL/SQL Array vorliegt, in eine separierte Liste als VARCHAR2-Datentyp um.
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array(1) := 'Wert A';
      l_array(2) := 'Wert B';
      l_array(3) := 'Wert C';
    
      dbms_output.put_line(apex_util.table_to_string(l_array, '$'));
    end;
    
    Wert A$Wert B$Wert C
    

Viel Spaß beim Ausprobieren der verschiedenen Funktionen und Prozeduren der APEX PL/SQL APIs. Und es lohnt sich, bei künftigen APEX-Releases in die Application Express API Reference hineinzusehen - da werden sich mit Sicherheit Änderungen und Neuerungen zeigen.

The first blog posting in 2016 will be about APEX without APEX. What does that mean? Well, APEX installs several PL/SQL packages into the database (which is obvious since APEX is implemented in PL/SQL). But some packages of the documented APEX PL/SQL API can be used also outside the context of APEX applications. And these are what this blog posting is about. I will not explain all these in the very detail - for some there are already existing how tos which I will refer to.

JSON parsing: APEX_JSON (APEX 5.0 or higher)

Beginning with release 12.1.0.2, the Oracle database contains SQL/JSON functions which allow to parse and process JSON documents with SQL functions. These functions are very powerful and fast, so when it's about parsing JSON in a 12c database, one should always use the native SQL/JSON functions. But there are also several applications still running on 11g. Also, the native JSON capabilities only cover JSON parsing, not JSON generation.

The APEX_JSON package which was introduced with APEX 5.0, comes to a rescue here. It's part of APEX, but it can easily be used outside of APEX as well. In 2015 I already had two blog postings about working with APEX_JSON, so I'll just reference these here.

ZIP-Archives: APEX_ZIP

The APEX_ZIP package allows to work with ZIP archives directly in the database and with PL/SQL. Having a ZIP archive stored as BLOB in the database, the APEX_ZIP package can extract the individual files as BLOBs. The following code example illustrates how this works.

declare
  l_zipfile_list apex_zip.t_files;
  l_zip_archive  blob;
begin
  select filecontent into l_zip_archive
  from zip_archives where filename = 'p22298106_503_Generic.zip';
 
  l_zipfile_list := apex_zip.get_files(
    p_zipped_blob => l_zip_archive
  );

  dbms_output.put_line('ZIP Archive contains: ' || l_zipfile_list.count || ' files.');
  for f in l_zipfile_list.first..l_zipfile_list.last loop
    dbms_output.put_line(l_zipfile_list(f));
  end loop;
end;
/

ZIP Archive contains: 12312 files.
patch/images/lowerboxL.gif
patch/images/FNDSUBSM.gif
patch/images/wwv_quick_picks2.gif
patch/images/dotted_dbl_line.gif
patch/images/wcenter.gif
patch/images/bottomredright.gif
patch/images/pobcol.gif
:

Building a new ZIP archive from existing BLOB data is also possible. The functions ADD_FILE and FINISH serve that purpose.

Access an LDAP server the easy way: APEX_LDAP

To access an LDAP server with PL/SQL, we have the DBMS_LDAP package for a very long time now. But the API is rather cumbersome and not easy to use. Even simple tasks like checking group membership require a lot of (boilerplate) code. So, the APEX development team added the APEX_LDAP package for the most important task. And since APEX 5.0, the SEARCH function is really interesting.

select dn, name, val from table(
  apex_ldap.search(
    p_host            => 'ldap.mycompany.com'
   ,p_port            => 389
   ,p_search_base     => 'dc=domain,dc=tld'
   ,p_search_filter   => 'cn=Czarski*'
   ,p_attribute_names => 'cn,title,ou,city'
  )
)
/

DN                                            NAME            VAL
--------------------------------------------- --------------- ------------------------
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CZARSKI,CARSTEN
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN,CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CCZARSKI_DE
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    cn              CARSTEN.CZARSKI
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    title           Senior Leitende/R System
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    ou              Bu St-Dbtec
cn=CARSTEN_CZARSKI,L=EMEA,DC=ORACLE,DC=COM    city            Munich
:

Applying the SQL PIVOT clause to that query result leads to the following ...

select cn, org, title,  city
from (
  select * from table(
    apex_ldap.search(
      p_host            => 'ldap.mycompany.com'
     ,p_port            => 389
     ,p_search_base     => 'dc=domain,dc=tld'
     ,p_search_filter   => 'cn=Czarski*' 
     ,p_attribute_names => 'cn,title,ou,city'
    )
  )
) pivot (
  listagg(val,','||chr(10)) within group (order by val) for name in (
    'cn'    as cn,
    'title' as title,
    'ou'    as org,
    'city'  as city
  )
)
/

CN                   ORG                  TITLE                                    CITY
-------------------- -------------------- ---------------------------------------- ----------
CARSTEN,             Bu St-Dbtec          Senior Leitende/R Systemberater/In       Munich
CARSTEN,CZARSKI,
CARSTEN.CZARSKI,
CARSTEN_CZARSKI,
CCZARSKI,
CCZARSKI_DE,
CZARSKI,
CZARSKI,CARSTEN

1 row selected.

Beyond the SEARCH function, APEX_LDAP provides other functions for standard LDAP requirements. The AUTHENTICATE function does exactly what the name indicates - it just logs into the LDAP server. So this function can be used to validate LDAP username/password combinations. The MEMBER_OF and MEMBER_OF2 functions return the groups, a given LDAP user belongs to, as PL/SQL array or VARCHAR2 separated list, respectively.

Escaping with APEX_ESCAPE

The APEX_ESCAPE package consolidates several escaping functions. For APEX developers this is a very important package, they need it all the time to escape HTML special characters like <, > and & (important to protect an application against XSS (cross site scripting) attacks).

But APEX_ESCAPE offers further helpful functions. The JSON function escapes JSON-specigic characters like " or '. Developers can pass data through that functions when it is to be added to a JSON document.

SQL> select apex_escape.json('Text: "To escape", ''To escape''') escaped from dual;

ESCAPED
------------------------------------------
Text: \"To escape\", \u0027To escape\u0027

APEX_ESCAPE also provides functions to escape for regular expressions (REGEXP), LDAP Distinguished Names (LDAP_DN and LDAP search filters (LDAP_SEARCH_FILTER).

Calling REST services with APEX_WEB_SERVICE

The APEX_WEB_SERVICE package allows to consume REST or SOAP webservices with PL/SQL calls. For instance, the MAKE_REST_REQUEST function calls a REST service at the given URL endpoint with the given HTTP method. Additional parameters allow to pass HTTP haeder fiels or authentication data.

select apex_web_service.make_rest_request(
  p_url       => 'http://sql-plsql-de.blogspot.com/feeds/posts/default?alt=json',
  http_method => 'GET'
) from dual;

APEX_WEB_SERVICE.MAKE_REST_REQUEST('HTTP://SQL-PLSQL-DE.BLOGSPOT.COM/FEEDS/POSTS/
--------------------------------------------------------------------------------
{"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom
","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$blogger":"
http://schemas.google.com/blogger/2008","xmlns$georss":"http://www.georss.org/ge
orss","xmlns$gd":"http://schemas.google.com/g/2005","xmlns$thr":"http://purl.org
:

But there is one caveat regarding APEX_WEB_SERVICE: Since Oracle11g, network resources are protected by PL/SQL Network ACLs. In order to connect to a network location, the database user needs to be granted a network ACL by the DBA. The DBA does this with the DBMS_NETWORK_ACL_ADMIN package. APEX_WEB_SERVICE performs its HTTP requests as the APEX engine user (APEX_050000, APEX_040200, ...) - so as soon as the APEX engine user has been granted a network ACL, all database users can connect to that network location with APEX_WEB_SERVICE.

Little helpers in APEX_UTIL

APEX_UTIL is the general store within the APEX PL/SQL packages. It contains many procedures and functions for various purposes. In the meantime, specialized packages like APEX_ESCAPE or APEX_IR have been introduced; the APEX_UTIL procedures are still present for backwards compatibility. Some very few functions might be useful also for the non-APEX PL/SQL developer.

  • GET_HASH calculates a hash value from a given set of VARCHAR2 items. APEX developers need this often to detect changes in underlying database tables (Lost update detection). But within a generic stored procedure, the function might also be useful.
    SQL> select apex_util.get_hash(apex_t_varchar2('WERT 1','WERT 2')) as HASH from dual; 
    
    HASH
    --------------------------------------------------------------------------------------
    Ohp8_wWM0lC8rR7Wmz8tzp_sLrSCjqRj5mTo6XMBVqrphnsv2C5Ec9inJHeOqydJLM-z394dOLp8zIjcI0h-zQ
    
  • STRING_TO_TABLE converts a separated string to a PL/SQL associative ARRAY as follows:
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array := apex_util.string_to_table('Item 1#Item 2#Item 3#', '#');
      for i in l_array.first..l_array.last loop
        dbms_output.put_line(l_array(i));
      end loop;
    end;
    
    Item 1
    Item 2
    Item 3
    
  • TABLE_TO_STRING takes the other way around: It converts a PL/SQL associative array to a separated string.
    declare
      l_array APEX_APPLICATION_GLOBAL.VC_ARR2;
    begin
      l_array(1) := 'Item A';
      l_array(2) := 'Item B';
      l_array(3) := 'Item C';
    
      dbms_output.put_line(apex_util.table_to_string(l_array, '$'));
    end;
    
    Item A$Item B$Item C
    

Have fun trying these things out - perhaps one or the other function of the APEX PL/SQL API is useful to you. And for upcoming APEX releases we can expect additions and changes - so it should be worth the effort to have a look into the Application Express API Reference from time to time.

Beliebte Postings