2012-07-20

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@

2012-07-18

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

2012-07-12

Manchmal ist char kürzer als char(xxx)

Aufgabe: Mit Hilfe einer einer externen Tabelle sollen Daten aus einer csv-Datei mit Oracle Warehouse Builder eingelesen und weiter verarbeitet werden.

Damit die ohne Probleme verarbeitet werden können und es zu keinen Fehlern mit falschen Formatmasken kommt wird die Tabellenstruktur folgendermaßen angelegt:

-- Create table
create table IMPORT_SRC
(
  id         VARCHAR2(4000),
  datum VARCHAR2(4000),
  daten VARCHAR2(4000)
)
organization external
(
  type ORACLE_LOADER
  default directory MOD_CSV_LOCATION
  access parameters
  (
    RECORDS DELIMITED BY '\r\n'
    CHARACTERSET WE8MSWIN1252
    STRING SIZES ARE IN BYTES
    NOBADFILE
    NODISCARDFILE
    NOLOGFILE
    SKIP 1
    FIELDS
      TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"' AND '"'
      NOTRIM
      (
        "ID" CHAR,
        "DATUM" CHAR,
        "DATEN" CHAR      )

  )
  location (MOD_CSV_LOCATION:Import.csv')
)
reject limit UNLIMITED;

 

Problem: Es werden nicht alle Zeilen der Datei verarbeitet. Auch ein SQL-Statement auf die Externe Tabelle liefert von den 10 sich in der Datei befindeten Zeilen nur 8 zurück. Beim betrachten der Datei sticht hervor, dass in 2 Zeilen die Werte in der Spalte für Daten deutlich länger sind als in den anderen Zeilen.

Lösung: Nach einigem Testen und Modifizieren der Datei stellt sich raus, dass die magische Grenze bei 255 Zeichen liegt. Sobald die Länge diesen Wert übersteigt, wird die Zeile nicht mehr verarbeitet.

Abhilfe schafft hier die Definition der Externen Tabelle mit einer Angabe für die maximale Spaltenlänge. So greift die Einschränkung der Länge nicht mehr und die Daten können verarbeitet werden.

-- Create table
create table IMPORT_SRC
(
id VARCHAR2(4000),
datum VARCHAR2(4000),
daten VARCHAR2(4000)
)
organization external
(
type ORACLE_LOADER
default directory MOD_CSV_LOCATION
access parameters
(
RECORDS DELIMITED BY '\r\n'
CHARACTERSET WE8MSWIN1252
STRING SIZES ARE IN BYTES
NOBADFILE
NODISCARDFILE
NOLOGFILE
SKIP 1
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
NOTRIM
(
"ID" CHAR,
"DATUM" CHAR,
"DATEN" CHAR (1000) )
)
location (MOD_CSV_LOCATION:Import.csv')
)
reject limit UNLIMITED;

 

Lesson learned: Manchmal ist char kürzer als char(xxx)

2012-07-03

asmlib: create disk schlägt fehl


Bei manchen Installationen scheint der folgende Befehl nicht zu funktionieren:
     > service oracleasm createdisk "ORADATA01" "/dev/sdc"
(Ausgabe: FAILED, Hinweis: Device "..." is not a partition)
 
Im Logfile /var/log/oracleasm findet man dann einen Hinweis, dass das Device keine Partition hat. Dieser Hinweis ist auch richtig, aber die Platte hat bewusst keine Partition und das sollte auch ohne gehen. (Andere Ursache kann auch ein BUG in EMC Powerpath sein.)

In diesem Fall hilft dann:
     > asmtool -C -l /dev/oracleasm -n "ORADATA01" -s "/dev/sdc" -a force=yes
Auch hier kommt eine Fehlermeldung, der Fehler wird aber ignoriert und die Platte mit dem Label "ORADATA01" versehen. In ASM läuft dann alles normal.

2012-07-02

Anwendungsmigration - Besser ein Blick auf‘ s Kleingedruckte

Auf der DOAG Development am 14. Juni 2012 hat Stephan La Rocca einen Vortrag mit dem Titel "Anwendungsmigration - Besser ein Blick auf‘ s Kleingedruckte" gehalten. Schauen Sie sich hier die Präsentation an!

Abstrakt:
Begeben sie sich im Internet auf die Suche nach Unterstützung für eine Migration von Oracle Forms, werden ihnen mehr als zweieinhalb Millionen Seiten angeboten. Eine Migration von Oracle Reports bringt gerade mal etwas mehr als 400.000 Fundstellen. Die Treffermenge mag ein Maßstab für die Bedeutung aber auch für den Fokus von Migrationsprojekten wiederspiegeln. Vielfach wird das Thema „Print-Services“ erst im Laufe oder gar am Ende des Migrationsprojektes betrachtet, weil es ja „nur“ darum geht, die Daten auf das Papier zu bringen.

Wenn auch der Ansatz „Reporting as a Service“ in einem Unternehmen sich noch nicht hat vollständig etablieren können, so übernimmt doch der Oracle Report Server bereits heute wesentlich mehr Aufgaben, als „nur“ Papier zu bedrucken. Er ist fester Bestandteil und nicht nur Ende einer Geschäftsprozesskette. Neben der kritischen Verfügbarkeit des Services (z.B. für Barcodes in der Logistik oder Produktion), werden häufig weitere Prozessschritte angestoßen, Schnittstellen befüllt, externe Systeme mit Dateien beliefert, etc.

Die Präsentation  "Anwendungsmigration - Besser ein Blick auf‘ s Kleingedruckte" beleuchtet den Weg, Oracle Reports auf den Oracle BI-Publisher zu migrieren und den BI Publisher Server als zentrales Reportingwerkzeug in einer heterogenen Applikationslandschaft zu integrieren. Dazu gehören Aufgaben, den Service in Forms, ADF oder auch Apex einzubinden, und sinnvoll die Prozesslogik, die vielfach in Report-Triggern implementiert ist, zu migrieren. In einer Gegenüberstellung werden die typischen Szenarien aus Oracle Reports im BI Publisher aufgezeigt. Abschließend sollen die Vorteile beleuchtet werden, die eine zusätzliche Motivation für einen Wechsel von Reports auf BI Publisher aufzeigen sollen.