Browsing "Older Posts"

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

Ä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;