Oracle SQL und PL/SQL ...

SOAP-Zugang zu PL/SQL: Database Native Webservices

noreply@blogger.com (Carsten Czarski) — Wed, 09 May 2012 08:12:00 +0000

Heute möchte ich etwas über die Database Native Webservices schreiben, die zwar bereits mit der Datenbankversion 11g Release 1 eingeführt wurden, die aber immer noch kaum bekannt sind. Worum geht es? Mit den Database Native Webservices kann eine SQL-Query oder PL/SQL-Funktion ohne weiteren Aufwand als SOAP-Style Webservice bereitgestellt werden. Und das geht so:

Zunächst muss die XML DB in der Datenbank vorhanden sein. Das prüft man am einfachsten mit einer Abfrage auf die View DBA_REGISTRY.
```
select comp_id, version from dba_registry where comp_id='XML'

COMP_ID                        VERSION
------------------------------ ---------------------
XML                            11.2.0.2.0
```
Wenn hier keine Zeile zurückgegeben wird, ist die XML DB nicht vorhanden und es können auch keine Native Webservices genutzt werden. Mit dem PL/SQL Skript catqm.sql in $ORACLE_HOME/rdbms/admin kann die XML DB nachinstalliert werden.
Der HTTP-Protokollserver der XML DB muss aktiviert sein. Normalerweise sind die FTP- und HTTP-Protokollserver deaktiviert. Nutzer des Embedded PL/SQL Gateway auf OracleXE arbeiten typischerweise mit bereits aktiviertem HTTP-Protokollserver, denn der wird hier für APEX genutzt. Wenn Ihr nicht auf OracleXE arbeitet oder euch generell nicht sicher seid, ob der HTTP-Protokollserver aktiv ist, könnt Ihr das mit einem lsnrctl status nachprüfen:
```
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-MAY-2012 10:58:27

Copyright (c) 1991, 2010, Oracle.  All rights reserved.
:
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
:
Services Summary...
```
Ist diese Zeile nicht vorhanden, so ist der Protokollserver inaktiv. Dann aktiviert Ihr ihn einfach (als DBA) wie folgt. Verwendet nach Möglichkeit einen TCP/IP-Port über 1024. Für die Portnummern darunter muss auf UNIX-Systemen der Listener besonders eingerichtet werden.
```
begin
  dbms_xdb.sethttport({http-port});
end;
```

Die Database Native Webservices müssen (einmalig) innerhalb der XML DB aktiviert werden. Hierzu muss (als DBA) folgendes SQL-Kommando abgesetzt werden:

DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet(
    NAME     => SERVLET_NAME,
    LANGUAGE => 'C',
    DISPNAME => 'Oracle Query Web Service',
    DESCRIPT => 'Servlet for issuing queries as a Web Service',
    SCHEMA   => 'XDB'
  );
  DBMS_XDB.addServletSecRole(
    SERVNAME => SERVLET_NAME,
    ROLENAME => 'XDB_WEBSERVICES',
    ROLELINK => 'XDB_WEBSERVICES'
  );
  DBMS_XDB.addServletMapping(
    PATTERN => '/orawsv/*',
    NAME    => SERVLET_NAME
  );
END;
/

Damit sind die datenbankweiten Vorbereitungen erledigt. Zum Ausprobieren können wir den Datenbankuser SCOTT hernehmen. Zuerst bauen wir eine kleine PL/SQL-Funktion wie folgt:

create or replace function get_sal(
  p_empno in emp.empno%type
) return emp.sal%type is
  l_sal emp.sal%type := null;
begin
  begin
    select sal into l_sal from emp where empno = p_empno;
  exception
    when no_data_found then null;
    when others then raise;
  end;
  return l_sal;
end;

Diese Funktion GET_SAL wollen wir nun als Native WebService aufrufen. Die Datenbank ist soweit vorbereitet. Allerdings kann man nicht mit jedem Datenbankuser einfach so Webservices aufrufen - es sind Rollen nötig. Für Database Native Webservices gibt es dero drei:

XDB_WEBSERVICES: Ein Datenbankbankuser, der diese Rolle hat, ist prinzipiell in der Lage, PL/SQL Objekte und SQL-Abfragen als SOAP-Webservices auszuführen.
XDB_WEBSERVICES_OVER_HTTP: Der Datenbankuser kann die Webservices mit dieser Rolle auch über ungesichertes HTTP aufrufen - es wird dann also kein HTTPS benötigt. Ohne dieses Privileg wird der Webservice-Call nur über SSL erlaubt.
HTTP_WEBSERVICES_WITH_PUBLIC: Der Datenbankuser kann auch auf Objekte zugreifen, die "nur" über einen "Public" Grant bereitstehen. Ohne diese Rolle muss der Datenbankuser Eigentümer der Objekte sein oder explizite Privilegien haben.

Also bekommt unser Datenbankuser SCOTT zwei Privilegien:

grant xdb_webservices to scott
/

grant xdb_webservices_over_http to scott
/

Damit sind alle Vorbereitungen gemacht. Um einen Webservice per SOAP aufrufen zu können, braucht es das WSDL-Dokument, also die "Schnittstellenbeschreibung" für den Web Service. Die Database Native Webservices generieren dieses automatisch. Probiert im Browser einfach folgende URL aus:

http://{host}:{port}/orawsv/SCOTT/GET_SAL?wsdl

Im Browser werdet Ihr dann zuerst nach Usernamen und Passwort gefragt. Hier loggt Ihr euch mit SCOTT und seinem Passwort ein. Wichtig ist: Der Login muss mit dem Datenbankaccount erfolgen, welcher die XDB_WEBSERVICES-Rollen hat - es muss nicht zwingend der Eigentümer der aufgerufenen Funktion sein. Nach dem Login zeigt euch der Browser das WSDL-Dokument für den Webservice an.

Mit Hilfe dieser URL kann nun jeder SOAP-Client mit der Datenbankfunktion GET_SAL arbeiten. Der folgende Screenshot zeigt ein Beispiel - typischerweise wird zunächst die URL zum WSDL-Dokument angegeben; das Werkzeug liest danach die Informationen aus und generiert einen SOAP-Request als Vorlage. Dort kann man dann eine der EMPNOs aus der Tabelle EMP eintragen und den Request zum Server senden - die Antwort kommt wiederum als SOAP-Response.

Hier habe ich noch einen Screenshot aus Application Express - es ist zwar eine seltsame Idee, eine PL/SQL-Funktion aus APEX heraus per Webservice-Schnittstelle aufzurufen, aber es funktioniert.

Es ist also sehr einfach, eine PL/SQL-Funktion oder SQL-Query als SOAP-Webservice verfügbar zu machen - ein paar Dinge gibt es aber noch zu sagen:

Die oben genannten Privilegien erlauben zunächst keine feingranulare Steuerung, welche PL/SQL-Funktionen oder -Prozeduren als Web Service verfügbar gemacht werden sollen. Man vergibt nur die Rollen an einen Datenbankuser - und alle Objekte, die diesem User gehören, können dann als Web Service aufgerufen werden. Das scheint erstmal sehr grob - aber nur auf den ersten Blick. Denn die XDB_WEBSERVICES-Rollen solle man niemals dem Eigentümer der Objekte geben. Vielmehr sollte man einen eigenen "Webservice-Connect-User" einrichten - dieser bekommt eine "Minimalausstattung" an Privilegien ...

create user webservice_connect identified by {password};

grant create session to webservice_connect;
grant xdb_webservices to webservice_connect;
grant xdb_webservices_over_http to webservice_connect;

Und an diesen User werden nun die EXECUTE-Rechte an den PL/SQL-Objekten, die man als Webservice bereitstellen möchte, explizit und einzeln vergeben. Als SCOTT wird also folgendes ausgeführt ...

grant execute on scott.get_sal to webservice_connect;

Zum Abrufen des WSDL wird nun die gleiche URL verwendet, wie vorhin; der Login muss allerdings nun als WEBSERVICE_CONNECT erfolgen und nicht mehr als SCOTT. Das WSDL wird danach ganz genauso aussehen und der Webservice lässt sich auch genauso nutzen - nur kann man nun auf Objektebene festlegen, welche Funktionen und Prozeduren als Webservice bereitstehen. Eben nur diejenigen, an denen WEBSERVICE_CONNECT Rechte hat.

Packages können auf den ersten Blick nur als Ganzes freigegeben werden - das ist zunächst auch richtig. Auch mit einem Grant des Execute-Privilegs an WEBSERVICE_CONNECT wird das ganze Package freigeschaltet. Allerdings würde ich für ein Webservice-Szenario ein eigenes "Wrapper-Paket" schreiben, welches nur die für den Webservice freigegebene Schnittstelle enthält. Hier kann man auch gleich PL/SQL-eigene Datentypen wie boolean und record, die von den Native Webservices nicht unterstützt werden, auf SQL-Typen abbilden.

Möchte man nicht nur einen skalaren Datentypen (wie NUMBER oder VARCHAR2), sondern ein ganzes Objekt als Parameter übergeben, so können Objekttypen eingesetzt werden. Objekttypen können als Input- oder als Output-Parameter verwendet werden. Eine Funktion CREATE_EMP könnte dann so aussehen ...

create type emp_t as object(
 EMPNO      NUMBER(4),
 ENAME      VARCHAR2(10),
 JOB        VARCHAR2(9),
 MGR        NUMBER(4),
 HIREDATE   DATE,
 SAL        NUMBER(7,2),
 COMM       NUMBER(7,2),
 DEPTNO     NUMBER(2)
)
/

create or replace procedure create_emp(
  p_emp emp_t 
) as
begin
 :
end;
/

Hierzu lässt sich wiederum direkt ein WSDL abrufen (wie oben). Und der Objekttyp EMP_T wird hier auch als komplexer XML-Datentyp erkannt ...

<xsd:element name="EMP_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="EMPNO" type="xsd:double"/>
      <xsd:element name="ENAME">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="10"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="JOB">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="9"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="MGR" type="xsd:double"/>
      <xsd:element name="HIREDATE" type="xsd:date"/>
      <xsd:element name="SAL" type="xsd:double"/>
      <xsd:element name="COMM" type="xsd:double"/>
      <xsd:element name="DEPTNO" type="xsd:double"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

Auch Arrays (VARRAY oder Nested Table-Types) können verwendet werden, aber nicht auf "oberster" Ebene. So führt folgendes bereits beim Versuch, das WSDL abzurufen, zu einer Fehlermeldung:

create type varchar_table_t as table of varchar2(4000)
/

create or replace procedure my_func(
  p_vtab varchar_table_t 
) as
begin
 :
end;
/

Die Lösung ist einfach: Auf der obersten Ebene (also als Funktionsparamater) darf kein VARRAY oder TABLE-Typ verwendet werden. Es ist aber durchaus erlaubt, solche in einem Objekttypen zu nutzen. Wir wandeln das Beispiel also ein wenig um ...

create type varchar_table_t as table of varchar2(4000)
/

create type param_t as object(
  varchar_table varchar_table_t
);
/

create or replace procedure my_func(
  p_vtab param_t 
) as
begin
  :
end;
/

Nun wird das WSDL korrekt generiert und der Webservice kann verwendet werden. Hier der Auszug aus dem WSDL, welcher den komplexen Typ PARAM_T beschreibt.

<xsd:element name="PARAM_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="VARCHAR_TABLE">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="VARCHAR2" type="xsd:string" maxOccurs="unbounded" minOccurs="0"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

Alles in allem sind die Native Webservices ein sehr eleganter und einfacher Weg, SQL-Abfragen und PL/SQL-Objekte als SOAP-Style Webservices im Unternehmen bereitzustellen. Macht man es mit Java- oder .NET-Mitteln, muss hierfür meist aufwändig Code geschrieben werden. Mehr dazu findet Ihr in der Oracle-Dokumentation:

Oracle XML DB Developers' Guide: 33 Using Native Oracle XML DB Web Services

This blog posting is about Database native webservices; a feature, which was introduced with Oracle11g Release 1 (11.1). Database Native Webservices allow to publish a SQL Query or a PL/SQL unit (Function, Procedure, Package) as a SOAP Style Webservice without additional programming.

Database native Webservices are Part of the XML DB functionaliry. So make sure that XML DB is part of your database. The dictionary view DBA_REGISTRY allows to check this:
```
select comp_id, version from dba_registry where comp_id='XML'

COMP_ID                        VERSION
------------------------------ ---------------------
XML                            11.2.0.2.0
```
If this query returns no rows, then XML DB is not installed in your database - and you cannot use the webservice feature. XML DB can be installed into an existing database by starting the SQL script catqm.sql in $ORACLE_HOME/rdbms/admin. Of course, this must be done by a DBA.
Nou need the XML DB protocol server for HTTP being activated. After a normal database creation these are disabled by default. OracleXE users probably will have an activated HTTP protocol server on port 8080 (it is being used for working with APEX). If you are not sure, simply check on the database server with the lsnrctl status command.

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-MAY-2012 10:58:27

Copyright (c) 1991, 2010, Oracle.  All rights reserved.
:
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud030.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
:
Services Summary...

XDBADMIN

begin
  dbms_xdb.sethttport({http-port});
end;

Now, having XML DB present and the protocol server enabled, you need to enable the webservice feature as such. The following command needs to be executed once - again with DBA privileges or the XDBADMIN role.

DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet(
    NAME     => SERVLET_NAME,
    LANGUAGE => 'C',
    DISPNAME => 'Oracle Query Web Service',
    DESCRIPT => 'Servlet for issuing queries as a Web Service',
    SCHEMA   => 'XDB'
  );
  DBMS_XDB.addServletSecRole(
    SERVNAME => SERVLET_NAME,
    ROLENAME => 'XDB_WEBSERVICES',
    ROLELINK => 'XDB_WEBSERVICES'
  );
  DBMS_XDB.addServletMapping(
    PATTERN => '/orawsv/*',
    NAME    => SERVLET_NAME
  );
END;
/

Now the database is ready to provide database native webservices. For testing we use a plain database account (say: SCOTT). First we create a little PL/SQL function as follows ...

create or replace function get_sal(
  p_empno in emp.empno%type
) return emp.sal%type is
  l_sal emp.sal%type := null;
begin
  begin
    select sal into l_sal from emp where empno = p_empno;
  exception
    when no_data_found then null;
    when others then raise;
  end;
  return l_sal;
end;

This function GET_SAL will now be executed as a native web service. The database has been prepared and the function is ready. Before actually doing it, we need some privileges (of course, this feature is being protected). There are three different roles.

XDB_WEBSERVICES: This role enables the database native web service feature in general. A user having this role can access own objects and objects with explicit privileges as SOAP style webservices. But for our test we will also need the next role.
XDB_WEBSERVICES_OVER_HTTP: With this role, the database user can access his webservices also over "plain" HTTP, i.e. SSL communication is not required. Without this role, webservice calls only work over HTTPS (the protocol server must be configured for HTTPS in that case).
HTTP_WEBSERVICES_WITH_PUBLIC: This role enables also executing public objects as web services. Without it, only own objects and objects with explicit privileges can be accessed.

So our database user SCOTT gets two additional roles ...

grant xdb_webservices to scott
/

grant xdb_webservices_over_http to scott
/

Now all requirements are met. The first thing a client needs in order to call the web service, is the WSDL document. The WSDL is the interface description in XML format. It contains information about the name of the webservice, its input and output parameters and so on. The Oracle database generates the WSDL automatically. Just open your browser and visit the URL as follows ...

http://{database-host}:{http-port}/orawsv/SCOTT/GET_SAL?wsdl

The browser not prompts you for username and password. Log in as wou would do in SQL*Plus. Keep in mind that you need the credentials of the database user having the XDB_WEBSERVICES roles. The object owner might be different. After logging in you'll see the WSDL as follows ...

Having that URL every SOAP client can call the PL/SQL function as a web service. Following you'll see some screen shots. The flow of operation is basically the same for all client: First, get the WSDL from the database, then send a SOAP request (which can be generated from the WSDL) and then receive the SOAP response. How each step is being performed, depends on the SOAP client you are actually using. The second screen shot is from Oracle Application Express (which also has a webservice interface).

Looking at the mentioned XDB_WEBSERVICES privileges it seems that only complete schemas can be enabled or disabled for webservices - the privileges can only be granted to database users. There is no PL/SQL package to work at the procedure or function level. But we can use standard database methods here. The XDB_WEBSERVICES roles should never be granted to the object owners directly. It's a better idea to create a separate "webservice connect user" with a limited set of privileges ...

create user webservice_connect identified by {password};

grant create session to webservice_connect;
grant xdb_webservices to webservice_connect;
grant xdb_webservices_over_http to webservice_connect;

Then the object (functiom procedure) owner does explicit and individual grants on the desired objects to the "webservice connect user". So, in our example, SCOTT issues the following GRANT statements ...

grant execute on scott.get_sal to webservice_connect;

We get the WSDL document we use the same URL as previously. But when the browser prompts for a login, we don't use SCOTT anymore - now we use the WEBSERVICE_CONNECT user. If the grants have been implemented correctly, we can just execute the one procedure SCOTT.GET_SAL as a native webservice.

When it's about packages there's a bit more of work to do: With the described approach we can enable or disable web service access only at the package level. When webservice access is being enabled for a particular package, all public functions and procedures in that package can be executed. If this should be limited to a reduced set of procedures and functions, we need a "wrapper package" on top of the functional one. The wrapper package contains just the functions and procedures to be enabled for webservice access. And execute privileges are then being granted on the wrapper package. Such a wrapper package can also be used to handle PL/SQL-only data types like boolean or record which are not supported by the webservice feature.

If object structures are needed as webservice arguments (in pure PL/SQL we might use records), then we need to create Object Types for this. Object types can be used as input or output arguments. A function CREATE_EMP with an object type argument could look like this:

create type emp_t as object(
 EMPNO      NUMBER(4),
 ENAME      VARCHAR2(10),
 JOB        VARCHAR2(9),
 MGR        NUMBER(4),
 HIREDATE   DATE,
 SAL        NUMBER(7,2),
 COMM       NUMBER(7,2),
 DEPTNO     NUMBER(2)
)
/

create or replace procedure create_emp(
  p_emp emp_t 
) as
begin
 :
end;
/

Now we can -again- retrieve the WSDL using the URL .../orawsv/SCOTT/GET_EMP?wsdl. Our input argument has been detected and "translated" to an XML structure. A SOAP client will now be able to contruct a matching SOAP request.

<xsd:element name="EMP_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="EMPNO" type="xsd:double"/>
      <xsd:element name="ENAME">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="10"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="JOB">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="9"/>
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="MGR" type="xsd:double"/>
      <xsd:element name="HIREDATE" type="xsd:date"/>
      <xsd:element name="SAL" type="xsd:double"/>
      <xsd:element name="COMM" type="xsd:double"/>
      <xsd:element name="DEPTNO" type="xsd:double"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

We can also use VARRAY and nested table types to pass arrays or lists to the web service. But here we have one restriction. VARRAY or NESTED table types cannot be used at the "top-level", directly as procedure or function arguments. The database would then not be able to generate a WSDL and the web service cannot be called.

create type varchar_table_t as table of varchar2(4000)
/

create or replace procedure my_func(
  p_vtab varchar_table_t 
) as
begin
 :
end;
/

But there is a simple workaround: Just embed the VARRAY or table type into another object type - as illustrated in the following example:

create type varchar_table_t as table of varchar2(4000)
/

create type param_t as object(
  varchar_table varchar_table_t
);
/

create or replace procedure my_func(
  p_vtab param_t 
) as
begin
  :
end;
/

Now the WSDL can be generated and the webservice is callable again. Here is the WSDL part describing the input argument PARAM_T.

<xsd:element name="PARAM_T">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="VARCHAR_TABLE">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="VARCHAR2" type="xsd:string" maxOccurs="unbounded" minOccurs="0"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

As we have seen: It's very easy to publish PL/SQL objects as SOAP style webservices - just using the onboard utilities of the Oracle database: All we have to do is to enable the feature once and to grant privileges. In advanced scenarios we'll need additional wrapper packages or optimized data types for the procedure arguments.

Oracle XML DB Developers' Guide: 33 Using Native Oracle XML DB Web Services

Workshop zum Thema "Oracle TEXT": Mai 2012 in Düsseldorf und Stuttgart

noreply@blogger.com (Carsten Czarski) — Mon, 30 Apr 2012 08:57:00 +0000

Das Thema "Suchen" ist beim Umgang mit SQL und PL/SQL allgegenwärtig. Sei es die Suche in Dokumenten, Kunden-, Produkt-, Lieferanten- oder Geschäftsstellendaten - immer wieder müssen Sachbearbeiter anhand eines Stichworts, eines Namens oder einer Nummer etwas finden. Folgerichtig statten Entwickler alle Formulare und Anwendungsseiten mit entsprechender Suchfunktionalität und entwickeln teilweise sehr ausgefallene Programmlogik zum Umsetzen der Suche aus.

Die Oracle-Datenbank enthält bereits in der Standard Edition Oracle TEXT. Dabei denken viele an die Suche in Dokumentbeständen, aber das ist noch nicht alles: Mit Oracle TEXT können Sie Ihrer Anwendung eine mächtige Suchfunktion hinzufügen - nicht nur auf Dokumente, sondern auch auf "normale" relationale Datenbestände. Ähnlichkeitssuche ist ebenso möglich wie die Suche über mehrere Tabellen hinweg. Im Mai 2012 führen wir in Düsseldorf und Stuttgart einen Workshop zum Thema Oracle TEXT durch. Dort habt Ihr Gelegenheit, mehr über die Möglichkeiten mit Oracle TEXT zu erfahren. Vormittags geben wir einen Überblick über Oracle TEXT, mögliche Einsatzszenarien sowie Tipps & Tricks zum Umgang damit. Nachmittags widmen wir uns fortgeschrittenen Themen wie dem Monitoring, Oracle TEXT-Indizes auf mehrere Tabellen, den verschiedenen Konfigurationsmöglichkeiten und einiges mehr.

Die Agenda

10:00 Beginn der Veranstaltung
	Oracle TEXT: Ein Überblick Konzepte und generelle Funktionsweise von Oracle TEXT, Einsatzszenarien
	Umgang mit Oracle TEXT: Tipps & Tricks aus der Praxis Einblick in die Wartung eines Textindex, wie formuliert man Oracle TEXT Abfragen richtig? Welche grundsätzlichen Möglichkeiten habe ich?
Mittagspause
	Überwachung und Wartung eines Oracle Text Index im Detail Synchronisierungs- und Optimierungsintervalle, Strategien, Feststellung der Fragmentierung, Hochverfügbarkeit mit Schattenindex-Technologie und mehr ...
	"Textindex nach Art des Hauses": Konfiguration eines TEXT-Index Lexer-Einstellungen, Stopwortlisten, Umgang mit Umlauten, Thesaurus und mehr ...
	Oracle TEXT für alle Daten: User Datastore Wie erzeugt man einen Oracle TEXT-Index auf mehrere Tabellen ...?
17:00: Ende der Veranstaltung

Es lohnt sich also - die Teilnahme an der Veranstaltung ist kostenlos, also am besten gleich anmelden. Termine und Anmeldeinformationen findet Ihr auf der Workshop-Webseite.

SLEEP für eine Datenbanksession - ohne DBMS_LOCK

noreply@blogger.com (Carsten Czarski) — Fri, 13 Apr 2012 09:26:00 +0000

Recht häufig braucht man auch in der SQL- und PL/SQL Programmierung eine "SLEEP"-Funktion, also eine Funktion, die einfach nur eine bestimmte Anzahl Sekunden wartet und gar nichts tut. In der Datenbank ist eine solche auch vorhanden. Im Package DBMS_LOCK gibt es die Prozedur SLEEP ...

SQL> desc dbms_lock
:
PROCEDURE SLEEP
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

So weit - so gut. Für den DBA.

Für den Entwickler ist es nicht so einfach - denn DBMS_LOCK gehört nun nicht zu den Paketen, auf die man normalerweise Zugriff hat. Ein GRANT von DBMS_LOCK an PUBLIC wird allerdings kaum ein DBA machen - aus guten Gründen, denn die anderen Funktionen in DBMS_LOCK sollten schon mit Vorsicht eingesetzt werden.

Es gibt aber Alternativen. Wenn APEX in der Datenbank installiert ist, steht im Paket APEX_UTIL (welches jeder APEX Entwickler kennt), die Prozedur PAUSE zur Verfügung. Diese arbeitet genauso wie DBMS_LOCK.SLEEP, mit einer Ausnahme: Sie wartet maximal 2 Minuten (120 Sekunden), wie man an der nicht gewrappten Package Spec sehen kann.

procedure pause (
    --
    -- Pause for number of seconds identified by p_seconds
    --  (capped at 120 seconds)
    --
    p_seconds   in number)
    ;

Da APEX_UTIL an PUBLIC gegranted ist, kann auch jeder andere Datenbankuser APEX_UTIL.PAUSE nutzen. Datenbanken, in denen APEX nicht installiert ist, haben kein APEX_UTIL. Allerdings könnte man sich (als DBA) APEX zum Vorbild nehmen und eine allgemein nutzbare SLEEP-Funktion bereitstellen. Da man die Datenbankprozesse unter Kontrolle behalten möchte, ist eine maximale Wartezeit (wie APEX es macht) eine gute Idee. Die eigene SLEEP-Funktion könnte also so aussehen:

create or replace procedure global_sleep(p_seconds in number) is
begin
  if p_seconds > 300 or p_seconds < 0 then 
    raise_application_error(-20000, 'SLEEP IS ONLY ALLOWED BETWEEN 1 SECOND AND 5 MINUTES');
  else 
    dbms_lock.sleep(p_seconds);
  end if;
end global_sleep;
/

grant execute on global_sleep to public
/

create public synonym global_sleep for sys.global_sleep
/

Das Ergebnis ist eine SLEEP-Funktion, die jeder nutzen kann, ohne dass das Paket DBMS_LOCK freigegeben wird. Das Maximum liegt im Beispiel bei 5 Minuten - aber das kann ja jeder DBA so einstellen, wie er möchte. Ich würde diesen Ansatz sehr empfehlen - denn so bleiben die SLEEPs in einer kontrollierten Umgebung, mit mit DBA_DEPENDENCIES kann der DBA auch jederzeit nachprüfen, wo das genutzt wird.

Das ist aber noch nicht alles: Dem Entwickler bleibt, wenn der Zugriff auf DBMS_LOCK fehlt, APEX nicht installiert ist und der DBA keine GLOBAL_SLEEP Funktion bereitgestellt hat, noch eine dritte Möglichkeit: Nämlich mit Hilfe von Java in der Datenbank.

Java kennt die Methode sleep() in der Klasse java.lang.Thread. Da Java in der Datenbank niemals multithreaded abläuft, sondern alle Threads stets serialisiert werden, wirkt ein java.lang.Thread.sleep() genauso wie DBMS_LOCK.SLEEP(). Und die Nutzung ist sehr einfach - außer einem CREATE PROCEDURE sind keine besonderen Privilegien nötig.

create or replace procedure my_java_sleep (
  p_milli_seconds in number
) as language java name 'java.lang.Thread.sleep(long)';
/

SQL> exec my_java_sleep(5000);

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:05.00
SQL>

Wie man sieht: Es gibt einige Möglichkeiten für eine SLEEP-Funktion - Zugriff auf das mächtige DBMS_LOCK ist dafür (im Gegensatz zur landläufigen Meinung) gar nicht nötig.

Sometimes a developer needs a "sleep" function - this is not only true in Java or C/C++, but also in SQL and PL/SQL programming environments. Looking into the Oracle documentation one will quickly find the procedure SLEEP in the DBMS_LOCK package.

SQL> desc dbms_lock
:
PROCEDURE SLEEP
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

So far - so good. If you have DBA privileges.

But most developers do not have these. And DBMS_LOCK is a package which should not be granted to everyone - it's far too mighty. So a developer would at least have a discussion with his DBA before getting access to DBMS_LOCK. But there are some alternatives ...

When APEX has been installed into the database there is the package APEX_UTIL. It contains a procedure PAUSE which does exactly the same as DBMS_LOCK.SLEEP with one exception: The sleeping time is being capped at 120 seconds, which we can see by examining the package spec of APEX_UTIL.

procedure pause (
    --
    -- Pause for number of seconds identified by p_seconds
    --  (capped at 120 seconds)
    --
    p_seconds   in number)
    ;

Since APEX_UTIL is a public package it can be used without the need for special privileges. If APEX is not present in the database I'd recommend to take this appraoch as a template: The DBA could build an own public SLEEP funktion and make this available to all developers. Here is an example implemention:

create or replace procedure global_sleep(p_seconds in number) is
begin
  if p_seconds > 300 or p_seconds < 0 then 
    raise_application_error(-20000, 'SLEEP IS ONLY ALLOWED BETWEEN 1 SECOND AND 5 MINUTES');
  else 
    dbms_lock.sleep(p_seconds);
  end if;
end global_sleep;
/

grant execute on global_sleep to public
/

create public synonym global_sleep for sys.global_sleep
/

As a result, we have a special sleep function which is available to everyone in the database. Of course, it would also be possible to grant execution privilege to a special developer role instead of PUBLIC. The limits (here: 5 minutes) and the violation rule (APEX: quiet capping, here: raise an exception) can be implemented according to individual needs. Personally I'd strongly recommend an approach like this: The DBA keeps control, since it is his own procedure. He can control its behaviour and he can (via DBA_DEPENDENCIES) always see where is is being used.

But that's not all - we have another alternative: Imagine, we have no access to DBMS_LOCK, there is no "global" SLEEP funktion as described above, APEX is not installed and we need a sleep funcktion. The solution: Use Java in the Database.

Java has the method sleep() within the class java.lang.Thread. Java in the database supports the complete multithreading API (there is no "real" multithreading, since all threads are being serialized). Therefore we can use java.lang.Thread.sleep() to let the database session sleep for the given amount of milliseconds. Summarized: Thread.sleep() does the same as DBMS_LOCK.SLEEP. But opposed to that, no special privileges are needed and every database user with the CREATE PROCEDURE privilege can use it.

create or replace procedure my_java_sleep (
  p_milli_seconds in number
) as language java name 'java.lang.Thread.sleep(long)';
/

SQL> exec my_java_sleep(5000);

PL/SQL procedure successfully completed.

Abgelaufen: 00:00:05.00
SQL>

So - you don't really need special privileges in order to let your database session sleep for a while. But having one global procedure (similar to APEX) is most probably the best approach.

APEX Entwicklertag: Ende April in München, Düsseldorf und Berlin

noreply@blogger.com (Carsten Czarski) — Thu, 29 Mar 2012 07:06:00 +0000

Sehen wir uns dort ...?

In 2004 wurde Application Express (APEX) zusammen mit Oracle10g herausgebracht. Acht Jahre später kann sich die Zwischenbilanz sehen lassen: Allein in der deutschsprachigen Community sind 1200 Leser registriert - in Unternehmen mit Oracle-Infrastruktur entstehen neue APEX-Anwendungen teilweise täglich. Und APEX-Entwickler machen weit mehr als Masken zur Datenerfassung: Sie beschäftigen sich mit Themen wie APEX-Anwendungen fürmobile Endgeräte , Entwicklung moderner "Web 2.0"-Oberflächen, Cloud Computing und mehr.

Wichtig ist aber immer das persönliche Kennenlernen, der Austausch und die Diskussion. Aus diesem Grund trifft sich die deutschsprachige APEX Community im April 2012 in München, Düsseldorf und Berlin. Auf diesem Entwicklertag erhalten Sie einen Einblick in aktuelle Entwicklerthemen rund um Application Express: Wir informieren über den aktuellen Stand in APEX 4.1 und werden speziell das Thema "mobile APEX-Anwendungen" beleuchten. Unser Partnern Muniqsoft informiert über Layoutvarianten in APEX-Anwendungen - mit Tipps & Tricks direkt aus der Praxis. Die MT AG schließlich wird Implementierungsvarianten für gängige Aufgabenstellungen vorstellen - wiederum mit ganz konkretem Bezug zu praktischen Projekten.

Lassen Sie sich diese Möglichkeit zum direkten Erfahrungsaustausch nicht entgehen. Genaue Zeiten, Orte, die detaillierte Agenda und Anmeldeinformationen finden Sie auf der Veranstaltungs-Webseite. Die Teilnahme an der Veranstaltung ist kostenlos. Melden Sie sich noch heute an.

Filetransfer mit FTP oder SCP? Nein, mit der Oracle-Datenbank!

noreply@blogger.com (Carsten Czarski) — Mon, 12 Mar 2012 07:40:00 +0000

Wusstet Ihr schon, dass Ihr Dateien von eine, Oracle-Datenbankserver auf einen anderen ohne Betriebssystem-Login übertragen könnt ...? Ihr braucht weder SCP, nocht FTP oder andere Dateitransferprogramme - nur die Oracle-Datenbank auf beiden Maschinen. Und solche Situationen sind ja durchaus normal ...

Man macht einen Data Pump-Export auf der Quelldatenbank. Die Dump-Datei liegt nun im Dateisystem des Datenbankservers.
Diese Datei muss auf den anderen Datenbankserver übertragen werden. Normalerweise nutzt man hierfür dann FTP. SCP oder ähnliche Werkzeuge
Auf dem Zielsystem wird der Dump dann per Import wieder eingespielt.

Wenn man nun keinen Betriebssystem-Login hat (oder der System-Admin gerade nicht da ist), geht es in der Tat auch ohne: Mit dem PL/SQL Paket DBMS_FILE_TRANSFER. Ihr braucht dazu:

Ein Directory-Objekt auf dem Quellsystem - die zu kopierende Datei sollte in diesem Verzeichnis sein:
```
create directory srcdir as '/path/to/src/folder/';

grant read on directory srcdir to send_user;
```

Ein Directory-Objekt auf dem Zielsystem:

create directory dstdir as '/path/to/dest/folder/';

grant write on directory destdir to receive_user;

Einen Database Link vom Quellsystem zum Zielsystem:
```
create database link dblink_dest_system connect to receive_user identified by {password}
using 'destDatabase'
```
Übrigens muss die Ziel-Datenbank nicht zwingend in der tnsnames.ora hinterlegt sein. Im Notfall tut es auch ein Easy-Connect wie folgt: using 'destDatabase:1521/service.domain.com'

Ist das alles getan, so kann der Dateitransfer angestoßen werden: Data Pump Exports sind sicherlich sehr naheliegend - DBMS_FILE_TRANSFER überträgt aber auch andere Dateitypen (deren Dateigröße muss aber ein Vielfaches von 512 sein).

begin
  dbms_file_transfer.put_file(
    source_directory_object      => 'SRCDIR',
    source_file_name             => 'file-to-transfer',
    destination_directory_object => 'DSTDIR',
    destination_file_name        => 'new-filename-on-dest-system',
    destination_database         => 'dblink_dest_system'
  );
end;

Und Voilá. Ein Blick in das Verzeichnis auf dem Zielsystem sollte zeigen, dass die Datei angekommen ist. Besonders interessant ist das übrigens beim Einsatz der ASM-Technologie - denn wenn Dateien aus dem ASM gelesen oder in dieses geschrieben werden sollen, geht das nicht ohne weiteres mit Betriebssystem-Mitteln. Der Weg über DBMS_FILE_TRANSFER ist in solchen Fällen sogar der einfachere ...
Mehr Information zu DBMS_FILE_TRANSFER findet Ihr in der Dokumentation oder in einem Tipp von den Kollegen der DBA Community.

Did you know that you can transfer normal operating system files from one database server to another ... wothout logging into the operating system? You need neither SCP, nor FTP or any other file transfer program - just the Oracle database on both systems - and this is a quite common situation ...

One does a datapump export on the source database. The resulting dumpfile is now within a directory on the database server's filesystem.
This file needs to be moved to the target system. Most often operating system methods are employed for this task.
On the target system the dumpfile is being imported into the database.

So far - so good. But when logging into the operating system is not possible (either we don't have an account and the Sysadmin is not here or our account is locked or ... or ...) we can't transfer the file. But we don't need to - we just need DBMS_FILE_TRANSFER and a bit of preparation ...

Create a directory object on the source database - the file to be transferred should reside in that directory:
```
create directory srcdir as '/path/to/src/folder/';

grant read on directory srcdir to send_user;
```

Then create a directory object on the target database. The file will be placed into that folder:

create directory dstdir as '/path/to/dest/folder/';

grant write on directory destdir to receive_user;

And finally we need a database link from the source to the target system:
```
create database link dblink_dest_system connect to receive_user identified by {password}
using 'destDatabase'
```
BTW: The target database does not need to be contained in tnsnames.ora. Easy Connect Syntax is also possible as follows: using 'destDatabase:1521/service.domain.com'. We just don't need to log into the operating system.

When all preparations are done the file transfer operation can be started - and it is so simple. Datapump Dumpfiles are a quite common scenario - but DBMS_FILE_TRANSFER works with all kind of files (but keep in mind that the filesize must be a multiple of 512).

begin
  dbms_file_transfer.put_file(
    source_directory_object      => 'SRCDIR',
    source_file_name             => 'file-to-transfer',
    destination_directory_object => 'DSTDIR',
    destination_file_name        => 'new-filename-on-dest-system',
    destination_database         => 'dblink_dest_system'
  );
end;

There is also the procedure GET_FILE - which does the operation vice-versa.
Done. Looking into the directory on the target system should show that the file is now present. This technique is particular interesting when it's about reading from or writing to Oracle's ASM - if the source or target database uses ASM (which cannot be easily accessed with normal operating system commands) this approach is the most simple one.
More information about DBMS_FILE_TRANSFER is contained in the documentation.

APEX und PL/SQL Entwicklerstammtisch in München

noreply@blogger.com (Carsten Czarski) — Mon, 05 Mar 2012 08:21:00 +0000

Die APEX und PL/SQL Entwicklercommunity trifft sich zum lockeren "APEX Stammtisch" am 13.03.2012 um 19:30 Uhr in München. Einfach nur ein leckeres Bier, gutes Essen, interessante Gespräche rund um APEX und interessante Leute. Ein Tisch ist im "Augustiner Restaurant" in München reserviert.

Neuhauserstr. 27
80331 München

http://augustiner-restaurant.com/

Bitte meldet euch hier kurz an, damit wir wissen, wie viele kommen und ob und wann wir einen größeren Tisch reservieren müssen.
https://apex.oracle.com/pls/apex/f?p=18226:1:::::P1_EVENT_ID:361

SQL HAVING ... Kennt jeder, oder?

noreply@blogger.com (Carsten Czarski) — Fri, 24 Feb 2012 09:14:00 +0000

Dieses Blog Posting widme SQL HAVING. Das sieht dann zwar eher nach "SQL Grundkurs" aus, aber mir fällt schon auf, dass HAVING in der Praxis gerne vergessen und durch Subselects "emuliert" wird. Und das muss ja nicht sein. Also machen wir heute ein kurzes, aber einfaches Blog Posting zum Thema HAVING. Wie immer, reicht die Tabelle EMP als Beispiel völlig aus.

select 
  d.dname, 
  sum(e.sal) sal_sum,
  count(e.empno) emp_cnt
from emp e join dept d on (d.deptno = e.deptno)
group by d.dname

DNAME             SAL_SUM    EMP_CNT
-------------- ---------- ----------
ACCOUNTING           8750          3
RESEARCH            10875          5
SALES                9400          6

Nun möchten wir nur die Abteilungen mit mindestens 5 Mitarbeitern haben ... häufig findet man dann diese Lösung, die auch durchaus funktioniert ...

select * from (
  select 
    d.dname, 
    sum(e.sal) sal_sum,
    count(e.empno) emp_cnt
  from emp e join dept d on (d.deptno = e.deptno)
  group by d.dname
) 
where emp_cnt >= 5

DNAME             SAL_SUM    EMP_CNT
-------------- ---------- ----------
RESEARCH            10875          5
SALES                9400          6

Analog geht es natürlich auch mit der WITH-Klausel. Es geht aber auch eleganter, denn für das Filtern auf aggregierten Werten gibt es eben HAVING.

select 
  d.dname, 
  sum(e.sal) sal_sum,
  count(e.empno) emp_cnt
from emp e join dept d on (d.deptno = e.deptno)
group by d.dname
having count(e.empno) > 5

So ... und das war's auch schon für heute ...

Today I'll post about SQL HAVING. This looks a bit like "SQL - Lesson 1", but my observation is that HAVING is very often "emulated" with subqueries or WITH clauses. Using the HAVING clause would make some SQL queries simpler - and for that reason: This short posting is about SQL HAVING . As always, I'll use the well-known table EMP as example.

select 
  d.dname, 
  sum(e.sal) sal_sum,
  count(e.empno) emp_cnt
from emp e join dept d on (d.deptno = e.deptno)
group by d.dname

DNAME             SAL_SUM    EMP_CNT
-------------- ---------- ----------
ACCOUNTING           8750          3
RESEARCH            10875          5
SALES                9400          6

Now we want to have only the departments with more than 5 employees. The following query is quite often being used in such scenarios. And it works fine ...

select * from (
  select 
    d.dname, 
    sum(e.sal) sal_sum,
    count(e.empno) emp_cnt
  from emp e join dept d on (d.deptno = e.deptno)
  group by d.dname
) 
where emp_cnt >= 5

DNAME             SAL_SUM    EMP_CNT
-------------- ---------- ----------
RESEARCH            10875          5
SALES                9400          6

A SQL WITH clause would also work, of course. But the more elegant solution to filter on aggregated values is HAVING.

select 
  d.dname, 
  sum(e.sal) sal_sum,
  count(e.empno) emp_cnt
from emp e join dept d on (d.deptno = e.deptno)
group by d.dname
having count(e.empno) > 5

And that's it ...

DOAG Entwicklerkonferenz im Juni 2012!

noreply@blogger.com (Carsten Czarski) — Fri, 10 Feb 2012 07:56:00 +0000

Heute ist es mal nichts technisches: Ich möchte einfach nur auf eine DOAG-Veranstaltung hinweisen, die ich sehr interessant finde. Ich zitiere aus dem Call for Papers der DOAG:

Erstmals veranstaltet die DOAG in Bonn eine Konferenz für den Erfahrungsaustausch der Software-Entwickler, die DOAG 2012 Development. Am 14. Juni 2012 verwandelt sich das Maritim Hotel Bonn zum Brennpunkt der Development-Szene im Oracle-Umfeld. Seien Sie dabei und tauchen Sie tief in die Software-Entwicklung mittels Tools und Technologien aus dem Hause Oracle ein! Im Mittelpunkt stehen der Erfahrungsaustausch zu Java, JDeveloper und PL/SQL, aber auch zu den Werkzeugen SQL Forms und Reports.

– C A L L F O R P R E S E N T A T I O N S –

Werden Sie Referent auf der DOAG 2012 Development! Reichen Sie ab sofort Ihr Vortragsangebot hier online ein!

Sie haben bis zum 05. März 2012 die Gelegenheit, sich um einen Vortrag zu bewerben! Als Referent erhalten Sie natürlich freien Eintritt zur Konferenz. Wir suchen Vorträge zu folgenden Themenbereichen:

SQL und DB-Design
PL/SQL und Apex
Forms und ADF
Reporting und BI
Karten und Geodaten (Spatial)
SOA und BPM
Java und Open Source
Strategie und Software-Architektur

Wir freuen uns auf Ihre Vortragsbewerbung!

Alle weiteren Informationen zur DOAG 2012 Development erhalten Sie hier:
http://www.doag.org/events/konferenzen/doag-2012-development.html

Ich werde auf jeden Fall Vorträge einreichen - und hoffe, dass diese a) etwas angenommen werden und b) dass ich mich dort mit vielen anderen Entwicklern austauschen kann.

XML Dokumente mit SQL vergleichen: XMLDIFF und XMLPATCH

noreply@blogger.com (Carsten Czarski) — Mon, 30 Jan 2012 13:04:00 +0000

Heute möchte ich etwas über zwei ganz interessante SQL-Funktionen in der Datenbank schreiben: XMLDIFF und XMLPATCH. Fangen wir mit XMLDIFF an: Es vergleicht XML-Dokumente. Und dazu am besten mal ein Beispiel ...

<document xmlns="my-namespace">
 <leeres-xmltag></leeres-xmltag>
 <xmltag-inhalt>"</xmltag-inhalt>
</document>

<ns:document xmlns:ns="my-namespace">
 <ns:leeres-xmltag/>
 <ns:xmltag-inhalt>&quot;</ns:xmltag-inhalt>
</ns:document>

Die Frage "Worin unterscheiden sich die beiden XML-Dokumente?" ist nur auf den ersten Blick einfach. Probieren wir die XMLDIFF-Funktion einfach mal aus ...

select xmldiff(
 xmltype('
  <document xmlns="my-namespace">
   <leeres-xmltag></leeres-xmltag>
   <xmltag-inhalt>"</xmltag-inhalt>
  </document>'
 ), xmltype('
  <ns:document xmlns:ns="my-namespace">
   <ns:leeres-xmltag/>
   <ns:xmltag-inhalt>&quot;</ns:xmltag-inhalt>
  </ns:document>'
 )
) from dual
/ 

XMLDIFF(XMLTYPE('<DOCUMENTXMLNS="MY-NAMESPACE"><LEERES-XMLTAG></LEERES-XMLTAG><X
--------------------------------------------------------------------------------
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns
.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmln
s:ns="my-namespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al
gorithm="global"?>
</xd:xdiff>

Die XMLDIFF-Funktion liefert zwar etwas zurück, bei genauer Betrachtung stellt man jedoch fest, dass das Tag xd:xdiff leer ist - es wurden also keine Unterschiede zwischen den XML-Dokumenten festgestellt - und das ist auch richtig so. Denn die im Text sichtbaren Unterschiede haben nach dem XML-Standard keine Bedeutung. Ein leeres Tag darf sowohl <tag></tag> als auch <tag/> geschrieben werden. Gleiches gilt für Zeichenentities oder Namensräume. XMLDIFF ist also zunächst eine sehr interessante Funktion, um überhaupt Unterschiede zwischen XML-Dokumenten zu erkennen. Doch XMLDIFF leistet noch mehr: Nehmen wir mal zwei XML-Dokumente, die sich tatsächlich unterscheiden ...

select xmldiff(
 xmltype('
  <document xmlns="my-namespace">
   <tag>Text 1</tag>
   <tag>Text 3</tag>
  </document>'
 ), xmltype('
  <ns:document xmlns:ns="my-namespace">
   <ns:tag>Text 1</ns:tag>
   <ns:tag>Text 2</ns:tag>
   <ns:tag option="a">Text 3</ns:tag>
  </ns:document>'
 )
) as xml_diff from dual
/ 

XML_DIFF
------------------------------------------------------------------------------------------
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.co
m/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:ns="my-namespace"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="g
lobal"?>
  <xd:update-node xd:node-type="text" xd:xpath="/ns:document[1]/ns:tag[2]/text()[1]">
    <xd:content>Text 2</xd:content>
  </xd:update-node>
  <xd:append-node xd:node-type="element" xd:parent-xpath="/ns:document[1]">
    <xd:content>
      <ns:tag option="a">Text 3</ns:tag>
    </xd:content>
  </xd:append-node>
</xd:xdiff>

Wie man sehen kann, werden die Unterschiede nicht einfach nur aufgelistet: Wie sein Unix-Vorbild diff liefert auch XMLDIFF Anweisungen zurück, wie man das erste XML-Dokument in das zweite überführen kann. Und ebenso wie das Ergebnis eines Unix diff mit dem patch Werkzeug auf andere XML-Dokumente angewendet werden kann, kann auch das Ergebnis von XMLDIFF auf ein neues XML-Dokument mit Hilfe von XMLPATCH angewendet werden.

with diff as (
  -- Obiges SELECT XMLDIFF hier ...
)
select xmlpatch(
 xmltype('
  <document xmlns="my-namespace">
   <tag>Text a</tag>
   <tag>Text b</tag>
  </document>'
 ),
 xml_diff
) from diff
/

Die zuerst ermittelten XMLDIFF-Anweisungen ...

Ändere den Text des zweiten XML-Tags tag innerhalb von document auf Text 2
Hänge innerhalb des Tags document ein neues Tag tag mit dem Inhalt Text 3 und dem Attribut option="a" an.

... werden auf das im zweiten Parameter übergebene XML-Dokument angewendet. Das Ergebnis sieht in etwa wie folgt aus (die durch XMLPATCH ausgelösten Änderungen sind rot markiert):

XMLPATCH
------------------------------------------------------------------------------------------
<document xmlns="my-namespace">
  <tag>Text a</tag>
  <tag>Text 2</tag>
  <tag xmlns:ns="my-namespace" option="a">Text 3</tag>
</document>

Intern nutzt Oracle die Funktionen auch selbst - speziell für die in Place Schema Evolution der XML DB sind sie wichtig. Allerdings kann man sie auch selbst nutzen, wenn es darum geht, Änderungen an einem XML-Dokument zu erkennen und diese ggfs. auf andere Dokumente zu übertragen ...

Today I'd like to write something about two interesting SQL functions in the Oracle database: XMLDIFF and XMLPATCH. I'll start with XMLDIFF : As its name indicates - it compares XML documents. I'll illustrate this with an example ...

<document xmlns="my-namespace">
 <empty-xmltag></empty-xmltag>
 <xmltag-content>"</xmltag-content>
</document>

<ns:document xmlns:ns="my-namespace">
 <ns:empty-xmltag/>
 <ns:xmltag-content>&quot;</ns:xmltag-content>
</ns:document>

What do you think? Are these XML documents equal ... or do they differ? This seems to be easy only at the first glance. But let's have XMLDIFF do the work ...

select xmldiff(
 xmltype('
  <document xmlns="my-namespace">
   <empty-xmltag></empty-xmltag>
   <xmltag-content>"</xmltag-content>
  </document>'
 ), xmltype('
  <ns:document xmlns:ns="my-namespace">
   <ns:empty-xmltag/>
   <ns:xmltag-content>&quot;</ns:xmltag-content>
  </ns:document>'
 )
) from dual
/ 

XMLDIFF(XMLTYPE('<DOCUMENTXMLNS="MY-NAMESPACE"><LEERES-XMLTAG></LEERES-XMLTAG><X
--------------------------------------------------------------------------------
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns
.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmln
s:ns="my-namespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-al
gorithm="global"?>
</xd:xdiff>

The functions' output indicates that the documents are equal - from the XML point of view. And this makes sense since the XML standard allows different markup for the same content. Empty tags, for instance, can be expressed as a single tag with a trailing slash (<tag/>) as well as with an opening and a closing tag (<tag></tag>). Character entities like " have the same meaning as the character itself ("). And there are more examples beyond these two. So comparing XML documents means more than just comparing the text contents - the XML standard must be kept in mind, and XMLDIFF does exactly this. In the next example we'll compare two documents which are different; also from the "XML point of view".

select xmldiff(
 xmltype('
  <document xmlns="my-namespace">
   <tag>Text 1</tag>
   <tag>Text 3</tag>
  </document>'
 ), xmltype('
  <ns:document xmlns:ns="my-namespace">
   <ns:tag>Text 1</ns:tag>
   <ns:tag>Text 2</ns:tag>
   <ns:tag option="a">Text 3</ns:tag>
  </ns:document>'
 )
) as xml_diff from dual
/ 

XML_DIFF
------------------------------------------------------------------------------------------
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.co
m/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:ns="my-namespace"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="g
lobal"?>
  <xd:update-node xd:node-type="text" xd:xpath="/ns:document[1]/ns:tag[2]/text()[1]">
    <xd:content>Text 2</xd:content>
  </xd:update-node>
  <xd:append-node xd:node-type="element" xd:parent-xpath="/ns:document[1]">
    <xd:content>
      <ns:tag option="a">Text 3</ns:tag>
    </xd:content>
  </xd:append-node>
</xd:xdiff>

We can see, that XMLDIFF returns more than just the information that these two documents are different. As it's UNIX pendant diff it returns a "delta" - instructions how to modify the first document in order to get the second. This output can be consumed by XMLPATCH; as we'll see in the next example: We'll apply the "patch instruction" to another XML document ...

with diff as (
  -- Obiges SELECT XMLDIFF hier ...
)
select xmlpatch(
 xmltype('
  <document xmlns="my-namespace">
   <tag>Text a</tag>
   <tag>Text b</tag>
  </document>'
 ),
 xml_diff
) from diff
/

The "patching" instructions ...

Modify the text contents of the second tag tag within document to Text 2
Append another tag tag containing the text Text 3 and the attrbute option="a" within document.

... are now being applied to another XML document which is (in this case) even different from the very first one (which we used to compare initially). The output of the XMLPATCH operation is as follows ...

XMLPATCH
------------------------------------------------------------------------------------------
<document xmlns="my-namespace">
  <tag>Text a</tag>
  <tag>Text 2</tag>
  <tag xmlns:ns="my-namespace" option="a">Text 3</tag>
</document>

XMLDIFF and XMLPATCH are used internally by Oracle for the XML DB functionality of In Place XML Schema Evolution. But one can (as we have seen) use them also for own purposes - the functions are interesting when it's about comparing XML documents and computing deltas between them. einem XML-Dokument zu erkennen und diese ggfs. auf andere Dokumente zu übertragen ...

Login als User "A" - mit dem Password von "B": Proxy Authentication!

noreply@blogger.com (Carsten Czarski) — Mon, 09 Jan 2012 09:00:00 +0000

Es ist in der Tat möglich: Ich kann mich an der Oracle-Datenbank als User "A" mit dem Passwort des Benutzers "B" anmelden - und das ist keine Sicherheitslücke. Ich möchte dieses Blog-Posting dem Thema Proxy Authentication widmen. Das kann man ganz besonders in einer dreischichtigen Webarchitektur gebrauchen. Denn dort werden Datenbankverbindungen nahezu immer statisch im Application Server konfiguriert ( data-sources.xml). Wenn man in einer solchem Umgebung mit den Nutzerkonten der Datenbank arbeiten möchte, hat man ein Problem: Man kann sie nicht alle in der data-sources.xml einrichten; erstmal sind es oft zu viele und zweitens kann man die data-sources.xml nicht so einfach im laufenden Betrieb ändern. Die Lösung ist Proxy Authentication: Dabei wird die Datenbankverbidnung im Application Server fest mit einem technischen User und einem Passwort eingerichtet; der tatsächliche Datenbankuser, nach dem sich auch die Privilegien richten, wird zur Laufzeit festgelegt. Und das ganze funktioniert wie folgt: Zuerst brauchen wir einen technischen User TECHUSER (mit Passwort TECHUSER) und zwei "echte" User (REALUSER1 und REALUSER2).

create user techuser identified by techuser
/

grant connect to techuser
/

reate user realuser1 identified by realuser1
/

grant connect, resource to realuser1
/

reate user realuser2 identified by realuser2
/

grant connect, resource to realuser2
/

Und damit die User in einer Anwendung unterscheidbar werden, bekommen Sie nun eine Kopie der EMP-Tabelle, aber mit unterschiedlichen Inhalten ...

craete table realuser1.emp as select * from scott.emp where deptno = 10
/

craete table realuser2.emp as select * from scott.emp where deptno = 20
/

Und jetzt geht es los: Zuerst muss man der Datenbank sagen, dass TECHUSER die Erlaubnis bekommt, sich mit seinem eigenen Passwort als REALUSER1 oder REALUSER2 "auszugeben". Man kann auch sagen: TECHUSER wird der Proxy User für die Clients REALUSER1 und REALUSER2.

alter user realuser1 grant connect through techuser
/

alter user realuser2 grant connect through techuser
/

Der Setup lässt sich in der Dictionary View PROXY_USERS auch überprüfen:

SQL> select * from proxy_users;

PROXY           CLIENT          AUT FLAGS
--------------- --------------- --- -----------------------------------
TECHUSER        REALUSER1       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES
TECHUSER        REALUSER2       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

Mit SQL*Plus kann man das jetzt schon ausprobieren:

D:\>sqlplus.exe techuser[realuser1]/techuser

SQL*Plus: Release 11.1.0.6.0 - Production on Do Dez 22 16:17:30 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> select user from dual;

USER
------------------------------
REALUSER1

Man sieht sehr schön, dass Username und Passwort des TECHUSER zum Login verwendet werden; in eckigen Klammern wird aber festgelegt, für wen die Datenbankverbindung eigentlich aufgemacht werden soll.

So weit, so gut, so einfach. Aber für eine dreischichtige Applikation nutzt das bis jetzt noch gar nichts. Schließlich kann man nicht einfach den "echten" User in eckige Klammern in die Definition der Datenbankverbindung eintragen, denn diese ist ja statisch. Der User, für den der Connect gelten soll, soll sich aber beliebig ändern können - also dynamisch sein. Also müssen wir in der Lage sein, den Usernamen, den man bei SQL*Plus in eckige Klammern setzt, per Java-Code zu setzen ... Schauen wir uns zunächst ein kleines Java-Testprogramm an: Es gibt zuerst den Namen des angemeldeten Users aus ( select user from dual) und dann die Inhalte der EMP Tabelle. Achtet darauf, dass Username und Passwort des TECHUSER hart kodiert sind - und das wird so bleiben!

import java.sql.*;
import oracle.jdbc.*;
import java.io.*;
import java.util.*;

public class proxyConnect {
  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@sccloud030:1521/orcl","techuser","techuser");
    
/*
 * Code zum Setzen des Client-Usernamens HIER !!!
 */

    Statement stmt = null;
    ResultSet rs1 = null;
    stmt = con.createStatement();
    rs1 = stmt.executeQuery("select user from dual");
    while (rs1.next()) {
      System.out.println("Database userid: " + rs1.getString(1));
    }  
    rs1.close();
    stmt.close();

    stmt = con.createStatement();
    rs1 = stmt.executeQuery("select * from emp");
    while (rs1.next()) {
      System.out.println("EMPNO: " + rs1.getString(1) + " ["+rs1.getString(2)+"] - DEPTNO: "+rs1.getString("DEPTNO"));
    }  
    rs1.close();
    stmt.close();
    con.close();
  }
}

Beim ersten Test sagt uns das Programm nur, dass wir als TECHUSER verbunden sind und dass es keine EMP-Tabelle gibt ...

D:\> java.exe proxyConnect realuser1
Database userid: TECHUSER
Exception in thread "main" java.sql.SQLSyntaxErrorException: 
ORA-00942: Tabelle oder View nicht vorhanden
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
        :

Dann bauen wir jetzt den Code ein, der den "richtigen" User setzt. Tauscht die Zeilen ...

/*
 * Code zum Setzen des Client-Usernamens HIER !!!
 */

... durch diese hier aus: Als Usernamen nehmen wir den ersten Parameter der Kommandozeile.

    Properties props = new Properties();
    props.put("PROXY_USER_NAME", args[0]);
    ((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);

Neu kompilieren und wieder ausführen. Und .. voilá:

D:\> java proxyConnect realuser1
Database userid: REALUSER1
EMPNO: 7782 [CLARK] - DEPTNO: 10
EMPNO: 7839 [KING] - DEPTNO: 10
EMPNO: 7934 [MILLER] - DEPTNO: 10

D:\> java proxyConnect realuser2
Database userid: REALUSER2
EMPNO: 7369 [SMITH] - DEPTNO: 20
EMPNO: 7566 [JONES] - DEPTNO: 20
EMPNO: 7788 [SCOTT] - DEPTNO: 20
EMPNO: 7876 [ADAMS] - DEPTNO: 20
EMPNO: 7902 [FORD] - DEPTNO: 20

Nur durch die Angabe des Namens wird nun festgelegt, als welcher User die Datenbanksession laufen soll. Damit kann ein Java-Programm (bspw. in einem Application Server) mit einer statischen Datasource-Definition dennoch dynamisch den Datenbankuser wechseln. Denn eine bestehende Proxy-Verbindung kann geschlossen werden, um danach auf der gleichen "physikalischen" Datenbankverbindung eine neue Proxy-Verbindung für einen anderen User zu öffnen. Im folgenden Code habe ich das mal illustriert - zur besseren Übersicht habe ich die Datenbankaktionen entfernt und gegen den Pseudocall auf erledigeDatenbankaktionen() ausgetauscht.

  public static void main(String args[]) throws Exception {
    // Physikalische Verbindung öffnen als TECHUSER
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@sccloud030:1521/orcl","techuser","techuser");
    
    // Proxy-Verbindung für User REALUSER1 öffnen, etwas tun und schließen
    Properties props = new Properties();
    props.put("PROXY_USER_NAME", "realuser1");
    ((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);  
    erledigeDatenbankaktionen(con);
    ((OracleConnection)con).close(OracleConnection.PROXY_SESSION); 

    // Die physikalische Verbindung ist immer noch offen ...
    // Proxy-Verbindung für User REALUSER2 öffnen, etwas tun und schließen
    props.put("PROXY_USER_NAME", "realuser2");
    ((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);  
    erledigeDatenbankaktionen(con);
    ((OracleConnection)con).close(OracleConnection.PROXY_SESSION); 

    // Physikalische Verbindung schließen
    con.close();
  }

Beim Umstieg von Client/Server- zu Webanwendungen ist ein solches Vorgehen hochinteressant.

Wichtig ist, dass REALUSER1 bzw. REALUSER2 immer noch das CREATE SESSION-Privileg brauchen; auch darf der Account nicht gelockt sein. Es ist aber durchaus möglich, direkte Connects zu unterbinden - dazu muss man "nur" das Passwort auf einen nicht ermittelbaren Wert setzen ...

SQL> alter user realuser1 identified by values 'Hallo'

Nun wird der Text "Hallo" als Hashwert in die Oracle-Passworttabelle geschrieben - ein Connect als REALUSER1 ist nun theoretisch möglich: wenn man das Passwort herausbekommt, dessen Hashwert "Hallo" ergibt. Defacto jedoch kann man sich nur noch über den TECHUSER als REALUSER1 anmelden. Aber auch ein Parallelbetrieb mit direkten und Proxy-Verbindungen ist natürlich problemlos machbar.

Mehr zur Proxy Authentication in der Dokumentation: Dazu sind zwei Links wichtig:

Security Guide: Using a Middle Tier Server for Proxy Authentication
http://docs.oracle.com/cd/E11882_01/network.112/e16543/authentication.htm#i1010326
JDBC Developer's Guide: Proxy Authentication
http://docs.oracle.com/cd/E11882_01/java.112/e16548/proxya.htm#CHDHHAAD

Yes - it is possible: You can connect to the Oracle database as say: User "A" with the password of User "B" - and that is not a security vulnerability. In this blog posting I'd like to elaborate a bit on proxy authentication - this feature is particular useful in three-tier-applications. Database connections are typically part of the static application server configuration ("data-sources.xml"). And when such a java environments want to use the user accounts in the database there is a problem: We cannot add each individual user to the datasource configuration. The first arguments is, that there might be just too many users - the second one is that changes to data-sources.xml are often not possible without downtime. So we need a connection using one single technical user's credentials and the ability to set the desired username at runtime. And that is all what proxy authentication is about. We'll start with creating the technical user (TECHUSER) and two "real" user accounts (REALUSER1 and REALUSER2).

create user techuser identified by techuser
/

grant connect to techuser
/

reate user realuser1 identified by realuser1
/

grant connect, resource to realuser1
/

reate user realuser2 identified by realuser2
/

grant connect, resource to realuser2
/

REALUSER1 and REALUSER2 now get a copy of the EMP table with different contents: we want to differentiate between the two later on.

craete table realuser1.emp as select * from scott.emp where deptno = 10
/

craete table realuser2.emp as select * from scott.emp where deptno = 20
/

Now we declare TECHUSER as the proxy user for REALUSER1 and REALUSER2. This is kind of a GRANT statement. The privilege to act as (the clients) REALUSER1 or REALUSER2 is granted to TECHUSER.

alter user realuser1 grant connect through techuser
/

alter user realuser2 grant connect through techuser
/

You might review this in the dictionary view PROXY_USERS:

SQL> select * from proxy_users;

PROXY           CLIENT          AUT FLAGS
--------------- --------------- --- -----------------------------------
TECHUSER        REALUSER1       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES
TECHUSER        REALUSER2       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

A first test with SQL*Plus:

D:\>sqlplus.exe techuser[realuser1]/techuser

SQL*Plus: Release 11.1.0.6.0 - Production on Do Dez 22 16:17:30 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> select user from dual;

USER
------------------------------
REALUSER1

TECHUSER used his own credentials to log in. But the user as which the connection should be established was provided within the brackets. In SQL*Plus it is that easy: We logged in as TECHUSER/techuser and we now are REALUSER1.

So far - so good. But for real world environments this is virtually useless - no end user connects with SQL*Plus. And we cannot add the brackets to the static datasource definition in the application server: we need to set the client user dynamically with some code - and the following example will show how to do this. First I have a little testing program in java language. It first connects to the database with hardcoded TECHUSER credentials, then it looks up "as who" it is connected and finally it shows the contents of the EMP table. Again: The username and password arguments in the call to DriverManager.getConnection are hard coded and this will not change!

import java.sql.*;
import oracle.jdbc.*;
import java.io.*;
import java.util.*;

public class proxyConnect {
  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@sccloud030:1521/orcl","techuser","techuser");
    
/*
 * The java code to set the client username goes here !!!
 */

    Statement stmt = null;
    ResultSet rs1 = null;
    stmt = con.createStatement();
    rs1 = stmt.executeQuery("select user from dual");
    while (rs1.next()) {
      System.out.println("Database userid: " + rs1.getString(1));
    }  
    rs1.close();
    stmt.close();

    stmt = con.createStatement();
    rs1 = stmt.executeQuery("select * from emp");
    while (rs1.next()) {
      System.out.println("EMPNO: " + rs1.getString(1) + " ["+rs1.getString(2)+"] - DEPTNO: "+rs1.getString("DEPTNO"));
    }  
    rs1.close();
    stmt.close();
    con.close();
  }
}

This program so far does not know anything about proxy connections - the results are therefore straightforward: We are connected as TECHUSER and there is no EMP table.

D:\> java.exe proxyConnect realuser1
Database userid: TECHUSER
Exception in thread "main" java.sql.SQLSyntaxErrorException: 
ORA-00942: table or view does not exist.
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
        :

Now we'll add the "magic" lines of code to set the client username. Exchange those three lines ...

/*
 * The java code to set the client username goes here !!!
 */

... with these - we'll take the first command line argument as the username.

    Properties props = new Properties();
    props.put("PROXY_USER_NAME", args[0]);
    ((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);

Recompile and test again - and ... it works.

D:\> java proxyConnect realuser1
Database userid: REALUSER1
EMPNO: 7782 [CLARK] - DEPTNO: 10
EMPNO: 7839 [KING] - DEPTNO: 10
EMPNO: 7934 [MILLER] - DEPTNO: 10

D:\> java proxyConnect realuser2
Database userid: REALUSER2
EMPNO: 7369 [SMITH] - DEPTNO: 20
EMPNO: 7566 [JONES] - DEPTNO: 20
EMPNO: 7788 [SCOTT] - DEPTNO: 20
EMPNO: 7876 [ADAMS] - DEPTNO: 20
EMPNO: 7902 [FORD] - DEPTNO: 20

Only by giving the username on the command line we connected as REALUSER1 or REALUSER2 - the password always was TECHUSER's one. With this approach a java program can obtain a database connection from its application server, set the client username and connect to the database as another "real" user. And this proxy connection can be changed while the main "physical" connection remains open. The following code shows this - for clarity I have removed the actual database actions and replaced them with the pseudo method doDatabaseActions().

  public static void main(String args[]) throws Exception {
    // open the physical connection as TECHUSER
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@sccloud030:1521/orcl","techuser","techuser");
    
    // open the proxy connection for REALUSER1, do something and close
    Properties props = new Properties();
    props.put("PROXY_USER_NAME", "realuser1");
    ((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);  
    doDatabaseActions(con);
    ((OracleConnection)con).close(OracleConnection.PROXY_SESSION); 

    // the physical connection is still open ...
    // now open the proxy connection for REALUSER2, do something and close
    props.put("PROXY_USER_NAME", "realuser2");
    ((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);  
    doDatabaseActions(con);
    ((OracleConnection)con).close(OracleConnection.PROXY_SESSION); 

    // finally close the physical connection
    con.close();
  }

When client/server applications are moving to a web environment, this technology gets highly interesting - because the application can keep its existing user, role and security model and move on to the web technology where all database connections are done with one technical user.

REALUSER1 or REALUSER2 still need their CREATE SESSION privilege - the accounts also must exist in the database and must not be locked. But (if needed) you actually can "forbid" direct connections by setting the password to an "impossible" value ...

SQL> alter user realuser1 identified by values 'Hello'

The REALUSER1 account is still open and connects are possible in theory. But since the IDENTIFIED BY VALUES command directly wrote "Hello" into the password table, one would have to find out one of the password - which evaluate to "Hello" during the hashing process - I'll say it that way: at least very difficult. Connections as REALUSER1 now are only possible as connections through TECHUSER. But it is also possible to have both direct and proxy connections in parallel.

More about proxy authentication is in the documentation:

Security Guide: Using a Middle Tier Server for Proxy Authentication
http://docs.oracle.com/cd/E11882_01/network.112/e16543/authentication.htm#i1010326
JDBC Developer's Guide: Proxy Authentication
http://docs.oracle.com/cd/E11882_01/java.112/e16548/proxya.htm#CHDHHAAD

Zum Ende von 2011: Oracle 4.1.4 ...

noreply@blogger.com (Carsten Czarski) — Thu, 22 Dec 2011 16:24:00 +0000

Das letzte Blog Posting in 2011 soll nichts technisches mehr sein - die letzten Tage im Jahr kann man ja ein wenig zur Ruhe kommen. Daher hier nur ein paar Screenshots, die ich gemacht habe, als ich eine alte Oracle 4.1.4 nochmals installiert habe. Kein XML, kein PL/SQL, kein Oracle TEXT, kein APEX ... aber EMP ... und DEPT!

Frohe Weihnachten und ein gutes neues Jahr 2012!

Anteile in Prozent in einem Bericht kalkulieren - nur mit SQL!

noreply@blogger.com (Carsten Czarski) — Wed, 07 Dec 2011 15:39:00 +0000

Heute morgen erreichte mich eine SQL-Frage - und das Problem taucht sicherlich öfter auf, daher veröffentliche ich meine Antwort hier für alle. Ausgangspunkt ist eine Tabelle.

|art   |anzahl     |kosten       |anteil    |
|------|-----------|-------------------------
| A    |        55 |       16,95 |     null |
| B    |        55 |        5,45 |     null |
| C    |        55 |        3,20 |     null |
| D    |        55 |        1,95 |     null |

Die rechte Spalte anteil soll nun den prozentualen (hier:) Kostenanteil der Zeile im Verhältnis zur Summe über alle Zeilen enthalten. Damit ich die Tabelle nicht neu anlegen muss, übertrage ich das Beispiel auf die uns allen bekannte Tabelle EMP: Wir wollen also zu jeder das Gehalt als prozentualen Anteil an der Gesamt-Gehaltssumme ausgedrückt sehen. Fangen wir mit dem "klassischen" Ansatz an ...

with summe as (
  select sum(sal) sal from emp
)
select 
  e.ename, 
  e.sal,
  e.sal / s.sal * 100 anteil
from emp e, summe s
/ 

ENAME            SAL  ANTEIL
---------- --------- -------
SMITH           1280    2,76
ALLEN           2560    5,51
WARD            2000    4,31
JONES           4760   10,25
MARTIN          2000    4,31

Diese Lösung dürfte auf nahezu allen Datenbanksystemen funktionieren - mit der WITH-Klausel wird zunächst die Gehaltsumme ermittelt und dann per Join in die eigentliche Abfrage integriert. Wenn man den Gehaltsanteil pro Abteilung (DEPTNO) sehen möchte, müsste man die Abfrage in der WITH-Klausel mit einem GROUP BY versehen und eine Joinbedingung in die Hauptabfrage einbauen ...

with summe as (
  select deptno, sum(sal) sal from emp
  group by deptno
)
select 
  e.ename, 
  e.deptno,
  e.sal,
  e.sal / s.sal * 100 anteil_dept
from emp e join  summe s on (e.deptno = s.deptno)
/

ENAME          DEPTNO       SAL ANTEIL_DEPT
---------- ---------- --------- -----------
SMITH              20      1280        7,36
ALLEN              30      2560       17,02
WARD               30      2000       13,30
JONES              20      4760       27,36
MARTIN             30      2000       13,30
BLAKE              30      4560       30,32

So weit - so gut. Aber es geht auch wesentlich schöner: Setzt man analytische Funktionen ein, so kann man sich die Inline-View sparen - das macht dann alles die Datenbank ... so könnte man die analytische Variante von SUM wie folgt verwenden.

select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over ()) * 100 anteil
from emp e
/
ENAME          DEPTNO       SAL  ANTEIL
---------- ---------- --------- -------
SMITH              20      1280    2,76
ALLEN              30      2560    5,51
WARD               30      2000    4,31

Hier wird SUM ohne GROUP BY verwendet - einfach weil es die analytische Variante ist. Das sog. Query Window, welches angibt, über welche Zeilen das Aggregat gebildet werden soll, wird in der OVER -Klausel festgelegt. Und wenn die Klammern leer sind, heißt das soviel wie "über alles". Aber auch die Variante mit den Anteil pro Abteilung ist machbar.

select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over (partition by deptno)) * 100 anteil
from emp e
/

ENAME          DEPTNO       SAL  ANTEIL
---------- ---------- --------- -------
CLARK              10      3920   28,00
KING               10      8000   57,14
MILLER             10      2080   14,86
JONES              20      4760   27,36
FORD               20      4800   27,59

Jetzt steht etwas in der OVER()-Klausel drin. Mit PARTITION BY wird festgelegt, dass das Aggregat (die Summe) nach Abteilungen berechnet werden soll. Das Schlüsselwort PARTITION BY ist übrigens nicht zu verwechseln mit der Tabellenpartitionierung und im Gegensatz zu dieser erfordern die analytischen Funktionen keine separaten Lizenzen. Aber es geht sogar noch einfacher: Denn für diese Aufgabe gibt es eine spezielle analytische Funktion: RATIO_TO_REPORT.

select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over () * 100 anteil
from emp e
/

ENAME          DEPTNO        SAL  ANTEIL
---------- ---------- ---------- -------
SMITH              20       1280    2,76
ALLEN              30       2560    5,51
WARD               30       2000    4,31
JONES              20       4760   10,25
MARTIN             30       2000    4,31

Und natürlich geht auch hier die PARTITION BY-Klausel ...

select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over (partition by deptno) * 100 anteil
from emp e
/

Analytische Funktionen lohnen auf jeden Fall eine genauere Betrachtung. Jeder, der mit SQL und der Oracle-Datenbanken arbeitet, sollte sie kennen. Neben den hier beschriebenen Dingen lassen sich damit auch gleitende Durchschnitte, fortlaufende Summen, Rankings und andere Dinge, die sonst recht kompliziertes SQL mit Subselects erfordern, mit einfachen Funktionsaufrufen erledigen. Zum Nachlesen ist hier noch ein Link zur Dokumentation.

|art   |count      |cost         |cost_share |
|------|-----------|--------------------------
| A    |        55 |       16,95 |      null |
| B    |        55 |        5,45 |      null |
| C    |        55 |        3,20 |      null |
| D    |        55 |        1,95 |      null |

with total as (
  select sum(sal) sal from emp
)
select 
  e.ename, 
  e.sal,
  e.sal / s.sal * 100 share
from emp e, total s
/ 

ENAME            SAL   SHARE
---------- --------- -------
SMITH           1280    2,76
ALLEN           2560    5,51
WARD            2000    4,31
JONES           4760   10,25
MARTIN          2000    4,31

with total as (
  select deptno, sum(sal) sal from emp
  group by deptno
)
select 
  e.ename, 
  e.deptno,
  e.sal,
  e.sal / s.sal * 100 share_dept
from emp e join  total s on (e.deptno = s.deptno)
/

ENAME          DEPTNO       SAL  SHARE_DEPT
---------- ---------- --------- -----------
SMITH              20      1280        7,36
ALLEN              30      2560       17,02
WARD               30      2000       13,30
JONES              20      4760       27,36
MARTIN             30      2000       13,30
BLAKE              30      4560       30,32

So far, so good. This works, but there are much more elegant approaches. Now I'll drop my inline view and use the analytic variant of SUM() for the problem.

select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over ()) * 100 share
from emp e
/
ENAME          DEPTNO       SAL   SHARE
---------- ---------- --------- -------
SMITH              20      1280    2,76
ALLEN              30      2560    5,51
WARD               30      2000    4,31

select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over (partition by deptno)) * 100 share
from emp e
/

ENAME          DEPTNO       SAL   SHARE
---------- ---------- --------- -------
CLARK              10      3920   28,00
KING               10      8000   57,14
MILLER             10      2080   14,86
JONES              20      4760   27,36
FORD               20      4800   27,59

select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over () * 100 share
from emp e
/

ENAME          DEPTNO        SAL   SHARE
---------- ---------- ---------- -------
SMITH              20       1280    2,76
ALLEN              30       2560    5,51
WARD               30       2000    4,31
JONES              20       4760   10,25
MARTIN             30       2000    4,31

select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over (partition by deptno) * 100 share
from emp e
/

DROP TABLE .. Sind Sie sicher?

noreply@blogger.com (Carsten Czarski) — Mon, 21 Nov 2011 10:45:00 +0000

UPDATE: Auf einem internen "Produktionsserver" (sofern man davon sprechen kann) habe ich das Package natürlich gleich installiert. Und heute morgen dann gemerkt, dass ein TRUNCATE / DROP auch mal der Normalfall sein kann. Als ich mich im APEX-Workspace einloggen wollte, kam die Fehlermeldung, dass ein DROP / TRUNCATE auf die Tabelle WWV_FLOW_USER_ACCESS_LOG2$ nicht möglich ist. APEX macht das von sich aus: Da hilft es nix: Der Trigger muss erweitert werden! Ich habe den Code (unten) angepasst und eine Ausnahme für APEX eingebaut; es kann durchaus sein, dass noch ein paar hinzukommen. Wenn euch da etwas vor mir auffällt, einfach in den Kommentar schreiben.

Jüngst bin ich nochmals über das Thema Database Event Triggers mit PL/SQL gestolpert - man kann ja (schon seit einiger Zeit) auch Trigger auf Datenbank-Events anlegen - ein klassisches Beispiel wäre das "Verbieten" einer DROP-Operation auf Produktionssystemen - einfach zur Sicherheit. Der Trigger dazu ist einfach und schnell gebaut ...

create or replace trigger tr_drop_is_prohibited
before drop on database
begin
  raise_application_error(-20000, 'DROP IS NOT ALLOWED ON THIS SYSTEM');
end;
/

Einfach, aber effektvoll. Man kann nun auf dieser Datenbank nichts mehr droppen (außer dem Trigger selbst, natürlich). Aber für den praktischen Einsatz etwas zu "grobstollig". Erstens ist ein TRUNCATE Table mittlerweile schlimmer als ein DROP TABLE, welches man mit dem Recycle Bin (FLASHBACK TABLE ... TO BEFORE DROP) rückgängig machen kann, zum anderen muss es ja manchmal doch sein, dass man etwas droppen will ... und dann muss man erst den Trigger deaktivieren und danach wieder aktivieren.

Nach diesem Gedanken kam ich auf die Idee, eine Art "Sind Sie Sicher?"-Mechanismus für DROP- und TRUNCATE-Operationen zu bauen. In der Datenbank funktioniert er nur andersherum als bei Microsoft Windows: Man muss die Drop-Operation zuerst deklarieren und dann ausführen. Auf einem Produktionssystem ist sowas vielleicht doch ganz hilfreich und erspart unter Umständen das eine oder andere versehentlich gedroppte Object. Hier ist nun der Code (muss als SYS eingespielt werden) - wir beginnen mit einem Package, mit dem die DROP-Operation vorher deklariert wird:

create or replace package PREDROP authid current_user is
  g_forsession boolean := false;
  g_nextobject boolean := false;
  g_objectname varchar2(4000) := '';
  g_objecttype varchar2(4000) := '';
  g_objectuser varchar2(4000) := '';

  procedure obj(
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  );
  procedure obj;
  procedure sess(
    p_enable in boolean default true
  );
  procedure clear; 
  procedure info;
end PREDROP;
/
sho err

create or replace package body PREDROP is
  procedure sess (
    p_enable in boolean default true
  ) is 
  begin
     g_forsession := p_enable;
  end sess;

  procedure obj is
  begin
    g_forsession := false;
    g_nextobject := true;

    g_objectname := null;
    g_objectuser := null;
    g_objecttype := null;
  end obj;

  procedure obj (
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  ) is
  begin
    g_forsession := false;
    g_nextobject := true;
    g_objectname := p_objectname;
    g_objecttype := p_objecttype;
    g_objectuser := p_objectuser;
  end obj;

  procedure clear is
  begin
    g_forsession := false;
    g_nextobject := false;
    g_objectname := null;
    g_objecttype := null;
    g_objectuser := null;
  end clear;

  procedure info is
  begin
    dbms_output.put_line('********************************************');
    dbms_output.put_line('* Enable Session Flag: ' ||(case when g_forsession then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Enable Object Flag:  ' ||(case when g_nextobject then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Object name:         ' ||g_objectname);
    dbms_output.put_line('* Object type:         ' ||g_objecttype);
    dbms_output.put_line('* Object owner:        ' ||g_objectuser);
    dbms_output.put_line('********************************************');
    dbms_output.put_line('');
  end info;
end PREDROP;
/
sho err

grant execute on PREDROP to public
/

create public synonym PREDROP for sys.PREDROP
/

Execute-Privileg und Public Synonym nicht vergessen und dann geht es auch schon weiter. Als nächstes kommt der Database Event Trigger, der ins Package hineinguckt, ob das Objekt, was da gerade bearbeitet werden soll, vorher deklariert wurde - wenn ja, geht die Operation durch, wenn nicht, wird ein Fehler ausgelöst ...

create or replace trigger tr_drop_protection 
before drop or truncate on database
declare
  l_raiseerror     boolean := true;

  l_namematch      boolean := false;
  l_typematch      boolean := false;
  l_usermatch      boolean := false;
  l_match          boolean := false;
  l_always_allowed boolean := false;

  /* UPDATE: 
   * Some code need to be allowed in general!
   */ 
  function always_allowed(p_owner in varchar2, p_object in varchar2) return boolean is
    l_always boolean := false;
  begin
    if p_owner like 'APEX_%' and p_object like 'WWV_FLOW_%LOG%' then
      l_always := true;
      goto ende;
    end if;
    <<ende>>
    return l_always;
  end always_allowed;

  function match(p_string1 in varchar2, p_string2 in varchar2) return boolean is
    l_match boolean := false;
  begin
    if p_string1 = p_string2 or p_string2 is null then 
      l_match := true;
    else 
      l_match := false;
    end if;
    return l_match;
  end match;
begin
    l_namematch := match(ora_dict_obj_name, PREDROP.g_objectname);
    l_typematch := match(ora_dict_obj_type, PREDROP.g_objecttype);
    l_usermatch := match(ora_dict_obj_owner, PREDROP.g_objectuser);
    l_match := l_namematch and l_usermatch and l_typematch;
    l_always_allowed := always_allowed(ora_dict_obj_owner, ora_dict_obj_name);

    if ((PREDROP.g_nextobject or PREDROP.g_forsession) and l_match) or l_always_allowed  then
      l_raiseerror := false;
    else
      l_raiseerror := true;
    end if;

    if not PREDROP.g_forsession and not l_raiseerror then 
      PREDROP.clear;
    end if;

    if l_raiseerror then
      raise_application_error(-20000, 'Enable DROP/TRUNCATE of '||ora_dict_obj_type|| ' "'||ora_dict_obj_name||'" with the '||upper('PREDROP')||' package.');
    end if;
end;
/

Und das war's auch schon. Von jetzt an funktioniert DROP und TRUNCATE etwas anders ...

SQL> drop table emp3;
drop table emp3
*
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-20000: Enable DROP/TRUNCATE of TABLE "EMP3" with the PREDROP package.
ORA-06512: in Zeile 36

SQL> exec predrop.obj('EMP3');

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> drop table emp3;

Tabelle wurde gelöscht.

SQL>

Für Truncate gilt das gleiche. Lässt man den Objektnamen auf SQL NULL, gilt die Freigabe für die nächste Tabelle, egal, wie sie heißt. Setzt man auch den Object Type auf SQL NULL, gilt die Freigabe für alle Objekte im Schema. Und der Parameter P_OBJECTUSER gilt analog. Ruft man dann noch die Prozedur PREDROP.SESS auf, gilt die Freigabe nicht nur für das nächste Objekt, sondern für die ganze Session. Auch auf Produktionsungebungen läuft ja mal ein Skript mit mehreren gewollten DROP-Operationen - solche Vorgänge sollten nicht behindert werden; das Package soll helfen und nicht im Weg sehen. Man könnte das jetzt natürlich auch noch mit regulären Ausdrücken erweitern und so die völlige Flexibilität bekommen - aber das machen wir ein anderes Mal ...

Was denkt Ihr ...?

create or replace trigger tr_drop_is_prohibited
before drop on database
begin
  raise_application_error(-20000, 'DROP IS NOT ALLOWED ON THIS SYSTEM');
end;
/

create or replace package PREDROP authid current_user is
  g_forsession boolean := false;
  g_nextobject boolean := false;
  g_objectname varchar2(4000) := '';
  g_objecttype varchar2(4000) := '';
  g_objectuser varchar2(4000) := '';

  procedure obj(
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  );
  procedure obj;
  procedure sess(
    p_enable in boolean default true
  );
  procedure clear; 
  procedure info;
end PREDROP;
/
sho err

create or replace package body PREDROP is
  procedure sess (
    p_enable in boolean default true
  ) is 
  begin
     g_forsession := p_enable;
  end sess;

  procedure obj is
  begin
    g_forsession := false;
    g_nextobject := true;

    g_objectname := null;
    g_objectuser := null;
    g_objecttype := null;
  end obj;

  procedure obj (
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  ) is
  begin
    g_forsession := false;
    g_nextobject := true;
    g_objectname := p_objectname;
    g_objecttype := p_objecttype;
    g_objectuser := p_objectuser;
  end obj;

  procedure clear is
  begin
    g_forsession := false;
    g_nextobject := false;
    g_objectname := null;
    g_objecttype := null;
    g_objectuser := null;
  end clear;

  procedure info is
  begin
    dbms_output.put_line('********************************************');
    dbms_output.put_line('* Enable Session Flag: ' ||(case when g_forsession then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Enable Object Flag:  ' ||(case when g_nextobject then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Object name:         ' ||g_objectname);
    dbms_output.put_line('* Object type:         ' ||g_objecttype);
    dbms_output.put_line('* Object owner:        ' ||g_objectuser);
    dbms_output.put_line('********************************************');
    dbms_output.put_line('');
  end info;
end PREDROP;
/
sho err

grant execute on PREDROP to public
/

create public synonym PREDROP for sys.PREDROP
/

create or replace trigger tr_drop_protection 
before drop or truncate on database
declare
  l_raiseerror     boolean := true;

  l_namematch      boolean := false;
  l_typematch      boolean := false;
  l_usermatch      boolean := false;
  l_match          boolean := false;
  l_always_allowed boolean := false;

  /* UPDATE: 
   * Some code need to be allowed in general!
   */
  function always_allowed(p_owner in varchar2, p_object in varchar2) return boolean is
    l_always boolean := false;
  begin
    if p_owner like 'APEX_%' and p_object like 'WWV_FLOW_%LOG%' then
      l_always := true;
      goto ende;
    end if;
    <<ende>>
    return l_always;
  end always_allowed;

  function match(p_string1 in varchar2, p_string2 in varchar2) return boolean is
    l_match boolean := false;
  begin
    if p_string1 = p_string2 or p_string2 is null then 
      l_match := true;
    else 
      l_match := false;
    end if;
    return l_match;
  end match;
begin
    l_namematch := match(ora_dict_obj_name, PREDROP.g_objectname);
    l_typematch := match(ora_dict_obj_type, PREDROP.g_objecttype);
    l_usermatch := match(ora_dict_obj_owner, PREDROP.g_objectuser);
    l_match := l_namematch and l_usermatch and l_typematch;
    l_always_allowed := always_allowed(ora_dict_obj_owner, ora_dict_obj_name);

    if ((PREDROP.g_nextobject or PREDROP.g_forsession) and l_match) or l_always_allowed  then
      l_raiseerror := false;
    else
      l_raiseerror := true;
    end if;

    if not PREDROP.g_forsession and not l_raiseerror then 
      PREDROP.clear;
    end if;

    if l_raiseerror then
      raise_application_error(-20000, 'Enable DROP/TRUNCATE of '||ora_dict_obj_type|| ' "'||ora_dict_obj_name||'" with the '||upper('PREDROP')||' package.');
    end if;
end;
/

SQL> drop table emp3;
drop table emp3
*
ERROR in line 1:
ORA-00604: Error at recursive SQL level 1
ORA-20000: Enable DROP/TRUNCATE of TABLE "EMP3" with the PREDROP package.
ORA-06512: line 36

SQL> exec predrop.obj('EMP3');

PL/SQL-Procedure successfully completed.

SQL> drop table emp3;

Table dropped.

SQL>

Ab morgen: DOAG2011

noreply@blogger.com (Carsten Czarski) — Fri, 21 Oct 2011 12:37:00 +0000

Bis zur DOAG2011 ist es ja nicht mehr solange hin - ich werde an allen drei Tagen dort sein und freue mich schon darauf, das eine oder andere Gesicht aus der Oracle-Community wiederzusehen ... Wie in den letzten Jahren habe ich auch dieses Jahr wieder ein paar Vorträge - hier sind sie ...

Demo-Kino: Transformers 4.1: Von Standard nach Cool
15.11.2011 10:00 - 10:45 Uhr - Foyer Tokio

Mit nur wenig Aufwand kann man einer APEX-Applikation moderne Elemente hinzufügen. Eine eher langweilige Geschäftsanwendung wird im Film transformiert - Elemente wie "Google Suggest", Karten, Volltextsuche und moderne Layoutelemente kommen hinzu. Die neue Anwendung holt den Nutzer dort ab, wo er steht, mit Anwendungsfeatures, die er aus dem Internet kennt ...
Das Navi in der Datenbank: Oracle11g has NAVTEQ on Board
16.11.2011 10:00 - 10:45 Uhr - Raum Tokio

Wussten Sie schon, dass Sie eine postalische Adresse mit der Oracle-Datenbank mit einer einfachen SQL-Abfrage in Koordinaten, also Längen- und Breitengrade, umwandeln können. Mit der Geocoding-Engine ist das überhaupt kein Problem - und durch die enge Integration mit der Datenbank kann dies Teil der normalen Geschäftslogik werden.

Basis dafür sind NAVTEQ-Daten - Oracle has NAVTEQ on Board. Der Vortrag zeigt, was das NAVTEQ ODF Dataset (Oracle Data Format) beinhaltet, wie man es installiert und wie man damit nicht nur Geocoding, sondern auch Routing oder andere Operationen durchführen kann (diesen Vortrag mache ich gemeinsam mit Till Kreiler von NAVTEQ).
SQL oder NoSQL? Das ist hier die Frage! Die "Oracle NoSQL Datenbank"
16.11.2011 14:00 - 14:45 Uhr - Raum Tokio

Dieses Thema finde ich ziemlich spannend - ein wenig habe ich schon mit der neuen Oracle NoSQL Database herumgespielt - und ich muss sagen: Das ist wirklich ein neuer Ansatz - und auf die Reaktionen und Diskussionen bin ich auch schon sehr gespannt ...

Bekannte Webseiten wie Twitter, Facebook oder URL-Verkürzer wie tinyurl.com setzen NoSQL-Datenbanken ein - und auf der OOW wurde jüngst die Oracle NoSQL Database angekündigt. Der Vortrag stellt die Oracle NoSQL Database vor, geht auf Unterschiede zwischen NoSQL-Datenbanken und den "klassischen" RDBMS ein und stellt mögliche Einsatzszenarien vor. NoSQL-Datenbanken werden genutzt, um extrem große Datenmengen extrem vieler User zu speichern. Im Gegensatz zu unserer "klassischen" Oracle-Datenbank spielen das Datenmodell, Transaktionskonzepte und Datenintegrität keine Rolle - das einzige, was zählt, ist die wirklich ständige Verfügbarkeit, die mit massiver Parallelisierung erreicht wird. Eine NoSQL-Datenbank richtet sich an Entwickler, denn es gibt keine Abfragesprache und keinen Query-Optimizer. Zugriffe erfolgen über eine Programmierschnittstelle (API) - alle Intelligenz steckt in der Anwendung.
apex.meinefirma.de: APEX Hosting im eigenen Unternehmen
17.11.2011 13:00 - 13:45 Uhr - Raum Istabul

Nahezu überall ändern sich Prozesse und Anforderungen immer schneller: gebraucht wird also eine Plattform, welche die Entwicklung neuer Anwendungen genauso schnell ermöglicht. Der öffentliche Demoserver "apex.oracle.com" zeigt, wie es geht: Entwickler können Ihre Workspaces selbstständig beantragen und verwalten, ohne manuelles des DBA Eingreifen sofort aktiv werden; neue Anwendungen stehen sofort bereit. Der Vortrag zeigt, wie ein Server "apex.meinefirma.de" aufgesetzt und betrieben werden kann, welche Hardware man braucht, wie Ressourcenkonflikte gelöst werden und worauf geachtet werden sollte. Das ist Cloud Computing "in Action".

Auf der ODTUG Kscope-Konferenz, an der ich im Juni teilgenommen hatte, wurde Twitter ganz massiv genutzt - Tweets über die einzelnen Vorträge und neue Ideen kamen quasi im Sekundentakt. Ich bin gespannt, wie das auf der DOAG2011 läuft. Meine Teamkollegen und ich werden euch über den Twitter Account @OracleBUDB und natürlich mit dem Hashtag #doag2011 auf dem Laufenden halten ... also dranbleiben ...

Von Java nach SQL: Java-Objekte und die Datenbank-JVM

noreply@blogger.com (Carsten Czarski) — Tue, 04 Oct 2011 07:39:00 +0000

In der Vergangenheit hatte ich ja schon einige Blog-Postings zum Thema "Java in der Datenbank"; das letzte Posting zum Thema Twitter-Postings mit der Datenbank, die diversen Postings zum Thema Dateisystem-Zugriffe haben alle eines gemein: Sie verwenden die in der Datenbank enthaltene Java-Engine. Wenn es aber darum geht, Java-Funktionen aus PL/SQL oder SQL heraus zu nutzen, ist immer auch eine Parameter-Mapping gefragt. Und genau dem möchte ich mich heute widmen. Dabei geht es mir aber nicht um das Abbilden eines VARCHAR2, NUMBER oder DATE auf Java-Datentypen - das ist sehr einfach, wie man hier sehen kann ...

create or replace and compile java source named simple_test as

public class SimpleTest {
  public static String getString() {
    return "Hallo Welt";
  }
  public static java.sql.Timestamp getDate() {
    return new java.sql.Timestamp(new java.util.Date().getTime());
  }
  public static int getNumber() {
    return 4711;
  }
} 
/
sho err

create or replace package simple_test_plsql is
  function get_string return varchar2;
  function get_date return date;
  function get_number return number;
end simple_test_plsql;
/
sho err

create or replace package body simple_test_plsql is
  function get_string return varchar2 as 
  language java name 'SimpleTest.getString() return java.lang.String';
  function get_date return date as
  language java name 'SimpleTest.getDate() return java.sql.Timestamp';
  function get_number return number as
  language java name 'SimpleTest.getNumber() return int';
end simple_test_plsql;
/
sho err

select 
  simple_test_plsql.get_string,
  simple_test_plsql.get_date,
  simple_test_plsql.get_number
from dual
/

GET_STRING           GET_DATE            GET_NUMBER
-------------------- ------------------- ----------
Hallo Welt           21.09.2011 09:19:40       4711

Den Experten ist sicher schon aufgefallen, dass die Java-Methoden alle "static" sind; das ist auch logisch so, denn PL/SQL ist ja keine objektorientierte Sprache. Mit "statischen" Java-Methoden wird Java wie eine prozedurale Sprache genutzt - es entspricht eher dem Konzept von PL/SQL, daher können nur statische Java-Methoden auf PL/SQL-Pakete, Prozeduren oder Funktionen abgebildet werden.

Das einzige, wo man ein wenig aufpassen muss, sind DATE-Mappings - diese können auf die Klassen java.sql.Date und java.sql.Timestamp abgebildet werden. In Java bedeutet das aber etwas anderes als in SQL. Arbeitet man beim Mapping mit java.sql.Date, dann werden nur Tag, Monat und Jahr an die SQL-Ebene zurückgegeben - also das Datum - möchte man die Uhrzeit haben, sollte man mit java.sql.Timestamp arbeiten. Aber abgesehen davon ist das Mapping solcher Datentypen ja wirklich einfach - und wenn man Java-Bibliotheken in der Datenbank verwendet, sollte man sich am besten stets eine eigene "Schicht" mit Java-Methoden schreiben, die einfach auf SQL und PL/SQL abzubilden sind - wo die Methodensignaturen also am besten nur solche einfachen Datentypen nutzen und keine komplexen Objekte.

Aber jetzt geht's ans Eingemachte: Angenommen, wir haben eine Java-Bibliothek (und als Beispiel nehmen wir mal java.io.File), die ein komplexes Objekt repräsentiert. Und wir möchten nun eben nicht für jedes Attribut einen eigenen Call bauen, sondern alle relevanten Attribute mit einem einzigen Call abholen. Uns prinzipiell gibt es in der Datenbank ja auch Objekttypen bzw. User Defined Types, mit denen man sowas wie ein "File" modellieren kann. Also fangen wir mal damit an.

create type file_t as object(
  file_path      varchar2(4000),
  file_name      varchar2(4000),
  file_size      number,
  last_modified  date,
  is_dir         char(1),
  is_writeable   char(1),
  is_readable    char(1),
  file_exists    char(1)
)
/

Allerdings kann man nun keine direkte Verbindung zwischen dem SQL-Typen FILE_T und der Java-Klasse java.io.File herstellen - beide haben ja überhaupt nichts miteinander zu tun - ein Mapping ist aber mit Hilfe von einer "Java-Brückenklasse" möglich: Mit oracle.sql.STRUCT kann man Objekttypen aus Java heraus erstellen und an SQL bzw. PL/SQL zurückgeben. Was wir also brauchen, ist eine (statische) Java-Methode, die mit Hilfe von java.io.File die Information zu einer Datei holt, damit ein STRUCT für den FILE_T erzeugt und diese kann dann in die SQL-Ebene zurückgibt.

create or replace and compile java source named java_file as
import java.math.*;
import java.util.*;
import java.io.*;

import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JavaFile {
  public static STRUCT getFile(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    Object[]         o      = new Object[8];

    File f = new File(pFile);
    if (f.exists()) {
     o[0] = f.getPath();
     o[1] = f.getName();
     o[2] = new BigDecimal(f.length());
     o[3] = new java.sql.Timestamp(f.lastModified());
     o[4] = (f.isDirectory()?"Y":"N");
     o[5] = (f.canWrite()?"Y":"N");
     o[6] = (f.canRead()?"Y":"N");
     o[7] = "Y";
     return new STRUCT(sDescr, con, o);
    } else {
     return null;
    } 
  }
}
/
sho err

create or replace function get_file_atts (p_file in varchar2) return file_t
is language java name 'JavaFile.getFile(java.lang.String) return oracle.sql.STRUCT';
/
sho err

Hier muss der Java-Code mit der SQL-Ebene zusammenspielen - deshalb wird zuerst eine JDBC-"Verbindung" aufgebaut - das ist aber nichts weiter als eine Art "Pointer" auf die Datenbanksitzung, denn das Java läuft ja bereits in der Datenbank. Es wird ein StructDescriptor-Objekt erzeugt, welches auf den vorher erzeugten Typen FILE_T zeigt. Alle Attribute des FILE_T werden auf Java-Seite in einem Array der Klasse Object[] abgelegt. Hier muss man als Entwickler aufpassen, dass die verwendeten Java-Typen auf die Datentypen des SQL-Typen passen (siehe einfache Mappings oben). Mit diesem Array, dem StructDescriptor-Objekt und dem Connection-Objekt wird dann ganz zum Schluß ein STRUCT-Objekt generiert, welches genau auf den Typen FILE_T passt. Natürlich kann man auch komplexere Dinge bauen und ein "STRUCT in ein STRUCT" schachteln, man muss nur aufpassen, dass alles zur Definition der Objekttypen in SQL passt.

Zum Abschluß kommt wieder die PL/SQL-Definition der Funktion - in PL/SQL wird als IN Parameter ein VARCHAR2 und als Rückgabewert ein FILE_T deklariert. Folgerichtig passt das auf ein java.lang.String als Eingabe- und ein oracle.sql.STRUCT als Rückgabewert. Alle Objekttypen werden auf Java-Seite als oracle.sql.STRUCT abgebildet - die Verknüpfung mit dem konkreten Objekttypen erledigt der StructDescriptor ...

Alles klar? Dann können wir testen ...

SQL> select get_file_atts('/') from dual;
select get_file_atts('/') from dual
                               *
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.io.FilePermission /
read) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '/', 'read'
)

Ach ja: Zum Dateisystemzugriff braucht es Privilegien - die muss der DBA einräumen. Wenn Ihr vor diesen Meldungen Ruhe haben wollt, gebt eurem Datenbankschema das JAVASYSPRIV-Privileg, dann har er alle Rechte, die man haben kann (für Produktion nicht zu empfehlen). Alternativ könnt Ihr einfach den in der Fehlermeldung dargestellten Aufruf ausführen - der räumt genau das fehlende Privileg ein. Wenn Ihr das Privileg habt, probiert es nochmal ...

SQL> select get_file_atts('/tmp') from dual;

GET_FILE_ATTS('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR, IS
--------------------------------------------------------------------------------
FILE_T('/tmp', 'tmp', 126976, '21.09.2011 10:25:22', 'Y', 'Y', 'Y', 'Y')

1 Zeile wurde ausgewählt.

So weit so gut. Wir haben es also geschafft, ein strukturiertes Objekt von Java nach PL/SQL zu übertragen. Analog dazu kann man nun für alle Objekte vorgehen:

SQL-Objekttypen erzeugen
Java Methode erzeugen, die das eigentliche Java-Objekt auf eine STRUCT-Instanz abbildet, dabei mit dem StructDescriptor und dem Object[]-Array arbeiten
STRUCT-Instanz aus Java zurückgeben und in SQL übernehmen
PL/SQL Wrapper für die neue Java Stored Procedure erstellen

Bleibt die nächste (und im Datenbankumfeld spannende) Aufgabe: Ich möchte ein Directory-Listing abbilden, also eine ganze Reihe von strukturierten Objekten zurückgeben. Im reinen PL/SQL geht haben wir hierfür die Table Functions - und ein ähnliches Konzept nutzen wir auch in Java. Zunächst brauchen wir, wie bei der PL/SQL Table Function, einen Objekttypen, der die Dateiliste repräsentiert - das ist einfach:

create type file_ct as table of file_t
/

Und wieder gilt es, aus Java heraus eine Instanz dieses Typs FILE_CT zu erzeugen. Für Varray- oder Table Types gibt es jedoch eine andere Java-"Brückenklasse": oracle.sql.ARRAY. Der Umgang damit ist aber ganz ähnlich wie bei der Klasse oracle.sql.STRUCT. Es wird ein ArrayDescriptor-Objekt benötigt, der die Verbindung zum konkreten SQL-Typen herstellt, und mit diesem, dem Connection-Objekt und einem Standard-Java-Array wird die Instanz vom Typ oracle.sql.ARRAY erzeugt, die genau auf den FILE_CT passt. Das ganze als Code ...

public class JavaFile {
  public static ARRAY getFileList(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    ArrayDescriptor  aDescr = ArrayDescriptor.createDescriptor("FILE_CT", con);
    Object[]         o      = new Object[8];

    /* Array containing java File objects */
    File[]   f = new File(pFile).listFiles();

    /* Array containing SQL STRUCT objects */
    STRUCT[] a = new STRUCT[f.length];

    /* now loop through the File array and create a STRUCT instance for each file */
    for (int i=0;i<f.length;i++) {
     o[0] = f[i].getPath();
     o[1] = f[i].getName();
     o[2] = new BigDecimal(f[i].length());
     o[3] = new java.sql.Timestamp(f[i].lastModified());
     o[4] = (f[i].isDirectory()?"Y":"N");
     o[5] = (f[i].canWrite()?"Y":"N");
     o[6] = (f[i].canRead()?"Y":"N");
     o[7] = "Y"
     a[i] = new STRUCT(sDescr, con, o);
    } 
    /* Create and return the ARRAY object which maps to the SQL type */
    return new ARRAY(aDescr, con, a);
  }
}
/
sho err

create or replace function get_file_list (p_file in varchar2) return file_ct
is language java name 'JavaFile.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
sho err

Und das ist im Grunde genommen nur eine Erweiterung obigen Codes. Durch die Liste der File-Objekte, die von java.io.File.listFiles() zurückgegeben wird, laufen wir in einer Schleife durch, erzeugen für jedes File-Objekt eine STRUCT-Instanz und packen auch diese in ein Array. Das wird dann mit dem ArrayDescriptor auf den SQL-Typen FILE_CT abgebildet und als oracle.sql.ARRAY-Objekt zurückgegeben. Fertig - Test.

SQL> select file_name, last_modified, file_size from table(get_file_list('/'))

FILE_NAME                 LAST_MODIFIED        FILE_SIZE
------------------------- ------------------- ----------
wget-log                  27.04.2011 16:24:02        496
boot                      02.02.2011 13:22:58       1024
misc                      25.07.2011 09:29:09          0
stage                     04.04.2011 11:27:57          7
lib                       15.07.2011 11:06:27       4096
etc                       25.07.2011 09:29:07       4096
root                      15.07.2011 11:14:01       4096
bin                       02.02.2011 13:28:45       4096
:                         :                            :

Eigentlich ganz einfach, oder? Mit dem hier vorgestellten lässt sich nun jede beliebige Java-Bibliothek in der Datenbank nutzen - die einzige Voraussetzung ist, dass sie keinen Native-Code verwendet - nur 100%-Java-Bibliotheken laufen in der Datenbank. Zum Ansprechen aus SQL und PL/SQL überlegt man sich dann eine vernünftige Schnittstelle, erzeugt die passenden Java-Klassen, die entweder einfache Datentypen (String, Date, numerische) oder komplexe Datentypen als STRUCT oder ARRAY zurückgeben. Diese werden dann auf SQL-Ebene durch PL/SQL-Packages und Objekttypen repräsentiert. Die Code-Packages zum Dateisystem-Zugriff, zum Umgang mit einem POP3- oder IMAP-Mailserver oder zum Ein- und Auspacken von ZIP-Archiven arbeiten alle genau so - und wie gesagt: Jede andere Java-Bibliothek lässt sich genauso einbinden. Damit gibt es keine Grenzen in der Oracle-Datenbank.

In the past I had quite a number of postinhs in which I made use of the Java engine within the Oracle Database. Examples are the previous posting about automated tweets from PL/SQL (which was about using "twitter4j" in the database) or the postings about File- and operating system access. Today I'd like to elaborate a bit about a fundamental thing which one has to accomplish when using Java in the database: The parameter mapping. This posting is about how to map input or return paramaters in a java method to SQL and PL/SQL types in a package. And the focus will not be on the simple mapping of VARCHAR2, NUMBER or DATE datatypes ... these are rather simple, as we can see with this code example ...

create or replace and compile java source named simple_test as

public class SimpleTest {
  public static String getString() {
    return "Hello World";
  }
  public static java.sql.Timestamp getDate() {
    return new java.sql.Timestamp(new java.util.Date().getTime());
  }
  public static int getNumber() {
    return 4711;
  }
} 
/
sho err

create or replace package simple_test_plsql is
  function get_string return varchar2;
  function get_date return date;
  function get_number return number;
end simple_test_plsql;
/
sho err

create or replace package body simple_test_plsql is
  function get_string return varchar2 as 
  language java name 'SimpleTest.getString() return java.lang.String';
  function get_date return date as
  language java name 'SimpleTest.getDate() return java.sql.Timestamp';
  function get_number return number as
  language java name 'SimpleTest.getNumber() return int';
end simple_test_plsql;
/
sho err

select 
  simple_test_plsql.get_string,
  simple_test_plsql.get_date,
  simple_test_plsql.get_number
from dual
/

GET_STRING           GET_DATE            GET_NUMBER
-------------------- ------------------- ----------
Hello World          21.09.2011 09:19:40       4711

And now we'll handle these: Let's assume we have a Java library doing some stuff and for this example we use the java.io.File class (which is part of standard Java). We'd like to access file attributes with SQL functions and we don't want to have a single call for each attribute. We need a SQL function which collects all attributes in one call - so we need a datatype containing all these attributes at the SQL side. We have Object types (or User-Defined-Types) for that purpose so the first thing we want to do is to create an object type representing a file.

create type file_t as object(
  file_path      varchar2(4000),
  file_name      varchar2(4000),
  file_size      number,
  last_modified  date,
  is_dir         char(1),
  is_writeable   char(1),
  is_readable    char(1),
  file_exists    char(1)
)
/

So far we have a Java representation for a file: java.io.File - and we have a SQL representation for a file: Our new FILE_T. But there is absolutly no connection between those. Of course, we cannot map the java.io.File class to our FILE_T type - the database has no clue how to map the attributes. We need to build a "bridge" between java.io.File and FILE_T. And this bridge is a special java class: oracle.sql.STRUCT. So we now need to implement a static Java method (we can only use static methods) which uses java.io.File to collect file attributes and builds a oracle.sql.STRUCT instance which can be mapped to FILE_T. This code goes here ...

create or replace and compile java source named java_file as
import java.math.*;
import java.util.*;
import java.io.*;

import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JavaFile {
  public static STRUCT getFile(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    Object[]         o      = new Object[8];

    File f = new File(pFile);
    if (f.exists()) {
     o[0] = f.getPath();
     o[1] = f.getName();
     o[2] = new BigDecimal(f.length());
     o[3] = new java.sql.Timestamp(f.lastModified());
     o[4] = (f.isDirectory()?"Y":"N");
     o[5] = (f.canWrite()?"Y":"N");
     o[6] = (f.canRead()?"Y":"N");
     o[7] = "Y";
     return new STRUCT(sDescr, con, o);
    } else {
     return null;
    } 
  }
}
/
sho err

create or replace function get_file_atts (p_file in varchar2) return file_t
is language java name 'JavaFile.getFile(java.lang.String) return oracle.sql.STRUCT';
/
sho err

Got it ...? Then try it!

SQL> select get_file_atts('/') from dual;
select get_file_atts('/') from dual
                               *
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.io.FilePermission /
read) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '/', 'read'
)

Ah, yes: You need Java privileges in order to access the file system with a Java stored porcedure. In the developing stage you might grant yourself the JAVASYSPRIV privilege - you will be a "Java superuser" then - on production systems this is not recommended you might grant individual privileges there. The practical bit is that Oracle not only throws the error message - it also gives the complete PL/SQL call to grant the required permission. So when you have the privilege, try again ...

SQL> select get_file_atts('/tmp') from dual;

GET_FILE_ATTS('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR, IS
--------------------------------------------------------------------------------
FILE_T('/tmp', 'tmp', 126976, '21.09.2011 10:25:22', 'Y', 'Y', 'Y', 'Y')

1 row selected.

So far - so good. We managed to create a structured object in java and pass it to PL/SQL. You can use this approach for any kind of structured object - and yes: You can nest one oracle.sql.STRUCT within another oracle.sql.STRUCT - just as you can nest object types in each other. The basic steps are:

Create the SQL object types
Create the static Java method which creates the STRUCT instance matching the object type using the Object[] array and the StructDescriptor class.
Return the STRUCT instance from java to SQL and PL/SQL
Create the PL/SQL wrapper for your Java stored procedure

Then we'll move on to the next (and more interesting) level: We want to pass not only a structured object but a list of structured objects from Java to PL/SQL. In the PL/SQL world we have Table Functions for that purpose - and as with these we now first create another object type representing our list of files ...

create type file_ct as table of file_t
/

And again: We now need to create an object within Java which maps to this FILE_CT type. But for Varray or table types there is another "Bridging class": oracle.sql.ARRAY. It's used the same way as oracle.sql.STRUCT. We first create an ArrayDescriptor instance using the JDBC connection and the type name. This helper object, the JDBC connection and a plain Java array are being used to create the actual instance of oracle.sql.ARRAY. And the code goes here ...

public class JavaFile {
  public static ARRAY getFileList(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    ArrayDescriptor  aDescr = ArrayDescriptor.createDescriptor("FILE_CT", con);
    Object[]         o      = new Object[8];

    /* Array containing java File objects */
    File[]   f = new File(pFile).listFiles();

    /* Array containing SQL STRUCT objects */
    STRUCT[] a = new STRUCT[f.length];

    /* now loop through the File array and create a STRUCT instance for each file */
    for (int i=0;i<f.length;i++) {
     o[0] = f[i].getPath();
     o[1] = f[i].getName();
     o[2] = new BigDecimal(f[i].length());
     o[3] = new java.sql.Timestamp(f[i].lastModified());
     o[4] = (f[i].isDirectory()?"Y":"N");
     o[5] = (f[i].canWrite()?"Y":"N");
     o[6] = (f[i].canRead()?"Y":"N");
     o[7] = "Y"
     a[i] = new STRUCT(sDescr, con, o);
    } 
    /* Create and return the ARRAY object which maps to the SQL type */
    return new ARRAY(aDescr, con, a);
  }
}
/
sho err

create or replace function get_file_list (p_file in varchar2) return file_ct
is language java name 'JavaFile.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
sho err

As you see: This code is basically just a small extension of the code above. We loop through the File[] array returned by java.io.File.listFiles(), create an oracle.sql.STRUCT instance for each java.io.File object and place it into a Java array (STRUCT[]). This java array is then being used to create the oracle.sql.ARRAY instance, which is finally passed back to PL/SQL. On the PL/SQL side we create the wrapper function and that's it. Try ...

SQL> select file_name, last_modified, file_size from table(get_file_list('/'))

FILE_NAME                 LAST_MODIFIED        FILE_SIZE
------------------------- ------------------- ----------
wget-log                  27.04.2011 16:24:02        496
boot                      02.02.2011 13:22:58       1024
misc                      25.07.2011 09:29:09          0
stage                     04.04.2011 11:27:57          7
lib                       15.07.2011 11:06:27       4096
etc                       25.07.2011 09:29:07       4096
root                      15.07.2011 11:14:01       4096
bin                       02.02.2011 13:28:45       4096
:                         :                            :

Twitter postings mit der Datenbank (SQL, PLSQL, Java)

noreply@blogger.com (Carsten Czarski) — Fri, 16 Sep 2011 10:08:00 +0000

Seit einiger Zeit verwenden meine Teamkollegen und ich den Twitter-Account @oraclebudb, um interessante Neuerungen, die keine langen Texte erfordern, bekanntzumachen. Wenn Ihr also stets über Neuigkeiten zur Oracle-Datenbank wie neue Releases, Patchsets, deutschsprachige Veranstaltungen oder ähnliches informiert sein wollt, einfach an diesen Account dranhängen ("Folgen").

Nun ist man ab und zu unterwegs und kann einen Tweet nicht immer von seinem iPhone oder PC absetzen - man möchte einen Tweet "planen" und dann zu einem festgelegten Zeitpunkt, bspw. "Montag 10:00 Uhr" automatisch absetzen. Sowas kann übrigens auch sehr interessant sein, wenn man Twitter für regelmäßige Statusmeldungen über etwas nutzen möchte - ich könnte mir sehr gut vorstellen, zum Beispiel die Pegelstände von Flüssen (vor allem bei Hochwasser) regelmäßig über Twitter zu posten. Auch hier braucht es einen Automatismus, von Hand geht nix mehr.

Der Nutzen ist klar - die Frage ist also: Wie setzt man es um? Und ich möchte es natürlich mit der Oracle-Datenbank, also mit SQL und PL/SQL machen. Eine erste Google-Suche führte mich denn auch zu zahlreichen Blogs und Webseiten mit PL/SQL-Codefragmenten, die alle eines gemein hatten: Ich brachte es nicht zum Laufen (wobei das wohl eher an mir lag) Allein das von Twitter verwendete OAuth-Verfahren, welches von einer Anwendung genutzt werden muss, implementiert man nicht mal eben in 5 Minuten in PL/SQL.

Im Java-Bereich gibt es allerdings eine sehr populäre Bibliothek für Twitter: twitter4j. Und da man in einer Oracle-Datenbank auch Java laufen lassen kann, habe ich mich entschlossen, das Rad nicht neu in PL/SQL zu erfinden, sondern das zu nutzen, was da ist ...

Im folgenden findet Ihr also eine Anleitung, wie man eine PL/SQL-Prozedur in die Datenbank legt, die unter einem bestimmten Account twittert. Mit dieser Variante kann nur der Account-Eigentümer per PL/SQL twittern - die Autorisierung durch andere Nutzer ist prinzipiell möglich, hier aber nicht beschrieben.

Schritt 1: Applikation auf "dev.twitter.com" registrieren:

Loggt euch mit eurem Twitter-Account auf dev.twitter.com ein. Navigiert im Menü oben rechts (dort, wo Ihr euren Accountnamen seht) zum Bereich My Applications und klickt dann die Schaltfläche Create a new Application. Füllt dann den Dialog aus. Ihr braucht einen Namen, eine Beschreibung und eine Webseite (die muss es für den Anfang aber nicht wirklich geben). Die Callback-URL wird für heute nicht benötigt (Abbildung 1). Dann müsst Ihr mit dem CAPTCHA fertig werden und eure Anwendung ist erstellt.

Abbildung 1: Anwendung erstellen

Wenn die Anwendung erstellt wurde, seht Ihr bereits einige wichtige URLs und das Schlüsselpaar für die Anwendung selbst (Consumer Key und Consumer Secret).

Abbildung 2: Die Anwendung wurde erstellt

Nun navigiert Ihr zu den Settings eurer neuen Anwendung und stellt die Berechtigungen auf Read and Write. Das ist wichtig, denn das Absenden eines Tweets ist ein "Write.

Abbildung 3: Vergeben der Berechtigung "Read and Write"

Nun zurück zur ersten Reiterkarte Details. Unten seht Ihr eine Schaltfläche Create My Access Token - damit generiert Ihr die nötigen Accountschlüssel für den eigenen Account (Access Token und Access Token Secret). Diese, den Consumer Key und das Consumer Secret bitte merken - das brauchen wir in der Stored Procedure.

Abbildung 4: Access Token und Access Token Secret

Nun seid Ihr mit dem Registrieren der Anwendung fertig. Jetzt geht es zur Oracle-Datenbank.

Schritt 2: Twitter4j in die Datenbank laden.

Ladet twitter4j von der Webseite twitter4j.org herunter. Twitter4j ist Open Source unter der Apache Lizenz - kann also für alle Anwendungsfälle, sei es kommerziell oder nicht, verwendet werden. Das ZIP-Archiv muss in ein Verzeichnis eurer Wahl ausgepackt werden - am besten legt Ihr es gleich auf den Datenbankserver.

Abbildung 5: ZIP Archiv "twitter4j"

Die Java-Bibliotheken befinden sich im Unterverzeichnis lib. Für einfache Status-Updates braucht Ihr nur das Archiv twitter4j-core-2.2.4.jar. Das wird nun wie folgt in die Datenbank geladen.

$ loadjava -user {dbuser}/{dbpassword} -o -r -v twitter4j-core-2.2.4.jar

Ihr sehr nun einige Meldungen und zum Schluß kommen einige Fehlermeldungen, die Ihr aber ignorieren könnt.

skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.xml
skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.properties
The following operations failed
    class twitter4j/internal/logging/SLF4JLogger: resolution
    class twitter4j/internal/logging/SLF4JLoggerFactory: resolution
exiting  : Failures occurred during processing

Schritt 3: Java-Klasse für ein Status-Update erstellen

Es wäre sehr aufwändig, die Methoden der Twitter4j-Bibliothek direkt auf PL/SQL abzubilden. Dafür sind es zu viele und vielfach werden Java-Objekte übergeben, deren Mapping auf Datenbanktypen nicht einfach ist. Der beste Weg ist es, die Operationen, die man haben möchte, in Java zu implementieren, dabei einfache Funktionssignaturen zu verwenden und diese "Top-Level"-Methoden dann nach PL/SQL abzubilden. Zuerst also die Java-Klasse, die auf Basis der Twitter4j-Beispiele gebaut wurde. Achtet darauf, in der Methode setOAuthAccessToken die Schlüssel von dev.twitter.com einzutragen. Weiter vorne in den Calls zu System.setProperty müsst Ihr euren Proxy-Server eintragen, wenn die Datenbank hinter einer Firewall steht. Wenn nicht, entfernt die Zeilen einfach.

create or replace and compile java source named TwitterStatusUpdate as
import twitter4j.*;
import twitter4j.conf.*;
import twitter4j.auth.*;

import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;

public class UpdateStatus {
  static Twitter twitter = null;

  static  {
    System.setProperty("http.proxyHost", "{Proxy-Server, falls nötig}");
    System.setProperty("http.proxyPort", "{Proxy-Server-Port, falls nötig}");
  
    ConfigurationBuilder confbuilder  = new ConfigurationBuilder(); 
    confbuilder.setOAuthAccessToken("{Access-Token aus dev.twitter.com}") 
    .setOAuthAccessTokenSecret("{Access-Token-Secret aus dev.twitter.com}") 
    .setOAuthConsumerKey("{Consumer-Key aus dev.twitter.com}") 
    .setOAuthConsumerSecret("{Consumer-Secret aus dev.twitter.com}"); 
    twitter = new TwitterFactory(confbuilder.build()).getInstance(); 
  }

  public static String updateStatus(String newStatus) throws Exception {
    Status status = twitter.updateStatus(newStatus);
    return status.getText();
  }
}
/
sho err

Schritt 4: PL/SQL Wrapper für die Java-Methode erstellen

Für diese statische Java-Metjode updateStatus, die einen String entgegennimmt und zurückgibt, lässt sich nun sehr einfach ein PL/SQL-Mapping erstellen.

create or replace function set_twitter_status(p_new_status in varchar2) return varchar2
is language java name 'UpdateStatus.updateStatus(java.lang.String) return java.lang.String';
/
sho err

Schritt 5: Privilegienvergabe

Ein Twitter-Posting wird über das Netzwerk abgesetzt - das darf ein "normaler" Datenbankuser mit Java erstmal nicht. Damit die obige Java-Klasse auch tatsächlich funktioniert, muss der Datenbankuser Privilegien zum Netzwerkzugriff und zum Einstellen des Proxy-Servers (falls nötig) erhalten. Das erledigt man wie folgt - das Skript muss als DBA gestartet werden.

begin
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyHost', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyPort', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.net.SocketPermission', 'api.twitter.com:80', 'connect, resolve' 
  );
end;
/

Abschluß

Dann ist es fertig. Nun könnt Ihr testen ...

SQL> select set_twitter_status('another test at friday') from dual;

SET_TWITTER_STATUS('ANOTHERTESTATFRIDAY')
------------------------------------------------------------------------
another test at friday

1 Zeile wurde ausgewählt.

Wenn Ihr euch die Twitter-Seite eures Accounts anseht, solltet Ihr das Posting nun sehen ... und von hier aus ist eine Automatisierung kein Thema mehr - ob man nun per Scheduler-Job regelmäßig etwas twittert, eine APEX-Anwendung zum Planen der Tweets erstellt oder andere Dinge tut - mit den Mitteln der Datenbank kann dies nun beliebig genutzt werden. Eine einfaches Beispiel ist demnach eine APEX-Anwendung mit einem Formular auf eine Tabelle. Im Formular kann man Tweets und eine Zeitpunkt eintragen, zu dem der Tweet gepostet werden soll. Ein DBMS_SCHEDULER-Job läuft dann regelmäßig los und postet automatisch alle "fälligen" Tweets. Und natürlich kann man mit twitter4j noch mehr tun als nur Status-Updates abzusetzen, aber wie man bspw. die Home-Timeline abruft, dazu mache ich am besten ein eigenes Blog Posting. Viel Spaß auf jeden Fall beim Ausprobieren ...

The java libraries are located in the subfolder lib. For simple status updates (tweets) the archive twitter4j-core-2.2.4.jar is sufficient. Load it into the Oracle database as follows.

$ loadjava -user {dbuser}/{dbpassword} -o -r -v twitter4j-core-2.2.4.jar

You will see some error messages at the end of the loading process, which you can ignore.

skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.xml
skipping : resource META-INF/maven/org.twitter4j/twitter4j-core/pom.properties
The following operations failed
    class twitter4j/internal/logging/SLF4JLogger: resolution
    class twitter4j/internal/logging/SLF4JLoggerFactory: resolution
exiting  : Failures occurred during processing

It would be a lot of work to map all the API methods to PL/SQL procedures and functions. There are many methods and most of the have java objects in their signature. It's better to implement the needed operations as Java methods with simple signatures and finally map those java methods to PL/SQL. So the following code creates a simple java method "updateStatus" which can be mapped to PL/SQL very easily. This java code has been created based on the twitter4j examples, which are also part of the downloaded archive. Note the placeholders for the key pairs from dev.twitter.com in the call of setOAuthAccessToken which need to be replaced. The calls to System.setProperty in the previous lines also contain placeholders for the proxy server and port, which also need to be adjusted to your environment.

create or replace and compile java source named TwitterStatusUpdate as
import twitter4j.*;
import twitter4j.conf.*;
import twitter4j.auth.*;

import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;

public class UpdateStatus {
  static Twitter twitter = null;

  static  {
    System.setProperty("http.proxyHost", "{Proxy server, if required}");
    System.setProperty("http.proxyPort", "{Proxy server port, if required}");
  
    ConfigurationBuilder confbuilder  = new ConfigurationBuilder(); 
    confbuilder.setOAuthAccessToken("{Access token from dev.twitter.com}") 
    .setOAuthAccessTokenSecret("{Access token secret from dev.twitter.com}") 
    .setOAuthConsumerKey("{Consumer key from dev.twitter.com}") 
    .setOAuthConsumerSecret("{Consumer secret from dev.twitter.com}"); 
    twitter = new TwitterFactory(confbuilder.build()).getInstance(); 
  }

  public static String updateStatus(String newStatus) throws Exception {
    Status status = twitter.updateStatus(newStatus);
    return status.getText();
  }
}
/
sho err

The created Java method "updateStatus" just takes and returns a java string. The mapping to SQL and PL/SQL is, as already stated, very easy:

create or replace function set_twitter_status(p_new_status in varchar2) return varchar2
is language java name 'UpdateStatus.updateStatus(java.lang.String) return java.lang.String';
/
sho err

Doing a tweet is a network request. A "normal" database user does not have privileges to execute network operations in Java. To get the above java class actually working the database schema needs java privileges to set the proxy server (if needed) and to do the network operation. The following SQL script, which has to be executed with DBA privileges, does the job.

begin
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyHost', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.util.PropertyPermission', 'http.proxyPort', 'read,write' 
  );
  dbms_java.grant_permission( 
    '{DB-SCHEMA}', 'SYS:java.net.SocketPermission', 'api.twitter.com:80', 'connect, resolve' 
  );
end;
/

And then you are done. Try it ...

SQL> select set_twitter_status('another test at friday') from dual;

SET_TWITTER_STATUS('ANOTHERTESTATFRIDAY')
------------------------------------------------------------------------
another test at friday

1 row selected.

Ausführungspläne mit DBMS_XPLAN

noreply@blogger.com (Carsten Czarski) — Fri, 02 Sep 2011 08:18:00 +0000

Heute wollte ich eigentlich ein Blog-Posting über die verschiedenen Möglichkeiten von DBMS_XPLAN bringen ... das kann ja wesentlich mehr als DBMS_XPLAN.DISPLAY(). Es ist allein schon lohnenswert, sich die verschiedenen Varianten des Parameters FORMAT anzusehen. Allerdings haben meine Kollegen, die die DBA Community-Seite betreiben, hierzu schon einiges geschrieben ... und man soll das Rad nicht neu erfinden. Daher verweise ich heute einfach nur auf den entsprechenden Community-Tipp.

Viel Spaß beim Lesen!

Der Browser in eurer Datenbank: HTTPURITYPE

noreply@blogger.com (Carsten Czarski) — Mon, 01 Aug 2011 08:10:00 +0000

Wenn es darum geht, HTTP-Zugriffe aus der Datenbank heraus zu machen, ist das Paket UTL_HTTP natürlich das Mittel der Wahl - damit kann ein Browser komplett imitiert werden; vom Setzen der HTTP Header Felder oder von Cookies bis hin zu den HTTP Methoden GET und POST ist alles drin, was man braucht.

In vielen Fällen braucht man das alles aber nicht - es muss einfach nur etwas per HTTP abgerufen werden. Und für genau diese Fälle gibt es die ganz einfache Variante: HTTPURITYPE - das kann man nutzen wie eine SQL-Funktion und es macht die ganze Arbeit auf einmal.

Da passiert erstmal noch nix ... Los geht's, wenn Ihr den Content selbst mit GETBLOB(), GETCLOB() oder GETXML() abruft.

Alles klar, dann setzen wir noch den Proxy ...

Voilá. HTTPURITYPE kann den Code wirklich ganz massiv vereinfachen - und wenn eine einfache Lösung ausreicht, dann soll man diese ja auch nutzen. Und als kleinen Exkurs kann man das Beispiel ja noch fortsetzen, indem man das XML auch direkt zerlegt ...

with xml as (
  select httpuritype('http://sql-plsql-de.blogspot.com/feeds/posts/default').getxml() as doc
  from dual
)
select 
  t.titel,
  t.pubdate,
  t.link
from xml, xmltable(
  'for $i in //item return $i'
  passing xml.doc
  columns 
    titel   varchar2(100) path '/item/title/text()',
    pubdate varchar2(35)  path '/item/pubDate/text()',
    link    varchar2(500) path '/item/link/text()'
) t
/

TITEL                          PUBDATE                             LINK
------------------------------ ----------------------------------- ------------------------------
DELETE löscht ... und das ist  Mon, 18 Jul 2011 09:00:00 +0000     http://sql-plsql-de.blogspot.c
es, oder ...?                                                      om/2011/07/delete-loscht-und-d
                                                                   as-ist-es-oder.html

Schon gewusst ...? Multi Table Mon, 04 Jul 2011 11:46:00 +0000     http://sql-plsql-de.blogspot.c
 Insert                                                            om/2011/07/schon-gewusst-multi
                                                                   -table-insert.html

Kleine Statistikvorlesung: Mit Fri, 10 Jun 2011 09:11:00 +0000     http://sql-plsql-de.blogspot.c
 der Datenbank                                                     om/2011/06/kleine-statistikvor

Einen RSS-Feed abholen und direkt zerlegen - mit nur einer SQL-Abfrage. Einfacher geht es nun wirklich nicht.

Ach ja ... fast hätte ich das vergessen. Ab Oracle11g braucht Ihr noch zusätzliche Netzwerk-Privilegien, damit Ihr aus der Datenbank überhaupt herauskommt: Mit dem PL/SQL-Paket DBMS_NETWORK_ACL_ADMIN müsst Ihr uerem Datenbankuser für die einzelnen Netzwerkziele freischalten. Wie das geht, ist in diesem Blog-Posting beschrieben.

Kleine Statistikvorlesung ... Fortsetzung auf einem neuen Blog

noreply@blogger.com (Carsten Czarski) — Fri, 29 Jul 2011 07:27:00 +0000

Vor kurzem hatte ich ja das Blog Posting zum Thema Kleine Statistikvorlesung mit der Oracle-Datenbank, in dem ich die einfache lineare Regression gezeigt habe. In der Praxis ist aber öfter die anspruchsvollere multiple Regression mit mehreren Variablen gefordert, wozu es erstmal keine einfache SQL-Funktion gibt.

Aber die Datenbank kann es doch! Wie immer, eigentlich. Thomas Uhren beschreibt in seinem Blog, wie man das mit dem PL/SQL-Paket OLS_REGRESSION hinbekommen kann. Auf jeden Fall mal reinschauen. Viel Spaß beim Lesen.

DELETE löscht ... und das ist es, oder ...?

noreply@blogger.com (Carsten Czarski) — Mon, 18 Jul 2011 09:00:00 +0000

Ein SQL DELETE löscht Zeilen von einer Tabelle - das ist bekannt. Und ein DELETE hat eine WHERE-Klausel, die festlegt, welche Zeilen gelöscht werden sollen - das ist auch bekannt.

Doch was ist, wenn ich die Zeilen, die gelöscht werden sollen, nur durch einen Join mit einer anderen Tabelle ermitteln kann? Als Beispiel möchte ich alle Mitarbeiter löschen, die in DALLAS arbeiten. Das steht in der EMP-Tabelle aber nicht drin - ich muss in die DEPT-Tabelle gucken. Aus der soll aber nichts gelöscht werden ...

Es wird selten gemacht, aber man kann bei SQL DML-Kommandos anstelle einer Tabelle oder View (zur Wirkung von DML auf Views gibt es schon ein Blog-Posting) auch eine SQL-Abfrage verwenden. Ein einfaches Beispiel: Anstelle der Tabelle EMP setze ich ein SELECT ein.

Das funktioniert genauso. Im Grunde genommen gilt von nun an das gleiche wie für DML-Kommandos auf Views. Solange ein DML-Kommando auf eine View ohne INSTEAD OF Trigger funktioniert, kann man die View-Abfrage auch direkt ins DML aufnehmen. Also können wir die o.g. Aufgabenstellung wie folgt lösen:

Wenn ich das SQL wie oben formuliere, könnte man ja die Frage stellen, ob nicht aus beiden Tabellen Zeilen gelöscht werden - man verwendet ja auch beide Tabellen in der SQL-Abfrage. Hier ist Oracle aber recht eindeutig - zunächst löscht Oracle grundsätzlich nur aus einer Basistabelle Zeilen (siehe Dokumentation: General Rule). Dann stellt sich aber die Frage: Wie entscheidet Oracle, aus welche Tabelle die Zeilen gelöscht werden sollen - warum nimmt Oracle die EMP-Tabelle und nicht die DEPT-Tabelle?

Hier kommt jetzt das Konzept der Key-Preserved Table ins Spiel. In einem Join sind die Tabellen Key-Preserved, die Ihre Schlüssel auch im Ergebnis des Joins behalten. Der obige Join ist so definiert, dass alle Zeilen der Tabelle EMP zurückgeliefert werden und die DEPTNO dupliziert wird. Also ist EMPNO auch im Join-Ergebnis immer noch eindeutig. Für die Tabelle DEPT sieht es anders aus - deren Schlüssel DEPTNO ist im Join-Ergebnis nicht mehr eindeutig. Wichtig ist, dass sich diese Eigenschaft nicht nach der tatsächlichen Ergebnismenge des Joins richtet, sondern nach dem konkreten Join und der Tabellendefinition im Datenbankschema. Daher gibt es in diesem Beispiel genau eine Key-Preserved Table und aus der werden die Zeilen gelöscht. Mehr zum Thema Key-Preserved Tables findet sich (wie immer) in der Dokumentation.

Nun sind auch andere Beispiele denkbar: ich möchte alle Zeilen aus DEPT löschen, zu denen es keine Einträge in EMP gibt. Hierzu brauche ich noch nicht einmal einen Join.

Es bestätigt sich immer wieder: SQL ist eine unglaublich mächtige Sprache. Viele Dinge, die man prozedural recht aufwändig programmieren muss, lassen sich mit SQL sehr elegant erledigen ...

Schon gewusst ...? Multi Table Insert

noreply@blogger.com (Carsten Czarski) — Mon, 04 Jul 2011 11:46:00 +0000

Normalerweise macht man ein SQL INSERT in genau eine Tabelle - die Syntax ist ja auch INSERT INTO {TABLE}. Wusstet Ihr, dass man mit einem SQL INSERT jedoch auch in meherer Tabellen gleichzeitig einfügen kann. Und ich meine nun nicht das SQL INSERT in eine VIEW und das Verteilen per INSTEAD-OF Trigger (hierzu gab es schonmal ein Blog Posting.

Nein, ich meine den sog. Multi-Table-Insert. Dazu ein Beispiel. Zuerst erstellen wir die Zieltabellen, die zugegebenermaßen recht einfach gestrickt sind, aber für ein einfaches Beispiel sollte das reichen ...

Und dann probieren wir unseren ersten Multi Table Insert.

Man sieht, dass danach beide Tabellen gefüllt sind - mit einem einzigen INSERT-Kommando. Doch das ist noch nicht alles. Wie man den Tabellennamen schon ansehen kann, möchten wir bestimmte Zeilen in bestimmte Tabellen haben ... und auch das geht mit dem Multi-Table-Insert.

Und auch das funktioniert - abhängig von der DEPTNO gehen die Zeilen in unterschiedliche Tabellen. Das kann noch mit einer ELSE-Anweisung kombiniert werden ...

Bislang wurden stets alle WHEN-Klauseln beachtet - die Klauseln selbst schließen sich ja schon gegenseitig aus. Anders sieht es hier aus ...

KING gehört zur DEPTNO 10 und hat ein SAL größer als 3000. Er landet also sowohl in der Tabelle EMP_HIGHSAL, als auch in EMP10. Unter Umständen sollen diejenigen Zeilen, die in EMP_HIGHSAL eingefügt wurden, nicht mehr weiter verarbeitet werden - es soll also nach der ersten passenden WHEN-Klausel aufgehört werden. Auch das geht - statt INSERT ALL schreiben wir dann INSERT FIRST.

Die Tatsache, dass viele INSERTs in einem Kommando abgearbeitet werden, kann die Performance bei großen Datenmengen extrem beeinflussen - denn die Quelldaten werden beim Multi-Table-Insert nur einmal durchgearbeitet - würde man einzelne INSERT-Anweisungen machen, würden die Daten für jedes INSERT einmal durchgescannt. Mehr Info in der Dokumentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/transform.htm#i1006530.

Kleine Statistikvorlesung: Mit der Datenbank

noreply@blogger.com (Carsten Czarski) — Fri, 10 Jun 2011 09:11:00 +0000

Heute machen wir eine kleine Statistikvorlesung - mit der Oracle-Datenbank. Es geht um die klassische, einfache lineare Regression. Das geht mit ganz einfachen SQL-Funktionen. Gegeben sei eine Tabelle, welche die getätigten Umsätze und die Ausgaben für TV-Werbung enthält.

MONAT      UMSATZ_EUR WERBUNG_TV_EUR
---------- ---------- --------------
2009/11        130000          14000
2009/12        140000          13200
2010/01        100000           9000
2010/02        103000           9500
2010/03        112000          10000
2010/04         90000          12000
2010/05         98000          13000
2010/06         70000           4000
2010/07        109010          12000
2010/08        120000          10000
2010/09        130000           9000
2010/10        150000          15000
2010/11        170000          18000
2010/12        200000          18000
2011/01                        10000
2011/02                        10200
2011/03                        15200

Die Statistikaufgabe wäre nun, den Umsatz der ersten drei Monate in 2011 anhand der Ausgaben für TV-Werbung zu prognostizieren - eine schöne Statistikaufgabe. Und für diesen Fall brauchen wir gar keinen Export nach Excel oder in eine Statistiksoftware - das machen wir direkt mit SQL ...

Zunächst stellen wir mit der "Methode der kleinsten Quadrate" den "Betrag" und die Steigung der linearen Funktion fest:

select 
   REGR_INTERCEPT(UMSATZ_EUR, WERBUNG_TV_EUR) as A,
   REGR_SLOPE(UMSATZ_EUR, WERBUNG_TV_EUR)     as B
from verkauf

Wenn man das dann in eine Inline View packt (WITH-Klausel), dann kann man Analyse und Prognose direkt mit einer SQL-Abfrage machen ...

with regr_koeffizienten as (
 select 
   REGR_INTERCEPT(UMSATZ_EUR, WERBUNG_TV_EUR) as A,
   REGR_SLOPE(UMSATZ_EUR, WERBUNG_TV_EUR)     as B
 from verkauf
)
select 
  monat,
  umsatz_eur,
  werbung_tv_eur,
  a + b * WERBUNG_TV_EUR as UMSATZ_PROGNOSE
from VERKAUF, REGR_KOEFFIZIENTEN
/

MONAT      UMSATZ_EUR WERBUNG_TV_EUR UMSATZ_PROGNOSE
---------- ---------- -------------- ---------------
2009/11        130000          14000          138784
2009/12        140000          13200          132751
2010/01        100000           9000          101076
2010/02        103000           9500          104847
2010/03        112000          10000          108618
2010/04         90000          12000          123701
2010/05         98000          13000          131243
2010/06         70000           4000           63368
2010/07        109010          12000          123701
2010/08        120000          10000          108618
2010/09        130000           9000          101076
2010/10        150000          15000          146326
2010/11        170000          18000          168950
2010/12        200000          18000          168950
2011/01                        10000          108618
2011/02                        10200          110126
2011/03                        15200          147834

Stellt man das dann in APEX mit Hilfe eines Diagramms dar, ergibt sich ein schönes, quasi klassisches Statistikbild mit tatsächlichen und prognostizierten Daten.

Man sieht sehr schön: Die Datenbank kann eine ganze Menge - und auch für diese einfachen Fälle gibt es Anwendungsmöglichkeiten. Die Datenbank kann seit 11g sogar die komplexere multiple Regression mit mehreren unabhängigen Variablen - das ist dann aber Bestandteil der Data Mining Option, die man separat lizensieren muss. Wäre ein Fall für ein anderes Blog-Posting.

Fundgrube Standard-Edition - und nicht nur das: DOAG SIG Development am 9. Juni

noreply@blogger.com (Carsten Czarski) — Mon, 06 Jun 2011 12:30:00 +0000

So .. der Urlaub ist rum und man ist zurück im Oracle-Leben. Ein technisches Posting habe ich heute noch nicht (das kommt später), aber ich kann wenigstens was ankündigen. Am 9. Juni (das ist am Donnerstag) bin ich mit zwei Vorträgen auf der DOAG SIG Development vertreten. Zum einen spreche ich über die "Fundgrube" Oracle Standard Edition (die vielen DB-Features und deren sinnvolle Kombination ist ja mein Lieblingsthema) und zum anderen spreche ich kurz über den neuen SQL Developer 3.0.

Naja - und viel wichtiger ist natürlich der Austausch und die Diskussion in der Community. Insofern hoffe ich auf zahlreiche Teilnehmer, gute Diskussionen und ... veilleicht sieht man sich ja.

APEX Hands-On Workshop am 7. Juni in Jena

noreply@blogger.com (Carsten Czarski) — Fri, 20 May 2011 13:30:00 +0000

Am 7. Juni veranstalten meine Kollegen Karin Patenge und Michal Soszynski einen APEX Hands On Workshop - in Zusammenarbeit mit der DOAG Regionalgruppe Thüringen. Also die Gelegenheit für APEX-Einsteiger im Raum Jena. Inhalte sind:

Die Oracle Datenbank als Entwicklungswerkzeug
Arbeiten mit APEX Workspaces
Von der Tabelle zur Applikation

Import von Tabellendaten
Erstellen einer Anwendung
Ausführen einer Anwendung

Erweitern und Ändern von Anwendungen
Berechtigungskonzepte in APEX
Das Layout von APEX-Anwendungen

Wer Interesse hat, also am besten gleich anmelden - Mehr Info und Anmeldedetails hier:
http://www.oracle.com/webfolder/technetwork/de/community/apex/event/workshop/APEX_HandsOnWorkshop_Jena_20110607.pdf

Ein wenig Spielerei: Monatskalender in SQL Plus!

noreply@blogger.com (Carsten Czarski) — Wed, 20 Apr 2011 08:26:00 +0000

Heute habe ich etwas, was wohl auch ein wenig in die Kategorie "Spielereien" gehört, aber die muss es ja auch geben. Zuletzt hatte ich mal ein paar Minuten und dachte mir, dass ein SQL-Skript zum Generieren eines Monatskalenders doch ganz nett wäre - die Grundversion mit der PIVOT-Klausel war schnell erstellt - dann fehlte nur noch der Feinschliff. Und hier ist es.

ol monat format a15
set pages 20
set verify off

with dates as (
  select  to_date('01&1', 'DDMMYYYY') + (level - 1) the_date from dual
  connect by level < trunc((add_months(to_date('01&2','DDMMYYYY'),1) - 1) - to_date('01&1', 'DDMMYYYY')) + 2 
) 
select monat, " MO ", " DI ", " MI ", " DO ", " FR ", " SA ", " SO ", ww KW 
from (
  select 
    rtrim(to_char(the_date, 'MONTH' ), 'nls_date_language=german')  monat 
   ,case 
      when to_char(the_date, 'IW') > 50 and to_char(the_date, 'MM') = '01' then 
        to_char(the_date, 'YYYY') - 1||to_char(the_date, 'IW')
      else 
        to_char(the_date, 'YYYYIW')
    end as yyyyww
   ,to_char(the_date, 'yyyyMM' ) m 
   ,case when trunc(the_date) = trunc(sysdate) then 
      '*' || to_char(the_date, 'DD') || '*' 
    else 
      ' ' || to_char(the_date, 'DD') || ' '
    end dd
   ,rtrim(upper(to_char(the_date, 'DY', 'nls_date_language=''german''' )))  d 
   ,to_char(the_date, 'IW') ww
  from dates where the_date between to_date('01&1', 'DDMMYYYY') and add_months(to_date('01&2', 'DDMMYYYY'),1) - 1
) pivot (
  min(dd) for d in (
    'MO' as " MO ",
    'DI' as " DI ",
    'MI' as " MI ",
    'DO' as " DO ",
    'FR' as " FR ",
    'SA' as " SA ",
    'SO' as " SO " 
  )
)
order by yyyyww, m
/

undefine 1
undefine 2

Aufrufen geht dann so.

SQL> @kal 042011 052011

MONAT            MO   DI   MI   DO   FR   SA   SO  KW
--------------- ---- ---- ---- ---- ---- ---- ---- --
APRIL                                01   02   03  13
APRIL            04   05   06   07   08   09   10  14
APRIL            11   12   13   14   15   16   17  15
APRIL           *18*  19   20   21   22   23   24  16
APRIL            25   26   27   28   29   30       17
MAI                                            01  17
MAI              02   03   04   05   06   07   08  18
MAI              09   10   11   12   13   14   15  19
MAI              16   17   18   19   20   21   22  20
MAI              23   24   25   26   27   28   29  21
MAI              30   31                           22

Bei der schnellen Terminplanung vielleicht ganz nützlich - nehmt's ansonsten als Beispiel für "Kunst mit SQL Plus". Bei mir liegt es neben einigen anderen Skripten im Verzeichnis, auf das die Umgebungsvariable SQLPATH zeigt; damit habe ich es immer im Zugriff.