its-people Blog – Der Oracle Blog neben Beiträgen aus den Bereichen: Datenbanken, Business Intelligence & Datawarehouse, IT-Management Service & ERP-Beratung

Umwandlung einer Dezimalzahl in eine Binärzahl

Für jeden der mal eine Dezimalzahl in eine Binärzahl innerhalb eines Select oder PL/SQL Programms umwandeln will hier eine kleine PL/SQL-Function (funktioniert nach der Restwertmethode):

create or replace function dec2bin(p_nr in number) return varchar2 is
   v_erg  number;
   v_rest number;
 begin
   v_erg  := trunc(p_nr/2);
   v_rest := mod(p_nr,2);
   if v_erg > 0 then
     return(dec2bin(v_erg)||v_rest);
   else
     return(v_rest);
   end if;
 end dec2bin;
 /

DBMS_METADATA – Flexibler Umgang mit DDL

Die Definitionen von Schemadaten über mehrere Systeme hinweg synchron zu halten und zu versionieren ist schwerer als den berühmten Sack Flöhe zu hüten.

DBMS_METADATA erweist sich für diese Aufgaben als gut versteckte aber schlecht dokumentierte Perle, die weit mehr kann, als man ihr auf den ersten Blick ansieht.

Das Paket dient als Basis für die Datapump und kann weit mehr, als nur DDLs im Klartext für Tabellendefinitionen zu erzeugen:
Unter anderem kann zwischen Script- und XML-Format gewählt, Filter gesetzt und Transformationen durchgeführt werden.
Die Skripte für abhängige Objekte können gleich mit exportiert werden.
Während die Entwickler oftmals nur die Differenzskripte zwischen ihren Releases pflegen und liefern, erzeugt dbms_metadata die Initialskripte automatisch, um die Konfigurationsmanager zufrieden zu stellen.

Wurzeln

Das Package dbms_metadata gehört seit Oracle 9i zum Standardlieferumfang der Datenbank.
Es wird beständig weiterentwickelt und im Funktionsumfang verbessert.
Seit Oracle 10g ist dbms_metadata das Fundament der Datapump. So ist es kaum verwunderlich, dass sich Parameter und Optionen der beiden Features sehr ähnlich sich.
read more…

Benutzer-Wechsel in einem Package

Immer wieder möchte man aus einem PL/SQL-Package heraus einen Befehl absetzen, der nicht unter dem aktuellen sondern unter einem anderen Benutzer abgesetzt wird. Das ist so ohne weiteres nicht möglich, da ein Package einen Reconnect oder Benutzer-Wechsel nicht unterstützt. Hier leistet der Oracle Scheduler wertvolle Hilfe. Man kann mit ihm aus einem Package heraus einen synchronen Job aufrufen, der unter einem beliebigen Benutzer gestartet werden kann.

Hierzu ein Beispiel:

create or replace package my_utilities

is

--

-- Ausführen eines beliebigen SQL-Statements

procedure exec_with_other_user(p_sql in varchar2);

--

-- Erstellen eines Scheduler Jobs

procedure exec_sql(p_user in varchar2

                  ,p_sql  in varchar2

                  );

/

end my_utilities;

Sinnvolle und unsinnige Festlegungen bei Passwörtern

Spätestens beim Einrichten einer neuen Datenbank steht jeder Oracle-DBA vor der Frage, was für Passwörter er zulassen soll.

In Oracle besteht die Möglichkeit, das neue Passwort eines Benutzers durch eine selbstdefinierte Prozedur zu überprüfen, damit es nicht zu trivial ausfällt und somit nicht zu einfach erraten werden kann. Aber wie soll eine solche Prüffunktion aussehen?

Zum Glück hat Oracle bereits ein Beispiel mitgeliefert. Im Verzeichnis

“ORACLE_BASE/ORACLE_HOME/RDBMS/ADMIN”

befindet sich das Skript “UTLPWDMG.SQL”. Führt man dieses Skript mit SQL*Plus als SYSDBA aus, so werden zwei Funktionen implementiert: verify_function_11G und verify_function (für Datenbanken älter als 11G). Mit dem SQL-Befehl:

ALTER PROFILE default LIMIT
PASSWORD_VERIFY_FUNCTION verify_function_11G;

wird die Prüffunktion verify_function_11G für alle Benutzer aktiviert.
Damit müssen alle Passwörter die folgenden Kriterien erfüllen:

• Das Passwort muss eine Länge zwischen 8 und 30 Zeichen (je einschließlich) haben
• Das Passwort darf nicht identisch mit dem Benutzernamen sein, oder dem Benutzernamen rückwärts geschrieben, oder dem Benutzernamen plus Zahlen
• Ebenfalls nicht zulässig als Passwort ist der Servername sowie der Servername plus Zahl zwischen 1 und 100 (je einschließlich)
• Zu triviale Passwörter wie etwa oracle1, abcdefg1, user1234 etc. sind ebenfalls nicht erlaubt
• Das Passwort muss minde*stens einen Buchstaben und mindestens eine Zahl enthalten
• Das Passwort muss sich vom vorherigen Passwort um mindestens drei Zeichen unterscheiden.

Diese Festlegungen erscheinen alle sinnvoll. Dennoch empfehle ich, das Skript UTLPWDMG.SQL an einem Punkt zu ändern. Dies betrifft nicht
die Funktionen, sondern einen Befehl, der direkt nach der Definition der Funktion verify_function_11G ausgeführt wird. Er lautet:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
[...]

Diese beiden Parameter bewirken, dass die Benutzer alle 180 Tage ein neues Passwort vergeben müssen. Der zweite setzt eine
“Gnadenfrist” (“grace time”) von sieben Tagen für die Vergabe eines neuen Passworts.

Auf den ersten Blick erscheint dies eine sinnvolle Steigerung der Passwortsicherheit zu sein. In der Praxis zeigt sich
jedoch, damit jede Menge Nachteile einhergehen, vor allem in Kombination mit den obigen Bedingungen. So werden sich die
meisten Benutzer einfach zwei Passwörter ausdenken und zwischen diesen hin- und herschalten. Außerdem gewöhnen sich
Benutzer an ihr Passwort. Wenn sie nun gezwungen werden, es zu ändern, dann geschieht es oft, dass sie am nächsten
Arbeitstag das neue Passwort vergessen haben – und dann die Administratoren um Hilfe beim Zurücksetzen des Passworts
bitten müssen. Oder sie schreiben sich das neue Passwort auf – tun also genau das, vor dem seit Jahrzehnten alle
Sicherheitsratgeber warnen. Der Hauptnachteil jedoch ist, dass auch für reine Batch-User nun eine PASSWORD_LIFE_TIME
von 180 Tagen gilt, wie Robert Marz in seinem Artikel Stolpersteine bei der Migration auf Oracle 11g (R2) ausführt. Daher empfehle
ich, entweder das Skript UTLPWDMG.SQL so abzuändern, dass der Befehl lautet:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
[...]

oder nach Ausführung des Skripts den Befehl

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

als SYSDBA von Hand auszuführen.

Excel-Berichte mit PL/SQL erstellen

Letzte Woche war ich auf der DOAG Konferenz in Nürnberg und habe einen gut besuchten Vortrag über das Erzeugen von Excel-Dateien mit PL/SQL gehalten.

Jetzt will ich mein Versprechen einlösen und den zugehörigen Quelltext veröffentlichen.

Datenmodell, das dem Package gen_excel zugrunde liegt

Die Programme dienen ausschließlich zur Demonstration des technisch Möglichen und sollen Denkanstöße geben.
Für den produktiven Einsatz sind die Programme NICHT geeignet.
Der Funktionsumfang wurde ganz bewußt auf das Wesentliche reduziert.
Alles, was nicht wirklich benötigt wird, aber für einen produktiven Einsatz unerläßlich sind, fehlt.
So finden sich in den Skripten weder Storage-Klauseln für Tabellen, noch Excpetion-Handler.

Für fehlerhafte Daten, wirtschaftliche Verluste oder Frusterlebnisse kann ich keine Verantwortung übernehmen.

Da es sich um eine Demonstration handelt, gibt es auch keinerlei Support.
Feedback, Anregungen und Fragen sollten in den Kommentaren zu dem Blogeintrag unter

http://www.its-people.de/blog/?p=187

veröffentlicht werden.
Um das SPAM-Aufkommen in den Griff zu bekommen, werden die Kommentare erst nach Sichtung freigegeben.

Vorrausetzungen:
Zugriff auf ein Schema in einer Oracle 10g Datenbank, oder neuer (getestet wurde bis zur aktuellen Version 11gR2).
Die Minimalen Berechtigungen für das Schema sind die Rollen CONNECT und RESOURCE.
Die Directories xmlfiles und excelfiles müssen mit entsprechenden Berechtigungen angelegt werden (create any directory).

Installation:

1. Entpacken Sie das Archiv in einen Ordner ihrer Wahl.
2. Laden Sie die Java-Klassen für die ZIP-Bearbeitung in die Datenbank
   Doku siehe hier:
   http://www.oracle.com/global/de/community/tipps/zip/index.html
   Wechseln Sie in das Verzeichnis package/zip.
3. Die Java-Klassen werden wie folgt geladen

   loadjava -u [user]/[passwort] -o -r -v jazzlib-binary-0.07.zip
   loadjava -u [user]/[passwort] -o -r -v ZipInputStream.class ZipOutputStream.class

   (unter Windows: loadjava.bat)

4. Kopieren Sie die Dateien aus dem xmlfiles auf den Datenbankserver.
5. Installation der Tabellen und PL/SQL Teile:
   Wechseln Sie ins Verzeichnis package.
   Bearbeiten Sie die Datei install.sql und passen Sie hier insbesondere die Pfade für die directories xmlfiles (Laden der Templates, etc)
   und excelfiles (Ausgabe der  Excel-Dateien) an. xmlfiles sollte auf das Verzeichnis aus (4.) zeigen.
   Führen Sie  install.sql aus
6. Test:
   Als Test können Sie das “Wäre es nicht schön”-Skript aus meinem Vortrag ausführen:

set serveroutput on size unl

declare
  ex  integer;
begin
  ex := gen_excel.erzeuge_bericht('Doag-Demo', 'select * from tabs', 'Tabs');
  gen_excel.erzeuge_blatt(ex, 'Wilde Abfrage', 'select * from doag_data where rownum <100');
  gen_excel.erzeuge_blatt(ex, 'Blatt mit komplexer Abfrage'
                            , 'select trunc(verkauft_am, ''MONTH''),verkaeufer, sum(umsatz)
                                 from doag_data
                                group by trunc(verkauft_am, ''MONTH''), verkaeufer
                                 order by 1' );
  gen_excel.erzeuge_mappen(ex, 'xls:xlsx');
  for ma in (select exma_id from excel_mappen where exbe_id =ex)
  loop
    gen_excel.schreibe_mappe(ex, ma.exma_id);
  end loop;
end;
/

Wenn alles gut gelaufen ist, finden Sie im Verzeichnis excelfiles anschließend zwei neue Dateien: Doag-Demo.xls und Doag-demo.xlsx.

Hier sind die Dateien:

38-2010-K-DEV-Marz-Excel-Berichte-mit-PLSQL-Quellcode

38-2010-K-DEV-Marz-Excel-Berichte-mit-PLSQL-Praesentation-Demo-Scripts

38-2010-K-DEV-Marz-Excel-Berichte-mit-PLSQL-Praesentation