22. Dezember 2011

Zum Ende von 2011: Oracle 4.1.4 ...

At the end of 2011: Oracle 4.1.4
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!
My last blog posting in 2011 will not contain technical stuff. The last few days in a year are a good chance to relax a bit - and so I just would like to post some screenshots which I have taken while installing the Oracle 4.1.4 files which I found a couple of weeks ago. No XML, no PL/SQL, no Oracle TEXT, no APEX ... but EMP ... and DEPT.
Merry Christmas and a happy new year 2012!

7. Dezember 2011

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

How to calculate "ratio to report" in SQL
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.
This morning I got a SQL question - nothing difficult - but I think, this kind of requirement is more frequent, and therefore I decided to publish the answer for everyone. The question is based on table data (as always) ...
|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 |
The right column cost_share should contain the row's share of total cost - expressed in percent. So the cost column needs to be divided by SUM(COST) over all rows. I'm lazy: so instead of creating this table I decided to solve the problem for the SAL column in the well known EMP table. Let's start with "classic" SQL.
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
This query should work on almost every database system. First we compute the total salary amount with an inline view using the WITH clause. This inline view will be joined in the main query. It returns only one row, so we don't need any join criteria and we can access the total salary amount just like a table column. And this can be extended - if we'd like to see the salary share expressed as "percentage of the department total", we add a GROUP BY to the inline view and extend the join in the main query as follows ...
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
This query uses SUM without GROUP BY, because it's the analytic SUM function. The query window , which defines the rows to be aggregated, is specified in the OVER() clause. The aggregate is then calculated to each row of the query result set. An empty OVER clause means as much as "over all rows". But we can also do the calculation on the department level.
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
Now we have the keywords PARTITION BY inside the OVER() clause. So the aggregate is being computed for each department. Don't confuse this PARTITION BY clause with table partitioning. Opposed to the latter, PARTITION BY within an analytic function has nothing to do with table storage and does not require an additional license. But we can solve the original problem even more elegant. Why? Because there is a special function for this purpose: RATIO_TO_REPORT.
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
And this (of course) also works with PARTITION BY ...
select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over (partition by deptno) * 100 share
from emp e
/
Analytic functions are absolutely worth the learing effort. Beyond the problem described here, analytic functions provide an easy query syntax for things like moving averages, rankings or other aggregates with flexible query windows. Solving this with "classic" SQL is possible (of course), but this most often gets cumbersome and difficult to read. You'll find more information in the documentation.

21. November 2011

DROP TABLE .. Sind Sie sicher?

"Are you sure?" for DROP TABLE
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 ...?
UPDATE: Just yesterday I installed this package on my own "production" instance - and as I wanted to login into APEX today I encountered my own message Cannot DROP/TRUNCATE table "WWV_FLOW_USER_ACCESS_LOG2$" - so APEX does some regular DROP / TRUNCATE operations which must not be trapped by the Trigger - So I extended the trigger code a bit (further extensions might be necessary in the future) - just have a look into the new trigger code. If you also encounter DROP or TRUNCATE operations which need to ge generally allowed in the trigger, feel free to post me a message or a comment.
Recently I again encountered the database event triggers in PL/SQL - using these you can place a trigger not only on table or view DML, but also on DDL commands or database events like STARTUP, SHUTDOWN oder LOGON. One thirst thought would be prohibiting the DROP operation on a production instance - the trigger code for this is rather simple ...
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;
/
Simple and powerful. On this instance no object can be dropped any more (except the trigger itself, of course). But this can also lead to a lot of problems, of course: Even on production systems, sometimes there must be dropped something. Then the trigger needs to be disabled before and enabled after the operation. And then there might be objects which are frequently dropped and re-created and other objects which not. And what about the TRUNCATE operation. This one cannot be undone with FLASHBACK TABLE TO BEFORE DROP - so accidentially use of TRUNCATE is much more dangerous than the DROP operation ...
So my next thought was to try a bit more sophisticated approach - I'd like to see the "Are you sure?" feature which we all know from Windows also in the database. And although we cannot catch the DROP operation to ask the "are you sure" question, we can implement a similar approach: The idea is to declare the object to be dropped beforehand. So we have a package which holds the declared object and a trigger which looks into the package and raises an error message if the object has not been declared. First we create the package.
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
/
Don't forget to grant EXECUTE Privileges and to create a public synonym in order to make the package accessible for everyone. Creating the Trigger is the next step. As said before, it simply checks whether the object to be dropped ( ora_dict_obj_name, ora_dict_obj_type, ora_dict_obj_owner) has been declared with the PRECODE package. If not, the trigger raises an error, otherwise it does nothing and the operation will succeed.
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;
/
That's it. From now on, DROP and TRUNCATE operations need to be declared beforehand ...
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>
This implementation of the PREDROP package already provides some flexibility. Setting the object name, type or owner to SQL NULL matches everything. Normally a declaration is valid until it has been matched by a DROP operation - after calling PREDROP.SESS it will be valid until the end of the session - this is useful when runnung SQL scripts. Using regular expressions or LIKE syntax would make the package even more flexible ... but this is a story for another blog posting ...
Tell me what you think!

21. Oktober 2011

Ab morgen: DOAG2011

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 ...
This is a posting about the DOAg2011 conference and therefore in german only.

4. Oktober 2011

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

From Java to SQL: Java objects and the database JVM
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
The "experts" might have notices that all java methods are "static" - that's evident. PL/SQL is a procedural, not an object-oriented language - so when we want to integrate PL/SQL with Java in the database we need to use the "procedural part" of Java which are static class methods. If you want to map java methods to procedures and functions in a PL/SQL package you must use static methods for that.
When mapping DATE values from Java to PL/SQL a bit attention is needed. Basically a date can be represented in java using java.sql.Date or java.sql.Timestamp. When those are being mapped to DATE in SQL or PL/SQL, the java.sql.Date class only maps Day, Month and Year - the time in the DATE would be set to midnight. For having the time component also you need to use java.sql.Timestamp. Apart from this the mapping of simple scalar datatypes from Java to SQL and PL/SQL is quite simple. So the first rule is to use simple types whenever possible. Complex objects should only be used when they're really needed ... because, as we will see, they require additional coding ...
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
The Java engine within the database needs to interact with the SQL layer - we need a JDBC database connection for that. This connection is not more than kind of a pointer to the actual database session in which the java code runs in. The StructDescriptor class is a utility which helps us to create a STRUCT object exactly matching FILE_T. Note the usage of the connection object and "FILE_T" when the StructDescriptor instance is being created. After that we collect all relevant file attributes in an Array of the fundamental Java class Object[]. The developer needs to take care about the order within that array: Our object type has eight simple, scalar attributes. The Java types in the Object array (String, int, java.sql.Date) must match the attributes of the object type (VARCHAR, NUMBER, DATE). With this array, the StructDescriptor instance, and the database connection the STRUCT instance is being created and returned in the last step of the program. This STRUCT instance exactly matches the FILE_T definition in the SQL layer.
The final step as (as always) the PL/SQL Wrapper for the static method. We create a function which takes a VARCHAR2 (containing the file path) and returns a FILE_T. These are being mapped to java.lang.String and oracle.sql.STRUCT . All object types are being encoded as oracle.sql.STRUCT - the StructDescriptor objects cares for the mapping to the correct SQL type.
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
:                         :                            :
It's quite simpe, isn't it? Using this approach you can pass any data structure between Java and PL/SQL - you can use any Java library you want. The only prerequisite is that your Java library does not depend on native code - it must be pure Java. First you might design a simple interface consisting of static Java methods which can be easily mapped to PL/SQL calls. Use simple, scalar types whenever possible and use object types and arrays only when you really need them. Then you need to implement the static java methods (using STRUCT and ARRAY, if necessary), create the SQL object types and PL/SQL wrapper packages and you are done. The code packages for filesystem access, zum accessing POP3 or IMAP4 mail servers with PL/SQL or topack and unpack ZIP archives all work that way. And as said: You can use integrate any Java library doing interesting stuff into the Oracle database. There are no limits.

16. September 2011

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

Twitter postings by the database
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 ...
My teammates and me are using the twitter account @oraclebudb, to post interesting news about the Oracle database (Events in german language, Releases and more) for a german audience. So if you are a german reader and interested in regular updates about the Oracle database, follow @oraclebudb. English readers might follow @oracledatabase.
But sometimes one wants to schedule a tweet - perhaps for tomorrow at 10:00am. If one wants to use Twitter for regular updates about something (think about floodwater levels for a river) some kind of automation or scheduling is needed - manual tweets are not possible. So the question is: How to implement this? I'd like to do it with APEX, the Oracle database and PL/SQL. So I asked Mr. Google how to do this and I got some PL/SQL fragments which I could not manage to work on my system. But I saw that the "OAuth" mechanism which Twitter uses for authenticating is something which I cannot implement in 5 minutes in PL/SQL.
Java developers have the very popular twitter4j. library - and since the Oracle database can run Java Stored Procures I decided not to reinvent the wheel and to use things already there (XE-Users: Sorry). The following posting describes how to do automatic tweets from the Oracle database using the Twitter4j library and some Java and PL/SQL coding with the "own" twitter account; the library allows also authorization for other Twitter accounts - but this is not described here.
Step 1: Register your application on "dev.twitter.com":
Log into dev.twitter.com with your Twitter account. In the menu in the upper right corner (where you can see your Twitter username) navigate to My Applications and click on Create a new Application. Then fill in the form. You need a name, a description and a Website URL (which don't need to work for now). The callback URL is not needed - after that manage the CAPTCA and your application is registered.
Register your application
You now see the key pair (Consumer Key and Consumer Secret) for your application. We will need that later on.
The application has been registered.
Now navigate to the Settings tab and adjust the Privileges of your Application. It will need Read and Write privileges, since a tweet is a "Write".
Grant "Read and Write" privileges to the application
Then go back to the Details tab and navigate down to the button named Create My Access Token - this will generate the keys for your account. (Access Token and Access Token Secret). Our application has to create a signature using the two application keys as well as the two account keys - but the Twitter4j library will do the necessary stuff.
Generated "Access Token" and "Access Token Secret"
Now you're done with registering the application. We can now start the implementation in the Oracle database.
Step 2: Load the "twitter4j" library into the database.
Download twitter4j from twitter4j.org. Twitter4j is Open Source software and it uses the Apache License. So you are free to use for either commercial or non-commercial usage without a fee. Unpack the ZIP archive into a folder of your choice - it's best to place it directly on the database server.
Contents of the downloaded ZIP archive
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
Step 3: Create a java method doing a status update
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
Step 4: Create the PL/SQL wrapper
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
Step 5: Grant privileges
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;
/
Final testing
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.
Point your browser to your twitter page - you should see that posing - done by SQL in the Oracle database. From this point we can use this procedure as any other one. Creating a DBMS_SCHEDULER job is as easy as creating an APEX application from which tweets are being posted. A simple example is an APEX application with a form on a table where the user can enter a tweet and a timestamp at which to post the tweet. A DBMS_SCHEDULER job runs in regular intervals an posts all tweets due. The twitter4j API allows much more operations ... we could query the Home Timeline and pass it back to the SQL - but this is another story ... for another blog posting ...

Beliebte Postings