Oracle SQL und PL/SQL ...

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.

Im Zeichen des Cloud Computing - Events "APEX Cloud im Mai"

noreply@blogger.com (Carsten Czarski) — Mon, 11 Apr 2011 08:16:00 +0000

Cloud Computing ist in aller Munde. Nahezu täglich kann man über neue Produkte und Ideen zum Thema Cloud Computing lesen. Auch an mir geht das Thema nicht vorbei ...

Wenn man sich (wie ich) längere Zeit mit APEX beschäftigt und sich dann das Cloud Computing näher ansieht, kommt man irgendwie zum Schluß, dass APEX das seit 2003 schon macht!. Denn APEX ist, wie man am öffentlichen Demoserver apex.oracle.com erkennen kann, hervorragend für das Hosting ausgelegt. Dann liegt doch die Frage nahe, wie man ein solches "APEX-Hosting" im eigenen Unternehmen realisieren könnte.

Und genau damit wollen wir uns auf einer Veranstaltung im Mai näher beschäftigen. Gemeinsam mit der MT AG aus Ratingen und Muniqsoft aus München stellen wir in Düsseldorf, Hamburg und München vor, wie man "Cloud Computing mit APEX" sofort umsetzen kann - APEX-Hosting im eigenen Unternehmen. Denn mehr als eine APEX-Installation braucht man im ersten Schritt nicht -> bereits dann hat man eine Entwicklerplattform "as a service" (PaaS). Natürlich gibt es auch danach noch etwas zu tun - was genau, das könnt Ihr auf der Veranstaltung erfahren.

Also, schaut mal rein - die Veranstaltung beginnt um 12:00 mit Imbiß und Networking und dann starten wir mit der Agenda.

3. Mai in Düsseldorf
10. Mai in Hamburg
18. Mai in München

OracleXE 11.2 (Beta) verfügbar!

noreply@blogger.com (Carsten Czarski) — Mon, 04 Apr 2011 09:54:00 +0000

Heute gibt es ein ganz kurzes Blog-Posting: Ich wurde ja nun schon oft danach gefragt, konnte aber nie eine genaue Auskunft geben. Heute kann ich es: OracleXE 11.2 ist als Betaversion verfügbar!. Ihr könnt es aus dem OTN herunterladen. Viel Spaß beim Ausprobieren ...

Table Functions, der Optimizer und Statistiken ...

noreply@blogger.com (Carsten Czarski) — Thu, 24 Mar 2011 10:04:00 +0000

Heute möchte ich mich mal dem Thema Table Functions widmen - ich werde aber nicht schreiben, wie man eine Table Function bauen kann, sondern vielmehr, wie man dem Query Opimizer ein wenig Information über die Table Function geben kann - wichtig wäre vor allem, dass der Optimizer weiss, wieviele Zeilen die Table-Function zurückgeben wird, denn das hat Einfluß auf seine Entscheidungen. So richtet sich die Join-Strategie doch erheblich nach der Anzahl Zeilen in den beteiligten Tabellen. Wir beginnen mit einem Beispiel - eine sehr einfache Table Function ...

create type tf_t as object(
  col1       number
);
/

create type tf_ct as table of tf_t
/ 

create or replace function tf (p_cnt in number)
return tf_ct pipelined as
begin
  for i in 1..p_cnt loop
    pipe row (tf_t(i*2));
  end loop;
  return;
end;
/
sho err

SQL> select * from table(tf(10));

      COL1
----------
         2
         4
         6
         8
        10
         :

So weit so gut - nun werfen wir mal einen Blick auf den Ausführungsplan dieser einfachen Abfrage ... und danach erstellen wir den Ausführungsplan nochmal mit dem Argument 100000. Also einen Ausführungsplan für 10 zurückgegebene Zeilen und einen für 100000 zurückgegebene Zeilen ...

SQL> explain plan for 
  2  select * from table(tf(10))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

SQL> explain plan for 
  2  select * from table(tf(100000))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Egal, was Ihr als Parameter angebt: die Anzahl Zeilen, die der Optimizer als Ergebnismenge der Table Function annimmt, ist immer gleich. Woher soll er es auch wissen? Es gibt eben hier keine Tabelle mit Statistiken. Aber: Die Datenbank erlaubt durchaus, dem Optimizer diese Information zu geben. Das ist auch in der Dokumentation zum Extensible Optimizer Interface beschrieben. Das ist übrigens Teil des Data Cartridge Developers Guide, einem meiner Meinung nach hochinteressanten Handbuch. Wir müssen demnach einen neuen Objekttypen implementieren. Und dieser Objekttyp muss zwei Funktionen enthalten: ODCIGetInterfaces und ODCIStatsTableFunction. Also brauchen wir zuerst den Object Type als solchen ...

CREATE TYPE tfstats as object (
  dummy number,                          -- object types need attributes - here is one.
  static FUNCTION ODCIGetInterfaces(
    ifclist  OUT ODCIObjectList
  ) RETURN NUMBER,
  STATIC FUNCTION ODCIStatsTableFunction(
   func      IN  SYS.ODCIFuncInfo, 
   outStats  OUT SYS.ODCITabFuncStats, 
   argDesc   IN  SYS.ODCIArgDescList, 
   argument  in number                   -- list all table function arguments here
  ) RETURN NUMBER
);
/
sho err

Das war einfach - nun kommt die konkrete Implementierung. Für unser Beispiel ist die aber nicht besonders schwierig. Der Parameter der Table Function gibt die Anzahl Zeilen ja an; also geben wir ihn einfach wieder zurück. Genau dieses Detail ist in der Praxis mit Sicherheit am schwierigsten, denn aufwändige Berechnungen und Abfragen sind hier fehl am Platz - man muss mit möglichst wenig Aufwand eine Abschätzung finden ...

create or replace type body tfstats as
  static FUNCTION ODCIGetInterfaces(
    ifclist  OUT ODCIObjectList
  ) RETURN NUMBER is
  begin
    -- Always return SYS.ODCISTATS2 here
    ifclist := ODCIObjectList(ODCIObject('SYS','ODCISTATS2'));
  return ODCIConst.Success;
  end;

  STATIC FUNCTION ODCIStatsTableFunction(
    func      IN SYS.ODCIFuncInfo, 
    outStats OUT SYS.ODCITabFuncStats, 
    argDesc   IN SYS.ODCIArgDescList, 
    argument  in NUMBER 
  ) RETURN NUMBER is
  begin
    outStats := SYS.ODCITabFuncStats(argument);
    return ODCIConst.Success;
  end;
end;
/
sho err

Nun ist der Objekttyp implementiert - fehlt noch die Verbindung zur konkreten Table Function. Und das geht so.

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS tf USING tfstats;

Dann schauen wir uns die obigen Ausführungspläne nochmals an ...

SQL> explain plan for 
  2  select * from table(tf(10))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |    10 |    20 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   |    10 |    20 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

SQL> explain plan for 
  2  select * from table(tf(100000))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

--------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows   |  Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      | 100000 | 200000 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   | 100000 | 200000 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Folgt nun noch ein Test, was der Optimizer daraus macht. Ich habe für mein Beispiel den Session-Parameter OPTIMIZER_INDEX_COST_ADJ auf "1" gestellt, damit Indexzugriffe "billig" sind und der Optimizer schneller auf Nested Loops umschaltet ... Und den Ausführungsplan berechne ich nun mal für folgende Query:

explain plan for 
select s.prod_id
from sh.sales s, table(tf({argument})) t
where t.col1 = s.prod_id
/

Ohne dieses Verfahren sieht der Ausführungsplan stets gleich aus. Der Optimizer geht von den oben schon dargestellten ca. 8000 Zeilen aus und entscheidet sich wie folgt.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |   104M|   596M|   620  (91)| 00:00:08 |
|*  1 |  HASH JOIN                         |                |   104M|   596M|   620  (91)| 00:00:08 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| TF             |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   PARTITION RANGE ALL              |                |   918K|  3589K|    29   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS     |                |   918K|  3589K|    29   (0)| 00:00:01 |
|   5 |     BITMAP INDEX FAST FULL SCAN    | SALES_PROD_BIX |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Mit dem Verfahren (und dem Parameter OPTIMIZER_INDEX_COST_ADJ = 1 sieht der Plan mit dem Parameter 100000 in der Table Function so aus ...

-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |  1276M|  7302M|       |  7677  (90)| 00:01:33 |
|*  1 |  HASH JOIN                         |                |  1276M|  7302M|  1368K|  7677  (90)| 00:01:33 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| TF             |   100K|   195K|       |    29   (0)| 00:00:01 |
|   3 |   PARTITION RANGE ALL              |                |   918K|  3589K|       |     1   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS     |                |   918K|  3589K|       |     1   (0)| 00:00:01 |
|   5 |     BITMAP INDEX FULL SCAN         | SALES_PROD_BIX |       |       |       |            |          |
-------------------------------------------------------------------------------------------------------------

... und mit dem Parameter 20 so.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |   255K|  1495K|    35   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |                |   255K|  1495K|    35   (0)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| TF             |    20 |    40 |    29   (0)| 00:00:01 |
|   3 |   PARTITION RANGE ALL              |                | 12762 | 51048 |    35   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS     |                | 12762 | 51048 |    35   (0)| 00:00:01 |
|*  5 |     BITMAP INDEX SINGLE VALUE      | SALES_PROD_BIX |       |       |            |          |
-------------------------------------------------------------------------------------------------------------

Das finde ich eine ganz interessante Möglichkeit - Wenn Table Functions stark schwankende Ergebnismengen liefern und sich diese einfach abschätzen lassen, dann ist das eine recht interessante Herangehensweise ...

Noch ein Szenario mit "User Defined Aggregates"

noreply@blogger.com (Carsten Czarski) — Tue, 08 Mar 2011 09:48:00 +0000

Im vorletzten Blog Posting hatte ich ja vorgestellt, wie man sich eigene Aggregatsfunktionen mit den User Defined Aggregates bauen kann. Heute greife ich das Thema nochmals auf, denn diese Funktionen haben mehr denkbare Einsatzgebiete, als man sich denken mag ...

Wenn man die Dokumentation liest, scheint es, als ob User Defined Aggregates nur einzelne skalare Werte aggregieren können - aber bei genauerer Betrachtung ist das nicht der Fall - man kann über den Umweg eines Objekttypen auch ganze "Datasets" aggregieren lassen. Als Beispiel habe ich das mal mit einem "Sparbuch" probiert - auf diesem Sparkonto gibt es Ein- und Auszahlungen - und mit einer Aggregatsfunktion sollen die Zinsen berechnet werden. Die hängen neben dem Betrag der Transaktion auch von deren Datum ab. Zuerst brauchen wir also unsere Tabelle für die Konten ...

create table tab_buchungen(
 id       number,
 konto    number,
 datum    date,
 betrag   number
)
/

alter session set nls_date_format='DD.MM.YYYY'
/

/*
 * Konto I
 */

insert into tab_buchungen values (1, 4711, '01.01.2010', 500);  

insert into tab_buchungen values (2, 4711, '09.01.2010', 1500);
insert into tab_buchungen values (3, 4711, '15.02.2010', -912);
insert into tab_buchungen values (4, 4711, '25.03.2010', 2500);
insert into tab_buchungen values (5, 4711, '18.05.2010', 2000);
insert into tab_buchungen values (6, 4711, '27.07.2010', -1500);
insert into tab_buchungen values (7, 4711, '03.08.2010', 850);
insert into tab_buchungen values (8, 4711, '19.10.2010', 1350);
insert into tab_buchungen values (9, 4711, '28.11.2010', -2000);


/*
 * Konto II
 */

insert into tab_buchungen values (10, 4712, '01.01.2010', 500);

insert into tab_buchungen values (11, 4712, '09.02.2010', 1500);
insert into tab_buchungen values (12, 4712, '28.02.2010', -912);
insert into tab_buchungen values (13, 4712, '25.03.2010', 2500);
insert into tab_buchungen values (14, 4712, '18.04.2010', 2000);
insert into tab_buchungen values (15, 4712, '19.04.2010', 5000);
insert into tab_buchungen values (16, 4712, '27.05.2010', -3500);
insert into tab_buchungen values (17, 4712, '04.06.2010', 100);
insert into tab_buchungen values (18, 4712, '03.08.2010', 152);
insert into tab_buchungen values (19, 4712, '19.10.2010', 950);
insert into tab_buchungen values (20, 4712, '28.11.2010', 4000);


/*
 * Konto III
 */

insert into tab_buchungen values (21, 4713, '31.12.2009', 5000);


commit
/

Für die Zinsberechnung nehmen wir die Zinszahlen-Methode. Wer genaueres wissen möchte, schaut am besten im Wikipedia-Artikel nach. Die Aggregatsfunktion muss also während der Iteration die Zinszahlen "aggregieren" und zum Abschluß aus der Zinszahl die Zinsen rechnen.

Um die Zinszahlen rechnen zu können, braucht man pro Transaktion eben den Betrag und das Datum, also zwei Werte. Das User Defined Aggregate kann aber nur einen Parameter entgegennehmen - später auf einer Spalte arbeiten. Diesen Gegensatz lösen wir auf, indem wir die beiden Werte in einen Objekttypen kapseln - den Objekttypen verwenden wir quasi als "Transportobjekt".

create type t_buchung as object(
  buchungsdatum        date,
  betrag               number
)
/

Als nächstes brauchen wir noch globale Daten wie den Zinssatz und das Abrechnungsdatum (meist der 31.12., könnte natürlich aber auch jedes andere sein. Da diese Angaben für alle Tabellenzeilen gleich sind, packen wir sie in ein PL/SQL-Paket - direkt erstellt mit get- und set-Methoden.

create or replace package pkg_zinsen is
 function get_zinssatz return number;
 procedure set_zinssatz(p_zinssatz in number);
 function get_abrechnungsdatum return date;
 procedure set_abrechnungsdatum (p_datum in date);
end pkg_zinsen;
/

create or replace package body pkg_zinsen is
 g_zinssatz number;
 g_datum    date;

 function get_zinssatz return number is begin return g_zinssatz; end;
 procedure set_zinssatz(p_zinssatz in number) is begin g_zinssatz := p_zinssatz; end;
 function get_abrechnungsdatum return date is begin return g_datum; end;
 procedure set_abrechnungsdatum (p_datum in date) is 
 begin 
  g_datum := p_datum; 
  if extract(DAY from g_datum) = 31 then
    g_datum := to_date('30'||to_char(g_datum, 'MMYYYY'), 'DDMMYYYY');
  end if;
  if to_char(g_datum, 'DDMM') = '2802' or to_char(g_datum, 'DDMM') = '2902' then
    g_datum := to_date('30'||to_char(g_datum, 'MMYYYY'), 'DDMMYYYY');
  end if;
 end set_abrechnungsdatum;
end pkg_zinsen;
/

Nun kommt dann die Implementierung der Aggregatsfunktion - achtet auf die Verwendung des Objekttypen T_BUCHUNG. Die Funktion bekommt daher für jede Iteration eben nicht einen skalaren, sondern dadurch gleich zwei Werte übergeben. Die anderen Daten nimmt sie aus dem PL/SQL-Paket und hat somit alle Informationen beisammen. In der ODCIAggregateIterate-Methode werden die Zinszahlen berechnet und in der ODCIAggregateTerminate werden die Zinsen ermittelt.Ist ein Zinssatz von 0% angegeben, werden die Zinszahlen selbst zurückgegeben. Wie im Bankwesen üblich, wird ein Monat stets mit 30 Tagen gerechnet.

create type agg_zinsen_t as object(
  v_agg_zinsen number,

  static function ODCIAggregateInitialize(
    sctx IN OUT agg_zinsen_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT agg_zinsen_t, value IN t_buchung
  ) return number,
  member function ODCIAggregateTerminate(
    self IN agg_zinsen_t, returnValue OUT number, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT agg_zinsen_t, ctx2 IN agg_zinsen_t
  ) return number
 );
/
sho err

create or replace type body agg_zinsen_t is

static function ODCIAggregateInitialize(sctx IN OUT agg_zinsen_t)
return number is
begin
  sctx := agg_zinsen_t(0);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT agg_zinsen_t, 
  value IN t_buchung
) return number is
  v_daysm number; 
  v_daysd number; 
  v_daysy number; 
begin
  v_daysy := (extract(YEAR from pkg_zinsen.get_abrechnungsdatum) - extract(YEAR from value.buchungsdatum)) * 360;
  v_daysm := (extract(MONTH from pkg_zinsen.get_abrechnungsdatum) - extract(MONTH from value.buchungsdatum)) * 30;
  if to_char(value.buchungsdatum, 'DD') = '31' or to_char(value.buchungsdatum, 'DDMM') in ('2802','2902') then
    v_daysd := 30;
  else 
    v_daysd := extract(DAY from value.buchungsdatum);
  end if;
  v_daysd := extract(DAY from pkg_zinsen.get_abrechnungsdatum) - v_daysd;
  self.v_agg_zinsen := self.v_agg_zinsen + round((value.betrag * (v_daysy + v_daysd + v_daysm) / 100)); 
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN agg_zinsen_t, 
  returnValue OUT number, 
  flags IN number
) return number is
begin
  if pkg_zinsen.get_zinssatz = 0 then 
    returnValue := self.v_agg_zinsen;
  else 
    returnValue := self.v_agg_zinsen / (360 / pkg_zinsen.get_zinssatz);
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT agg_zinsen_t, ctx2 IN agg_zinsen_t) return number is
begin
  self.v_agg_zinsen := self.v_agg_zinsen + ctx2.v_agg_zinsen;
  return ODCIConst.Success;
end;
end;
/
sho err

Nun kommt die Einrichtung der Aggregatsfunktion an sich - die auf der eben eingespielten Implementierung basiert - wiederum nimmt sie keinen skalaren Datentypen entgegen, sondern BUCHUNG_T.

CREATE or replace FUNCTION agg_zinsen (input t_buchung) RETURN number
PARALLEL_ENABLE AGGREGATE USING agg_zinsen_t;
/
sho err

Und das war's - nun kann man testen. Zuerst setzen wir die globalen Parameter im PL/SQL-Paket.

begin
 pkg_zinsen.set_abrechnungsdatum(to_date('31122010', 'DDMMYYYY'));
 pkg_zinsen.set_zinssatz(1.5);
end;
/

Und dann können wir die Zinsen eines Kontos abrechnen - mit nichts als einer SQL-Abfrage. Achtet darauf, dass Ihr syntaktisch zuerst ein BUCHUNG_T-Objekt mit Buchungsdatum und Betrag erzeugt und dieses dann an die Aggregatsfunktion übergebt.

select konto, agg_zinsen(t_buchung (datum, betrag)) zinsen from tab_buchungen
group by konto
/

     KONTO     ZINSEN
---------- ----------
      4711 61,5666667
      4712      96,95
      4713         75

Ein Zinssatz von 0% gibt die Zinszahl direkt zurück.

begin
 pkg_zinsen.set_abrechnungsdatum(to_date('31122010', 'DDMMYYYY'));
 pkg_zinsen.set_zinssatz(0);
end;
/

select konto, agg_zinsen(t_buchung (datum, betrag)) zinsen from tab_buchungen
group by konto
/

     KONTO     ZINSEN
---------- ----------
      4711      14776
      4712      23268
      4713      18000

Es lassen sich also auch komplexere Dinge mit Benutzerdefinierten Aggregaten erledigen. Interessant sind hier viele Aufgaben aus der Finanzmathematik - Neben der einfachen Zinsrechnung ist auch jede Form der Renditeermittlung interessant. Und wenn es als Aggregatsfunktion bereitsteht, erhöhen sich die Nutzungsmöglichkeiten nochmals massiv - denn die Funktion steht per SQL-Abfrage bereit.

BLOBs ins Dateisystem "entladen" - nur mit einer "SQL Abfrage"

noreply@blogger.com (Carsten Czarski) — Wed, 23 Feb 2011 12:21:00 +0000

Wolltet Ihr schonmal eine Tabelle mit BLOBs in ein Verzeichnis "ausladen" ... wobei jeder BLOB in eine eigene Datei geschrieben werden soll ...? Bei mir war es heute wieder mal soweit ... und natürlich hätte man dazu ein wenig PL/SQL-Code mit UTL_FILE schreiben können - ich wollte es aber schnell haben. Also habe ich mein Package für Betriebssystem-Kommandos und Dateisystem-Zugriffe genommen. Meine Tabelle sah so aus ...

SQL> desc target_table
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------------
 ID                                                 NUMBER
 FILENAME                                           VARCHAR2(4000)
 FILECONTENT                                        BLOB

Das "Entladen" konnte ich mit einem einfachen SQL SELECT machen:

SQL> select 
  2  filename, 
  3  file_pkg.get_file('/home/oracle/test/'||filename).make_file().append_to_file(filecontent) bytes_written 
  4 from target_table;

FILENAME                                           BYTES_WRITTEN
-------------------------------------------------- -------------
2010-003a_yahoo_quote.html                                  3305
2010-003b-bestdbproject.html                                1433
2010-007_charset_nls_iana.html                              3125
2010-009_emalert_rss.html                                  17047
2010-009_emalert_rss_01.png                               171007
2010-009_emalert_rss_02.png                                38661
2010-009_emalert_rss_03.png                               141388
2010-010_blob-remote.html                                  14636
2010-011_imp_dataonly.html                                  3471
2010-012_recursive-table-functions.html                     4916
2010-013_romannumber.html                                   1153
2010-014-doag-sig-spatial.html                              1025
2010-015-dbms-applicationinfo.html                          7539
2010-016-addmonths.html                                     3842
2010-017-zip.html                                          29251
:                                                              :

50 rows selected.

Elaped: 00:00:00.17

Danach sah das Verzeichnis /home/oracle/test so aus ...

[oracle@sccloud038 test]$ ls -lah
total 752K
drwxr-xr-x 2 oracle oinstall 4.0K Feb 23 13:13 .
drwxr-xr-x 3 oracle oinstall 4.0K Feb 23 13:12 ..
-rw-r--r-- 1 oracle oinstall 2.8K Feb 23 13:13 2010-001a_apex-plugins.html
-rw-r--r-- 1 oracle oinstall 8.9K Feb 23 13:13 2010-001_dbms_datapump_exp.html
-rw-r--r-- 1 oracle oinstall 3.6K Feb 23 13:13 2010-002a_oscomm_0.9.html
-rw-r--r-- 1 oracle oinstall 8.0K Feb 23 13:13 2010-002_dbms_datapump_imp.html
-rw-r--r-- 1 oracle oinstall 3.3K Feb 23 13:13 2010-003a_yahoo_quote.html
-rw-r--r-- 1 oracle oinstall 1.4K Feb 23 13:13 2010-003b-bestdbproject.html
-rw-r--r-- 1 oracle oinstall  12K Feb 23 13:13 2010-003_dbms_stat_funcs.html
-rw-r--r-- 1 oracle oinstall 7.7K Feb 23 13:13 2010-004_miniskript_lo.html
-rw-r--r-- 1 oracle oinstall  11K Feb 23 13:13 2010-005_resumable.html
-rw-r--r-- 1 oracle oinstall 6.5K Feb 23 13:13 2010-006_video.html
-rw-r--r-- 1 oracle oinstall 3.1K Feb 23 13:13 2010-007_charset_nls_iana.html
-rw-r--r-- 1 oracle oinstall  29K Feb 23 13:13 2010-008-recursive_with.html
-rw-r--r-- 1 oracle oinstall 167K Feb 23 13:13 2010-009_emalert_rss_01.png

Ist doch nett, oder ...? Alles im allem habe ich für das Schreiben dieses Postings mehr Zeit gebraucht ...

SQL-Aggregatsfunktion "PRODUCT" fehlt ...? Kein Problem - wir bauen sie selbst!

noreply@blogger.com (Carsten Czarski) — Fri, 11 Feb 2011 08:58:00 +0000

Auf einer Veranstaltung in München, während es um das Thema Analytische Funktionen ging, fragte mich jemand nach einer Aggregatsfunktion für die Bildung eines Produktes - also nicht das Aufsummieren der Werte wie bei SUM, sondern das multiplizieren - und in der Tat: eine solche Funktion gibt es in Oracle nicht.

Nun kann man das mit PL/SQL natürlich auf einfachste Weise nachprogrammieren - aber eine solche Funktion lässt sich nicht in SQL-Abfragen mit GROUP BY nutzen. Berechnungen für Datengruppen in einer Tabelle müssen dann mühsam mit PL/SQL nachgebildet werden.

Aber das muss nicht sein. Es ist kaum bekannt, aber man kann eigene Aggregatsfunktionen in der Datenbank hinterlegen. Das habe ich in einem sehr frühen Blog-Posting schonmal genutzt: Mit der dort beschriebenen LIST-Funktion kann man VARCHAR2-Spalten auch von vor Oracle11g zusammenfassen (in Oracle11g gibt es mit LISTAGG ja eine eingebaute Funktion). Um ein User Defined Aggregate zu bauen, muss man eine Schnittstelle ausprogrammieren. Und hier ist der Code für AGG_PRODUCT.

drop function agg_product;
drop type agg_product_t;

create type agg_product_t as object(
  v_agg_product number,

  static function ODCIAggregateInitialize(
    sctx IN OUT agg_product_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT agg_product_t, value IN number
  ) return number,
  member function ODCIAggregateTerminate(
    self IN agg_product_t, returnValue OUT number, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT agg_product_t, ctx2 IN agg_product_t
  ) return number
 );
/
sho err

create or replace type body agg_product_t is

static function ODCIAggregateInitialize(sctx IN OUT agg_product_t)
return number is
begin
  sctx := agg_product_t(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT agg_product_t, 
  value IN number
) return number is
begin
  self.v_agg_product:=self.v_agg_product * nvl(value, 1);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN agg_product_t, 
  returnValue OUT number, 
  flags IN number
) return number is
begin
  returnValue := self.v_agg_product;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT agg_product_t, ctx2 IN agg_product_t) return number is
begin
  if ctx2.v_agg_product is null and self.v_agg_product is not null then
    null;
  elsif ctx2.v_agg_product is not null and self.v_agg_product is not null then
    self.v_agg_product := self.v_agg_product * ctx2.v_agg_product;
  elsif ctx2.v_agg_product is not null and self.v_agg_product is null then
    self.v_agg_product := ctx2.v_agg_product * 1;
  elsif ctx2.v_agg_product is null and self.v_agg_product is null then
    null;
  end if;
  return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION agg_product (input number) RETURN number
PARALLEL_ENABLE AGGREGATE USING agg_product_t;
/
sho err

Die Funktion könnt Ihr danach sofort verwenden.

SQL> select deptno, agg_product(sal) product_sal from emp
  2  group by deptno

    DEPTNO PRODUCT_SAL
---------- ------------------------------
        10 15925000000
        20 23562000000000000
        30 10153125000000000000

Auch als analytische Funktion kann sie verwendet werden.

select 
  ename, 
  agg_product(sal) over (order by sal desc rows between unbounded preceding and current row) product_sal 
from emp;

ENAME          PRODUCT_SAL
---------- ---------------
KING                  5000
FORD              15000000
SCOTT          45000000000
JONES      133875000000000
BLAKE      3,815437500E+17
CLARK      9,347821875E+20
:                        :

Das geht mit einer normalen PL/SQL-Funktion nun wirklich nicht; die ganze analytische "Power" von SQL kann so ausgenutzt werden. Nicht jeder braucht wirklich eine AGG_PRODUCT-Funktion; aber wenn Ihr Aggregate mit eigenen Funktionen bilden müsst, sind die User Defined Aggregates auf jeden Fall einen Blick wert.