JasperReports und REF_CURSOR

In vielen Reports zu JasperReports Migrationsprojekten werden die Daten per REF_CURSOR dem Report zur verfügung gestellt.

Wie diese Cursor in JasperReports verwendet werden möchte ich hier zeigen:

1. Die Beispiel-Routine in der Datenbank:

CREATE OR REPLACE PROCEDURE emps_holen(emp_cursor OUT sys_refcursor,anzahl in number) IS
BEGIN 
  OPEN emp_cursor FOR 
    SELECT first_name,last_name,email 
    FROM employees 
    WHERE rownum < anzahl; 
END; 

2. Report anlegen

als Platzhalter ein select * from dual

und speichern (Finish)

3. REF_CURSOR Aufruf einfügen

Wieder den Query-Editor öffnen 

Language auf plsql umstellen und folgenden Code hinzufügen:

{
  call emps_holen($P{ORACLE_REF_CURSOR},$P{anzahl})
}

Danach wieder den Dialog schließen

und neu öffnen. Durch das Schließen wird der Parameter automatisch angelegt. 

Hinweis: Wenn man den Parameter von Hand anlegt, kommt beim Schließen eine  Meldung, dass man einen eindeutigen Parameternamen eingeben soll. Um die Meldung zu vermeiden das Schließen und wieder Öffnen.

Nun können über Read Fields die Felder eingelesen werden.

Zur Demonstration wie man mit Parametern für die PL/SQL-Funktion umgeht legen wir den Zusätzlichen Parameter anzahl an, der an die PL/SQL-Prozedur übergeben wird.

Dann schließen wir den Query-Editor.

4. Report erstellen

Hier die Felder

(Wunderschönen) Report erstellen

Ausführen

Fertig!

TAKE Four - Amazon Echo (Alexa) mit PL/SQL

Es ist wieder soweit, TEAM lädt zum vierten TAKE (TEAM After Work Knowledge). Wer sich schon immer Oracle Datenbank-orientiert mit dem Thema "Sprachgesteuerte Anwendungen mit Amazon Echo" auseinandersetzen oder generell einen Einblick in das Erstellen von Amazon Anwendungen (Skills) erhalten wollte ist hier genau richtig. Das Beste daran? Es ist vollkommen kostenfrei und sie dürfen selbst Hand an legen.

Was erwartet Sie genauer?

Lassen Sie sich schon vorab mit diesem Video von Wolf G. Beckmann in das Thema Amazon Echo (Alexa) mit PL/SQL einführen.

 

Mit Amazon Echo steuern Sie über die Spracherkennung Alexa Ihr Smart Home und alle damit verbundenen Geräte. Amazon Echo lässt sich aber auch für das eigene Unternehmen einsetzen – speziell mit der Oracle Datenbank-Welt.

 

Bei TAKE four schreiben wir einen Skill in PL/SQL, da Amazon Echo hervorragend und direkt mit der Oracle-Datenbank zusammen arbeitet.

 

Lassen Sie sich die Chance für Ihr Business nicht entgehen: Zur Agenda und Anmeldung

 

TAKE four – „Amazon Echo (Alexa) mit PL/SQL"
Dienstag, 28.03.17 bei TEAM in Paderborn
Beginn: 17:00 Uhr | Ende: 19:30 Uhr

Bitte bringen Sie zur Veranstaltung Ihren eigenen Laptop mit.

 

Wir freuen uns auf Ihr Kommen zu TAKE four!

PL/SQL Performancebremsen mit dem hierarchischen Profiler finden

Ein paar scheinbar unbedeutende Anpassungen in einer PL/SQL Prozedur, in einer weiteren nur ein neues, im „Trockentest“ rasend schnelles SQL Query hinzugefügt, noch eben ein kleiner Testdurchlauf auf dem Entwicklungssystem: Funktioniert, ist auch schnell genug. Also können die Erweiterungen in den Ladeprozess des Datawarehouse-Testsystems eingespielt werden. Was aber tun, wenn der veränderte Code auf dem Testsystem mit einem deutlich größeren Datenvolumen nun eben nicht mehr schnell genug ist und den vorher performanten Ladelauf ausbremst?

Anstatt jetzt sofort alle Code-Änderungen in Frage zu stellen, gibt es eine recht einfache Möglichkeit, zumindest das PL/SQL-Unterprogramm, dass für die Performanceverschlechterung verantwortlich ist, oder sogar die Zeile, in der ein langsamer SQL-Befehl steht, zu identifizieren: Der hierarchische Profiler im DBMS_HPROF-Package, das seit der Version 11g Release 1 in der Oracle-Datenbank enthalten ist! Dieser Profiler speichert das dynamische Ausführungsprofil von PL/SQL-Programmen und deren Unterprogrammen und lässt so die Analyse von z.B. Anzahl der Aufrufe und in einzelnen Unterprogrammen verbrauchter Zeit zu. Dieser Blogeintrag soll einen kleinen Einblick in die Möglichkeiten des Profilers geben.

Um das DBMS_HPROF-Package zu nutzen, müssen zunächst einige Voraussetzungen mit DBA-Rechten geschaffen werden:

1. Ein Oracle-Directory zum Ablegen einer Tracedatei durch DBMS_HPROF anlegen. Der im Beispiel verwendete Name profiler ist dabei beliebig gewählt.

CREATE OR REPLACE DIRECTORY profiler       AS '/home/oracle/profiler';

2. Lese- und Schreibrechte auf dem Verzeichnis vergeben, im Beispiel an den Benutzer dwh_target.

GRANT READ, WRITE ON DIRECTORY profiler TO dwh_target;

3. Zu guter Letzt muss noch das Privileg zum Ausführen des DBMS_HPROF-Package vergeben werden.

GRANT EXECUTE ON DBMS_HPROF TO dwh_target;

Das Profiling wird, z.B. in einem anonymen PL/SQL-Block, mit DBMS_HPROF.START_PROFILING (unter Angabe von Ziel-Directory und Ziel-Datei) gestartet und mit DBMS_HPROF.STOP_PROFILING wieder angehalten. Im Beispiel wird die Prozedur fehlerpruefung aus dem Package dq_check_pkg aufgerufen. Diese Prozedur ruft, wie später zu sehen sein wird, weitere Unterprogramme auf.

BEGIN       -- Profiling starten unter Angabe von Ziel-Directory und -Datei       DBMS_HPROF.START_PROFILING('PROFILER', 'profiler.log');          -- Aufruf der zu untersuchenden PL/SQL-Unterprogrammstruktur           dq_check_pkg.fehlerpruefung;          -- Profiling stoppen       DBMS_HPROF.STOP_PROFILING;    END;

Die dabei entstandene Datei kann man sich mit einem Texteditor anschauen.

P#V PLSHPROF Internal Version 1.0

P#! PL/SQL Timer Started

P#C PLSQL."DWH_TARGET"."DQ_CHECK_PKG"::11."__pkg_init"

P#X 5

P#R

P#C PLSQL."DWH_TARGET"."DQ_CHECK_PKG"::11."FEHLERPRUEFUNG"#980980e97e42f8ec #2130

P#X 26

P#C SQL."DWH_TARGET"."DQ_CHECK_PKG"::11."__static_sql_exec_line2147" #2147

P#X 89

P#R

P#X 14

P#C SQL."DWH_TARGET"."DQ_CHECK_PKG"::11."__static_sql_exec_line2153" #2153

P#X 31496559

P#R

P#X 22

P#C SQL."DWH_TARGET"."DQ_CHECK_PKG"::11."__static_sql_exec_line2157" #2157

P#X 9645

P#R

P#X 6

P#C PLSQL."DWH_TARGET"."DQ_CHECK_PKG"::11."FEHLERPRUEFUNG.C_SENDUNGEN"#980980e97e42f8ec #2140

P#X 22

P#C SQL."DWH_TARGET"."DQ_CHECK_PKG"::11."__static_sql_exec_line2141" #2141

P#X 937

P#R

P#X 2

.

.

.

P#R

P#C PLSQL."SYS"."DBMS_HPROF"::11."STOP_PROFILING"#980980e97e42f8ec #59

P#R

P#! PL/SQL Timer Stopped

Das Zeilenprefix beschreibt dabei den Inhalt der jeweiligen Zeile:

P#V: Startzeile mit Versionsinformation

P#C: Unterprogrammaufruf

P#R: Rückkehr / Rückgabe vom Unterprogramm

P#X: Zwischen vorherigem und folgendem Ereignis verstrichene Zeit (Mikrosekunden)

P#!: Kommentarzeile

Eine Aufrufzeile ist wie folgt aufgebaut:

Die Analyse der Ausführung mag für kleine Programmstrukturen noch anhand dieser Tracedatei möglich sein, je größer die Datei jedoch wird, desto schwieriger wird es auch, grundsätzliche Informationen auf einen Blick zu erkennen. In einem solchen Fall hilft wieder DBMS_HPROF weiter, denn das Package hält eine weitere nützliche Funktion bereit: DBMS_HPROF.ANALYZE. Mit dieser lassen sich die Ergebnisse aus der Tracedatei aufbereitet in Datenbanktabellen abspeichern.

Bevor die Analyse beginnen kann, muss noch die entsprechende Tabellenstruktur angelegt werden. Ein Script, das diese Aufgabe übernimmt, liegt unterhalb des Datenbank-Home-Verzeichnisses und kann z.B. mit SQL*Plus aufgerufen werden:

   >sqlplus user/passwort@connect_id @$ORACLE_HOME/rdbms/admin/dbmshptab.sql

Dann wird der Inhalt der Tracedatei analysiert, z.B. mit folgenden Code:

DECLARE       runid NUMBER;    BEGIN       runid := DBMS_HPROF.ANALYZE(LOCATION => 'PROFILER',                                           FILENAME => 'profiler.log');       DBMS_OUTPUT.PUT_LINE('runid = ' || runid);    END;

Mit der RUNID, die zurückgegeben wird, lassen sich in den Tabellen die zum jeweiligen Analysedurchlauf gehörenden Datensätze identifizieren. Die Ergebnisse sind in den folgenden drei Tabellen zu finden:

DBMSHP_RUNS: Allgemeine Informationen über den Analysedurchlauf

RUNID:             ID des Analysedurchlaufs

RUN_TIMESTAMP:     Zeitstempel des Analysedurchlaufs

TOTAL_ELAPSED_TIME:Dauer des Analysedurchlaufs in Mikrosekunden

RUN_COMMENT:       Kommentar, den man dem ANALYZE optional übergeben kann

DBMSHP_FUNCTION_INFO: Informationen über Unterprogramme

RUNID:                ID des Analysedurchlaufs

SYMBOLID:             Eindeutige ID für dieses Unterprogramm in diesem Analysedurchlauf

OWNER:                Besitzer des Moduls, in dem das Unterprogramm definiert ist

MODULE:               Modul, in dem das Unterprogramm definiert ist

TYPE:                 Typ des Moduls, in dem das Unterprogramm definiert ist

FUNCTION:             Name des Unterprogramms

LINE#:                Zeilennummer, an der das Unterprogramm im Modul definiert ist

HASH:                 MD5 Hash für die Signatur des Unterprogramms

NAMESPACE:            Namespace des Unterprogramms (z.B. SQL oder PLSQL)

SUBTREE_ELAPSED_TIME: Im Unterprogramm und dessen Unterprogrammen verbrauchte Zeit

FUNCTION_ELAPSED_TIME:Im Unterprogramm verbrauchte Zeit

CALLS:                Anzahl der Aufrufe dieses Unterprogramms

DBMSHP_PARENT_CHILD_INFO: Informationen über Eltern-Kind-Aufrufe

RUNID:                ID des Analysedurchlaufs

PARENTSYMID:          Eltern-SYMBOLID (DBMSHP_FUNCTION_INFO)

CHILDSYMID:           Kind-SYMBOLID (DBMS_FUNCTION_INFO)

SUBTREE_ELAPSED_TIME: Im Unterprogramm und dessen Unterprogrammen beim Aufruf durch dieses Eltern-Element verbrauchte Zeit

FUNCTION_ELAPSED_TIME:Im Unterprogramm beim Aufruf durch dieses Eltern-Element verbrauchte Zeit

CALLS:                Anzahl der Aufrufe dieses Unterprogramms durch dieses Eltern-Element

Verknüpft man nun die Tabellen DBMSHP_PARENT_CHILD_INFO und DBMSHP_FUNCTION_INFO in einem Query miteinander, kann man über entsprechende Filterbedingungen einzelne Unterprogrammaufrufstrukturen gezielt analysieren und erhält Ergebnisse, mit denen imperformante Unterprogramme und sogar Zeilen mit imperformantem SQL-Code identifiziert werden können:

SELECT pfi.module PARENT_MODULE,           pfi.function PARENT_FUNCTION,           pfi.namespace PARENT_NAMESPACE,           cfi.module CHILD_MODULE,           cfi.function CHILD_FUNCTION,           cfi.namespace CHILD_NAMESPACE,           pci.subtree_elapsed_time/1000000 SUBTREE_TIME_S,           pci.function_elapsed_time/1000000 FUNCTION_TIME_S,           pci.calls CALLS      FROM dbmshp_parent_child_info pci,           dbmshp_function_info pfi,           dbmshp_function_info cfi     WHERE pci.parentsymid = pfi.symbolid       AND pci.childsymid = cfi.symbolid       AND pci.runid = 1     START WITH pfi.function = 'FEHLERPRUEFUNG'           CONNECT BY PRIOR pci.childsymid = pci.parentsymid;

Beschäftigt man sich etwas mit den Möglichkeiten und Informationen, so lassen sich auch komplexere PL/SQL-Aufrufstrukturen relativ einfach analysieren.

PL/SQL - Eine Liste von Tokens aus einem Text mit Hilfe von Regular Expressions erstellen

Problem:

Ein Text soll in PL/SQL nach Auftreten von Ausdrücken durchsucht werden. Als Ergebnis sollen die Treffer als Collection zurückgeben werden.

Lösung:

Der hier vorgestellte Lösungsansatz basiert auf der Verwendung von Regular Expressions. Wir nutzen eine Nested Table zum Sammeln der gefundenen Token und nutzen diesen Typ auch als Rückgabe unserer Splitfunktion.
Mit der Standard-PL/SQL-Funktion regexp_substr lassen sich Bereiche aus einem String herauslesen:

REGEXP_SUBSTR(    source_string,    pattern [,    position [,    occurrence [,    match_parameter ]]] )

source_string:       Zu untersuchender Text pattern:                 regular expression position:                Position in source_string, ab der gesucht werden soll occurence:            Nr. des Tokens, welches als Ergebnis zurückgeben                             werden soll match_parameter:  kann eine beliebige Kombination der folgenden                              Angaben sein:                                    i: Groß-/Kleinschreibung wird ignoriert                                   c: Groß-/Kleinschreibung wird beachtet                                   n: Punkt (.) im Pattern gibt an, dass neue                                       Zeilen ebenfalls beachtet werden sollen                                   m: ^ und $ markieren den Anfang bzw.                                       das Ende einer Zeile bei Multi-Line Strings

Des Weiteren wird die Funktion regexp_instr verwendet, die die Position (Anfang oder Ende) eines Teilstrings ermittelt:

REGEXP_INSTR(    source_string,    pattern [,    position [,    occurrence [,    return_option[,    match_parameter ]]]] )

source_string:       Zu untersuchender Text pattern:                regular expression position:               Position in source_string, ab der gesucht werden soll occurence:           Nr. des Tokens, welches als Ergebnis zurückgeben                            werden soll return_option:      0, wenn die erste Position eines gefundenen Tokens                                zurückgegeben werden soll;                            1, wenn die Position nach dem gefundenen Token                                ausgegeben werden soll match_parameter: kann eine beliebige Kombination der folgenden                             Angaben sein:                                    i: Groß-/Kleinschreibung wird ignoriert                                   c: Groß-/Kleinschreibung wird beachtet                                    n: Punkt (.) im Pattern gibt an, dass neue                                       Zeilen ebenfalls beachtet werden sollen                                   m: ^ und $ markieren den Anfang bzw. das                                       Ende einer Zeile bei Multi-Line Strings

Im Folgenden werden hier zwei Lösungswege vorgestellt. In der ersten Lösung (reg_split(p_string, p_pattern)) wird der String über ein CONNECT-BY-SELECT-Statement mehrfach untersucht. Dabei wird die LEVEL-Variable dazu genutzt den Occurence-Parameter zu füllen.
Die zweite Lösung (reg_split_rec(p_string, p_pattern, p_pos)) verwendet einen rekursiven Ansatz, bei dem der nächste verfügbare Startpunkt an die nächste Rekursionsebene übergeben wird.

IMPLEMENTIERUNG ALS ANONYMER PL/SQL-BLOCK: ========================================== declare    type nt_tab_vc2 is table of varchar2(4000);    v_return nt_tab_vc2;    function reg_split(p_string in varchar2,                        p_pattern in varchar2)    return nt_tab_vc2 is       v_ret nt_tab_vc2;    begin       v_ret := nt_tab_vc2();       select          regexp_substr(p_string, p_pattern, 1, level) token       bulk collect into          v_ret       from          dual       where          regexp_substr(p_string,                         p_pattern,                         1,                        level,                        'i') is not null       connect by          regexp_instr(p_string,                        p_pattern,                        1,                        level) > 0;       return v_ret;    end;    function reg_split_rec(p_string in varchar2,                            p_pattern in varchar2,                           p_pos in number default 1)    return nt_tab_vc2 is       v_ret nt_tab_vc2;       v_token varchar2(4000);       v_pos_next number;    begin       v_token := regexp_substr(p_string,                                 p_pattern,                                 p_pos,                                 1);       if v_token is not null then          --pattern is found          v_pos_next := regexp_instr(p_string,                                      p_pattern,                                      p_pos,                                     1,                                     1);          v_ret := reg_split(p_string,                             p_pattern,                             v_pos_next);          v_ret.extend(1);          v_ret(v_ret.last) := v_token;          return v_ret;       else          --pattern is not found          return nt_tab_vc2();       end if;   end; begin    v_return := reg_split(                     'Hello @world@! The weather is @great@ today.',                      '(\@)([a-z-]\w+)(\@)');    if v_return.count > 0 then       for i in v_return.first .. v_return.last       loop          dbms_output.put_line(lpad(i,                                     round(log(10,v_return.count))+1)||' '||v_return(i));       end loop;    end if; end; BEISPIEL-AUSGABE: ================= 1 @world@ 2 @great@

WM_CONCAT? Ab 11gR2 dann doch lieber LISTAGG

Ausgangssituation:

Als SQL-Programmierer steht man häufig vor der Aufgabenstellung Werte aus mehreren Spalten in einer einzelnen Spalte darzustellen. Als Beispiel soll hier mal das bekannte Oracle HR-Schema mit der Tabelle Employees herhalten.

Ein einfacher Select auf die Tabelle liefert die Mitarbeiter mit ihrer Department-ID

select e.department_id, e.last_name from employees e;

DEPARTMENT_ID LAST_NAME
------------- --------------------------------------------------------------------------------
           90 King
           90 Kochhar
           90 De Haan
           60 Hunold
           60 Ernst
           60 Austin
           60 Pataballa
           60 Lorentz
          100 Greenberg
          100 Faviet
          100 Chen
          100 Sciarra
          100 Urman
          100 Popp
           30 Raphaely
           30 Khoo
           30 Baida
           30 Tobias
           30 Himuro
           30 Colmenares

……………………………….

Ist die jetzt die Aufgabenstellung zu jedem Department die Mitarbeiter in einer Spalte darzustellen wir sehr häufig die nicht dokumentierte Oracle-Funktion wm_concat benutzt. Mit dieser Funktion lässt sich folgendes Ergebnis erzielen:

select e.department_id, wm_concat(e.last_name) name from employees e
group by e.department_id

DEPARTMENT_ID NAME
------------- --------------------------------------------------------------------------------
           10 Whalen
           20 Hartstein,Fay
           30 Raphaely,Colmenares,Himuro,Tobias,Baida,Khoo
           40 Mavris
           50 Weiss,Grant,OConnell,Feeney,Walsh,Jones,McCain,Everett,Bell,Perkins,Gates,Dilly,
           60 Hunold,Lorentz,Pataballa,Austin,Ernst
           70 Baer
           80 Russell,Johnson,Livingston,Taylor,Hutton,Abel,Kumar,Bates,Smith,Fox,Bloom,Ozer,B
           90 King,De Haan,Kochhar
          100 Greenberg,Popp,Urman,Sciarra,Chen,Faviet
          110 Higgins,Gietz
                Grant

Das Problem hierbei ist allerdings, dass es sich um eine nicht dokumentierte Oracle-Funktion handelt und Oracle sich das Recht vorbehält Änderung an solchen Funktionen vorzunehmen ohne diese zu publizieren.

Genau dieses ist auch der Fall bei der Funktion wm_concat. Bis zu den Datenbank-Versionen 10.2.0.4 und 11.2.0.1 war der Rückgabe-Wert vom Typ varchar2. Ab den Versionen 10.2.0.5 und 11.2.0.2 ist es ein clob.

Die Auswirkungen bei einem Upgrade der Datenbank sollten den meisten bekannt sein. (invalide Views,….).

Hier die passende Oracle  Support Eintrag:

WM_CONCAT- Oracle Support

 

Lösung:

Um diese Problematik zu umgehen, empfiehlt es sich ab der Datenbank Version 11gR2 die analytische Funktion LISTAGG einzusetzen. Sie bietet nicht nur den kompletten Funktionsumfang von WM_CONCAT, sondern ist auch wesentlich schneller.

 

SELECT e.department_id, LISTAGG(e.last_name, ',') WITHIN GROUP (ORDER BY e.last_name) emps
FROM   employees e
GROUP BY e.department_id;

 

DEPARTMENT_ID EMPS
------------- --------------------------------------------------------------------------------
           10 Whalen
           20 Fay,Hartstein
           30 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
           40 Mavris
           50 Atkinson,Bell,Bissot,Bull,Cabrio,Chung,Davies,Dellinger,Dilly,Everett,Feeney,Fle
           60 Austin,Ernst,Hunold,Lorentz,Pataballa
           70 Baer
           80 Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Cambrault,Doran,Errazuriz,Fox,Gr
           90 De Haan,King,Kochhar
          100 Chen,Faviet,Greenberg,Popp,Sciarra,Urman
          110 Gietz,Higgins
                Grant

 

Exkurs:

Erweitert man das SQL-Statement um ein PARTITION BY ist es sogar möglich Werte der Einzeldatensätze kombiniert mit den zusammengefassten Daten in einer Zeile darzustellen:

SELECT department_id Dept,
       hire_date Datum,
       last_name Name,
       LISTAGG(last_name, ', ')
           WITHIN GROUP (ORDER BY hire_date, last_name)
                   OVER (PARTITION BY department_id) as Emp_list
FROM employees
ORDER BY Dept, Datum, Name;

 

DEPT DATUM NAME EMP_LIST

----- ----------- -------------------------------------------------------------------------------- ---------------------------------------------------------------

10 17.09.1987 Whalen Whalen

20 17.02.1996 Hartstein Hartstein, Fay

20 17.08.1997 Fay Hartstein, Fay

30 07.12.1994 Raphaely Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares

30 18.05.1995 Khoo Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares

30 24.07.1997 Tobias Raphaely, Khoo, Tobias, Baida, Himuro, Colmenares
.
.
.

50 17.10.1995 Rajs Kaufling, Ladwig, Rajs, Sarchand, Bell, Mallin, Weiss, Davies, Marlow, Bull, Eve

     24.05.1999 Grant Grant

107 rows selected

Download: team-vertumnus-sample

In unserem Artikel: “Migration von Forms über APEX zu Oracle ADF” der in den DOAG News veröffentlich wurde hat sich leider ein falscher Download eingeschlichen. Das dort angegebene Sample gibt es hier:  team-vertumnus-sample

Hinter dem nächsten Bild versteckt sich ein kleines Viewlet, dass zeigt wie man mit dem von uns beschriebenen Ansatz eine entsprechende PL/SQL Table API erzeugen kann.