30. Mai 2007

Fließkommazahlen: NUMBER, BINARY_FLOAT, BINARY_DOUBLE ... oder was ...?

Seit Oracle10g gibt es die neuen Datentypen BINARY_FLOAT und BINARY_DOUBLE (Doku). Sie speichern Fließkommazahlen im IEEE754-Format und dienen als Ergänzung zu NUMBER. BINARY_FLOAT und BINARY_DOUBLE haben zwei Vorteile: zum einen werden Fließkommazahlen wesentlich kompakter gespeichert und zum anderen werden Fließkommaoperationen wesentlich schneller durchgeführt. Grund für diesen Performancevorteil ist, dass die Operationen durch die Hardware (die CPU) unterstützt werden. Auf der anderen Seite steht allerdings auch ein Nachteil: BINARY_FLOAT und BINARY_DOUBLE speichern Fließkommazahlen im Binärformat (Basis 2), NUMBER speichert im Dezimalformat (Basis 10). Geht es also um dezimale Rundungen, so garantiert nur NUMBER die exakte Rundung ohne Fehler. Dazu folgendes Zitat aus der Doku: "For a decimal floating-point number format like Oracle NUMBER, rounding is done to the nearest decimal place (for example. 1000, 10, or 0.01). The IEEE 754 formats use a binary format for floating-point values and round numbers to the nearest binary place (for example: 1024, 512, or 1/64)."
SQL> select dump(to_number(sqrt(2)),16) from dual;

DUMP(TO_NUMBER(SQRT(2)),16)
------------------------------------------------------------------------
Typ=2 Len=21: c1,2,2a,2b,e,39,18,4a,a,33,31,51,11,59,49,2b,a,46,51,4f,3a

SQL> select dump(to_binary_double(sqrt(2)), 16) from dual;

DUMP(TO_BINARY_DOUBLE(SQRT(2)),16)
--------------------------------------
Typ=101 Len=8: bf,f6,a0,9e,66,7f,3b,cd
Man sieht deutlich, dass BINARY_DOUBLE den Wert (Quadratwurzel aus "2") in nur 8 Byte speichert, während NUMBER deren 21 benötigt. Das nachfolgende SQL*Plus-Skript zeigt auch den Performancevorteil. Wichtig ist, dass bei Funktionen wie SQRT, der Parameter (also die Zahl, aus der die Wurzel gezogen werden soll) vorher in BINARY_FLOAT bzw. BINARY_DOUBLE umgewandelt wird (Test 3) - ansonsten kann die Hardwareunterstützung nicht genutzt werden und die Performance wird sogar schlechter (Test 2).
set timing on
set verify off
set serveroutput on size 200000
set echo off

accept COUNTER default '1000000' prompt '>> Anzahl Iterationen [1000000]: '


prompt >> 1. Test mit NUMBER
prompt >>
prompt >> val := val + sqrt(i)
prompt >>

declare
  i     pls_integer;
  val   number := 0;
begin
  for i in 1..&COUNTER. loop
    val := val + sqrt(i);
  end loop;
  dbms_output.put_line('Value: '||val);
end;
/

prompt >> 2. Test mit BINARY_DOUBLE
prompt >>
prompt >> val := val + sqrt(i)
prompt >>

declare
  i     pls_integer;
  val   binary_double := 0d;
begin
  for i in 1..&COUNTER. loop
    val := val + sqrt(i);
  end loop;
  dbms_output.put_line('Value: '||val);
end;
/

prompt >> 3. Test mit BINARY_DOUBLE
prompt >>
prompt >> val := val + sqrt(to_binary_double(i))
prompt >>

declare
  i     pls_integer;
  val   binary_double := 0d;
begin
  for i in 1..&COUNTER. loop
    val := val + sqrt(to_binary_double(i));
  end loop;
  dbms_output.put_line('Value: '||val);
end;
/

23. Mai 2007

Quote-Zeichen in PL/SQL ...

Heute mal ein kleiner Tipp für's SQL und PL/SQL-Programmieren. Man schreibt PL/SQL-Code, welcher SQL zurückliefern soll...
declare
  v_sql varchar2(32767);
begin
  v_sql := 'select ''Hallo Welt'' from dual';
end;
Ein Hochkomma wird durch ein zweites maskiert: So weit - so gut. Vielleicht ein wenig unübersichtlich ... Aber richtig übel wird's, wenn das PL/SQL ein PL/SQL erzeugen soll, welches ein SQL erzeugen soll ... ... und das ist in der Praxis gar nicht so unüblich ...
declare
v_sql varchar2(32767);
begin
v_sql := 'declare ' ||
         '  v_sql varchar2(32767); ' ||
           'begin '   ||
           '  v_sql := ''select ''''Hallo Welt'''' from dual'''||
           ' end;';
end;
Dann muss der Apostroph schon viermal verwendet werden, damit alles richtig maskiert ist. Und wenn der Code umfangreicher wird und man irgendwo ein Hochkomma vergessen hat ... ... tja, dann geht die Fehlersuche los! Ab Oracle10g ist das aber auch einfacher möglich: Zuerst das noch einfache Beispiel:
declare
 v_sql varchar2(32767);
begin
 v_sql := q'#select 'Hallo Welt' from dual#';
end;
q'# bedeutet, dass von nun an '# das neue Quote-Zeichen ist. Hochkommata werden bis zum nächsten #' nicht mehr als Quote-Zeichen interpretiert. Interessant ist dies natürlich vor allem für den zweiten Fall, denn es muss ja nicht '# verwendet werden, anstelle des # sind alle anderen Zeichen ebenfalls möglich ...
declare
  v_sql varchar2(32767);
begin
  v_sql := q'#declare #' ||
           q'#  v_sql varchar2(32767); #' ||
           q'#begin   #' ||
           q'#  v_sql := q'{select q'[Hallo Welt]' from dual}'#' || 
           q'# end;#';
end;
Das ist schon wesentlich leichter handzuhaben als die vielen Hochkommata ... Die neuen Quote-Zeichen sind einfach nur wärmstens zu empfehlen ...

14. Mai 2007

Betriebssystem-Kommandos aus der Datenbank starten

Ab und zu kommt die Anforderung auf, ein Betriebssystem-Kommando aus der Datenbank heraus aufzurufen. Wenn man mal danach sucht (via Oracle MetaLink oder Google), dann finden sich dazu auch recht viele Tipps und Möglichkeiten. Manchmal wird empfohlen, das Paket DBMS_SCHEDULER einzusetzen, an anderer Stelle findet sich der Hinweis, man solle sich ein kleines C-Programm schreiben, kompilieren und dann mit CREATE LIBRARY einbinden und und und ...
Hier findet Ihr nun die dritte, ganz bequeme Variante. Der Aufruf des Betriebssystem-Kommandos wird mit Java in der Datenbank gemacht - das ist aber bereits vorhanden und erfordert keine separate Software. Als kleines Extra erlaubt die Java-Variante auch das Auslesen der Standardausgabe bzw. das Schreiben von Daten in die Standardeingabe - wendet man es richtig an, so kann man auf dem Betriebssystem eine Pipe mit mehreren Kommandos erzeugen, Daten nach STDIN schreiben und die Ergebnisse aus STDOUT herausholen ...
Eine Warnung vorab: Das Ausführen von Betriebssystem-Kommandos durch "normale" Datenbankuser ist ein Sicherheitsrisiko, da alle Kommandos mit den Rechten des Oracle-Systemusers ausgeführt werden. Weiter unten seht Ihr, dass der DBA dazu auch passende Rechte vergeben muss. Diese Rechte sollten so restriktiv wie möglich vergeben werden. Insbesondere wenn Benutzereingaben aus einer Anwendung in die Aufrufe eingebaut werden sollen, ist höchste Vorsicht geboten !!!
Doch genug der Vorrede: Hier der Code (Download)
Nochmal: Betriebssystem-Aufrufe sind ein Sicherheitsrisiko. Wie oben bereits erwähnt, darf in der Oracle-Datenbank nicht jeder Datenbankuser einfach Betriebssystem-Kommandos ausführen. Der DBA muss vorher Rechte einräumen werden. Dies geht mit folgendem Skript:
declare
  v_grantee constant varchar2(30) := 'SCOTT';
begin
  dbms_java.grant_permission(
    grantee =>           v_grantee,
    permission_type =>   'SYS:java.lang.RuntimePermission',
    permission_name =>   'readFileDescriptor',
    permission_action => null
  );
  dbms_java.grant_permission(
    grantee =>           v_grantee,
    permission_type =>   'SYS:java.lang.RuntimePermission',
    permission_name =>   'writeFileDescriptor',
    permission_action => null
  );
  -- gibt ALLE Kommandos frei: Sehr unsicher
  /*
  dbms_java.grant_permission( 
    grantee =>           v_grantee, 
    permission_type =>   'SYS:java.io.FilePermission', 
    permission_name =>   '<<ALL FILES>>', 
    permission_action => 'execute' 
  );
  */
  -- erlaubt nur die Ausführung des "ls"-Kommandos
  dbms_java.grant_permission(
    grantee =>           v_grantee,
    permission_type =>   'SYS:java.io.FilePermission',
    permission_name =>   '/bin/ls',
    permission_action => 'execute'
  );
end;
/
sho err

Achtung: Der auskommentierte Teil des Skripts mit << ALL FILES >> gibt sehr weitreichende Privilegien - Der Datenbankuser könnte damit alle Betriebssystem-Kommandos ausführen. Dies sollte in der Praxis eigentlich nie verwendet werden!. Anstelle dessen lieber (wie auch hier) die Befehle einzeln freigeben - oder ein Shell-Skript schreiben und nur das Skript freigeben.
Ist alles vorbereitet, kann das neue PL/SQL-Paket OS_COMMAND verwendet werden:
select os_command.exec_clob('ls -la /') from dual

OS_COMMAND.EXEC_CLOB('LS-LA/')
---------------------------------------------------------------------

insgesamt 203
drwxr-xr-x   22 root   root        512 2007-04-17 01:33 .
drwxr-xr-x   22 root   root        512 2007-04-17 01:33 ..
drwxr-xr-x    2 root   root       2920 2006-12-13 15:42 bin
drwxr-xr-x    3 root   root        464 2005-07-19 14:20 boot
drwxr-xr-x   33 root   root     180056 2007-05-22 09:18 dev
drwxr-xr-x   66 root   root       6400 2007-05-22 09:18 etc
drwxr-xr-x    3 root   root         72 2005-07-19 14:23 home
drwxr-xr-x   12 root   root       3256 2005-07-19 14:06 lib
drwxr-xr-x    4 root   root         96 2004-10-04 17:24 media
drwxr-xr-x    3 root   root         72 2006-12-13 13:25 mnt
:

8. Mai 2007

XML Erzeugen mit den SQL/XML-Funktionen: Das geht sogar rekursiv ...

Wie erzeugt man XML ...? Eigentlich nun soweit geklärt: Mit den SQL/XML-Funktionen kann man XML-Dokumente beliebiger Struktur erzeugen - Kommentare, CDATA-Abschnitte, Namespaces: alles kein Problem! ... bis auf eines ... wie macht man eigentlich "rekursives" XML ...? Am Beispiel der EMP-Tabelle bedeutet das: Jeder Angestellte wird durch ein XML-Tag repräsentiert. Wenn er Manager ist, sollen seine "Untergebenen" als 1:n-Beziehung unterhalb eingeordnet werden ... und dies rekursiv ... Nur mit SQL SELECT-Anweisungen bzw. mit Views ist das nicht machbar - man braucht eine PL/SQL-Prozedur, die sich rekursiv selbst aufruft, als Hilfsmittel ...
create or replace function get_emps_xml(p_mgr_empno in EMP.EMPNO%TYPE default -1)
return xmltype
is
 v_xml xmltype;
begin
 select
  xmlelement("employees",
   xmlagg(
    xmlelement("employee",
     xmlattributes(empno as "id"),
     xmlforest(
       ename as "name",
       hiredate as "hiredate",
       sal as "salary"
     ),
     get_emps_xml(empno)
    )
   )
  )
 into v_xml
 from emp where nvl(mgr, -1) = p_mgr_empno;
 return v_xml;
end;
/

create or replace view emp_rec_xml as
select get_emps_xml from dual;
Ruft man die so erzeugte View EMP_REC_XML dann auf, so erhält man folgendes:

4. Mai 2007

XML-Dokumente erzeugen ... aber wie?

So, nun geht es also darum, XML-Dokumente aus den relationalen Tabellen zu erzeugen. Und dazu muss ich auf jeden Fall was schreiben, denn wenn man mal nach diesem Thema sucht, dann bietet das Internet schon jede Menge Informationen, aber nicht unbedingt immer die besten und effizientesten:
  • Oracle XDK (Oracle XML Developers Kit)
  • PL/SQL: DBMS_XMLQUERY
  • PL/SQL: DBMS_XMLGEN
  • SQL/XML-Kommandos (XMLElement & Co)
  • :
Von diesen vier Optionen ist für aktuelle Oracle-Versionen (9.2, 10.1, 10.2) eigentlich nur die vierte interessant: Die anderen haben so ihre Nachteile:
  • Oracle XDK: Java außerhalb der Datenbank; das XML lässt sich direkt in der Datenbank wesentlich effizienter und eleganter erzeugen - Es wird ein XML-Dokument erzeugt und direkt nach außen gegeben. Generiert man das XML außerhalb der Datenbank, so werden viele, viele Informationen einzeln nach außen gegeben (Netzwerk!) und dann wird erst das XML erzeugt ...
  • PL/SQL (DBMS_XMLQUERY): Dies ist ein recht veraltetes PL/SQL-Paket. Es entstammt noch der Oracle8i-Zeit, als die Datenbank noch nicht richtig mit XML umgehen konnte. Es ist sehr langsam (Java in der Datenbank) und kann nur sehr einfache XML-Strukturen erzeugen. Man sollte es gar nicht mehr verwenden.
  • PL/SQL (DBMS_XMLGEN): Dieses PL/SQL-Paket ist etwas neuer und kann auch komplexere XML-Strukturen erzeugen. Es ist allerdings im Vergleich zu SQL/XML kein Standard und darüber hinaus ebenfalls nicht die performanteste Variante. Würde ich also auch nicht verwenden.
Dann bleiben nur noch die SQL/XML-Kommandos, zu denen man in der Oracle-Dokumentation mehr Informationen findet. SQL/XML ist inzwischen Teil des SQL-Standards (SQL:2003) - das gute daran ist also, dass die Kommandos dann auch auf anderen Datenbanken funktionieren sollten. Ein weiterer Vorteil ist die Performance. Tests haben gezeigt, dass der "Overhead" für das Erzeugen des XML bei den SQL/XML-Funktionen sehr gering ist - ich kann mich an einen Test mit 1G Daten erinnern: Das Erzeugen des XML-Dokumentes dauerte ca. 3% länger als der reine, relationale SELECT. Hier am besten mal selbst ausprobieren ... Und so geht's: Das einfachste denkbare Beispiel ist wie folgt:

select XMLElement("xml-tag", sysdate) from dual

XMLELEMENT("XML-TAG",SYSDATE)
--------------------------------
2007-05-11
Und in diesem Online-Artikel hier findet sich ein ausführliches Beispiel: http://xml-magazin.de/itr/online_artikel/psecom,id,795,nodeid,69.html

Beliebte Postings