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

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.

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

Oracle Routing Engine einsetzen für LKW-Routen

Seit Oracle 10g verfügt Oracle Spatial über die Routing Engine. Diese ermöglicht es, direkt in der Datenbank Routen zu berechnen und in verschiedenen Formen (Geometrien, Wegbeschreibungen) auszugeben.

In Oracle 11gR2 wurde die Routing Engine erweitert. Sie basiert jetzt auf dem Network Data Model (NDM). Dies ermöglicht es, das Routing-Netzwerk um zusätzliche Informationen und Parameter zu erweitern. Ein Anwendungsfall für diese Erweiterung ist Truck Routing.

Eine mit normalem Oracle Routing (ohne Zusatzinformationen) berechnete Route ist für PKWs benutzbar, aber nicht unbedingt für LKWs. Hierzu ein Beispiel: Wir berechnen eine Route von Hamburg nach Westerland/Sylt. Die vom Router errechnete Strecke verwendet u.a. zwischen Billund und Westerland den DB Autoreisezug. Nehmen wir nun an, wir wollen die gleiche Strecke mit einem LKW (Gewicht: 20 t, Höhe: 4,30 m) einsetzen. Für diesen LKW ist die Route nicht benutzbar, da der Autoreisezug Fahrzeuge nur bis zu einer maximalen Höhe von 4,05 m transportiert. Ergänzt man jedoch das Routing Network um Truckattribute, gibt die Routing Engine eine ca. 40 km längere Strecke zurück, die über die dänische Insel Rømø führt. Letztere ist mit Sylt verbunden durch eine Fähre, die auch 4,30 m hohe LKWs transportieren kann.

Die Truckattribute werden im Normallfall als Zusatztabelle zu den Oracle Routing Daten geliefert (Hinweis: Je nach Lizenz muss diese Tabelle ggf. zusätzlich erworben werden, für Details fragen Sie bitte den  für Sie zuständigen Oracle-Vertriebsmitarbeiter). Sind sie installiert, so können die bereits bekannten XML-Anfragen an den Router einfach erweitert werden:

<?xml version=”1.0″ standalone=”yes”?> <route_request
id=”8″ route_preference=”shortest”
road_preference=”highway” vehicle_type=”truck”
[...truck-parameter..]
return_driving_directions=”true” distance_unit=”km”
time_unit=”hour” return_route_geometry=”false” >

Die möglichen Parameter (wie etwa truck_height) sind in dieser Präsentation näher beschrieben.

Oracle 11g – Never change a running system, oder doch?

Never change a running system – eine mit einiger Berechtigung immer wieder gern bemühte Argumentation, um mit dem Upgrade auf ein neues Softwarerelease noch abzuwarten, bis tatsächliche oder vermutete Kinderkrankheiten ausgemerzt sind.

Offensichtlich verhalten sich viele Anwender genau so abwartend bezüglich eines Upgrades auf die bereits seit einiger Zeit verfügbare Version 11g der Oracle Datenbank – im Feld begegnen uns überwiegend 9i- und 10g-Versionen, das aktuelle Release ist bisher noch spärlich vertreten.
Unter den vielen Neuerungen, Optionen und Features gibt es dabei einige, die es durchaus wert sind, einen baldigen Umstieg in Betracht zu ziehen. Eines der Top Features ist dabei sicherlich “Database Replay”, Bestandteil der “Real Application Testing” Option.
Wenn man bisher eine geänderte Anwendung einem realistischen Lasttest unterziehen wollte, war man auf die bekannten Werkzeuge von Drittherstellern angewiesen. Dies bedeutete eine Analyse von Anwenderverhalten, Erstellung von Benutzerprofilen, Analyse von bisherigen Belastungen über die Zeit und letztendlich die Zusammenstellung einer Simulation. Trotz des nicht unerheblichen Aufwands stand am Ende immer noch eine Simulation, als solche weiterhin mit dem Risiko behaftet, die Realität dann doch nicht ganz zu treffen.
Und – Hand auf’s Herz – in wie vielen Fällen wurde auf den Lasttest ganz verzichtet, wenn Aufwand und Kosten für eine einigermaßen realistische Simulation erst einmal bekannt waren?
Mit “Database Replay” gibt es nunmehr die Möglichkeit, alle Datenbankaktivitäten auf dem bisherigen System zu loggen, diese Logdateien auf das neue System zu transferieren und dort gegen die Datenbank “abzuspielen”.

“Database Replay” besteht dabei aus 4 Schritten:

• Workload Capture
• Workload Processing
• Workload Replay
• Analyse und Reporting

Workload Capture: Alle Datenbankaktivitäten werden aufgezeichnet und in spezielle binäre, sog. “capture files” in einem eigenen Verzeichnis geschrieben. Neben Start- und Stoppzeit für die Aufzeichnung lassen sich hier bereits bestimmte Aktivitäten ein- oder ausschließen. Für den Capture-Prozess muss das Package “DBMS_WORKLOAD_CAPTURE” installiert sein.

Der nächste Schritt ist CPU-intensiv und sollte daher nach dem Transfer auf die Test-Plattform erfolgen.
Workload Processing: Ein Arbeitsschritt, in dem die in den “capture files” enthaltenen Informationen aufbereitet, mit Metadaten ergänzt und in die sog. “replay files” umgewandelt werden. Die “replay files” sind wieder verwendbar und können somit für multiple Tests gegen eine mit “lash back” in den Ausgangszustand zurück versetzte Datenbank angewendet werden.

Workload Replay: Hierzu müssen ein oder mehrere “Replay Clients” aufgesetzt werden. Dazu ist das Programm “wrc.exe” im Oracle Home Verzeichnis notwendig. Für den eigentlichen Replay-Prozess muss das Package “DBMS_WORKLOAD_REPLAY” installiert sein.

Analyse und Reporting: Es werden bereits vorgefertigte Reports mitgeliefert, die z.B. Fehler während der Verarbeitung der replay files ausweisen und Hinweise auf die Performance auf Quell- und Zielsystem liefern.

Sie wollen wissen, ob ein neuer Patch bestehende Probleme behebt – oder neue schafft? Sie fragen sich, ob Konfigurationsparameter, die nach Richtwertempfehlungen gesetzt werden (z.B. sga_max_size ca. 50% des freien Hauptspeichers) für Ihre reale Instanz und Anwendung optimal sind? Sie fragen sich, welche Auswirkungen ein zusätzlicher Index oder ein Wechsel des Indextyps hat? Was bringt die nächst größere Hardware? Sie planen einen Plattformwechsel?
Bei all diesen Aufgaben und Fragestellungen liefert “Database Replay” wertvolle Antworten und reduziert Risiken. Der Capture-Prozess kann auch auf früheren Versionen als 11g eingerichtet und gestartet werden, z.B. auf einer 10gR2. Den Replay-Prozess gibt es jedoch erst mit der Version 11g. Sozusagen ein “rekursiver” Grund mehr, Bedenken gegen ein Upgrade zu überdenken – schließlich kann man mit dem Upgrade selbst Risiken reduzieren.
Mehr Informationen bei: frankfurt@its-people.de

Partitionen einer Tabelle in Oracle auslesen

Für meinen ersten Block hier wollte ich erstmal ein einfaches Thema ansprechen:
Das Auslesen von vorhanden Partitionen einer Tabelle.

Mir passierte es, dass ich bei einem Kunden saß und mir mit Toad die Partitionen einer Tabelle anschauen wollte.
Also erstmal über die GUI die Tabelle im entsprechenden Schema suchen und sich dann die Description anzeigen lassen. Anschließend ganz einfach auf den Tab “Partitions” wechseln.
Eigentlich ganz einfach. Wenn nun aber die GUI Probleme hat wieder ins Leben zurückzukehren und aufzutauen, weil bereits so viele Partitionen existieren, hört die Einfachheit auf.

Was tut man also in so einer Situation?
Richtig! SQLPLUS öffnen und per Hand abfragen:

– Für den User:
SELECT table_name
, partition_name
, partition_position
, tablespace_name
FROM user_tab_partitions
WHERE TABLE_NAME = ‘&P_TABLE’
ORDER BY PARTITION_POSITION DESC;

– Mit Blick auf die dba_tab_partitions:
SELECT table_owner
, table_name
, partition_name
, partition_position
, tablespace_name
FROM dba_tab_partitions
WHERE TABLE_NAME = ‘&P_TABLE’
ORDER BY PARTITION_POSITION DESC;

listagg

In Oracle 11gRel2 gibt es eine neue Gruppierungsfunktion das LISTAGG. Mit dieser Funktion ist man in der Lage, Spaltenwerte einer Wiederholungsgruppe in einer Zeile zusammenzufassen getrennt durch ein definiertes Trennzeichen.

Beispiel:

Inhalt der Tabelle Team:

GRUPPE               NAME

==================== ============ 

Team Blau            Harald

Team Blau            Robert

Team Blau            Thomas

Team Gelb            Alex

Team Gelb            Sabine

Team Rot             Angelika

Team Rot             Uwe

 Erzeugen einer Mitgliederliste pro Team:

select    distinct

          gruppe

         ,listagg(name,’;')

          within group (order by name asc)

          over (partition by gruppe) as mitglieder

from      teams

order by  gruppe

;

Ergebnis:

GRUPPE               MITGLIEDER

==================== ==============================

Team Blau            Harald;Robert;Thomas

Team Gelb            Alex;Sabine

Team Rot             Angelika;Uwe

Die Wiederholungsgruppe wird über die Klausel PARTITION BY <COLUMN> angegeben, die Sortierung innerhalb der Liste über die Klausel ORDER BY <COLUMN>. In der Funktion LISTAGG bestimmt die erste Angabe die Spalte, die als Liste zusammengefasst werden soll, die zweite Angabe das Trennzeichen.