9. Dezember 2016

A PL/SQL "JSON explorer" based on the APEX_JSON package

Vor kurzem hat mich ein Kollege um Hilfe beim Parsen eines JSON-Dokumentes gebeten. Dafür gibt es zwar schon einige Beispiele mit dem Package APEX_JSON oder den nativen SQL/JSON Funktionen in Oracle12c. Der Fall lag hier aber etwas anders.

Es geht darum, ein JSON-Dokument zu parsen, dessen Struktur man gar nicht kennt. Egal, ob man das Dokument mit APEX_JSON oder mit den nativen SQL/JSON-Funktionen in Oracle12c parsen möchte - stets muss man wissen, an welchen Attributen man in welchen Stellen des JSON-Dokumentes interessiert ist. Möchte man das JSON jedoch einfach nur "erforschen", so kommt man mit diesen beiden Ansätzen nicht weiter.

Angenommen, es geht darum, das folgende JSON zu parsen und Informationen über alle Knoten zu erhalten:

{
  "string": "value",
  "number": 0,
  "boolean": true,
  "array": [ 1,2,3 ],
  "object": {
     "string": "value",
     "array": [
       {
          "number": 0,
          "string": "value"
       },
       {
          "number": 1,
          "string": "value"
       }
     ],
     "object": { 
        "array": [ "v1", "v2", "v3" ]
     }
  }
}

Man siegt, eine Menge geschachtelter Strukturen. Eine Idee ist es, den in diesem Blog Posting erwähnten Kniff mit APEX_JSON.TO_XMLTYPE zu nutzen: Damit wandelt man das JSON in ein XML-Dokument um ...

select xmlserialize(
  content apex_json.to_xmltype(
    '{ "string": "value", "number": 0, "boolean": true, "array": [ 1,2,3 ], "object": { "string": "value", "array": [ { "number": 0, "string": "value" }, { "number": 1, "string": "value" } ], "object": { "array": [ "v1", "v2", "v3" ] } } }'
  ) indent 
) from dual; 

<?xml version="1.0" encoding="UTF-8"?>
<json>
  <string>value</string>
  <number>0</number>
  <boolean>true</boolean>
  <array>
    <row>1</row>
    <row>2</row>
    <row>3</row>
  </array>
  :

... und die SQL/XML-Funktionen bieten einige Möglichkeiten an, die Struktur zu erforschen ...

with xml_from_json as (
  select apex_json.to_xmltype(
    '{ "string": "value", "number": 0, "boolean": true, "array": [ 1,2,3 ], "object": { "string": "value", "array": [ { "number": 0, "string": "value" }, { "number": 1, "string": "value" } ], "object": { "array": [ "v1", "v2", "v3" ] } } }'
  ) xml from dual
)
select 
  tagname, 
  parentname,
  count(*) occurrences
from xml_from_json, xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xml
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname

TAGNAME  PARENTNAME  OCCURRENCES  
-------- ----------- ------------
object   object      1            
string   object      1            
string   json        1            
object   json        1            
number   row         2            
json                 1            
row      array       8
:        :           :

Unter Umständen kommt man hiermit schon sehr weit - eine Kleinigkeit ist allerdings beim Umwandeln nach XML verlorengegangen; und das sind die Datentypen der JSON-Attribute. JSON kennt hier immerhin numerische, boolean und String-Datentypen - im XML-Dokument ist alles zum String geworden. Möchte man diese Information behalten, so bleibt noch der programmatische Weg mit dem Paket APEX_JSON. Der nun folgende PL/SQL Code implementiert einen "JSON-Explorer" als Table-Function; man gibt ein JSON-Dokument hinein und bekommt eine Liste mit enthaltenen Elementen, Datentypen und Positionen in der Hierarchie zurück.

drop type json_element_ct
/

drop type json_element_t
/

create type json_element_t as object (
   attr_level       number,
   attr_name        varchar2(255),
   attr_type        varchar2(30),
   attr_value       varchar2(255),
   attr_path        varchar2(255),
   parent_attr_path varchar2(255)
)
/

create type json_element_ct as table of json_element_t
/

create or replace function parse_json( p_json in clob ) return json_element_ct is
   l_parsed_json    apex_json.t_values;

   l_root_children  wwv_flow_t_varchar2;
   l_root_node      apex_json.t_value;

   l_p0             number := 0;
   l_p1             number := 0;
   l_p2             number := 0;
   l_p3             number := 0;
   l_p4             number := 0;

   l_result         json_element_ct := json_element_ct();

   function replace_path( p_path in varchar2 ) return varchar2 is 
   begin
       return regexp_replace(
                 regexp_replace(
                    regexp_replace(
                       regexp_replace(
                          regexp_replace( p_path, '%d', l_p0, 1, 1 ),
                          '%d', l_p1, 1, 1 ),
                       '%d', l_p2, 1, 1 ),  
                    '%d', l_p3, 1, 1 ),
                 '%d', l_p4, 1, 1 );
   end replace_path;

   procedure evaluate_node(
       p_prefix       in varchar2,
       p_node         in apex_json.t_value,
       p_node_name    in varchar2,
       p_parent_name  in varchar2            default null,
       p_level        in number              default 0,
       p_arraylevel   in number              default 1
   ) is
       l_node          apex_json.t_value;
       l_node_children wwv_flow_t_varchar2;

       l_type          varchar2(30);
       l_value         varchar2(255);
   begin
       if p_node.kind = 2 then
           l_type := 'boolean';
           l_value := 'true';

       elsif p_node.kind = 3 then
           l_type := 'boolean';
           l_value := 'false';

       elsif p_node.kind = 4 then
           l_type := 'number';
           l_value := to_char( p_node.number_value );

       elsif p_node.kind = 5 then
           l_type := 'varchar2';
           l_value := p_node.varchar2_value;

       elsif p_node.kind = 7 then
           l_type := 'array';
           l_value := null;

           l_result.extend(1);
           l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );

           if p_arraylevel = 1 then l_p0 := 0; end if;
           if p_arraylevel = 2 then l_p1 := 0; end if;
           if p_arraylevel = 3 then l_p2 := 0; end if;
           if p_arraylevel = 4 then l_p3 := 0; end if;
           if p_arraylevel = 5 then l_p4 := 0; end if;

           for j in 1 .. p_node.number_value loop

               if p_arraylevel = 1 then l_p0 := l_p0 + 1; end if;
               if p_arraylevel = 2 then l_p1 := l_p1 + 1; end if;
               if p_arraylevel = 3 then l_p2 := l_p2 + 1; end if;
               if p_arraylevel = 4 then l_p3 := l_p3 + 1; end if;
               if p_arraylevel = 5 then l_p4 := l_p4 + 1; end if;

               l_node := apex_json.get_value( 
                   p_values => l_parsed_json, 
                   p_path =>   p_prefix || p_node_name|| '[%d]', 
                   p0 =>       l_p0, 
                   p1 =>       l_p1, 
                   p2 =>       l_p2, 
                   p3 =>       l_p3, 
                   p4 =>       l_p4 );
 
               evaluate_node( 
                   p_prefix =>      p_prefix || p_node_name || '[%d]', 
                   p_node =>        l_node, 
                   p_node_name =>   null,
                   p_parent_name => p_prefix || p_node_name,
                   p_level =>       p_level + 1, 
                   p_arraylevel =>  p_arraylevel + 1 );

           end loop;

       elsif p_node.kind = 6 then
           l_type := 'object';
           l_value := null;

           l_result.extend(1);
           l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );

           l_node_children := apex_json.get_members( 
               p_values => l_parsed_json, 
               p_path =>   p_prefix || p_node_name,  
               p0 =>       l_p0, 
               p1 =>       l_p1, 
               p2 =>       l_p2, 
               p3 =>       l_p3, 
               p4 =>       l_p4 );

           if l_node_children is not null then

               for i in 1 .. l_node_children.count loop

                   l_node := apex_json.get_value( 
                        p_values => l_parsed_json, 
                        p_path =>   p_prefix || p_node_name || '.' || l_node_children( i ), 
                        p0 =>       l_p0, 
                        p1 =>       l_p1, 
                        p2 =>       l_p2, 
                        p3 =>       l_p3, 
                        p4 =>       l_p4 );

                    evaluate_node( 
                        p_prefix =>      p_prefix || p_node_name || '.', 
                        p_node =>        l_node, 
                        p_node_name =>   l_node_children( i ),
                        p_parent_name => p_prefix || p_node_name,
                        p_level =>       p_level + 1, 
                        p_arraylevel =>  p_arraylevel );

                end loop;
            end if;
       end if;

       if p_node.kind in ( 2, 3, 4, 5 ) then 
           l_result.extend( 1 );
           l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );
       end if;
  end;
begin
    apex_json.parse( l_parsed_json, p_json );
    l_root_children := apex_json.get_members( p_values => l_parsed_json, p_path => '.' );

    l_result.extend( 1 );
    l_result( l_result.count ) := json_element_t ( null, '<root>', 'object', null, '<root>', null );

    if l_root_children is not null then

         for i in 1 .. l_root_children.count loop

             l_root_node := apex_json.get_value( 
                 p_values => l_parsed_json, 
                 p_path =>   l_root_children( i ) );

             evaluate_node( 
                 p_prefix =>      null,
                 p_node =>        l_root_node, 
                 p_node_name =>   l_root_children( i ),
                 p_parent_name => '<root>' );
         end loop;
    end if;
    return l_result;
end;
/
sho err

Ausprobieren ist dann einfach ...

select * from table(parse_json('{ "string": "value", "number": 0, "boolean": true, "array": [ 1,2,3 ], "object": { "string": "value", "array": [ { "number": 0, "string": "value" }, { "number": 1, "string": "value" } ], "object": { "array": [ "v1", "v2", "v3" ] } } } ' ));

ATTR_LEVEL  ATTR_NAME  ATTR_TYPE  ATTR_VALUE  ATTR_PATH               PARENT_ATTR_PATH     
----------- ---------- ---------- ----------- ----------------------- -------------------
            <root>     object                 <root>                                       
0           string     varchar2   value       string                  <root>               
0           number     number     0           number                  <root>               
0           boolean    boolean    true        boolean                 <root>               
0           array      array                  array                   <root>               
1           <no name>  number     1           array[1]                array                
1           <no name>  number     2           array[2]                array                
1           <no name>  number     3           array[3]                array                
0           object     object                 object                  <root>               
1           string     varchar2   value       object.string           object               
1           array      array                  object.array            object               
2           <no name>  object                 object.array[1]         object.array         
3           number     number     0           object.array[1].number  object.array[1]      
3           string     varchar2   value       object.array[1].string  object.array[1]      
:           :          :          :           :                       : 

Wer mag, kann das ganze mit hierarischem SQL ein wenig aufbereiten ...

select lpad(' ', level * 4 - 1, ' ') || attr_name, attr_type, attr_value, attr_path
from data
start with parent_attr_path is null 
connect by parent_attr_path = prior attr_path
/

ATTR_NAME                      ATTR_TYPE  ATTR_VALUE  ATTR_PATH  
------------------------------ ---------- ----------- ------------------------              
<root>                         object                 <root>                  
    array                      array                  array                   
        <no name>              number     1           array[1]                
        <no name>              number     2           array[2]                
        <no name>              number     3           array[3]                
    boolean                    boolean    true        boolean                 
    number                     number     0           number                  
    object                     object                 object                  
        array                  array                  object.array            
            <no name>          object                 object.array[1]         
                number         number     0           object.array[1].number 
:                              :          :           : 

Fertig; viel Spaß beim Ausprobieren. Die Funktion arbeitet sich durch die ganze Hierarchie; allerdings gilt für Arrays, dass die Schachtelungstiefe maximal fünf sein darf. In das sechste, geschachtelte (!) Array kann die Funktion nicht einsteigen, da APEX_JSON hier auf fünf Ebenen begrenzt ist. Für die meisten Anforderungen sollte das aber genug sein. Außerdem habe ich den Fall, dass das JSON-Dokument bereits auf Root-Ebene ein Array ist, nicht berücksichtigt; es muss ein Objekt sein (das erste Zeichen im JSON muss die geöffnete, geschweifte Klammer "{" sein).

Recently, I helped colleague in how to parse a JSON document in PL/SQL - that was a very interesting case, which I did not blog about so far. I already have blog postings about parsing JSON with SQL or PL/SQL: JSON parsing with the APEX_JSON package or JSON parsing using the Oracle12c native SQL functions. But this case was different.

The requirement was to explore a JSON document - it's not about looking up a very specific value; we want to know which attributes and values are in the document. Assumed, we have the following JSON document and we want to explore it programmatically - what's in the document?

{
  "string": "value",
  "number": 0,
  "boolean": true,
  "array": [ 1,2,3 ],
  "object": {
     "string": "value",
     "array": [
       {
          "number": 0,
          "string": "value"
       },
       {
          "number": 1,
          "string": "value"
       }
     ],
     "object": { 
        "array": [ "v1", "v2", "v3" ]
     }
  }
}

We see some nested structures, objects and arrays. One approach would be to use the APEX_JSON.TO_XMLTYPE function as described in the JSON parsing with APEX_JSON blog posting. With that function, we can convert the JSON document to an XMLTYPE ...

select xmlserialize(
  content apex_json.to_xmltype(
    '{ "string": "value", "number": 0, "boolean": true, "array": [ 1,2,3 ], "object": { "string": "value", "array": [ { "number": 0, "string": "value" }, { "number": 1, "string": "value" } ], "object": { "array": [ "v1", "v2", "v3" ] } } }'
  ) indent 
) from dual; 

<?xml version="1.0" encoding="UTF-8"?>
<json>
  <string>value</string>
  <number>0</number>
  <boolean>true</boolean>
  <array>
    <row>1</row>
    <row>2</row>
    <row>3</row>
  </array>
  :

... which allows us to use all the XML DB and SQL/XML functions on it.

with xml_from_json as (
  select apex_json.to_xmltype(
    '{ "string": "value", "number": 0, "boolean": true, "array": [ 1,2,3 ], "object": { "string": "value", "array": [ { "number": 0, "string": "value" }, { "number": 1, "string": "value" } ], "object": { "array": [ "v1", "v2", "v3" ] } } }'
  ) xml from dual
)
select 
  tagname, 
  parentname,
  count(*) occurrences
from xml_from_json, xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xml
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname

TAGNAME  PARENTNAME  OCCURRENCES  
-------- ----------- ------------
object   object      1            
string   object      1            
string   json        1            
object   json        1            
number   row         2            
json                 1            
row      array       8
:        :           :

That is a nice result; we can see the attribute names, parent names and (if we adjust the SQL query) also the attribute values. But the data types of the JSON attributes are lost: JSON attributes are either numeric, boolean or strings - with the conversion to XMLTYPE they all became strings - and we cannot get that information any more.

The APEX_JSON package provides procedures and functions to explore an unknown JSON document. The following code implements a PL/SQL table function which acts as a "JSON explorer": It parses a JSON and returns all found nodes and attributes together with some context information. Here is the code:

drop type json_element_ct
/

drop type json_element_t
/

create type json_element_t as object (
   attr_level       number,
   attr_name        varchar2(255),
   attr_type        varchar2(30),
   attr_value       varchar2(255),
   attr_path        varchar2(255),
   parent_attr_path varchar2(255)
)
/

create type json_element_ct as table of json_element_t
/

create or replace function parse_json( p_json in clob ) return json_element_ct is
   l_parsed_json    apex_json.t_values;

   l_root_children  wwv_flow_t_varchar2;
   l_root_node      apex_json.t_value;

   l_p0             number := 0;
   l_p1             number := 0;
   l_p2             number := 0;
   l_p3             number := 0;
   l_p4             number := 0;

   l_result         json_element_ct := json_element_ct();

   function replace_path( p_path in varchar2 ) return varchar2 is 
   begin
       return regexp_replace(
                 regexp_replace(
                    regexp_replace(
                       regexp_replace(
                          regexp_replace( p_path, '%d', l_p0, 1, 1 ),
                          '%d', l_p1, 1, 1 ),
                       '%d', l_p2, 1, 1 ),  
                    '%d', l_p3, 1, 1 ),
                 '%d', l_p4, 1, 1 );
   end replace_path;

   procedure evaluate_node(
       p_prefix       in varchar2,
       p_node         in apex_json.t_value,
       p_node_name    in varchar2,
       p_parent_name  in varchar2            default null,
       p_level        in number              default 0,
       p_arraylevel   in number              default 1
   ) is
       l_node          apex_json.t_value;
       l_node_children wwv_flow_t_varchar2;

       l_type          varchar2(30);
       l_value         varchar2(255);
   begin
       if p_node.kind = 2 then
           l_type := 'boolean';
           l_value := 'true';

       elsif p_node.kind = 3 then
           l_type := 'boolean';
           l_value := 'false';

       elsif p_node.kind = 4 then
           l_type := 'number';
           l_value := to_char( p_node.number_value );

       elsif p_node.kind = 5 then
           l_type := 'varchar2';
           l_value := p_node.varchar2_value;

       elsif p_node.kind = 7 then
           l_type := 'array';
           l_value := null;

           l_result.extend(1);
           l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );

           if p_arraylevel = 1 then l_p0 := 0; end if;
           if p_arraylevel = 2 then l_p1 := 0; end if;
           if p_arraylevel = 3 then l_p2 := 0; end if;
           if p_arraylevel = 4 then l_p3 := 0; end if;
           if p_arraylevel = 5 then l_p4 := 0; end if;

           for j in 1 .. p_node.number_value loop

               if p_arraylevel = 1 then l_p0 := l_p0 + 1; end if;
               if p_arraylevel = 2 then l_p1 := l_p1 + 1; end if;
               if p_arraylevel = 3 then l_p2 := l_p2 + 1; end if;
               if p_arraylevel = 4 then l_p3 := l_p3 + 1; end if;
               if p_arraylevel = 5 then l_p4 := l_p4 + 1; end if;

               l_node := apex_json.get_value( 
                   p_values => l_parsed_json, 
                   p_path =>   p_prefix || p_node_name|| '[%d]', 
                   p0 =>       l_p0, 
                   p1 =>       l_p1, 
                   p2 =>       l_p2, 
                   p3 =>       l_p3, 
                   p4 =>       l_p4 );
 
               evaluate_node( 
                   p_prefix =>      p_prefix || p_node_name || '[%d]', 
                   p_node =>        l_node, 
                   p_node_name =>   null,
                   p_parent_name => p_prefix || p_node_name,
                   p_level =>       p_level + 1, 
                   p_arraylevel =>  p_arraylevel + 1 );

           end loop;

       elsif p_node.kind = 6 then
           l_type := 'object';
           l_value := null;

           l_result.extend(1);
           l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );

           l_node_children := apex_json.get_members( 
               p_values => l_parsed_json, 
               p_path =>   p_prefix || p_node_name,  
               p0 =>       l_p0, 
               p1 =>       l_p1, 
               p2 =>       l_p2, 
               p3 =>       l_p3, 
               p4 =>       l_p4 );

           if l_node_children is not null then

               for i in 1 .. l_node_children.count loop

                   l_node := apex_json.get_value( 
                        p_values => l_parsed_json, 
                        p_path =>   p_prefix || p_node_name || '.' || l_node_children( i ), 
                        p0 =>       l_p0, 
                        p1 =>       l_p1, 
                        p2 =>       l_p2, 
                        p3 =>       l_p3, 
                        p4 =>       l_p4 );

                    evaluate_node( 
                        p_prefix =>      p_prefix || p_node_name || '.', 
                        p_node =>        l_node, 
                        p_node_name =>   l_node_children( i ),
                        p_parent_name => p_prefix || p_node_name,
                        p_level =>       p_level + 1, 
                        p_arraylevel =>  p_arraylevel );

                end loop;
            end if;
       end if;

       if p_node.kind in ( 2, 3, 4, 5 ) then 
           l_result.extend( 1 );
           l_result( l_result.count ) := json_element_t ( p_level, nvl( p_node_name, '<no name>'), l_type, l_value, replace_path( p_prefix || p_node_name ), replace_path( p_parent_name ) );
       end if;
  end;
begin
    apex_json.parse( l_parsed_json, p_json );
    l_root_children := apex_json.get_members( p_values => l_parsed_json, p_path => '.' );

    l_result.extend( 1 );
    l_result( l_result.count ) := json_element_t ( null, '<root>', 'object', null, '<root>', null );

    if l_root_children is not null then

         for i in 1 .. l_root_children.count loop

             l_root_node := apex_json.get_value( 
                 p_values => l_parsed_json, 
                 p_path =>   l_root_children( i ) );

             evaluate_node( 
                 p_prefix =>      null,
                 p_node =>        l_root_node, 
                 p_node_name =>   l_root_children( i ),
                 p_parent_name => '<root>' );
         end loop;
    end if;
    return l_result;
end;
/
sho err

We can test it easily ...

select * from table(parse_json('{ "string": "value", "number": 0, "boolean": true, "array": [ 1,2,3 ], "object": { "string": "value", "array": [ { "number": 0, "string": "value" }, { "number": 1, "string": "value" } ], "object": { "array": [ "v1", "v2", "v3" ] } } } ' ));

ATTR_LEVEL  ATTR_NAME  ATTR_TYPE  ATTR_VALUE  ATTR_PATH               PARENT_ATTR_PATH     
----------- ---------- ---------- ----------- ----------------------- -------------------
            <root>     object                 <root>                                       
0           string     varchar2   value       string                  <root>               
0           number     number     0           number                  <root>               
0           boolean    boolean    true        boolean                 <root>               
0           array      array                  array                   <root>               
1           <no name>  number     1           array[1]                array                
1           <no name>  number     2           array[2]                array                
1           <no name>  number     3           array[3]                array                
0           object     object                 object                  <root>               
1           string     varchar2   value       object.string           object               
1           array      array                  object.array            object               
2           <no name>  object                 object.array[1]         object.array         
3           number     number     0           object.array[1].number  object.array[1]      
3           string     varchar2   value       object.array[1].string  object.array[1]      
:           :          :          :           :                       : 

And with some hierarchical SQL, we can pretty-print the JSON hierarchy.

select lpad(' ', level * 4 - 1, ' ') || attr_name, attr_type, attr_value, attr_path
from data
start with parent_attr_path is null 
connect by parent_attr_path = prior attr_path
/

ATTR_NAME                      ATTR_TYPE  ATTR_VALUE  ATTR_PATH  
------------------------------ ---------- ----------- ------------------------              
<root>                         object                 <root>                  
    array                      array                  array                   
        <no name>              number     1           array[1]                
        <no name>              number     2           array[2]                
        <no name>              number     3           array[3]                
    boolean                    boolean    true        boolean                 
    number                     number     0           number                  
    object                     object                 object                  
        array                  array                  object.array            
            <no name>          object                 object.array[1]         
                number         number     0           object.array[1].number 
:                              :          :           : 

That's it - have fun trying this out. Basically the function walks through the complete JSON hierarchy, but due to a restriction in APEX_JSON, it can only access up to five nested(!) arrays. Also I did not take care for the case that the JSON document is an array at the root level (starting with a "["); it only works for JSON documents which are objects at the root level (starting with a "{").

9. November 2016

DOAG2016 und mehr ...? Wer ist dabei?

Nächste Woche, ab dem 15. November findet wieder die DOAG Konferenz statt. Dort bin ich dieses Jahr mit zwei Vorträgen vertreten - zum einen, im Rahmen des Oracle Cloud Day, werde ich ein wenig über die Nutzung von REST Services in Application Express sprechen - vorab schon mal soviel: APEX eignet sich nicht nur zur Arbeit auf lokalen Tabellendaten, auch JSON-Daten, die von einem REST Service kommen, lassen sich hervorragend mit APEX-Komponenten wie Reports, Forms oder anderen verarbeiten. Des weiteren werde ich in einem Demo-Kino einige Packaged Apps vorstellen; wer sich noch nicht allzuviel mit Packages Applications auseinandergesetzt hat, kann hier einen Einblick bekommen.

Besonders freue ich mich aber auf die vielen Vorträge aus der Community und auf die 3. APEX Open Mic Night, die am Mittwoch, dem 16. November, im Rahmen der DOAG-Konferenz um 20:30 im Raum "Istanbul" stattfinden wird. Fünf Minuten Redezeit erhalten alle Teilnehmer, um der Gruppe eigene, spannende APEX-Projekte zu präsentieren und Erfahrungen auszutauschen. Bier und Fingerfood stehen bereit – einzige Voraussetzung: gute Laune.

Am Freitag nach der DOAG-Konferenz treffen sich einige Meetup-Gruppen zum Austausch, was für diejenigen, die nicht an der Konferenz teilnehmen können, sicherlich eine gute Gelegenheit ist, neueste Informationen zu bekommen. Ich weiss von München und Frankfurt: In München wird Joel Kallman dabei sein, in Frankfurt Marc Sewtz.

Und eines noch: Für die im Mai 2017 stattfindende APEX Connect Konferenz läuft noch bis zum 21. November der Call For Papers. Wenn Ihr also eine Vortragsidee habt, am besten gleich einreichen. Und: In die APEX Connect eingebettet ist der PL/SQL Day an ersten Konferenztag. Dort darf APEX zwar auch erwähnt werden, es geht aber vornehmlich um PL/SQL. Man kann es zusammenfassen zu: Wenn Ihr eine Vortragsidee zum Thema "Entwicklung mit der Oracle-Datenbank" habt, reicht sie zur APEX Connect ein.

This posting is about events in German language and therefore in German only.

11. Oktober 2016

OTN Appreciation Day: The Java VM in the Oracle Database

... nachdem ich auf Twitter die Tweets zum OTN Appreciation Day gelesen habe, möchte ich das auf jeden Fall nutzen, um mal wieder ein Blog Posting zu veröffentlichen. Als Mitglied des APEX-Teams scheidet APEX für mich wohl aus - daher möchte ich auf ein Feature hinweisen, was mich die letzten Jahre immer wieder erstaunt hat: die Java-Engine in der Oracle-Datenbank.

Bereits seit der Version 8i ist Oracle mit einer Java-Engine ausgestattet - das bedeutet, dass man Stored Procedures nicht nur mit PL/SQL - sondern eben auch in Java implementieren kann. In den meisten Fällen, das muss man zugeben, macht das nur wenig Sinn, denn PL/SQL ist für die Arbeit mit SQL und Daten optimiert. Allerdings bietet die Java-Funktionsbibliothek einige Dinge an, die mit PL/SQL gar nicht oder nur schwer machbar sind.

  • Dateisystemzugriffe und Ausführen von Betriebssystem-Kommandos
  • Ausführen von ECMA Skriptsprachen (ab Java7)
  • Zugriffe auf Email-Server (IMAP, POP3)

Eine Java Stored Procedure entsteht, in dem man den Java Code, entweder mit dem Kommando CREATE OR REPLACE JAVA oder mit dem Kommandozeilentool loadjava in die Datenbank lädt. Das coole ist nun, dass man für statische Java-Methoden einen PL/SQL Wrapper erzeugen kann. Die Java-Methode bekommt also eine PL/SQL Schnittstelle: Für den Aufrufer ist alles PL/SQL, tatsächlich ausgeführt wird Java. Dazu ein (wirklich kurzes) Beispiel: Wir wollen für jedes Oracle Data File wissen, wieviel Platz auf dem Device (der Disk) noch vorhanden ist. Auf Unix/Linux wäre das das Kommando df - und das soll es in der Datenbank sein. Für das Beispiel braucht Ihr Oracle12c.

Erstmal die Java Stored Procedure anlegen - am besten macht Ihr das als SYS.

create or replace and compile java source named JAVA_GET_FREESPACE as
import java.io.*;

public class FreeSpaceHelper {
  public static long getFreeSpace(String pPath) throws Exception {
    return new File(pPath).getFreeSpace();
  }
}
/
sho err

create or replace function get_free_disk_space (p_path in varchar2) return number
is language java name 'FreeSpaceHelper.getFreeSpace(java.lang.String) return long';
/
Dann ruft man diese Funktion für jeden Eintrag in DBA_DATA_FILES auf. Fertig.
select 
  file_name, 
  bytes file_size,
  get_free_disk_space(file_name) free_space
from dba_data_files
/
Ein wenig formatiert, sieht der Output so aus ...
FILE_NAME                                          FILE_SIZE       FREE_SPACE
-------------------------------------------------- --------------- -------------
/opt/oracle/oradata/orcl/pdb1/system01.dbf         270MB           22GB
/opt/oracle/oradata/orcl/pdb1/sysaux01.dbf         680MB           22GB
/opt/oracle/oradata/orcl/pdb1/pdb1_users01.dbf     5MB             22GB

Java in der Datenbank bringt eine ganze Menge Möglichkeiten mit - in der Vergangenheit habe ich öfter darüber gebloggt; das eine oder andere steht auch im Internet zum Download bereit. Hier eine kleine Auswahl:

... after reading the tweets about the OTN Appreciation Day, I would like to take the opportunity to publish a posting on this blog again ( I know that this blog needs more postings ;-) ). As a member of the APEX development team, I'm not supposed to talk about APEX, so I decided to choose another of my favorite database topics: The Java engine within the Oracle database. This feature opens so many opportunities, I'm fascinated about this all the time.

Since Oracle8i, the Oracle database comes with an embedded Java engine. It contains the full functionality from a JDK - and allows to implement Stored Procedures in Java. I have to admit: In many cases this simply does not make sense, since PL/SQL is way faster in working with cursors, tables and SQL data types. But, as said, the JVM comes with the full java class library - and that library contains much more functionality than PL/SQL. So the Java engine allows you to to tasks in your database which, in PL/SQL, are either difficult, unsupported or simply not possible.

To create a Java Stored Procedure, load the java code into the database. This can be done either with the CREATE OR REPLACE JAVA SQL command or with the command line utility loadjava. The cool thing is now that you can create a PL/SQL wrapper for any static Java method. I'll illustrate this with a (short, really) example: For each row of the DBA_DATA_FILES view, we want to know, how much space is available on the device (on Linux/Unix we would use df). PL/SQL does not have any call for this - but Java can help (note: you need Oracle 12.1) to run this example. It's best to run as SYS.

First, create the Java Stored Procedure. Then, create the PL/SQL wrapper.

create or replace and compile java source named JAVA_GET_FREESPACE as
import java.io.*;

public class FreeSpaceHelper {
  public static long getFreeSpace(String pPath) throws Exception {
    return new File(pPath).getFreeSpace();
  }
}
/
sho err

create or replace function get_free_disk_space (p_path in varchar2) return number
is language java name 'FreeSpaceHelper.getFreeSpace(java.lang.String) return long';
/
Then call that new "SQL function" for each row of DBA_DATA_FILES.
select 
  file_name, 
  bytes file_size,
  get_free_disk_space(file_name) free_space
from dba_data_files
/
You should see output like this. Done.
FILE_NAME                                          FILE_SIZE       FREE_SPACE
-------------------------------------------------- --------------- -------------
/opt/oracle/oradata/orcl/pdb1/system01.dbf         270MB           22GB
/opt/oracle/oradata/orcl/pdb1/sysaux01.dbf         680MB           22GB
/opt/oracle/oradata/orcl/pdb1/pdb1_users01.dbf     5MB             22GB

The Java engine within the Oracle database offers huge opportunities. Functionality, you might have thought, is impossible to run inside the database, can be easily coded as Java stored procedure. In the past, I authored blog postings about this - here's a tiny choice:

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 das LOB-Segment 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.

Beliebte Postings