16. Dezember 2008

Subquery oder Inline View? Die SQL WITH Klausel und deren Ausführung

English title: Subquery or inline view: The WITH clause and its execution

Als letztes Posting für dieses Jahr möchte ich ein wenig was über die WITH-Klausel erzählen; ich selbst nutze sie ziemlich häufig und auch sehr gerne - aber immer wieder bekomme ich dann Fragen wie: "Was ist denn das ...?". Zunächst ein Beispiel - damit ist alles leichter ...
My last posting for this year will be about the SQL WITH clause. I use it very frequently but also get often questions like "What's that?. We'll start with an example:
with dept_sal as (
  select d.deptno, d.dname, sum(e.sal) sum_sal
  from dept d join emp e on (d.deptno = e.deptno)
  group by d.deptno, d.dname
)
select deptno, dname, sum_sal
from dept_sal 
where sum_sal > 2000
Wie man sieht, ist die WITH-Klausel ein etwas andere Form der Subquery; die "Subquery" erhält mit der WITH-Klausel einen Namen und kann unter diesem wie eine View nachfolgend beliebig wiederverwendet werden. Daher wird die Subquery innerhalb einer WITH-Klausel auch Inline View genannt. Und natürlich gehen auch mehrere WITH-Klauseln in einer Abfrage:
The WITH clause is, as you can see, another form of a subquery. The subquery gets a name (which is valid only for this particular SQL query) and can be reused under this name as often as the developer wants. Since this is very similar to a view the query inside a WITH clause is also called an inline view.
with dept_sal as (
  select d.deptno, d.dname, sum(e.sal) sum_sal
  from dept d join emp e on (d.deptno = e.deptno)
  group by d.deptno, d.dname
), dept_sal_mit_avg as (
  select 
    deptno, dname, sum_sal,
    avg (sum_sal) over (order by dname) dept_sum_sal_avg
  from dept_sal
)
select deptno, dname, sum_sal
from dept_sal_mit_avg 
where sum_sal > dept_sum_sal_avg
/
Ein direkter Vorteil der WITH-Klausel gegenüber einer "normalen" Subquery ist, dass man die Inline Views mehrfach wiederverwenden kann. Eine Subquery müsste man mehrfach in die Abfrage hineinschreiben. Persönlich bin ich der Ansicht, dass komplexere Abfragen mit Inline Views übersichtlicher bleiben als mit Subqueries - aber dazu gibt es bestimmt auch andere Ansichten.
One direct advantage of the WITH clause over a traditional subquery is that it can be used multiple times. A subquery would have to created as view objects or the actual query would have to be repeated. And my personal thinking is that complex queries are much better readable with inline views instead of subqueries.
Schauen wir uns nun noch an, wie der Optimizer mit einer WITH-Klausel umgeht. Das ist vor allem dann interessant, wenn man deren größten Vorteil nutzen, eine Inline View also mehrfach in einer Abfrage nutzen möchte. Dazu ein Beispiel.
Now let's have a look how the optimizer deals with the inline view. This is particular interesting when we use the inline view multiple times and the inline view query is expensive. The following example illustrates this:
set serveroutput on

create or replace function doit return number as
begin
  dbms_output.put_line('function called.');
  return 1;
end;
/

with do_func as (
  select doit val from dual
)
select val from do_func, emp
/
Das Beispiel ruft die Funtion doit in einer Inline View als SELECT .. FROM DUAL auf. Diese Inline View wird anschließend mit der Tabelle EMP per Join zusammengeführt. Nun ist die Frage: Wie oft wird doit ausgeführt. Die Codezeile mit DBMS_OUTPUT sagt es uns.
The function doit (which just writes a line with DBMS_OUTPUT) is being used in an inline view as SELECT ... FROM DUAL. Now this inline view gets joined with the EMP table (14 rows).Now the question: How often gets the function called?

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
:
:
function called.
Der Ausführungsplan:
Execution Plan:
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |     5   (0)| 00:00:01 |
|   2 |   FAST DUAL        |      |     1 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Die Ausgabe function called sehen wir 14 mal; der Optimizer hat die Inline View also mit der "Hauptabfrage" gemerged und führt die Funktion entsprechend 14 mal aus. Man sieht es im Ausführungsplan: Nested Loops. Wenn die Funktion nun sehr teuer ist, ist genau dies u.U. nicht erwünscht - die Syntax der WITH-Klausel legt ja den Eindruck nahe, dass die Funktion nur einmal ausgeführt wird. Und das kann man auch erreichen: Im Prinzip kommen hierfür zwei Hints in Betracht: NO_MERGE, der in 11g neue RESULT_CACHE und das undokumentierte MATERIALIZE. Probieren wir zuerst NO_MERGE.
We see function called 14 times. The optimizer merged the inline view query with the "main" query and executed it using nested loops. Therefore the function is called for each row of the EMP table. If the function is very expensive this is not a very good execution strategy - and the syntax of the WITH clause leads to the assumption that the function is executed only one time. We'll try Optimizer hints to improve the behaviour: NO_MERGE, the in Oracle11g introduced RESULT_CACHE and the undocumented MATERIALIZE. First we try NO_MERGE.
with do_func as (
  select doit val from dual
)
select /*+ NO_MERGE (d) */ d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
function called.
Der Ausführungsplan:
Execution Plan:
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   182 |     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    14 |   182 |     5   (0)| 00:00:01 |
|   2 |   VIEW               |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | EMP  |    14 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Die Funktion wurde nun nur noch zweimal ausgeführt. Das ist schon besser, aber man stellt sich schon die Frage, ob man die Datenbank nicht dazu bringen kann, die Funktion einmal auszuführen, sich das Ergebnis zu merken und dann weiterzuarbeiten. Und in Oracle11g kann man dies mit dem Result Cache erreichen. Wenn wir mit einer PL/SQL-Funktion arbeiten, muss diese allerdings als deterministisch deklariert sein. Deterministisch meint, dass gleiche Eingabeparameter zum gleichen Ergebnis führen. Ist das nicht der Fall, so bleibt nur der weiter hinten folgende undokumentierte MATERIALIZE-Hint.
The function is now called twice. This is much better but there is still the question whether it is possible that the optimizer executes the query once, "memorize" the result and continue. And this can be achieved in Oracle11g using the result cache. But when using a PL/SQL function, it is important that this is declared deterministic. Deterministic means that equal input parameters lead to equal function results. If your function is not deterministic then the undocumented MATERIALIZE hint might be a solution.
create or replace function doit return number deterministic as
begin
  dbms_output.put_line('function called.');
  return 1;
end;
/

with do_func  as (
  select /*+ result_cache */ doit val from dual
)
select /*+ result_cache */ d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
Die Funktion wird einmal ausgeführt. Der Ausführungsplan:
The function is being executed one. The Execution Plan:
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |    14 |   182 |     5   (0)| 00:00:01 |
|   1 |  RESULT CACHE         | d0r3jha88bjgjgrpt9rgv2aw98 |       |       |            |          |
|   2 |   MERGE JOIN CARTESIAN|                            |    14 |   182 |     5   (0)| 00:00:01 |
|   3 |    VIEW               |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |     RESULT CACHE      | 3q99fnp78n4wr4nbdpj223u8b9 |       |       |            |          |
|   5 |      FAST DUAL        |                            |     1 |       |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | EMP                        |    14 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; dependencies=(SCOTT.EMP, SCOTT.DOIT); 
       name="with do_func  as (select /*+ result_cache */ doit val from dual)
             select/*+ result_cache */  d.val from do_func d, emp e"

   4 - column-count=1; dependencies=(SCOTT.DOIT); attributes=(single-row); 
       name="select /*+ result_cache */ doit val from dual"
Das Ergebnis der Inline View wurde im Result Cache gespeichert (im Ausführungsplan unter 4 erkennbar). Aber was ist, wenn die Funktion gar nicht deterministisch ist - also gleiche Eingabeparameter durchaus (vielleicht zu einer anderen Zeit) zu anderen Ergebnissen führen können)? Wenn die Abhängigkeiten in der Datenbank bekannt sind (bspw. das Funktionsergebnis hängt von den Inhalten einer Tabelle ab) könnte man die Abhängigkeiten deklarieren:
The inline view results were placed into the result cache ("4" in the execution plan). But what if the actual function is not deterministic? If the dependencies are known in the database (e.g. table contents) they can be declared in the function code as follows:
create or replace function doit return number result_cache relies_on ([table]) as
:
Wenn die Funktion nicht deterministisch ist und die Abhängigkeiten nicht bekannt sind (bspw. Zufallswerte, Abhängigkeit von der aktuellen Zeit), dann kann der Result Cache nicht genutzt werden. In diesen Fällen und in Oracle10g kann der undokumentierte MATERIALIZE hint eine Lösung sein.
If the function dependencies are not known in the database (random values, result depends on the current timestamp) then the result cache cannot be used. In those cases and in Oracle10g the undocumented MATERIALIZE hint might be a solution.
with do_func as (
  select /*+ MATERIALIZE */ doit val from dual
)
select d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
Man sieht: Die Funktion wurde genau einmal ausgeführt. Schauen wir und den Ausführungsplan an:
The function is executed once. Look at the execution plan:
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |    14 |   182 |     7   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | EMP                         |       |       |            |          |
|   3 |    FAST DUAL               |                             |     1 |       |     2   (0)| 00:00:01 |
|   4 |   MERGE JOIN CARTESIAN     |                             |    14 |   182 |     5   (0)| 00:00:01 |
|   5 |    VIEW                    |                             |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_40E25BF1 |     1 |    13 |     2   (0)| 00:00:01 |
|   7 |    BUFFER SORT             |                             |    14 |       |     5   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | EMP                         |    14 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Der Optimizer "merkt" sich das Ergebnis wirklich in einer temporären Tabelle. Ihr könnt auch nachsehen ... (als SYS)
The optimizer "memorizes" the query result in a temporary table. You can describe it (as SYS).
SQL> desc SYS_TEMP_0FD9D6604_40E25BF1
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 C0                                                 NUMBER
Gerade bei sehr teuren Funktionen (Beispiele wären Funktionen, welche Daten von extern laden) kann dies sehr nützlich sein. Beachtet aber bitte, dass der Hint MATERIALIZE von Oracle nicht dokumentiert ist; man müsste ihn also in jedem neuen Release testen ... Die Nutzung eines undokumentierten Hints sollte stets die "Ultima Ratio" sein.
This is extemely useful for long running or expensive subquries. But keep in mind that Oracle does not document this hint - so you have to test it in new releases. The usage of an undocumented hint should always the the "ultima ratio" - the choice when other things fail.
So - das war's für dieses Jahr. In zwei Wochen iss 'rum - und nächstes Jahr sieht man sich wieder. Bis dahin schöne Feiertage, kommt gut ins Neue Jahr und lasst den Rechner ein paar Tage aus.
That's it ... for 2008. In two weeks the year is over - Season's greetings and in 2009 we'll see each other again.

11. Dezember 2008

IN-Klauseln, dynamisches SQL und Bindevariablen

English title: IN conditions, dynamic SQL and Bind Variables

Auf der DOAG-Konferenz diskutierte ich mit jemandem, wie man IN-Klauseln mit Bindevariablen (für die Java-Programmierer: PreparedStatements) implementieren kann. Das Problem dabei ist, dass die Anzahl der Werte in der IN-Klausel nicht immer gleich ist. Für den Java-Programmierer kann ein Abfrage mit vier Argumenten so aussehen:
At the "DOAG conference" two weeks ago I discussed with somebody how an IN condition within a SQL WHERE clause could be used with bind variables (for with Java words: with PreparedStatements). And the challenge is that the number of arguments is not each time the same. A query with four arguments might look like the following ...
select [col1], [col2], [col3] from [table] where [col4] in (?, ?, ?, ?)
Wenn für die nächste Abfrage nun fünf Werte in die IN-Klausel gesetzt werden sollen, muss ein neues Statement erzeugt und geparst werden - in der Datenbank kann der vorhandene Cursor also nicht wiederverwendet werden; auf Java-Seite muss man neue Objekte erzeugen.
The next query now might have five arguments - so the Java programmer cannot reuse the first query statement. A new object has to be constructed which means that the current cursor in the database can also not be reused.
Ein Lösungsansatz ist, mit temporären Tabellen zu arbeiten; die Werte dort mit SQL INSERT-Kommandos hineinzuschreiben und dann anstelle der konkreten Werte eine Subquery in der eigentlichen Abfrage zu verwenden. Es gibt jedoch auch noch einen anderen Ansatz: Anstelle der temporären Tabelle kann man auch einen Objekttypen nehmen: Abhängig von dem Datentypen, der in der IN-Klausel verwendet werden soll, erzeugt man sich also zunächst einen "Array-Typen":
One possible solution is the usage of temporary tables. So the developer can create a temporary table, insert the arguments into that table and use a subquery in the actual query. But (if you don't like temporary tables) there is also another solution: You also can use object types for this. So first we create an array type suitable for our query.
CREATE TYPE QUERY_ARRAY AS TABLE OF [NUMBER | VARCHAR2(x) | DATE] 
Die SQL-Abfrage muss nun so umgestellt werden:
Next we rearrange the SQL query:
SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))
Wir verwenden wieder eine Subquery; allerdings nicht mit einer temporären Tabelle, sondern mit einer Variablen innerhalb der Funktion TABLE(). Diese Variable muss vom soeben neu erstellten Typ QUERY_ARRAY sein. Dann stellt sich natürlich sofort die Frage, wie man einen solchen Typen (von Java aus) erzeugt ... Hier ist ein Beispiel:
We use (as we would have done with a temporary table) a subquery to embed the array of arguments into the SQL query. The TABLE() function contains a variable of the just created data type QUERY_ARRAY and makes the contents accessible to the SQL engine. This leads immediately to the next question: How are those user-defined data types representated in JDBC code ...? Here is some example code.
import java.sql.*;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

/*
 * This program queries multiple rows from the EMP
 * table using the IN clause.
 *
 * It always uses THE SAME STATIC SQL Query - the number
 * of arguments for the IN clause does not matter.
 *
 * SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))
 *
 * Prerequisite: 
 * - An object type NUMBER_ARRAY must exist in the DB Schema
 *   CREATE TYPE QUERY_ARRAY AS TABLE OF NUMBER   
 *   
 */ 

public class JdbcDynamicInQuery {
  static String sConn = "jdbc:oracle:thin:@192.168.2.140:1521:orcl";
  static String sUser = "scott";
  static String sPass = "tiger";
  static String sSql =  "SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))";

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(sConn, sUser, sPass);
    con.setAutoCommit(false);

    // Those following lines generate the JDBC representation of the
    // object type QUERY_ARRAY 
    ArrayDescriptor dbArrayDesc = ArrayDescriptor.createDescriptor("QUERY_ARRAY", con);
    ARRAY dbArray = new ARRAY(dbArrayDesc, con, args);

    // prepare the Statement
    PreparedStatement pstmt = con.prepareStatement(sSql);

    // Use the setArray method to pass the Array from JDBC to the database
    pstmt.setArray(1, dbArray);
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      System.out.println(rs.getString("ENAME"));
    }
    rs.close();
    pstmt.close();
    con.close();
  }
}
Die Klasse oracle.sql.ARRAY repräsentiert einen vom Benutzer in der Datenbank definierten Array-Typen. Da der Name dieses Typen frei wählbar ist, wird der oracle.sql.ArrayDescriptor benötigt, um ein solches Objekt zu erzeugen. Die Inhalte kommen aus einem gewöhnlichen Java-Array (hier: args). Ihr könnt das Beispielprogramm testen, indem Ihr eine beliebige Anzahl EMPNOs als Argumente übergebt ...
The java class oracle.sql.ARRAY represents user-defined array data type. Since the name of this data type is also user-defined JDBC needs the oracle.sql.ArrayDescriptor to create an instance of the oracle.sql.ARRAY class. The actual contents come from an ordinary java array (here: args). You can test the sample program after compiling as follows: Just provide a random amount of EMPNOs as command line arguments ...
$ java JdbcDynamicInQuery 7844 7839 7822
... und es werden stets alle in der IN-Klausel berücksichtigt. Es wird dabei stets ein- und dieselbe SQL-Abfrage verwendet. Übrigens: Das ist mit PL/SQL und EXECUTE IMMEDIATE genauso nutzbar - hier ist das Erzeugen der Variable sogar noch leichter:
... and you'll see that all of them are considered in the IN condition. But the SQL statement is always the same. BTW: This is also (of course) usable with PL/SQL and EXECUTE IMMEDIATE - the construction of the array variable is even much simpler.
set serveroutput on

declare
  type cursor_t is ref cursor;

  v_array  query_array;
  v_cursor cursor_t;  

  v_ename  SCOTT.EMP.ENAME%TYPE;
begin
  v_array := query_array(7844, 7839);
  open v_cursor for 'select ename from scott.emp where empno in (select column_value from table(:1))' using v_array;
  loop
    fetch v_cursor into v_ename;
    exit when v_cursor%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close v_cursor;
end;
/

4. Dezember 2008

PL/SQL Packages intialisieren

English title: PL/SQL initializing code ...

Wusstet Ihr, dass man in einem PL/SQL-Package auch Initialisierungscode hinterlegen kann; man braucht also nicht unbedingt eine Prozedur oder Funktion "init" - dazu gibt es im Package Body ein vorgesehenes Konstrukt:
Did you know that you can add initializing code to your PL/SQL packages? You don't need a special "init" procedure or function - there is a special language feature in the package body:
create or replace package test_init is
  g_max_salary number;
  function return_global_var return number;
end test_init;
/

create or replace package body test_init is
  function return_global_var return number is
  begin
    return g_max_salary;
  end return_global_var;

  begin
    select max(sal) into g_max_salary from emp;
end test_init;
/
Der rote Code ist der Initialisierungscode; er wird bei der ersten Nutzung des PL/SQL-Paketes aufgerufen. Als Beispiel rufe ich einfach nur die globale Variable ab:
The code is the initialization code; it is being executed on the first usage of the package. The following example just gets the global package variable:
SQL> var v_sal number
SQL> exec :V_SAL := test_init.g_max_salary

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> print

     V_SAL
----------
      5000

Man sieht es recht selten, deshalb war mir gar nicht bewußt, dass das geht ... Man lernt halt nie aus ...
Until I've heard about this a few weeks ago I did not realize that this is possible ... Live and learn ...

24. November 2008

Mehr Sicherheit in Oracle11g: PL/SQL Netzwerk ACL

English title: Oracle11g is more secure: PL/SQL Networking ACLs

Oracle11g wird ja mehr und mehr genutzt. Und eine Erfahrung, die jeder Nutzer von UTL_SMTP, UTL_HTTP und anderen "Netzwerk"-Paketen auf Oracle11g macht ist, dass sie irgendwie nicht mehr funktionieren ... das sieht dann typischerweise so aus:
More and more people are actually working with Oracle11g and everyone who works with the PL/SQL network packages makes the same experience: Calls to UTL_SMTP, UTL_HTTP or the other network related PL/SQL packages don't work anymore - this looks then like the following ...
SQL> select httpuritype('www.oracle.de').getclob() from dual;
ERROR:
ORA-29273: HTTP-Anforderung nicht erfolgreich
ORA-06512: in "SYS.UTL_HTTP", Zeile 1674
ORA-24247: Netzwerkzugriff von Zugriffskontrollliste (ACL) abgelehnt
ORA-06512: in "SYS.HTTPURITYPE", Zeile 34
Am EXECUTE-Privileg liegt es nicht ... das ist vorhanden. Trotzdem gelingt der Netzwerkzugriff nicht. Grund ist ein in Oracle11g neu eingeführtes Sicherheitskonzept: Netzwerkzugriffe, welche durch PL/SQL-Pakete erfolgen, müssen vom DBA separat freigegeben werden.
It's not the missing EXECUTE privilege - this was granted. But the network access is blocked though. The reason is a new security concept for PL/SQL network access in Oracle11g. Such network access needs additional privileges.
Auf den ersten Blick macht das mehr Arbeit - genauer betrachtet ist das meiner Meining nach jedoch eins der besten Sicherheitsfeatures in Oracle11g. Man denke nur an einen Hacker, der durch eine SQL Injection-Lücke in die Datenbank eindringt und feststellt, dass er Zugriff auf das UTL_HTTP-Paket hat ... er kann nun neben der Datenbank auch auf das gesamte Netzwerk (welches von der Datenbank erreicht werden kann) zugreifen. Genau aus diesem Grund existiert auch die Empfehlung, das EXECUTE-Privileg auf diese Netzwerkpakete von PUBLIC zu entfernen (REVOKE EXECUTE on UTL_HTTP from PUBLIC) und es nur den Nutzern zu vergeben, die es wirklich brauchen.
At the first glance this is just more work: but IMHO this is one of the best security features in Oracle11g - ist think about a hacker which gets into your database via a SQL injection vulnerability. As soon as he finds out that he can use UTL_HTTP he gains access not only to the database but also to the network areas the database server is connected to. And exactly this is the reason for the "official" recommendation to revoke exceute privileges on those "network packages" from PUBLIC.
In Oracle11g kann, nein muss der Netzwerkzugriff für die einzelnen Ziele separat und feinganular freigegegeben werden. Und das geht mit dem neuen PL/SQL-Paket DBMS_ACL_ADMIN so:
Recap: In Oracle11g network access privileges can, no must be granted fine-grained. And this is done with the new PL/SQL package DBMS_ACL_ADMIN.
begin
  begin
    dbms_network_acl_admin.drop_acl(
      acl =>         'HTTP_OracleDe.xml'
    );
  exception 
    when others then null; -- ACL does not exist yet
  end;
  -- Privilege to connect to a host
  dbms_network_acl_admin.create_acl(
    acl =>         'HTTP_OracleDe.xml',
    description => 'HTTP-Connects zu www.oracle.de',
    principal =>   'CCZARSKI', -- DB Schema (grantee)
    is_grant =>    true,
    privilege =>   'connect',
    start_date  => null, 
    end_date  =>   null
  );
  -- Privilege to resolve a hostname (DNS lookup)
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl =>         'HTTP_OracleDe.xml',
    principal =>   'CCZARSKI', -- DB Schema (grantee)
    is_grant  =>   true,
    privilege =>   'resolve'
    start_date  => null, 
    end_date  =>   null
  );
  -- Privilege to connect to www.oracle.de
  dbms_network_acl_admin.assign_acl(
    acl =>         'HTTP_OracleDe.xml',
    host =>        'www.oracle.de',
    lower_port =>  80,
    upper_port =>  80
  );
  -- Privilege to connect to www.oracle.com (oracle.de is redirected to oracle.com)
  dbms_network_acl_admin.assign_acl(
    acl =>         'HTTP_OracleDe.xml',
    host =>        'www.oracle.com',
    lower_port =>  80,
    upper_port =>  80
  );
end;
/
    
commit
/
Diese Aufrufe schalten die Server www.oracle.de und www.oracle.com auf Port 80 frei. Wie Ihr an den Aufrufen erkennen könnt (Parameter is_grant, start_date und end_date), können Privilegien für bestimmte Zeiträume vergeben werden und neben "positiven" ACL's können auch "negative" Privilegien vergeben werden; á la "Das ganze Subnet "192.168.10.0", aber nicht den Server "192.168.10.88". Als "grantee" können wie immer auch Rollen und auch PUBLIC eingetragen werden. Und so kann man sich auch ein Skript schreiben, welches den "alten" Oracle10g-Zustand wiederherstellt - wobei ich davon für Produktionssysteme dringend abraten möchte; die ACLs sind ein echter Sicherheitsgewinn und es wäre töricht, sie abzuschalten. Auf einer Entwicklermaschine oder für den Übergang kann das folgende Skript aber ganz hilfreich sein:
The calls grant HTTP access (port 80) to www.oracle.de and www.oracle.com to the database schema or role CCZARSKI. As you can see the parameters is_grant, start_date and end_date allow to restrict network access ("negative" privileges) or to grant a privilege only for a particular timeframe. So a grant might include the whole subnet 192.168.10.0 but not the host 192.168.10.88. Not only database schemas can be used as grantees but also roles and PUBLIC. Having this in mind it's easy to write a script which restores 10g behaviour in 11g: I'd strongly recommand not to run this in a production environment - it makes the system less secure! But for a development server or for a migration period this might be helpful.
begin
  begin
    dbms_network_acl_admin.drop_acl(
      acl =>         'all-network-PUBLIC.xml'
    );
  exception 
    when others then null;  
  end;
  dbms_network_acl_admin.create_acl(
    acl =>         'all-network-PUBLIC.xml',
    description => 'Netzwerk-Connects fuer ALLE',
    principal =>   'PUBLIC',
    is_grant =>    true,
    privilege =>   'connect'
  );
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl =>         'all-network-PUBLIC.xml',
    principal =>   'PUBLIC', 
    is_grant  =>   true,
    privilege =>   'resolve'
  );
  dbms_network_acl_admin.assign_acl(
    acl =>         'all-network-PUBLIC.xml',
    host =>        '*'
  );
end;
/
sho err
    
commit
/
Eine Dictionary View für die ACLs gibt es auch: DBA_NETWORK_ACLS. Ach ja: noch etwas Wichtiges: Wie man an den "Dateinamen" für die ACL erkennen kann, ist hier XML im Spiel. Und tatsächlich: Die ACLs werden als XML in der XMLDB (dem XML DB Repository) gespeichert. Ab Oracle11g gilt also: Wenn man mit den genannten PL/SQL Netzwerk-Paketen arbeiten möchte, muss die XML DB in der Datenbank vorhanden sein.
There is also a dictionary view for the ACLs: DBA_NETWORK_ACLS. And by the way: The ACLs are actually stored in the XML DB Repository - XML DB functionality is being used for the ACL implementation. That means that if you want to use PL/SQL network packages in Oracle11g you must have XML DB enabled in your database.
Mehr Informationen zum Thema findet Ihr in der Oracle-Dokumentation.
More information is contained in the Oracle documentation.

17. November 2008

XML erzeugen in der Datenbank: Das Tutorial wird fortgesetzt

English title: Generating XML from database tables ... cont'd ...

Heute geht es mit dem im vorletzten Posting begonnenen Tutorial zum Thema XML generieren weiter. Versprochen waren ja Themen wie Hierarchien, Zeichensätzen und komplexe XML-Views. Zunächst bauen wie eine Hierarchie ein: Dazu gibt es die Funktion XMLAgg()
This is the second part of the generating XML tutorial which started in the posting a week ago .... Today's topics are XML hierarchys, more complex views and custom XML encodings. First we add a hierarchy; the SQL/XML function XMLAgg() is used for this
select
 xmlserialize(
  content
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
        xmlelement(
         "ename",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement(evalname(e.job)),
         xmlelement("salary", e.sal),
         xmlelement("commission", e.comm),
         XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
        )
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
   indent
 ) as xml
from scott.dept d
/

XML
------------------------------------------------------------------------------
<department>
  <name>ACCOUNTING</name>
  <employee>
    <ename id="7782" hire-date="1981-06-09">
      <name>CLARK</name>
      <MANAGER/>
      <salary>2450</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
  <employee>
    <ename id="7839" hire-date="1981-11-17">
      <name>KING</name>
      <PRESIDENT/>
      <salary>5000</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
  <employee>
    <ename id="7934" hire-date="1982-01-23">
      <name>MILLER</name>
      <CLERK/>
      <salary>1300</salary>
      <commission/>
      <!--Dieses XML-Dokument wurde am 14.11.2008 20:53 generiert.-->
    </ename>
  </employee>
</department>

<department>
  <name>RESEARCH</name>
  <employee>
    <ename id="7369" hire-date="1980-12-17">
:
:
Diese XMLAgg()-Funktion kann beliebig geschachtelt werden, so dass auch komplexere, hierarchische XML-Dokumente möglich werden. Es leuchtet aber sofort ein, dass diese SQL-Abfragen bzw. die View-Definitionen in der Praxis etwas komplizierter werden, da meistens mehr als zwei Tabellen beteiligt sind. Versucht man, das alles in ein- und dieselbe View-Definition zu packen, dürfte man recht schnell die Übersicht verlieren ...
You can nest the XMLAgg() calls as much as you like; more complex XML documents with more hierarchy levels are possible. But it's obvious that the SQL queries get more and more confusing as you include more and more tables. And in real life there are most often more than two tables involved ...
Es empfiehlt sich eine Art "Bausteinkonzept". Zunächst erzeugen wir eine XML-View auf die Angestellten. Das ist wiederum die letzte Abfrage des vorletzten Postings.
The best approach is to create building blocks. First we create a view for just the employees - the SQL query is the last query in the first part of this tutorial.
create or replace view emp_xml as
select
 empno, 
 deptno,
 xmlelement(
  "employee",
  xmlelement(
   "ename",
   XMLAttributes(
    e.empno as "id",
    e.hiredate as "hire-date"
   ),
   xmlelement("name", e.ename),
   xmlelement(evalname(e.job)),
   xmlelement("salary", e.sal),
   xmlelement("commission", e.comm)
  )
 ) xml
from scott.emp e 
/

SQL> desc emp_xml
 Name                                      Null?    Typ
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 DEPTNO                                             NUMBER(2)
 XML                                                XMLTYPE
Die Informationen, die wir zum Join brauchen, werden als normale, relationale Tabellenspalten "neben" das XML gestellt. Außerdem haben wir die Funktion XMLSerialize() wieder entfernt, da das generierte XML im weiteren nochmals (und zwar als XML) benötigt wird. Die Umwandlung in den CLOB (und damit die Formatierung des XML) sollte immer nur zum Schluß erfolgen. Nun die View für die Abteilungen:
The information required for the joins is in normal relational columns besides the XML content. Furthermore the function XMLSerialize() is removed, since the generated XML will be needed by other views (as XML!). The rendering as a CLOB should be the last step in each case. Now the department view follows ...
create or replace view dept_xml as
select
 d.deptno,  
 xmlelement("department",
  xmlelement("name", d.dname),
  (
   select 
    XMLAgg(xml)
   from emp_xml e 
   where e.deptno = d.deptno
  )
 ) xml
from scott.dept d
/

SQL> desc dept_xml
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------
 DEPTNO                                             NUMBER(2)
 XML                                                XMLTYPE
Die Spalte XML dieser View enthält nun auch die Inhalte der View EMP_XML. Nach diesem Konzept können nun "Bausteine" anhand fachlicher Einheiten gebildet und zu komplexeren Objekten zusammengesetzt werden. Und wenn sich an der XML-Struktur für den employee etwas ändert, braucht man nur die View EMP_XML anzupassen - die Änderung wirkt sich wie immer auch auf alle anderen Views, in denen das XML verwendet wird.
The column XML of this view also contains the content of the EMP_XML view. Following this concept "building blocks" can be created based on business objects. And if the XML structure of EMP_XML changes, this is also reflected in all depending views.
Das Rendering des XML ist nun der letzte Schritt: Hier kann wieder die Funktion XMLSerialize() verwendet werden ...
The actual XML rendering is now the last step - and here the function XMLSerialize() might be used ...
select
 xmlserialize(
  document xmlroot(xml, version '1.0') 
 ) as xml_clob
from dept_xml
/

XML_CLOB
---------------------------------------------------------------------------
<?xml version="1.0"?>
<department>
  <name>ACCOUNTING</name>
  <employee>
    <ename id="7782" hire-date="1981-06-09">
      <name>CLARK</name>
      <MANAGER/>
      <salary>2450</salary>
      <commission/>
    </ename>
  </employee>
  <employee>
    <ename id="7839" hire-date="1981-11-17">
      <name>KING</name>
      <PRESIDENT/>
:
:
Nun (als letztes) zu den Zeichensätzen. Zunächst ist festzuhalten, dass XML in der Datenbank immer im Zeichensatz der Datenbank vorliegt. Ist der Datenbank-Zeichensatz also AL32UTF8, so liegt ein XML-Dokument auch stets in diesem Zeichensatz vor (das ist genauso wie bei einem CLOB). Soll das XML nun einen anderen Zeichensatz haben, so kann es nicht mehr als CLOB oder XMLTYPE vorliegen. Es muss in einen BLOB umgewandelt werden. Ein BLOB ist ohne Zeichensatzinformation; hier sind also beliebige Encodings möglich.
Last but not least we come to the XML encodings. The most important prerequisite is that any char datatype (except for NCHAR or NVARCHAR2) is encoded in the database characterset. This also applies to XMLTYPE. When the database has the characterset AL32UTF8 then each XMLTYPE is encoded as Unicode. If another encoding is needed the document has to be rendered as a BLOB.
select
 xmlserialize(
  document xmlroot(xml, version '1.0') as blob encoding 'windows-1252'
 ) as xml_blob
from dept_xml
/

XML_BLOB
--------------------------------------------------------------------------------
3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D2257494E444F57532D3132
3532223F3E0A3C6465706172746D656E743E0A3C6E616D653E4143434F554E54494E473C2F6E616D
653E0A3C656D706C6F7965653E0A3C656E616D652069643D22373738322220686972652D64617465
:
Guckt man sich das dann "richtig" an, so verbirgt sich hinter den Hexcodes folgender Inhalt.
These hexcodes are XML ...
<?xml version="1.0" encoding="WINDOWS-1252"?>
<department>
<name>ACCOUNTING</name>
:
Alternativ zur Funktion XMLSerialize() können auch die XMLTYPE-Methoden getblobval() und getclobval() verwendet werden. XMLSerialize() ist aber der Standard.
As an alternative to XMLSerialize() the XMLTYPE methods getblobval() or getclobval() might be also used. But XMLSerialize() is standard ...
select
  xmlroot(xml, version '1.0').getblobval(nls_charset_id('WE8MSWIN1252')) as xml_blob
from dept_xml
/

10. November 2008

Betriebssystem-Kommandos mit SQL und PL/SQL ausführen: Neue Version verfügbar

New version 0.6 available: Executing OS commands from the database

Letzte Woche hielt ich einen Vortrag über die Möglichkeiten von Java in der Datenbank auf der DOAG SIG Development. Am intensivsten nutze ich Java für das Paket zur Interaktion mit dem Betriebbsystem. Und dieses habe ich jüngst nochmals vervollständigt - die neue Version 0.6 steht nun zum Download bereit:
  • Es kann nun ein working directory für das Betriebssystem-Kommando angegeben werden
  • Es können eigene Umgebungsvariablen gesetzt werden.
  • Im FILE_PKG gibt es nun Methoden zum Abrufen der root-Verzeichnisse (wichtig für Windows) und des sog. path separator Zeichens.
  • Und einige Fehler sind auch behoben - so braucht FILE_TYPE nun keine Java Schreibrechte mehr, wenn eine Datei nur gelesen werden soll.
Last week I had a presentation about java in the database for the development sig of the German Oracle Users Group (DOAG). This is therefore a good time for an update to my PL/SQL package for operating system interaction - the new version 0.6 is now available for download:
  • Support for working directories when executing shell commands
  • Support for custom environment variables
  • Get environment specific information: Root directories and name separator character
  • FILE_TYPE no longer required write privileges when just reading files
Feedback ist natürlich erwünscht ... und das Tutorial "XML Generieren" wird mit dem nächsten Posting fortgesetzt - versprochen!
Feedback is very appreciated ... and the tutorial "generating XML" will be continued with the next posting - this is promised!

5. November 2008

XML erzeugen in der Datenbank: Ein kleines Tutorial

English title: Generating XML in the database - a brief tutorial

Wie man XML aus der Datenbank heraus generieren kann, hatte ich zwar das eine oder andere Mal in einem Blog-Posting behandelt, jedoch niemals so richtig komplett. Daher widme ich dieses und die nächsten Postings den SQL/XML-Funktionen zum Erzeugen von XML.
Generating XML from table data is a pretty frequent requirement. I had written something about this in the past, but these blog postings were not comprehensive. So this (and the next) blog posting is about XML generation - today in a more comprehensive way.
Wir fangen mal ganz einfach an und erzeugen das einfachste denkbare XML-Dokument mit einem leeren Tag.
We start simple and generate an XML document containing just one empty tag.
SQL> select xmlelement("tag") from dual;

XMLELEMENT("TAG")
--------------------------------------------------
<tag></tag>
Die SQL/XML-Funktionen sind SQL-Funktionen (übrigens Teil von SQL:2003), mit denen aus einer Abfrage heraus beliebig XML generiert werden kann. Für die diversen Elementtypen, die in XML möglich sind, gibt es diverse Funktionen:
Funktion:Verfügbar ab:Zweck:
XMLElement()9.2.0Erzeugt ein XML-Tag
XMLAttributes()9.2.0Erzeugt ein oder mehrere XML-Attribute innerhalb eines Tags
XMLForest()9.2.0Erzeugt mehrere XML-Tags auf einmal
XMLCDATA()10.2.0Erzeugt eine sog. CDATA-Section
XMLComment()10.2.0Erzeugt einen XML-Kommentar
XMLPI()10.2.0Erzeugt eine sog. Processing Instruction
XMLAgg()10.2.0Fasst mehrere XML-Tags zusammen und erzeugt eine Hierarchiestufe
XMLRoot()10.2.0Erzeugt den sog. XML-Prolog
The SQL/XML functions are part of the SQL:2003 standard and allow to construct XML from ordinary SQL queries. For the various components of the XML data model exist different functions:
Function:Availibility:Purpose:
XMLElement()9.2.0Produces a XML-Tag
XMLAttributes()9.2.0Generates one or more XML-Attributes inside a tag
XMLForest()9.2.0Generates multiple tags at once
XMLCDATA()10.2.0Generates a CDATA Section
XMLComment()10.2.0Produces a XML comment
XMLPI()10.2.0Generates a XML processing instruction
XMLAgg()10.2.0Aggregates multiple XML tags to a new hierarchy level
XMLRoot()10.2.0Generates the XML-Prolog
Die Funktionen geben stets XML zurück - aus Sicht der Datenbank ist das dann ein XMLTYPE. Nun einige Beispiele:
The functions return XML - so the returning datatype is XMLTYPE
1. Ein Tag mit Inhalt:
1. Producing a XML tag with contents:
SQL> select xmlelement("tag", 'Inhalt') from dual;

XMLELEMENT("TAG",'INHALT')
-------------------------------------------------------------
<tag>Inhalt</tag>
2. Die Inhalte werden aus einer Tabelle selektiert:
2. The tag content is being selected from a table:
SQL> select xmlelement("ename", ename) from scott.emp;

XMLELEMENT("ENAME",ENAME)
-------------------------------------------------------------
<ename>SMITH</ename>
<ename>ALLEN</ename>
<ename>WARD</ename>
<ename>JONES</ename>
:

14 rows selected.
3. Wir nehmen XML-Attribute hinzu:
3. Add XML attributes:
select 
 xmlelement(
  "ename", 
  XMLAttributes(
   empno as "id", 
   hiredate as "hire-date"
  ),
  ename
) from scott.emp
/

<ename id="7369" hire-date="1980-12-17">SMITH</ename>
<ename id="7499" hire-date="1981-02-20">ALLEN</ename>
<ename id="7521" hire-date="1981-02-22">WARD</ename>
:
4. Wir schachteln XML-Tags ineinander:
4. Nesting XML tags:
select 
 xmlelement(
  "employee",
  xmlelement(
   "name", 
   XMLAttributes(
    empno as "id", 
    hiredate as "hire-date"
   ),
   ename
  )
) from scott.emp
/

<employee><name id="7369" hire-date="1980-12-17">SMITH</name></employee>
<employee><name id="7499" hire-date="1981-02-20">ALLEN</name></employee>
<employee><name id="7521" hire-date="1981-02-22">WARD</name></employee>
:
Einschub: Kann man das ganze auch eingerückt darstellen (pretty print)? Ja, das geht - aber erst ab Oracle11g (11.1.0). Aber Vorsicht: Der zurückgegebene Datentyp ist nun CLOB - XMLSerialize() dient dazu, das XML in einen Text umzuwandeln, wird also genutzt, bevor das generierte XML die Datenbank "verlässt".
BTW: You might want to have pretty printed XML (with indents). This is possible with 11g (11.1.0). The returning data type changes to CLOB when you use XMLSerialize(). This function should be used when the generated XML has to "leave" the database as text.
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     ename
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">SMITH</ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">ALLEN</ename>
</employee>

<employee>
  <ename id="7521" hire-date="1981-02-22">WARD</ename>
</employee>
Technisch hat die Einrückung keine Bedeutung - sie dient lediglich dazu, das XML für den Menschen besser lesbar zu machen. Meistens ist jedoch eine Anwendung oder ein Prozeß der Konsument für das XML - und dieser benötigt kein Pretty Print. Im folgenden sind alle SQL-Abfragen mit Pretty-Printing angegeben; auf einer 10g-Umgebung einfach die XMLSerialize()-Funktion zu Beginn und das INDENT zum Ende entfernen
Pretty printing is just for better "human readability" of the XML document. A pretty printed XML document has the same semantics as a non pretty printed one. So if the XML is being consumed by another application pretty printing is not necessary. The following queries are shown with the pretty printing clause; for a 10g environment just remove the XMLSerialize() function (don't forget the INDENT keyword at the end).
5. Der Inhalt des Tags soll als sog. CDATA-Section generiert werden; das wird vor allem dann genommen, wenn die Texte länger sind und viele XML-Zeichen (<, >, &) beinhalten:
5. Now we want to generate a tag content as CDATA section; this is useful for longer texts with many "special XML characters" (<, >, &):
select
 xmlserialize(
  content
   xmlelement(
    "employee",
    xmlelement(
     "ename",
     XMLAttributes(
      empno as "id",
      hiredate as "hire-date"
     ),
     xmlcdata(ename)
    )
   )
  indent
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17"><![CDATA[SMITH]]></ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20"><![CDATA[ALLEN]]></ename>
</employee>
6a. Mehrere Tags auf einmal erzeugen (mit XMLForest()):
6a. Generate multiple XML tags at one (with XMLForest()):
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlforest(
      ename as "name",
      job as "job",
      sal as "salary",
      comm as "commission"
     )
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <job>SALESMAN</job>
    <salary>1600</salary>
    <commission>300</commission>
  </ename>
</employee>
Man sieht, dass das Tag commission (welches aus der Spalte COMM gespeist wird), mal vorhanden ist und mal nicht. XMLForest() erzeugt ein Tag, wenn Inhalte da sind, wenn nicht, wird keins erzeugt.
The tag commission (which contains the value of the table column COMMN) is present for some XML documents and for not for others. XMLForest() does not generate a tag if the column value is NULL.
6b. Mehrere Tags auf einmal erzeugen (jeweils explizit mit XMLElement()):
6b. Now we do the same with XMLElement():
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement("job", job),
     xmlelement("salary", sal),
     xmlelement("commission", comm)
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
    <commission/>
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <job>SALESMAN</job>
    <salary>1600</salary>
    <commission>300</commission>
  </ename>
</employee>
Und ist das Tag commission immer vorhanden - es wurde ja auch explizit verlangt. Wenn die Tabellenspalte den Inhalt NULL hat, bleibt das XML-Tag leer.
The tag commission is now present in each XML document. For NULL values the tag is empty.
7. Wir fügen einen Kommentar ein:
7. Generating a comment:
select 
 xmlserialize(
  content 
   xmlelement(
    "employee",
    xmlelement(
     "ename", 
     XMLAttributes(
      empno as "id", 
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement("job", job),
     xmlelement("salary", sal),
     xmlelement("commission", comm),
     XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
    )
   ) 
  indent 
 ) as xml
from scott.emp
/

<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <job>CLERK</job>
    <salary>800</salary>
    <commission/>
    <!--Dieses XML-Dokument wurde am 02.11.2008 19:18 generiert.-->
  </ename>
</employee>
8. Nun soll der Inhalt der Tabellenspalte den Namen des XML-Tags bestimmen (dies ist ab Version 10.2.0.3 möglich):
8. Now the column value should determine the XML tag's name (this is available with 10.2.0.3 and higher):
select
 xmlserialize(
  content
   xmlelement(
    "employee",
    xmlelement(
     "ename",
     XMLAttributes(
      empno as "id",
      hiredate as "hire-date"
     ),
     xmlelement("name", ename),
     xmlelement(evalname(job)),
     xmlelement("salary", sal),
     xmlelement("commission", comm),
     XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
    )
   )
  indent
 ) as xml
from scott.emp
/

XML
----------------------------------------------------------------------
<employee>
  <ename id="7369" hire-date="1980-12-17">
    <name>SMITH</name>
    <CLERK/>
    <salary>800</salary>
    <commission/>
    <!--Dieses XML-Dokument wurde am 03.11.2008 11:18 generiert.-->
  </ename>
</employee>

<employee>
  <ename id="7499" hire-date="1981-02-20">
    <name>ALLEN</name>
    <SALESMAN/>
    <salary>1600</salary>
    <commission>300</commission>
    <!--Dieses XML-Dokument wurde am 03.11.2008 11:18 generiert.-->
  </ename>
</employee>

:
Für heute soll's das sein. Beim nächsten Mal geht's dann mit Themen wie XML-Hierarchien, komplexen Views oder XML-Zeichensätzen (Encoding) weiter ...
Okay ... this should be enough for today ... We'll continue in the next blog posting with advanced topics like hierarchies, complex Views or how to produce an XML encoding clause ...

31. Oktober 2008

Einzelne Cursor invalidieren mit DBMS_SHARED_POOL

English title: Purging Cursors from the shared pool with DBMS_SHARED_POOL

Die Tage bekam ich die Frage, ob und wenn ja, wie man denn einen Cursor im Shared Pool invalidieren könne. Es wurde also eine SQL-Abfrage in der Datenbank ausgeführt - diese wurde (natürlich) geparst, ein Ausführungsplan wurde erstellt und diese Daten sind nun im Shared Pool gecacht. Wenn die gleiche SQL-Abfrage nun nochmals ankommt, wird der gecache Ausführungsplan wiederverwendet. Im Zusammenhang mit Bind Variablen hatten wir das Thema auch schonmal ...
The last days I was asked the question how to invalidate one particular cursor in the shared pool. So there was a SQL query executed, it was (of course) parsed, an execution plan was generated and all this is now cached in the shared pool. When this query comes again the cached information is being used. There was a posting about this regarding the discussion of bind variables.
Nun tritt ab und zu die Situation auf (Tuning-Maßnahmen), dass man diesen Cursor eben nicht nutzen möchte. Obwohl die SQL-Abfrage "schon da" ist, möchte man, dass sie neu geparst, also ein neuer Ausführungsplan erstellt wird. Der erste (und einfachste Ansatz) wäre, einen SQL-Kommentar in die Abfrage einzubauen - dann ist es ein neuer SQL-Text und die Abfrage passt nicht mehr zur gecachten - sie ist dann tatsächlich "neu".
Sometimes there is the situation where you just don't want the database to use that cache. Now - the easiest way is then to add some comment to the SQL query - the SQL text changes, so you have a new query.
Und wenn man an die Abfrage nicht herankommt? Tja ... dann könnte man ...
  • ... den Shared Pool komplett leeren (alter system flush shared_pool) ...
    ... was aber alle Inhalte löscht, also auch den Buffer Cache.
  • ... die Tabelle ändern (bspw. einen Kommentar hinzufügen) ...
    ... würde aber alle Cursor invalidieren, die mit der Tabelle arbeiten.
But you can't always change the query; if you don't have access to the applications' code you can't change any SQL ... So you might ...
  • ... flush the shared pool completely (alter session flush shared_pool)...
    ... but this elimiates all cached information - including the buffer cache.
  • ... change the table definition (add a table comment) - this invalidates the cursor ...
    ... but it invalidates all other cursors selecting this table also.
Eigentlich soll ja nur der einzelne Cursor aus der SGA elimiert werden. Und das geht in Oracle11g (und lt. Metalink Note 457309.1 auch in 10.2.0.4) mit dem PL/SQL-Paket DBMS_SHARED_POOL. Getestet habe ich bislang nur auf Oracle11g.
The goal is just to eliminate one particular cursor. And in Oracle11g this is possible with DBMS_SHARED_POOL (Metalink note 457309.1 states that it is also possible with 10.2.0.4 but I tested only on 11g).
Wenn DBMS_SHARED_POOL nicht vorhanden ist, muss es mit dem Skript $ORACLE_HOME/rdbms/admin/dbmspool.sql eingespielt werden.
If DBMS_SHARED_POOL is not present in your database you have to run the script $ORACLE_HOME/rdbms/admin/dbmspool.sql.
Zuerst also ein SQL absetzen - mit dem Kommentar finden wir es später leichter wieder ...
First we issue a SQL query ... the comment makes it easier to find again ...
SQL> select /* SQL 1 !!!*/ sal from scott.emp where empno=7839;

       SAL
----------
      5000
Dann den Cursor in der View V$SQLAREA heraussuchen ...
Then look it up in V$SQLAREA...
SQL> select address, hash_value, executions, invalidations, parse_calls, sql_text from v$sqlarea where sql_text like 'select /* SQL 1 !!!*/%';

ADDRESS  HASH_VALUE EXECUTIONS INVALIDATIONS PARSE_CALLS SQL_TEXT
-------- ---------- ---------- ------------- ----------- --------------------------------------------------
4158E358 2329752635          1             0           1 select /* SQL 1 !!!*/ sal from scott.emp where
                                                         empno=7839
Nun DBMS_SHARED_POOL.PURGE aufrufen ... Ein Cursor wird durch seine ADDRESS und HASH_VALUE identifiziert.
Now we call DBMS_SHARED_POOL.PURGE - to purge a cursor we identify it by concatenating its ADDRESS and HASH_VALUE.
begin 
  dbms_shared_pool.purge('4158E358 2329752635', 'C');
end;
/

PL/SQL procedure successfully completed.
Nochmal in der V$SQLAREA nachgucken ...
Check in V$SQLAREA...
SQL>  select address, hash_value, executions, invalidations, parse_calls, sql_text from v$sqlarea where sql_text like 'select /* SQL 1 !!!*/%';

no rows selected
Der Cursor ist also weg ... führen wir das Original-SQL nochmals aus, dann wird es neu geparst.
It's gone. We now want to check further and execute the original query again.
SQL> select /* SQL 1 !!!*/ sal from scott.emp where empno=7839;

       SAL
----------
      5000
Und ein drittes Mal in der V$SQLAREA nachgucken ...
And look into V$SQLAREA - the third time.
SQL> select address, hash_value, executions, invalidations, parse_calls, sql_text from v$sqlarea where sql_text like 'select /* SQL 1 !!!*/%';

ADDRESS  HASH_VALUE EXECUTIONS INVALIDATIONS PARSE_CALLS SQL_TEXT
-------- ---------- ---------- ------------- ----------- --------------------------------------------------
4158E358 2329752635          1             1           1 select /* SQL 1 !!!*/ sal from scott.emp where
                                                         empno=7839
Wir haben nun eine Invalidation. Mit DBMS_SHARED_POOL kann man aber noch mehr machen. Die SIZES-Prozedur zeigt bspw. an, welche Objekte sich im Shared Pool befinden. Der folgende Aufruf listet alle Objekte, die mehr als 500 Kilobyte im Shared Pool belegen.
Now we have one invalidation - the SQL query was parsed like a new query - the cursor object was infact purged from the shared pool. But DBMS_SHARED_POOL can do more: the SIZES procedure shows the objects currently residing in the shared pool. The following call lists all objects greater than 500 kilobytes.
SQL> set serveroutput on size 2000000
SQL> exec dbms_shared_pool.sizes(500);
SIZE(K) KEPT   NAME
------- ------ ----------------------------------------------
2870 YES    XDB.XDNsht+pGJQ9jgQESYGWQVVg==(XDB)
1717 YES    XDB.XDbD/PLZ01TcHgNAgAIIegtw==(XDB)
1274        XDB.XDNsht+plHQ9jgQESYGWQVVg==(XDB)
1210        XDB.XDNsht+o5NQ9jgQESYGWQVVg==(XDB)
835        XDB.XDNsht+o3tQ9jgQESYGWQVVg==(XDB)
650 YES    SYS.oracle/i18n/text/OraMapTable(JAVA CLASS)
531 YES    SYSMAN.MGMT_JOB_ENGINE        (PACKAGE BODY)

PL/SQL-Prozedur erfolgreich abgeschlossen.
Die Ausgabe via DBMS_OUTPUT ist zwar etwas unübersichtlich (eine Table Function wäre mir lieber gewesen) ... aber immerhin ...
The output via DBMS_OUTPUT is a little bit cumbersome (a table function would fit better, IMHO) - but its better than nothing ...

23. Oktober 2008

Aktuelle Session Tracedatei ansehen: Mit SQL und PL/SQL

English title: Viewing Tracefiles with SQL and PL/SQL

Man kommt als Entwickler des öfteren in die Situation, sich eine Tracedatei ansehen zu müssen ... zwei Beispiele:
  • Wenn man einen SQL Trace aktiviert, um ein SQL-Kommando zu analysieren, werden die Informationen in die Tracedatei geschrieben
  • Wenn man eine Java Stored Procedure laufen lässt und diese eine Exception auslöst, landet der Java-Fehler-Stack im Tracefile
Und in diesen Fällen bedeutet das, dass man die Datenbankumgebung verlassen, sich an der Datenbankmaschine anmelden, die Tracedatei suchen und ggfs. mit tkprof aufbereiten muss - erst dann kann man sich die Inhalte ansehen. Das ist zumindest mal aufwändig und manchmal hat man auch gar keine Login-Daten für das Betriebssystem der Datenbankmaschine.
Sometimes there are situations where developers have to look into the database's tracefiles. Here are two examples ...
  • If you activate a SQL trace to analyze a particular SQL command then this information is being written into a tracefile
  • If a java stored procedure (java in the database) throws an exception the java error stack is by default written to a tracefile
... and this means that you have to log into the operating system of the database machine, change to the tracefile directory, lookup the file, process it with tkprof (when necessary) and finally view the results. This costs at least time and in some cases you don't even have credentials for the operating system.
In Oracle11g gibt es eine nette, sehr hilfreiche Kleinigkeit: die View V$DIAG_INFO. Diese gibt den Namen des für die aktuelle Session gültigen Tracefiles wie folgt heraus:
In Oracle11g there's a nice very helpful new view: V$DIAG_INFO shows the name of the current sessions' tracefile as follows:
SQL> select value from v$diag_info where name='Default Trace File'
  2  /

VALUE
--------------------------------------------------------------------------------
/oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6328.trc
Damit entfällt das Suchen auf dem Server - das Tracefile-Verzeichnis kann durchaus mal viele Dateien enthalten. Man kann die View aber auch ganz anders nutzen ...
Ok - with this you don't have to search for the tracefile - you already know its name ... but this information can furthermore be used to simplify the whole process.
Ich nehme dazu mal wieder das Package zum Ausführen von Betriebssystem-Kommandos zur Hilfe - damit kann man die Datei direkt auslesen und sogar das tkprof-Utility direkt aus der Datenbank starten. Das folgende PL/SQL-Paket TRACE_HELPER macht genau das. Das Skript kann nur in eine 11g-Datenbank eingespielt werden, da es V$DIAG_INFO erst in 11g gibt. Für frühere Versionen muss ich noch ein wenig Code zum Ermitteln der aktuellen Tracedatei zusammenstellen - das werde ich dann in einem späteren Posting veröffentlichen.
I'll -again- take the package for operating system interaction in order to create a PL/SQL package which does all the stuff automatically. This package TRACE_HELPER does only run in an Oracle11g database - previous versions don't have the V$DIAG_INFO view). To run in previous versions I have to write some code which determines the current session's tracefile ... this will be posted here when finished.
create or replace package trace_helper is
  function get_session_trace_file return clob;
  function get_session_tkprof_trace(
    p_recursive_sql  in number default 1,
    p_explain        in varchar2 default null,
    p_sort           in varchar2 default null
  ) return clob;
  procedure set_output_tempfile_prefix(p_prefix in varchar2);
end trace_helper;
/
sho err

create or replace package body trace_helper is
  C_TKPROF_COMMAND constant varchar2(200) := '/oracle/u01/app/oracle/product/11.1.0/bin/tkprof';
  C_OUTFILE_PREFIX constant varchar2(200) := '/tmp/tkprof_out';

  g_outfile_prefix varchar2(200) := C_OUTFILE_PREFIX;

  procedure set_output_tempfile_prefix(p_prefix in varchar2) is
  begin
    g_outfile_prefix := p_prefix;
  end set_output_tempfile_prefix;
  function get_trc_file_name return varchar2 is 
    v_filename varchar2(32767);
  begin
    SELECT value into v_filename FROM v$diag_info WHERE name = 'Default Trace File';
    return v_filename;
  end get_trc_file_name;

  function get_session_trace_file return clob is
  begin
    return file_pkg.get_file(get_trc_file_name).get_content_as_clob('iso-8859-1');
  end get_session_trace_file;

  function get_session_tkprof_trace(
    p_recursive_sql  in number default 1,
    p_explain        in varchar2 default null,
    p_sort           in varchar2 default null
  ) return clob is
    v_tkprof_command varchar2(32767) := C_TKPROF_COMMAND;
    v_tkprof_success number;
    v_tkprof_content clob;

    v_output_file    file_type;
  begin
    v_output_file := file_pkg.get_file(
      g_outfile_prefix || substr(get_trc_file_name, instr(get_trc_file_name, '/', -1) + 1)
    );
    v_tkprof_command := v_tkprof_command || ' ' || get_trc_file_name || ' ' || v_output_file.file_path;

    if p_explain is not null then
      v_tkprof_command := v_tkprof_command || ' explain=' || p_explain;
    end if;
    if p_recursive_sql = 0 then 
      v_tkprof_command := v_tkprof_command || ' sys=no';
    end if;
    if p_sort is not null then
      v_tkprof_command := v_tkprof_command || ' sort=' || p_sort;
    end if;  

    v_tkprof_success := os_command.exec(v_tkprof_command);
    v_tkprof_content := v_output_file.get_content_as_clob('iso-8859-1');
    v_output_file := v_output_file.delete_file();

    return v_tkprof_content;
  end get_session_tkprof_trace; 
end trace_helper;
/
sho err
Das entstandene Package sieht so aus ...
The resulting package looks like this:
FUNCTION GET_SESSION_TKPROF_TRACE RETURNS CLOB
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_RECURSIVE_SQL                NUMBER                  IN     DEFAULT
 P_EXPLAIN                      VARCHAR2                IN     DEFAULT
 P_SORT                         VARCHAR2                IN     DEFAULT
FUNCTION GET_SESSION_TRACE_FILE RETURNS CLOB
PROCEDURE SET_OUTPUT_TEMPFILE_PREFIX
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_PREFIX                       VARCHAR2                IN
ACHTUNG: Die Zeile mit dem Pfad zum tkprof Executable müsst Ihr an euere Umgebung anpassen ...
Attention: The line containing the path to the tkprof executable must be adjusted to your environment before using the package.
  C_TKPROF_COMMAND constant varchar2(200) := '/oracle/u01/app/oracle/product/11.1.0/bin/tkprof';
  • Die Funktion GET_SESSION_TRACE_FILE liest das Tracefile der aktuellen Session aus und gibt es als CLOB zurück. Das ist bspw. bei Java in der Datenbank hilfreich, wenn eine Exception ausgelöst wurde und die Details im Tracefile stehen ...
  • Die Function GET_SESSION_TKPROF_TRACE gibt eine mit dem tkprof-Werkzeug aufbereitete Version des Tracefile als CLOB zurück. Während dieses Prozesses wird von eine temporäre Datei (diese nimmt den aufbereiteten Text auf) erzeugt, deren Inhalte werden in einen CLOB kopiert und dieser dann zurückgegeben. Schließlich wird die temporäre Datei gelöscht. Das Verzeichnis, in welches diese temporäre Datei abgelegt wird, könnt Ihr mit der Konstante C_OUTFILE_PREFIX oder mit der Prozedur SET_OUTPUT_TEMPFILE_PREFIX beeinflussen.
  • Die Prozedur SET_OUTPUT_TEMPFILE_PREFIX legt den Pfad und das Namens-Präfix für die eben erwähnte temporäre Datei fest.
Damit das ganze funktioniert, braucht euer Datenbankschema nun noch einige Privilegien ... das folgende Skript müsst Ihr als DBA ausführen und den TRCTEST durch euren DB User ersetzen.
  • The function GET_SESSION_TRACE_FILE reads just the tracefile content and returns it as a CLOB. This is helpful when java stored procedures throw exceptions - you then see the java error stack in the returning CLOB.
  • The function GET_SESSION_TKPROF_TRACE executes tkprof on the session's tracefile and returns the tkprof output as a CLOB. During this process a temporary file with the tkprof output is being created, its contents are then being copied into a CLOB and finally it's being deleted. The directory into which this temporary file is placed, is determined by the PL/SQL constant C_OUTFILE_PREFIX - so you might want to adjust this according to your environment. The directory and the filename prefix can also be adjusted by the procedure SET_OUTPUT_TEMPFILE_PREFIX.
  • The procedure SET_OUTPUT_TEMPFILE_PREFIX is used to set the directory and filename prefix for the temporary file generated by tkprof.
Your database schema needs some privileges in order to use the package. Just run the following script as the SYS user and change the TRCTEST user to the database user you're working with.
 
-- execute privilege for the "tkprof" utility
begin
  dbms_java.grant_permission( 
    'TRCTEST',
    'SYS:java.io.FilePermission',
    '/oracle/u01/app/oracle/product/11.1.0/bin/tkprof', 
    'execute' 
  );
end;
/

-- read privilege for the tracefile directory
declare
  v_diag_dir varchar2(4000);
begin
  select value into v_diag_dir 
  from v$diag_info where name = 'Diag Trace';
 
  dbms_java.grant_permission( 
    'TRCTEST',
    'SYS:java.io.FilePermission',
    v_diag_dir || '/-', 
    'read' 
  );
end;
/

-- read and write privileges for a temporary directory
-- the temporary files for the tkprof output are placed here
begin
  dbms_java.grant_permission( 
    'TRCTEST',
    'SYS:java.io.FilePermission',
    '/tmp/-',
    'read,write' 
  );
end;
/

-- this grants write permission on STDIN
begin
   dbms_java.grant_permission(
     grantee =>           'TRCTEST',
     permission_type =>   'SYS:java.lang.RuntimePermission',
     permission_name =>   'writeFileDescriptor',
     permission_action => null
   );
end;
/

-- this grants read permission on STDOUT
begin
   dbms_java.grant_permission(
     grantee =>           'TRCTEST',
     permission_type =>   'SYS:java.lang.RuntimePermission',
     permission_name =>   'readFileDescriptor',
     permission_action => null
   );
end;
/
Fertig. Testen ...
That's it ... here's a test ...
SQL> alter session set sql_trace=true;

Session altered.

SQL> select ... from ...;

:
:

SQL> select trace_helper.get_session_tkprof_trace from dual;

GET_SESSION_TKPROF_TRACE
--------------------------------------------------------------------------------

TKPROF: Release 11.1.0.7.0 - Production on Wed Oct 22 02:36:46 2008

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

Trace file: /oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6380.trc
Sort options: default

********************************************************************************

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers f ...
:
Ihr bekommt die Infos also direkt im SQL*Plus oder dem Werkzeug, mit dem Ihr gerade arbeitet - ein Wechseln der Umgebung ist nicht mehr nötig ...
You can now see the tracefile information within SQL*Plus (or your database development tool) - changing the environment is no longer necessary.

Beliebte Postings