Browsing "Older Posts"

Das war 2015 und nun?

Von Tobias Arnhold → 12.20.2015
In meinem letzten Blogpost für dieses Jahr, möchte ich noch einmal kurz auf alle Highlights in 2015 eingehen. Ich wünsche viel Spaß beim Revue passieren lassen! :)

  1. Das war 2015
    1. APEX 5.0
    2. APEX Webseitenupdate
    3. APEX CONNECT
    4. APEX SUMMERSCHOOL
    5. APEX Competition
    6. APEX Community Webseite
    7. DOAG Konferenz
    8. Abräumer des Jahres
    9. Ausblick auf 2016

APEX CONNECT 2016 und das HH24:MI Dilemma

Von Tobias Arnhold → 12.12.2015
Als ich das Vortragsprogramm der APEX CONNECT 2016 gelesen habe, musste ich mich doch kurz wundern...

APEX 5 Migration - Part 2 - Common application issues after the migration

Von Tobias Arnhold → 12.10.2015
This time I will tell you more about the most common problems in your APEX applications after an upgrade to APEX 5.

You will find the most migration bugs in following situations:

Generierung von Bitlisten mit Hilfe von SQL am Beispiel einer Datum zu Monat Konvertierung

Von Tobias Arnhold → 12.09.2015
Ich hatte vor kurzem die Aufgabe erhalten eine Bitliste auf Basis eines Monats zu generieren.
Bedeutet, ich habe einen String mit 31 Zeichen der je Zeichen den Zustand 1 oder 0 einnehmen kann.
 - 1 steht für aktiv
 - 0 steht für inaktiv

Beispieldaten:
'0000000001010001000000000000000'
'0000000000000000111111000011010'

Um dies anhand eines verständlichen Beispiels zu verifizieren, habe ich mir eine Dienstplan-Tabelle ausgedacht.

APEX 5 Migration - Part 1 - Requirements and Installation

Von Tobias Arnhold → 12.03.2015
During this year I have done several APEX 5 migrations and I held three lectures on the topic.
Unfortunately it only reached out in Germany, however the content would contribute to simplifying the work for everyone. So I decided to blog it. I will cover this topic in three different posts:
- Requirements and Installation
- Common issues after the migration
- Universal Theme migration

I start with the requirements and installation of APEX 5.
The most of the content at this part is described in the APEX documentation and I will shorten it to the most important parts.

Oracle JET Charts - Do we need alternatives anymore?

Von Tobias Arnhold → 11.11.2015
After I looked through the chart types supported by the new Oracle JET framework I asked myself if we need other chart plugins like D3JS or RaphaelJS in future APEX releases anymore?

The answer is: Yes in special cases.

My supposition is that we can use all available JET charts in one of the next versions of Oracle APEX.

Now I will describe the "special case" with two examples where other charting frameworks still make sense:

All JET charts in APEX 5.1

Von Tobias Arnhold → 11.06.2015
I added a new feature request for APEX:
APEX 5.1 with support for all available JET Data Visualizations Charts

Description:
APEX 5.1 will include a new charting engine based on the new Javascript Framework "Oracle JET". Unfortunately APEX 5.1 will only include those charts from the JET website (http://www.oracle.com/webfolder/technetwork/jet/uiComponents-dataVisualizations.html) which are supported in the current version of APEX. For example the "Spark Chart" or the "Timeline" will not be part of APEX 5.1 This request has the purpose that APEX 5.1 will support every single available JET chart.

Available Charts:

If you like APEX and want to have more default charting functionality in the future then please support my request:
Feature AM06 - APEX 5.1 with support for all available JET Data Visualizations Charts


SQL: Calculate the past time between two dates in percent

Von Tobias Arnhold →
Just a simple example how easy APEX can handle this kind of problem.

Example:
We have today the 06.11.2015 (11-06-2015) and we have two date values 01.11.2015 and 30.11.2015.
Now I want to know how much time has past in percent since the beginning (01.11.2015):
Result: 17 %

Kostenpflichtige Erweiterungen für APEX

Von Tobias Arnhold → 11.04.2015
Bei dem letzten APEX Meetup in Frankfurt kam eine Frage zur Erweiterbarkeit von APEX auf.
Hierbei ging es darum zu klären was sich besser eignet:
  - Kaufsoftware
  - Opensource Software
  - Eigenentwicklung

Die Frage was sich besser eignet hängt von unterschiedlichen Faktoren ab, die ich nicht Heute im Detail erklären werde.

Ziel im heutigen Blogpost ist es, eine vollständige Liste an möglicher Kaufsoftware Erweiterungen für APEX aufzuzeigen, ohne eine Wertung der jeweiligen Software Lösung vorzunehmen.

Pager Designer Plugin Xplug

Von Tobias Arnhold → 11.03.2015
The new APEX Page Designer is a great tool in doing your development work faster then ever. Actually I only discovered two more or less annoying things so far.

1. Doing "mouse miles" from the left side to the right side.
The new Grid Layout takes much space especially on the big screens. When you access an item on the left side you need to move your mouse to the right and edit the stuff you need to change.
So why having the right side not directly beside the left side?
A new APEX star on the horizon "Filip Van Vooren" has created a nice plugin called Xplug and this one is moving the Grid Layout to the right side. The plugin is available for Chrome and Firefox.


I guess this will only be a temporarily solution because in my opinion APEX 5.1 will include a customization part inside the Page Designer where you can do this kind of stuff.

2. Switch between next and previous pages
Currently we don't have the Previous/Next page buttons in the page designer. Luckily Filip also fixed this in Xplug.

APEX 4.2

APEX 5 - Page Designer

APEX 5 - Page Designer with Xplug


Imho:
Give it a try and delete it if you don't like it.

Was ist Oracle JET

Von Tobias Arnhold →
Oracle JET ist das neueste Oracle Development Werkzeug mit dem Ziel moderne Webapplikationen auf Basis von Javascript zu bauen.

Die Grundlage dafür bildet ein eigens dafür entwickeltes JavaScript Development Framework das zusätzlich um mehrere mehr oder weniger bekannte OpenSource JS Frameworks (jQuery, RequireJS, Knockout und weitere) erweitert wurde.

Witzigerweise scheint Oracle JET nicht allein zu sein, denn die Abkürzung bezeichnet innerhalb des Oracle Toolsets zwei unterschiedliche Technologien.
Das neue Oracle JET (JavaScript Extension Toolkit) und das bestehende technisch komplett andere Oracle JET (Jumpstart Enterprise Toolkit).
Imho: Etwas unglücklich gelöst, da dadurch sehr viel Konfusion erzeugt wird. :)

Die JET Technologie richtet sich an erfahrene WEB Anwendungsentwickler die Erfahrung in Javascript besitzen müssen.

Fragen zur Lizenzierung werden hier beantwortet: Oracle JET FAQ

Oracle JET und APEX
Soweit mir bekannt, wird Oracle JET die neue Standard Charting Engine in APEX 5.1.  Wer sehen möchte wie diese Charts in Realität ausschauen, der findet hier sein Glück: Oracle JET Data Visualizations.
Außerdem können Oracle JET Lösungen in APEX Plugins verpackt werden und wären so leicht zugänglich für APEX Entwickler.

Weiterführende Links zum Thema:
Was ist JET I
Was ist JET II
Get Started
Elemente in JET
Demo Seite
Verwendete OpenSource Engines

Ps.: Auf der DOAG habe ich bisher keine Vorträge zu gesehen. Ich denke erste Erfahrungsberichte wird es auf der APEX Connect geben.

Page Designer bug in combination with APEX plugin settings

Von Tobias Arnhold → 10.28.2015
Yesterday evening I struggled with a strange issue inside the Page Designer.

One APEX application created on each page of the Page Designer the following javascript parsing error:

Error: parsererror - SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data

It only happened in 1 of several applications I currently check after an APEX 5.0.2 migration.

What I did to find the issue (Thanks to Denes for some really good tips):
 - Copied the application into a new ID --> error could be reproduced
 - I dropped every page in the application --> error still occurred
 - My application had only one new empty page (page 1) and I switched to Universal Theme --> error still occurred
 - I checked several "Supporting Objects" and found the issue in one plugin
   - After deleting the plugin the parsing error disappeared
   - Unfortunately this plugin was referenced in 8 pages so I looked deeper into it
 - The issue was a wrongly referenced "Label appearance"



 - After I changed the setting to "- Not Depending -" the Page Designer run as expected

jQuery dialog z-index problem after migrating to APEX 5

Von Tobias Arnhold →
After migrating an APEX 4 application to APEX 5.0.2 I got an issue with the plugin called "Dialog Region".


The modal dialog wasn't accessible because the overlay effect was on top of my dialog.

Luckily some CSS code fixed the issue:

body div.ui-widget-overlay {
    z-index: 1;
}


The issue comes from the plugin in combination with an old theme. Actually it is easy to fix and no reason not to upgrade to APEX 5.0.2. :)

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

Btw.: The return to page function don't work in the "edit page" view.

Custom CSV Export as ZIP file

Von Tobias Arnhold → 10.23.2015
At the beginning of this year I wrote an article about "Custom CSV Export in APEX".

Now I want to extend the solution by an optional ZIP export.

Why?
You can not export several files at the same time with standard APEX features. For that you have to create a ZIP file including all the files you want to download.

I made an example application "Multi CSV Download as ZIP file" where you see a report including all employee columns and the department name. Above the report is a select list where you can filter for departments.

When you select a department and click on EXPORT then you get an CSV file including all employees for the selected department. In case you don't filter you get an ZIP file including a CSV file for each department.



To create the ZIP file I used the AS_ZIP Package created by Anton Scheffer.

Here is code I used to create the CSV/ZIP file.
DECLARE
    -- Blob Conversion Parameter
    L_BLOB           BLOB;
    L_BLOB_FINAL     BLOB;
    L_NAME           VARCHAR2(200); 
    L_NAME_DOWNLOAD  VARCHAR2(200); 
    L_CLOB           CLOB;
    L_DEST_OFFSET    INTEGER := 1;
    L_SRC_OFFSET     INTEGER := 1;
    L_LANG_CONTEXT   INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    L_WARNING        INTEGER;
    L_LENGTH         INTEGER;
    
    
    -- CSV Select
    CURSOR c1 IS
      SELECT
       DNAME,
       '"EMPNO"¡"ENAME"¡"JOB"¡"MGR"¡"HIREDATE"¡"SAL"¡"COMM"' 
         || CHR(13)|| CHR(10) 
         || DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))).EXTRACT('//text()').GETCLOBVAL(),1) AS CLOB_VAL
      FROM  (
            SELECT
              DNAME,
              '"'||EMPNO||'"¡"'||ENAME||'"¡"'||JOB||'"¡"'||MGR||'"¡"'||HIREDATE||'"¡"'||SAL||'"¡"'||COMM||'"' as COL_VALUE
            FROM EMP E
            JOIN DEPT D 
            ON (E.DEPTNO = D.DEPTNO)
            WHERE :P1_DEPT IS NULL
            OR    D.DEPTNO = :P1_DNAME
      ) 
      GROUP BY 
        DNAME
      ;
BEGIN
    if :P1_DNAME is null then
   
      for rec in c1 loop    
        DBMS_LOB.createtemporary(L_BLOB, FALSE);
        L_DEST_OFFSET    := 1;
        L_SRC_OFFSET     := 1;
        L_LANG_CONTEXT   := DBMS_LOB.DEFAULT_LANG_CTX;
        L_WARNING        := null;
      
        -- tranform the input CLOB into a BLOB of the desired charset
        DBMS_LOB.CONVERTTOBLOB( DEST_LOB     => L_BLOB,
                                SRC_CLOB     => rec.CLOB_VAL,
                                AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                                DEST_OFFSET  => L_DEST_OFFSET,
                                SRC_OFFSET   => L_SRC_OFFSET,
                                BLOB_CSID    => NLS_CHARSET_ID('WE8MSWIN1252'),
                                LANG_CONTEXT => L_LANG_CONTEXT,
                                WARNING      => L_WARNING
                              );
    
         L_NAME := 'EXPORT_' || trim(to_char(rec.DNAME)) || '.csv';
    
         AS_ZIP.add1file (  P_ZIPPED_BLOB => L_BLOB_FINAL,
                  P_NAME => L_NAME,
                  P_CONTENT => L_BLOB
                  ) ;  
    
         -- dbms_lob.freetemporary(L_BLOB);
      end loop; 
  
      L_NAME_DOWNLOAD := 'EXPORT_ALL.zip';
      AS_ZIP.finish_zip (  P_ZIPPED_BLOB => L_BLOB_FINAL) ;  
    
    else
      for rec in c1 loop    
        DBMS_LOB.createtemporary(L_BLOB_FINAL, FALSE);
      
        -- tranform the input CLOB into a BLOB of the desired charset
        DBMS_LOB.CONVERTTOBLOB( DEST_LOB     => L_BLOB_FINAL,
                                SRC_CLOB     => rec.CLOB_VAL,
                                AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                                DEST_OFFSET  => L_DEST_OFFSET,
                                SRC_OFFSET   => L_SRC_OFFSET,
                                BLOB_CSID    => NLS_CHARSET_ID('WE8MSWIN1252'),
                                LANG_CONTEXT => L_LANG_CONTEXT,
                                WARNING      => L_WARNING
                              );
    
         L_NAME_DOWNLOAD := 'EXPORT_' || trim(to_char(rec.DNAME)) || '.csv';
    
         exit;
      end loop; 
    end if;

    -- determine length for header
    L_LENGTH := DBMS_LOB.GETLENGTH(L_BLOB_FINAL);  

    -- first clear the header
    htp.flush;
    htp.init;

    -- create response header
    OWA_UTIL.MIME_HEADER( 'application/zip', FALSE);
    htp.p('Content-length: ' || L_LENGTH);
    htp.p('Content-Disposition: attachment; filename="'||L_NAME_DOWNLOAD||'"');
    htp.p('Set-Cookie: fileDownload=true; path=/');

    OWA_UTIL.HTTP_HEADER_CLOSE;
    -- download the BLOB
    WPG_DOCLOAD.DOWNLOAD_FILE( L_BLOB_FINAL );

    -- stop APEX
    APEX_APPLICATION.STOP_APEX_ENGINE;
  EXCEPTION
    WHEN OTHERS THEN
      if :P1_DNAME is null then
        DBMS_LOB.FREETEMPORARY(L_BLOB);
      end if;
      DBMS_LOB.FREETEMPORARY(L_BLOB_FINAL);
      RAISE;
END;


Die APEX Community gibt Vollgas!

Von Tobias Arnhold → 10.22.2015
Am Montag dem 19.10. wurde getrieben von Jürgen Schuster und weiteren APEX Entwicklern die apex.world Plattform veröffentlicht.


Eine nur von der APEX Community entwickelte und betriebene Plattform, die die Kommunikation und Zusammenarbeit zwischen Entwicklern drastisch vereinfachen und verbessern will.

Die Highlights:
 - eine LIVE Integration von Slack und Twitter
 - Die APEX Job Börse
 - Plug-In Bereitstellung mit Hilfe von GitHub
 - APEX Terminübersicht zu Meetups, Konferenzen und Live Webinars
 - APEX Einsteigerbereich (noch in Arbeit) und eine Link-Zusammenfassung über APEX Themen

Einbringen kann sich ein jeder APEX Entwickler.
Interesse? Hierzu wendet ihr euch am Besten an Jürgen. :)

Imho
Eine Klasse Leistung aller Beteiligten und wieder einmal beweist die Community selbst wie Weiterentwicklung funktioniert.

Was fehlt noch?
Ich persönlich fände eine Integration des APEX Blogaggregators sehr sinnvoll, damit wäre im Prinzip alles beisammen. Eine Integration von Beispielanwendungen wäre ebenfalls eine sinnvolle Erweiterung. Ich allein hätte da eine ganze Menge zu bieten:
http://www.apex-at-work.com/p/beispielanwendungen.html

Neben apex.world gibt es weitere Neuigkeiten rund um APEX zu berichten.


Generell:
 - APEX 5.0.2 steht zum Download bereit. Die Installation sollte in 10 Minuten erledigt sein.

 - Eine Aktualisierung des Statement of Direction für APEX 5.1 wurde veröffentlicht.

 - Die Developer Choice Awards wurden vergeben und Dietmar ist dabei.
   Glückwunsch! Respekt! Absolut Verdient!

 - Die APEX Connect Seite und die Seite der MT AG (APEX only by Material Design) wurden rund erneuert.
   Ps.: Bei der APEX Connect dachte ich Vorträge über SQL Techniken in APEX und Geovisualisierung einzureichen. Was denkt Ihr?

 - Neue APEX Anwendung: livesql.oracle.com 
   Imho: Ab jetzt immer und jederzeit SQL coden!


- Die wichtigste aktuelle Programmiersprache ist: SQL!!!

- Der SQL Developer und der ORDS sind jeweils in neuen Versionen erschienen.

Events die in naher Zukunft jeder kennen sollte:
28.10. Stuttgart - Meetup Stuttgart
30.10. Frankfurt - Meetup Frankfurt
16.11. Nürnberg - APEX Treff vor der DOAG Konferenz 2015
17.11. - 19.11. Nürnberg - DOAG 2015

Seit Juli diesen Jahres gibt es auch ein monatlich stattfindendes Webseminar rund um aktuelle Oracle Themen: tinyurl.com/oradevmonthly
Aktuelle Infos dazu findet ihr auch in Twitter.

Und nächstes Jahr geht es gleich weiter:
Im Januar 2016 starten Karin Patenge, Rainer Willems, Beda Hammerschmidt, Kai Donato, Dietmar Aust und Carsten Czarski - in Zusammenarbeit mit der DOAG eine Online-Videoserie zum Thema Modern Application Development - in der Praxis.

SQL Probleme und das Oracle SQL Forum

Von Tobias Arnhold →
Viele meiner Kollegen (um die 90 %) nutzen bei schwierigen SQL Problemen nicht das SQL Forum, sondern versuchen sich lieber an inperformanten PL/SQL Code.

Die Gründe sind leicht erklärt: Zeitmangel!

Der kurzfristige Vorteil kann im Nachhinein aber sehr teuer / zeitaufwendig werden. Nämlich dann, wenn die schnelle Lösung mit erhöhten Datenmengen versagt und statt ursprünglich wenigen Sekunden plötzlich Minuten an Zeit verbraucht.

Hierbei möchte ich auf die goldene Regel von Tom Kyte referenzieren:
     1.) Nutze solange SQL wie es geht
     2.) Wenn SQL nicht mehr reicht, dann nehme PL/SQL
     3.) Wenn PL/SQL nicht mehr reicht, dann nehme JAVA oder irgendetwas Anderes was weiterhilft

Meine Anmerkung wäre die folgende zu Punkt 1.)
     1.)  Nutze solange SQL wie es geht und befrage bei Problemen das Oracle SQL Forum

Wie aber stelle ich eine Frage richtig?
Um auch richtige Hilfe zu erhalten, muss eine entsprechende Vorarbeit Ihrerseits geleistet werden.
Das heißt:
     1.) Beschreiben Sie das Problem und fokussieren Sie sich dabei auf das Kernproblem
     2.) Hinterlegen Sie Quelldaten und ihre gescheiterten SQL Versuche
     3.) Beschreiben Sie das Zielszenario (Ergebnis in Tabellenform)
Sie sollten mit 30 Minuten Vorbereitungszeit rechnen (das erste Mal wird etwas länger dauern).
Info: Achten Sie auch auf den Inhalt der Quelldaten.  :)

Sie können sich dabei an meinen Beispielen inspirieren lassen:
     Beispiel 1: Generate some kind of cartesian list
     Beispiel 2: wi (dd.mm.yyyy-dd.mm.yyyy)
     Beispiel 3: Check crossing time periods between rows

Der aufwendigste Teil bleibt immer wieder die Aufbereitung der Quell- und Zieldaten. Ich bin zwar kein Fan der WITH-Klausel aber bei der Hilfe im Forum wird Sie von den Entwicklern immer wieder verwendet. Nun wäre es die einfachste Vorgehensweise die Quelldaten immer mit Hilfe der WITH-Klausel aufbereitet zur Verfügung zu stellen. Denn dann könnten die Entwickler deren Hilfe ich in Anspruch nehme, viel schneller reagieren. Wobei auch so die Reaktionszeit bei durchschnittlich 10 Minuten liegt (siehe Beispiele).

Deshalb habe ich eine Funktionen gebaut die auf Basis einer Tabelle oder View die Quelldaten als WITH-Klausel aufbereitet.
create or replace function get_with_clause(p_table_name in varchar2, p_include_with_clause number default 1, p_rows in number default 10) return clob as
/* Dynamische WITH-Klausel mit SELECT FROM DUAL auf Basis einer vorhandenen Tabelle generieren
   Übergabeparameter: 
     p_table_name = Tabellename
     p_include_with_clause = 1 oder 0, wenn 1 dann mit WITH-Klausel ansonsten nur SELECT FROM DUAL
     p_rows  = Anzahl Zeilen die zurückgegeben werden sollen, wenn NULL, dann alle
*/
 l_sql1 varchar2(4000);
 l_sql2 clob;
begin 
  select 'SELECT ' || chr(13) 
    || 
       case 
        when p_include_with_clause = 1 then
         '''WITH ' || max(table_name) || ' AS ('' ||' || chr(13) 
        else 
         null
       end
    || 'REGEXP_REPLACE(DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E, ' || chr(13) 
       || 'CHR(13) || ''SELECT '' || CHR(13) || '
       || replace(listagg(
                case 
                  when data_type = 'NUMBER' 
                    then 'nvl(trim(to_char('||column_name||')),''NULL'') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'VARCHAR2' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||'||column_name||'||'''''''') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'DATE' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||to_char('||column_name||',''dd.mm.yyyy hh24:mi'')||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
                  else 
                    null
                end
                , ' '
          ) within group (order by rownum) || 'ZZ',',''|| CHR(13) || ZZ',' '' ||')
       || chr(13) 
       || '''FROM DUAL UNION ALL '''
       || chr(13) 
    || ')).EXTRACT(''//text()'').GETCLOBVAL(),1)||''XX'','' UNION ALL XX'','''')'
    ||
       case 
        when p_include_with_clause = 1 then
          '|| CHR(13) || '') ' || 'SELECT t1.* FROM ' || max(table_name) || ' t1 ORDER BY 1'''
        else 
          null
       end
    || chr(13) 
    || 'FROM ' 
    || max(table_name)
    || chr(13) 
    || case when p_rows is null then null else 'WHERE ROWNUM BETWEEN 1 AND ' || trim(to_char(p_rows)) end
  into l_sql1
  from user_tab_columns
  where table_name = p_table_name
  and data_type in ('NUMBER','VARCHAR2','DATE');
 
  execute immediate (l_sql1) into l_sql2;
 
  return l_sql2;
end get_with_clause;
Innerhalb der Funktion werden die Spalten der übergebenen Tabelle ausgelesen. Daraus generiere ich dann ein dynamisches SQL das mir die "select * from dual" - je Zeile generiert. Das Ergebnis ist ein CLOB mit einem ausführbaren SQL Code.

Unter 12c sollte es auch ohne Funktionsaufruf abbildbar sein (Grundlagen sie Blogpost von Carsten), ich konnte es aber wegen fehlen einer 12c Umgebung noch nicht abschließend testen. :(
with function get_with_clause(p_table_name in varchar2, p_include_with_clause number default 1, p_rows in number default 10) return clob as
 L_SQL1 VARCHAR2(4000);
 L_SQL2 CLOB;
BEGIN 
  SELECT 'SELECT ' || CHR(13) 
    || 
       case 
        when p_include_with_clause = 1 then
         '''WITH ' || MAX(table_name) || ' AS ('' ||' || CHR(13) 
        else 
         null
       end
    || 'REGEXP_REPLACE(DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E, ' || CHR(13) 
       || 'CHR(13) || ''SELECT '' || CHR(13) || '
       || replace(listagg(
                case 
                  when data_type = 'NUMBER' 
                    then 'nvl(trim(to_char('||column_name||')),''NULL'') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'VARCHAR2' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||'||column_name||'||'''''''') || ''  as ' || column_name || ',''|| CHR(13) || '
                  when data_type = 'DATE' 
                    then 'decode('||column_name||',NULL,''NULL'',''''''''||to_char('||column_name||',''dd.mm.yyyy hh24:mi'')||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
                  else 
                    null
                end
                , ' '
          ) WITHIN GROUP (ORDER BY ROWNUM) || 'ZZ',',''|| CHR(13) || ZZ',' '' ||')
       || CHR(13) 
       || '''FROM DUAL UNION ALL '''
       || CHR(13) 
    || ')).EXTRACT(''//text()'').GETCLOBVAL(),1)||''XX'','' UNION ALL XX'','''')'
    ||
       case 
        when p_include_with_clause = 1 then
          '|| CHR(13) || '') ' || 'SELECT t1.* FROM ' || MAX(table_name) || ' t1 ORDER BY 1'''
        else 
          null
       end
    || CHR(13) 
    || 'FROM ' 
    || MAX(table_name)
    || CHR(13) 
    || case when p_rows is null then null else 'WHERE ROWNUM BETWEEN 1 AND ' || trim(to_char(p_rows)) end
  INTO L_SQL1
  FROM user_tab_columns
  WHERE table_name = p_table_name
  AND DATA_TYPE IN ('NUMBER','VARCHAR2','DATE');
 
  EXECUTE IMMEDIATE (L_SQL1) INTO L_SQL2;
 
  RETURN L_SQL2;
end get_with_clause;
select get_with_clause('DEPARTMENT') as result from dual
Ergebnis Beispiel für Tabelle DEPT:
SELECT GET_WITH_CLAUSE( P_TABLE_NAME => 'DEPT', P_INCLUDE_WITH_CLAUSE => 1, P_ROWS => 100 ) as RESULT
FROM DUAL;

WITH DEPT AS (
SELECT 
10  as DEPTNO,
'ACCOUNTING'  as DNAME,
'NEW YORK'  as LOC FROM DUAL UNION ALL 
SELECT 
20  as DEPTNO,
'RESEARCH'  as DNAME,
'DALLAS'  as LOC FROM DUAL UNION ALL 
SELECT 
30  as DEPTNO,
'SALES'  as DNAME,
'CHICAGO'  as LOC FROM DUAL UNION ALL 
SELECT 
40  as DEPTNO,
'OPERATIONS'  as DNAME,
'BOSTON'  as LOC FROM DUAL
) SELECT t1.* FROM DEPT t1 ORDER BY 1

Info: Die Lösung funktioniert nur mit den Datentypen: NUMBER, VARCHAR2, DATE. Die restlichen Datentypen werden derzeit ignoriert.

Analytische Funktionen (Teil 3): Gruppen mit zufälliger Auswahl der Datenbasis

Von Tobias Arnhold → 10.07.2015
Das folgende Beispiel bezieht sich auf die Datenbasis meines ersten Post zu "Analytischen Funktionen".
Es geht darum, dass für das Jahr 2014 drei Gruppen (mit einem Hash-Wert je Gruppe) gebildet werden müssen.
Gruppe 1:
- 3 zufällig ausgewählte Bundesländer die mit B anfangen
Gruppe 2:
- 3 zufällig ausgewählte Bundesländer die mit S anfangen
Gruppe 3:
- 3 zufällig ausgewählte Bundesländer die NICHT mit B und S anfangen

Hier das dafür notwendige SQL:
SELECT
  BUNDESLAND,
  EINWOHNER,
  GRUPPE,
  /* Hash Generierung */
  DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW (GRUPPE||'XYZ'), 1) AS HASH_WERT
FROM (
  SELECT
   JAHR,
   BUNDESLAND,
   EINWOHNER,
    /* Gruppe definieren */
    CASE 
      WHEN BUNDESLAND LIKE 'B%' 
      THEN 1
      WHEN BUNDESLAND LIKE 'S%'
      THEN 2
      ELSE 3
    END  AS GRUPPE,
   /* Zufällige Sortierung innerhalb der Gruppe */
   ROW_NUMBER() OVER (
        PARTITION BY
          CASE 
            WHEN BUNDESLAND LIKE 'B%' 
            THEN 1
            WHEN BUNDESLAND LIKE 'S%'
            THEN 2
            ELSE 3
          END 
        ORDER BY DBMS_RANDOM.VALUE(1,10)
    ) AS RN
  FROM AS_EINWO_BUNDESL_JAHR
  WHERE JAHR = 2014
)
WHERE RN <= 3
ORDER BY GRUPPE, BUNDESLAND

/* 
   Nicht vergessen bei der Verwendung von DBMS_CRYPTO.HASH: 
   grant execute on sys.dbms_crypto to APEX_SCHEMA; 
*/
Ergebnis:

BUNDESLAND EINWOHNER GRUPPE HASH_WERT
Baden-Württemberg 10666000 1 C5B1DF1A52DCC34C648FC79804F49A0A
Bayern 12643000 1 C5B1DF1A52DCC34C648FC79804F49A0A
Bremen 659000 1 C5B1DF1A52DCC34C648FC79804F49A0A
Sachsen 4045000 2 38FAEEE9CD02869F273DE5A44CF75218
Sachsen-Anhalt 2238000 2 38FAEEE9CD02869F273DE5A44CF75218
Schleswig-Holstein 2819000 2 38FAEEE9CD02869F273DE5A44CF75218
Hamburg 1762000 3 7DA2D9ED3366BAEA4B1FA0D6269F7F9F
Mecklenburg-Vorpommern 1594000 3 7DA2D9ED3366BAEA4B1FA0D6269F7F9F
Rheinland-Pfalz 3996000 3 7DA2D9ED3366BAEA4B1FA0D6269F7F9F

Im Endeffekt wird mit Hilfe der ROW_NUMBER() Funktion und DBMS_RANDOM.VALUE Funktion eine zufällige Verteilung geschaffen.

DOAG 2015 noch wenige Wochen...

Von Tobias Arnhold → 10.01.2015
Noch wenige Wochen bis zur DOAG 2015.

Dieses Jahr habe ich die Ehre 2 Vorträge zu halten, nachdem ich...
 - 2012 abgelehnt wurde
 - 2013 abgelehnt wurde
 - 2014 dabei war mit: Dynamisches Arbeiten mit Grafiken innerhalb von APEX
 - und 2015...

3 Gründe warum es sich lohnt meine Vorträge anzuschauen
1.) Egal was ich mache, ich mache es zu 100%!
2.) Wenn Ihr nicht den "WOW"-Effekt habt, dann ist es für mich ne Niederlage!
3.) Ich entwickle einen Großteil meiner Präsentationen mit APEX. Weil darin die Leidenschaft eines jeden APEX Entwicklers steckt. Und diese Leidenschaft gilt es weiterzugeben!

Hier nun die Details zu meinen Vorträgen:

    APEX & SQL = THE Reporting Solution - 100% APEX Dienstag 17.11. 14:00 - Helsinki
    --> Wer meint APEX und BI passt nicht zusammen, der wird hier eines besseren belehrt.
    --> Woran scheitern BI-Projekte? Wie kann APEX helfen? Wie kann Visualisierung mit APEX aussehen?
    --> Genau die richtige Präsentation, um nach dem Mittag nicht einzuschlafen. :)


    Die APEX 5 Migration - 50% APEX / 50 % PowerPoint Mittwoch 18.11. 11:00 - Hongkong
    --> Fokus auf das Universal Theme
    --> Die Universal Theme Migration anhand einer echten Businessanwendung präsentiert.


Ps.:
Schaut euch auch mal die anderen Vorträge an: DOAG Konferenz Planer
Wie immer sind auch jede Menge APEX Themen und TOP-Speaker mit dabei:
  Denes
  Dietmar
  Peter
  Oliver
  Andreas
  Jürgen (Vote for at the APEX Developer Choice Awards)
  Carsten
  Patrick
  uvm...

Analytische Funktionen (Teil 2): LISTAGG mit eindeutiger Liste

Von Tobias Arnhold →
Die LISTAGG Funktion dient der Generierung von zusammenkonkatenierten Strings auf Basis einer Spalte. Wenn in der Spalte ein Wert mehrfach vorkommt, dann wird die Liste ebenfalls mit doppelten Werten generiert.

Im folgenden demonstriere ich ein Beispiel um dieses Problem zu lösen.

Beispieldaten - Land und Ort:
SELECT
 LAND, ORT
FROM STADT_LISTE 
ORDER BY 1,2;
Ergebnis:
LAND ORT
Belgien Bruxelles
Belgien Bruxelles
Polen Katowice
Polen Wegliniec
Schweiz Basel
Schweiz Bern
Schweiz Riehen
Schweiz Thayngen
Schweiz Thayngen

Ziel ist es nun, nur die Länder mit jeweils einer Liste an Orten auszugeben. Unglücklicherweise hat meine Tabelle die Orte Bruxelles und Thayngen doppelt hinterlegt. Mit Hilfe der ROW_NUMBER() Funktion selektiere ich diese Werte vor der LISTAGG-Funktion heraus.
SELECT
  LAND,
  REPLACE(':'||LISTAGG(ORT_UNGUELTIG,':')  WITHIN GROUP (ORDER BY ORT_UNGUELTIG)||':','::',NULL) AS ORT_LISTE_UNGUELTIG,
  REPLACE(':'||LISTAGG(ORT_GUELTIG,':')  WITHIN GROUP (ORDER BY ORT_GUELTIG)||':','::',NULL) AS ORT_LISTE_GUELTIG
FROM (
  SELECT  
   LAND, 
   ORT AS ORT_UNGUELTIG,
   CASE WHEN 
         ROW_NUMBER() OVER (PARTITION BY LAND, ORT ORDER BY ORT) = 1
         THEN ORT
         ELSE NULL
    END AS ORT_GUELTIG
  FROM STADT_LISTE 
)
GROUP BY LAND
ORDER BY LAND;
Ergebnis:
LAND ORT_LISTE_UNGUELTIG ORT_LISTE_GUELTIG
Belgien :Bruxelles:Bruxelles: :Bruxelles:
Polen :Katowice:Wegliniec: :Katowice:Wegliniec:
Schweiz :Basel:Bern:Riehen:Thayngen:Thayngen: :Basel:Bern:Riehen:Thayngen:

Weiterführende Infos zum Thema LISTAGG und Analytische Funktionen findet Ihr hier: Die besten HowTo's rund um fortgeschrittene SQL-Techniken

Analytische Funktionen (Teil 1): SUM mit CASE WHEN

Von Tobias Arnhold → 9.27.2015
Es gibt wie in meinem letzten Post beschrieben, sehr viele Artikel zu analytischen Funktionen. Diese zeigen die Fähigkeiten aber nur an der Oberfläche. Deshalb möchte ich in den nächsten Monaten die besten Tricks mal beispielhaft abbilden.

Als Grundlage habe ich eine Tabelle mit Einwohnern je Bundesland und Jahr. Als Quelle gilt das Statistische Bundesamt.

SELECT
 JAHR,
 BUNDESLAND,
 EINWOHNER
FROM AS_EINWO_BUNDESL_JAHR
WHERE JAHR = 2014
ORDER BY 1,2
JAHR BUNDESLAND EINWOHNER
2014 Baden-Württemberg 10.666.000
2014 Bayern 12.643.000
2014 Berlin 3.443.000
2014 Brandenburg 2.449.000
2014 Bremen 659.000
2014 Hamburg 1.762.000
2014 Hessen 6.059.000
2014 Mecklenburg-Vorpommern 1.594.000
2014 Niedersachsen 7.799.000
2014 Nordrhein-Westfalen 17.579.000
2014 Rheinland-Pfalz 3.996.000
2014 Saarland 989.000
2014 Sachsen 4.045.000
2014 Sachsen-Anhalt 2.238.000
2014 Schleswig-Holstein 2.819.000
2014 Thüringen 2.156.000

In meinem ersten Beispiel möchte ich eine bedingte Summierung zeigen. Dazu soll für die Top 5 Bundesländer mit der höchsten Einwohnerzahl das Verhältnis zur Gesamtanzahl aller Bundesbürger in Prozent aufgezeigt werden. Zusätzlich werden die TOP 5 Bundesländer FETT markiert.

Info: Wenn meine Beispiel nicht gefallen, dann kann ich es verstehen. Ich löse für gewöhnlich Probleme und denke sie mir nicht aus. :)
SELECT
 BUNDESLAND,
 EINWOHNER,
 SUM(EINWOHNER) OVER () as EINWOHNER_GESAMT,
 /* Beispiel: Summe über Einwohner nur wenn TOP5 = 1 */
 SUM(CASE WHEN IN_TOP5 = 1 THEN EINWOHNER ELSE 0 END) OVER () AS EINWOHNER_TOP5,
 /* Prozentwertberechnung */
 round(SUM(CASE WHEN IN_TOP5 = 1 THEN EINWOHNER ELSE 0 END) OVER ()  / SUM(EINWOHNER) OVER () * 100,0) IN_PROZ
FROM
(
  SELECT
    JAHR,
    /* Generierung der TOP 5 mit B-Tag */
    case when 
          row_number () over (order by EINWOHNER desc) <= 5
         then
          ''||BUNDESLAND||''
         else
          BUNDESLAND
    end as BUNDESLAND,
    /* Ausgabe TOP-5 mit 1 Sonst 0 */
    case when 
          row_number () over (order by EINWOHNER desc) <= 5
         then
          1
         else
          0
    end as IN_TOP5,
    EINWOHNER
  FROM AS_EINWO_BUNDESL_JAHR
  WHERE JAHR = 2014
)
ORDER BY 1, 3 desc
BUNDESLAND EINWOHNER EINWOHNER_GESAMT EINWOHNER_TOP5 IN_PROZ
<b>Baden-Württemberg</b> 10.666.000 80.896.000 54.746.000 68
<b>Bayern</b> 12.643.000 80.896.000 54.746.000 68
Berlin 3.443.000 80.896.000 54.746.000 68
<b>Hessen</b> 6.059.000 80.896.000 54.746.000 68
<b>Niedersachsen</b> 7.799.000 80.896.000 54.746.000 68
<b>Nordrhein-Westfalen</b> 17.579.000 80.896.000 54.746.000 68
Brandenburg 2.449.000 80.896.000 54.746.000 68
Bremen 659.000 80.896.000 54.746.000 68
Hamburg 1.762.000 80.896.000 54.746.000 68
Mecklenburg-Vorpommern 1.594.000 80.896.000 54.746.000 68
Rheinland-Pfalz 3.996.000 80.896.000 54.746.000 68
Saarland 989.000 80.896.000 54.746.000 68
Sachsen 4.045.000 80.896.000 54.746.000 68
Sachsen-Anhalt 2.238.000 80.896.000 54.746.000 68
Schleswig-Holstein 2.819.000 80.896.000 54.746.000 68
Thüringen 2.156.000 80.896.000 54.746.000 68

Hmm die Reihenfolge mit dem Order By hätte ich etwas besser auswählen können. :O

Die besten HowTo's rund um fortgeschrittene SQL-Techniken

Von Tobias Arnhold → 9.25.2015
Im Zuge des aktuellen Community Tips, möchte ich mal eine Liste an HowTo's, Tips und Tricks rund um die verschiedenen aktuellen
SQL-Techniken veröffentlichen. Dabei habe ich mich möglichst auf deutsche Lektüre fokussiert. Am häufigsten habe ich Artikel von Carsten Czarski gefunden, was wohl wenig verwunderlich ist. :)
Die folgenden Links richten sich an jene Entwickler, die mehr als SQL-92 aus Ihrer Oracle Datenbank rausholen möchten.


Analytische SQL-Funktionen
 - Daten zusammenfassen mit Aggregatsfunktionen
 - Mächtige Berichte mit "Analytischen Funktionen"
 - Analytische Funktionen - eine Einführung
 - Analytische Funktionen - Einführung
 - Einsatz analytischer Funktionen
 - Probably the Coolest SQL Feature: Window Functions (Englisch)

Volltextsuche
 - Ähnlichkeitssuche in SQL-Abfragen: Oracle TEXT für alle!
 - Volltextrecherche und mehr: Wozu man Oracle Text nutzen kann
 - Oracle Text - Einführung
 - Ähnlichkeitssuche mit Oracle Text
 - "Unscharfe Suche" in Datenbeständen

Räumliche Abfragen (Geodaten)
 - oracle-spatial.blogspot.de
 - spatialdbadvisor.com (Englisch)
 - SDO_GEOMETRY für Newbies
 - Was kann der Oracle Locator (Englisch)

XML-Funktionen
 - Working with XML files and APEX - Part 1: Upload (Englisch)
 - Working with XML files and APEX - Part 2: Selecting data from XMLType column with XMLTable (Englisch)
 - XML-Dokumente relational aufbereiten: mit SQL!
 - XML-Strukturen analysieren - mit SQL!

JSON-Funktionen
 - APEX_JSON ohne APEX: JSON parsen mit SQL und PL/SQL
 - APEX_JSON ohne APEX: JSON erzeugen mit SQL und PL/SQL

 - Native JSON-Unterstützung in der Oracle-Datenbank 12c (12.1.0.2)
 - Oracle 12.1.0.2 ist da: Ein Einblick in die JSON-Unterstützung
 - Speed Up Your APEX apps with JSON and Mustache.js

SQL Pattern Matching (ab 12.1)
 - Datenmuster finden: SQL Pattern Matching in Oracle12c
 - Oracle12c: Was für SQL- und PL/SQL-Entwickler einfacher wird
 - Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (Englisch)

SQL MODEL-Klausel
 - Kalkulationen, Simulationen und mehr - in APEX: SQL MODEL Klausel
 - SQL Model Clause Tutorial (Englisch)
 - Rechnen wie in Excel - in einer SQL-Abfrage
 - SQL Model Clause Tutorial
 - Model Klausel - Der Excel-Killer von Oracle?
 - Forum Lösung - Gantt Chart überschneidende Einträge (Englisch)

Stringverkettungen
 - LISTAGG function in 11g release 2 (Englisch)
 - SQL LISTAGG mit CLOB-Ausgabe - kein VARCHAR2-Limit mehr
 - XML CLOB Trick - Custom CSV Export in APEX


String to Table
 - Split comma delimited strings in a table in Oracle
 - emulating string-to-table functionality using sql
 - When APEX_UTIL.STRING_TO_TABLE is not enough

Ähnlichkeitssuche (Beispiel: Jaro Winkler)
 - UTL_MATCH : String Matching by Testing Levels of Similarity/Difference (Englisch)
 - Oracle UTL_MATCH (Englisch)

Hierarchische Abfragen (CONNECT BY)
 - Treewalking mit SQL (Hierarchische Strukturen) 
 - Oracle: connect by (Hierarchische Queries)
 - Hierarchische Abfragen in Oracle11g Rel2: Recursive WITH clause 

Regular Expressions
 - Reguläre Ausdrücke in der Oracle Datenbank in SQL verwenden
 - Reguläre Ausdrücke ... in der Datenbank!
 - Reguläre Ausdrücke in Application Express 
 - Regular Expressions in Oracle

Kreuztabellen (Pivot / Unpivot)
 - Kreuztabellen in Oracle11g: SQL PIVOT-Klausel
 - Pivoting in 11g - Zeilen zu Spalten
 - pivot and unpivot queries in 11g (Englisch)
 - Dynamic SQL Pivoting – Stealing Anton’s Thunder (Englisch)
 - APEX Beispielanwendung

Zufallszahlen
 - Arbeiten mit DBMS_CRYPTO
 - Generating random numbers and strings in Oracle (Englisch)
 - Oracle DBMS_CRYPTO (Englisch) und Oracle DBMS_RANDOM (Englisch)

Zeilengenerierung
 - Oracle Row Generator Techniques (Englisch)

Oracle Spatial (Teil 2) - Füllen der USER_SDO_GEOM_METADATA

Von Tobias Arnhold →
Damit Tools wie
  - Oracle Spatial Reader
  - FeatureReader
  - OracleQuerier
  - GeoServer
mit Oracle Spatial Tabellen arbeiten können, müssen diese in einer Meta-Schicht bekannt gemacht werden. Dies ist im Prinzip ganz einfach, denn dazu muss nur die View USER_SDO_GEOM_METADATA gefüllt werden.

Die View beinhaltet die folgenden Spalten:
(
  TABLE_NAME   VARCHAR2(32),
  COLUMN_NAME  VARCHAR2(32),
  DIMINFO      SDO_DIM_ARRAY,
  SRID         NUMBER
);

Um diese richtig zu füllen muss neben Tabelle, Spalte und SRID (Geotyp), auch die Matrix in welchen Bereich sich die Daten befinden hinterlegt werden.

Dies ist relativ einfach über ein SQL Statement abbildbar.

Hierbei beziehe ich mich auf meine Testtabelle aus dem 1. Teil meiner Spatial Posts:
-- Komplette Matrix
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
  'GV_STANDORT_LAYER2_LIST',
  'GEO_WGS84',
  MDSYS.SDO_DIM_ARRAY( 
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5)
  ),
  8307
);

----------------------------------------------------
-- Insert auf Basis eines vorhandenen Eintrags
INSERT INTO USER_SDO_GEOM_METADATA
(
  TABLE_NAME,
  COLUMN_NAME,
  DIMINFO,
  SRID
)
select 
  'GV_STANDORT_LAYER2_LIST' as TABLE_NAME,
  COLUMN_NAME,
  DIMINFO,
  SRID
from USER_SDO_GEOM_METADATA
WHERE TABLE_NAME = 'GV_STANDORT_LAYER1'

----------------------------------------------------
-- Teilmenge aus Ausgangsdaten
INSERT INTO USER_SDO_GEOM_METADATA 
(
  TABLE_NAME,
  COLUMN_NAME,
  DIMINFO,
  SRID     
)
SELECT
  'MEINE_GEO_DATEN' AS TABLE_NAME,
  'GEO_WGS84' COLUMN_NAME,
  MDSYS.SDO_DIM_ARRAY( 
         MDSYS.SDO_DIM_ELEMENT('X', MINX, MAXX, 0.05), 
         MDSYS.SDO_DIM_ELEMENT('Y', MINY, MAXY, 0.05) 
  ) AS DIMINFO,
  8307  AS SRID -- WGS84
FROM ( 
       SELECT
         TRUNC( MIN( V.X ) - 1,0) AS MINX,
         ROUND( MAX( V.X ) + 1,0) AS MAXX,
         TRUNC( MIN( V.Y ) - 1,0) AS MINY,
         ROUND( MAX( V.Y ) + 1,0) AS MAXY
       FROM (
          SELECT SDO_AGGR_MBR(DS.GEO_WGS84) AS AMBR
          FROM MEINE_GEO_DATEN DS
          --WHERE DS.X_KOORDINATE IS NOT NULL -- Geodaten dürfen nicht leer sein
       ) DA,
       TABLE(MDSYS.SDO_UTIL.GETVERTICES(DA.AMBR)) V
     )

Die Quelle des Teilmenge-Statements stammt von dem Spatial Blog: http://spatialdbadvisor.com/

Wenn Ihr tiefergehendes Wissen zum Thema USER_SDO_GEOM_METADATA sucht, dann schaut euch mal diese Beiträge an:
docs.oracle.com - Geometry Metadata Views
knowledge.safe.com - Adding metadata entries for Oracle spatial tables or views

Ergebnis:

Der hinterlegte Bereich kann nun noch mit den Detaildaten verifiziert werden:
SELECT
  ADRESSE, 
  SDO_GEOM.VALIDATE_GEOMETRY(LAY.GEO_WGS84, MET.DIMINFO)  RESULT
FROM MEINE_GEO_DATEN LAY
CROSS JOIN USER_SDO_GEOM_METADATA MET
WHERE ROWNUM BETWEEN 1 AND 10
Oftmals wird auch ein Spatial-Index zur Datenvisualisierung benötigt:
CREATE INDEX "MEINE_GEO_DATEN_IDX" ON "MEINE_GEO_DATEN" ("GEO_WGS84") 
   INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

Oracle Spatial (Teil 1) - Mein erster Tag

Von Tobias Arnhold → 9.11.2015
Ich habe in der Oracle DB Welt viele Themenkomplexe kennengelernt und mit gearbeitet. Eines ist bisher außen vor geblieben: Oracle Spatial mit dem Datentyp SDO_GEOMETRY

Warum?
Es hat sich wohl einfach noch nicht ergeben.
Aber irgendwann ist ja immer das erste Mal! :)

Heute habe ich die Aufgabe erhalten bestehende Koordinatendaten (X,Y) in das SDO_GEOMETRY Format GK3 zu transformieren.

Was wusste ich bisher?
Der SDO_GEOMETRY Datentyp wird über ein gesondertes Schema bereit gestellt:
MDSYS
Der Datentyp besteht aus X,Y und Z Koordinaten. Außerdem gibt es unterschiedliche Typen: Punkte, Linien, Objekte... Und anscheinend auch unterschiedliche Arten das Koordinatensystem (WGS 84, Gauss-Kruger 3) abzubilden.

Wie war mein Vorgehen um möglichst schnell Wissen zu erlangen?
Ich habe mich mit dem Datentyp auseinander gesetzt und bin dabei auf die genaue Datentyp-Definition gestoßen (Spatial User's Guide and Reference 10gR2 - 2 Spatial Data Types and Metadata):
CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY
);

Der SDO_GTYPE definiert den Typ des Objekts: Punkt, Linie, Polygon,...
Info: Die folgende Tabelle zeigt die möglichen Typen und beschreibt die genaue Definition: http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm#g1013735

Die SDO_SRID definiert das Koordinatensystem (WGS84, GK3, Arc 1950)
Info: Mit der folgenden Abfrage können Sie die verschiedenen System auslesen:
select srid,cs_name 
from cs_srs 
-- where srid in (31467, 8307, 8199);

Der SDO_POINT definiert die Koordinaten des Objekts.

SDO_ELEM_INFO und SDO_ORDINATES habe ich an meinem ersten Tag mit Oracle Spatial nicht verwendet. :P

Nun stellte sich die Frage, wie ich die Koordinaten aus meiner Testtabelle in den SDO_GEOMETRY Datentyp umwandeln kann? 
Bei der Suche bin ich auf den hervorragenden deutschsprachigen Blog gelandet:
http://oracle-spatial.blogspot.de

Und habe die passenden Antworten in dem Blogpost gefunden:
http://oracle-spatial.blogspot.de/2010/09/gauss-kruger-epsg-oder-nicht-epsg-das.html

Die Antwort war viel einfacher als gedacht:
SELECT 
  ADRESSE,
  GEO_KOORDINATE_X,
  GEO_KOORDINATE_Y,

  SDO_CS.TRANSFORM( 
    MDSYS.SDO_GEOMETRY (
      2001, -- Zweidimensionaler Punkt
      8307, -- Typ: WGS84
      SDO_POINT_TYPE(
          X => TO_NUMBER(GEO_KOORDINATE_X),  -- Längengrad / Longitude / Ost
          Y => TO_NUMBER(GEO_KOORDINATE_Y),  -- Breitengrad / Latitude / Nord
          Z => NULL
      ), 
      NULL,
      NULL
    ),
   31467 -- Umwandlung in GK3
   ) AS GEOMETRY_GK3

FROM MEINE_GEO_DATEN
Da mein Problem gelöst war, konnte ich ja Feierabend machen.
Hmm... woher weiß ich eigentlich, dass die Ergebnisse stimmen?

Hier liefert der SQL Developer die passende Lösung.
Der hat nämlich eine simple Geodatenanzeige integriert.

Außerdem liefert Oracle eine Geodaten-DB zum testen aus:
http://www.oracle.com/technetwork/database/options/spatialandgraph/downloads/navteq-data-download-2577974.html

Diese habe ich zum verifizieren meiner Ergebnisse direkt mal importiert und meine virtuelle Umgebung gesprengt, da die DB über 20GB groß wurde. :)

Dennoch konnte ich ein paar Interessante Tabellen importieren:
 - MAP_WORLD (Länderdarstellung - Flächen)
 - NTC_MAP_POI_CITY_CENTER (Städte mit Einwohnerzahlen - Punkte)

Diese habe ich dann in meine "Grafische Ansicht" integriert:


SELECT - Europa - Länderflächen:
SELECT
  COUNTRY,
  sdo_cs.transform(
    GEOMETRY,
    8307 -- Transformation in WGS84 notwendig
  ) as GEOMETRY FROM MAP_WORLD
where continent = 'Europe';

SELECT - Deutschland - Städte:
SELECT
  GEOMETRY,
  name
FROM NTC_MAP_POI_CITY_CENTER
where population > 30000
and iso_country_code = 'DEU';

SELECT - Meine Objekte:
SELECT
  GEO_WGS84,
  NAME
FROM MEINE_GEO_DATEN;

Und hier das Ergebnis:

Imho:
Innerhalb eines Tages kann man in einem ganz offensichtlich hochkomplexen Themengebiet, doch ganz beachtliche Ergebnisse erzielen.

Unterschiedliche Wege um APEX Anwendungsparameter zu setzen

Von Tobias Arnhold → 9.10.2015
Viele APEX Anwendungen benötigen zur korrekten Ausführung User-spezifische nicht veränderbare Variablen. Auf diese Variablen bezieht man sich, um sicherheitsrelevante Abfragen für den angemeldeten User auszuführen.

Hier eine kurze Liste an möglichen Anwendungsfällen:
- User ID
- User basierte Parameter
  (Land / Region / Ort / Niederlassung / Firmen- und Anwendungsspezifische Hintergründe)
- Rechte und Rollen
- Berechtigungen auf Zeilenebene
- Schreib- und Leserechte

Diese Variablen sollten je nach Inhalt und Verwendung auf eine der folgenden Arten gespeichert werden:
- APEX Application Items (Mit Protection Level: Restricted - May not be set from browser)
- APEX Collections (Eine Zeile)
- Eigene User- und Session spezifische Tabelle

Hinweis:
Die genannten Inhalte sollten nicht in Page-Items abgespeichert werden, auf diese Sie sich später beziehen.
Beispiel: P1_USER_ID

Die Vorteile ergeben sich wie folgt:
Application Items lassen sich leicht auslesen (Debugging) und verarbeiten (da diese immer pro Session gespeichert werden).

Collections und Custom-Tabellen haben erhebliche Performancevorteile bei Verwendung innerhalb von Datenbank-Views. Außerdem können mehr als 4000 Zeichen (CLOB) abgespeichert werden.
Beim Debugging und entwickeln (beispielsweise Views) sind eigene Tabellen (Imho) einfacher zu verarbeiten, dafür werden Collections automatisch pro Session angelegt und wieder gelöscht.

Was ist nun zu empfehlen?
Wenn die Größe einer Anwendung nicht feststeht oder es sich generell um eine kleine bis mittelgroße Anwendung handelt (10-60 Seiten), dann sollte man mit Application Items starten und im späteren Verlauf die Technik bei Notwendigkeit umstellen.
Wenn von vornherein klar ist, dass die Anwendung mehr als 100 Seiten groß wird und viele Nutzer mit vielen unterschiedlichen Attributen und Berechtigungen arbeiten, dann sind Collections oder eigene Tabellen zu bevorzugen.

Wie kann ein solcher Prozess aussehen?
Prinzipiell sollte ein solcher Prozess als "Application Process" angelegt sein. Außerdem sollte die komplette Logik in eine Package Prozedur ausgelagert werden und als Übergabeparameter den :APP_USER beinhalten.

Aufruf-Beispiel: 
APP_PACKAGE.START_APPLICATION (P_APP_USER => UPPER(:APP_USER));

Die Ausführung sollte dann nur einmal nach der Anmeldung geschehen:
Process Point: On New Instance (new session)
Info:
Hierbei hatte ich das ein oder andere Mal Probleme bei der korrekten Ausführung.
Daher ist alternativ auch eine PL/SQL Prüfung möglich:
:APP_USER <> 'nobody' and :AI_USER_ID IS NULL

Hinweis:
Achten Sie innerhalb ihrer Package Prozedur darauf, dass Sie Fehler in eine entsprechende Fehlertabelle wegschreiben.

Code Beispiel zum setzen der User ID:
-- ID auslesen
SELECT ID 
INTO V_USER_ID 
FROM MY_USER_TABELLE 
WHERE UPPER(USER_NAME) = P_APP_USER;
 

-- Item setzen
APEX_UTIL.SET_SESSION_STATE (
        P_NAME => 'AI_USER_ID',
        P_VALUE => V_USER_ID

);

Komplettes Beispiel:
Zentraler - Application Process

Package Prozedur:
Basierend auf einem alten Package-Beispiel, könnte der Code wie folgt aussehen:
create or replace package body pkg_login_example as
/* Package Variables */
  gv_proc_name    varchar2(100);
  gv_action       varchar2(4000);
  gv_ora_error    varchar2(4000);
  gv_custom_error varchar2(4000);
  gv_parameter    varchar2(4000);
  -- Globale User-Variable direkt im Package, Übergabeparameter ist so nicht notwendig
  gv_user         varchar2(20) := upper(nvl(v('APP_USER'),user));
  
/* Save errors */
/*
  --------------------------------------------------------
  --  DDL for Table ERR_LOG
  --------------------------------------------------------

  CREATE TABLE "ERR_LOG" 
   ( 
    "PROC_NAME" VARCHAR2(200), 
     "ACTION" VARCHAR2(4000), 
     "APP_ID" NUMBER, 
     "APP_PAGE_ID" NUMBER, 
     "APP_USER" VARCHAR2(20), 
     "ORA_ERROR" VARCHAR2(4000), 
     "CUSTOM_ERROR" VARCHAR2(4000), 
     "PARAMETER" VARCHAR2(4000), 
     "TIME_STAMP" DATE
   ) ;
/

*/
procedure add_err  is
pragma autonomous_transaction;
begin
     insert
     into err_log
      ( proc_name,action,app_id,app_page_id,app_user,ora_error,custom_error,parameter,time_stamp )
      values
      ( gv_proc_name,gv_action,nvl(v('APP_ID'),0),nvl(v('APP_PAGE_ID'),0),nvl(nvl(v('APP_USER'),user),'Unknown'),
        gv_ora_error,gv_custom_error,gv_parameter,sysdate );
     commit;
end;


/* ************************************************************************************************************************************** */
/* Anwendungsparameter zu setzen                                                                                                          */  
/* ************************************************************************************************************************************** */

procedure start_application
is
  v_user_id    my_user_table.id%type;
  v_user_role  my_user_table.user_role%type;

begin
  gv_proc_name := 'pkg_login_example.start_application';
  gv_parameter := '';
  
  gv_action := '1. Get USER_ID'; 
  select id 
  into v_user_id 
  from my_user_table
  where upper(user_name) = gv_user; 

  apex_util.set_session_state (p_name => 'AI_USER_ID',p_value => v_user_id);
  
  gv_action := '2. Get USER_ROLE'; 
  select user_role 
  into v_user_role
  from my_user_table
  where upper(user_name) = gv_user; 
  
  apex_util.set_session_state (p_name => 'AI_USER_ROLE',p_value => v_user_role);
  
  gv_action := '3. Get REGION_ROLES or whatever'; 
  -- ...
  
  commit;
exception
when others then   
      gv_ora_error := sqlerrm;
      gv_custom_error := 'Internal Error. Action canceled.';
      rollback;
      add_err; raise_application_error(-20001, gv_custom_error);
end;
 
end pkg_login_example;


APEX Connect 2015 - Review

Von Tobias Arnhold → 6.13.2015
Die APEX Connect 2015 (#apexconnect15) war die erste ihrer Art in Deutschland. Eine Konferenz bei der es nur um Oracle APEX ging.


Das waren die Fakten:
 - 2 Tage voll gepackt mit Themen rund um Application Express
 - 4 Vortragsstreams
 - Die Konferenz war ausverkauft (rund 250 APEX Enthusiasten)
 - TOP Developer aus dem In- und Ausland

Was sind die Eindrücke die ich mitnehme?
 - Die Organisation rund um das Event war TOP
 - Die technischen Möglichkeiten vor Ort waren angemessen
 - Networking zu jeder Zeit in sehr hoher Qualität möglich (In den Pausen, Während der Vorträge, bei der Abendveranstaltung)
 - Viele bekannte Gesichter und umso mehr Neue die ich mir nun einprägen muss. :)
 - Alle Vortragenden haben alles gegeben, wenngleich die Qualität doch variiert hat
 - Wo war Joel?

Meine persönlichen Highlights:
Ich selbst habe natürlich auch einen Vortrag zum Thema "Plug-Ins maßgerecht verwenden" gehalten. Wobei die Konkurrenz mit einem Parallelvortrag von Shakeeb Rahman zum Thema "Universal Theme" natürlich extrem hoch war. So muss es auch sein, ich war ja schließlich auf der APEX Connect und nicht auf der DOAG bei der es nur einen APEX Track gibt. ;)

Am Abend hatte ich dann die Ehre den ersten APEX Community Award an Carsten Czarski zu vergeben. Eine Tradition die nur der Anfang sein kann.

Ré­su­mé:
Ein perfekter Start mit viel Qualität und Spaß. Ich werde dieses Event so schnell nicht vergessen.

Wie gehts weiter?
Das nächste Großereignis wäre dann die DOAG 2015 in Nürnberg.
Nächstes Jahr zieht die APEX Connect von Düsseldorf nach Berlin (7. bis 9. Juni 2016). Ich bin gespannt ob wir da 400er Marke knacken! :)

Ps.:
Hier sind die Bilder zur Veranstaltung: apex.doag.org und die offizielle DOAG Review




APEX Connect Vortrag: Plug-Ins maßgerecht verwenden

Von Tobias Arnhold → 6.07.2015
SAVE THE DATE: Am Dienstag dem 09.06. zwischen 11:00 - 11:45 halte ich meinen Vortrag.

Wie so oft habe ich auch für diesen Vortrag eine individuell zusammengestellte APEX Anwendung entwickelt, anhand dessen ich die Präsentation halten werden.

Zusammengefasst:
#NOPOWERPOINT, #APEXONLY, #APEXPLUGINS

Der Vortrag eignet sich für Einsteiger wie Fortgeschrittene. Ich werde anhand unterschiedlichster Plug-Ins auf die Vorteile und Gefahren aufmerksam machen.

Also schaut rein und genießt die Show! :)

Ps.: Für Alle die nicht warten können, hier schon mal zwei Screenshots von der Anwendung: