15. Dezember 2010

SQL Queries "magisch" umschreiben: DBMS_ADVANCED_REWRITE

The "magic" rewrite of SQL Queries DBMS_ADVANCED_REWRITE
Heute geht es um ein etwas ausgefalleneres Feature der Oracle-Datenbank: Ihr könnt SQL-Abfragen automatisch umschreiben lassen. Die technische Basis dafür sind Materialized Views - diese sind in Oracle nicht nur einfache eine Tabelle mit den Inhalten der (eben materialisierten) View. Dazu gehört auch das Query Rewrite - ein komplexes SQL wird vom Optimizer automatisch hin zu einem einfachen Select auf die Materialized View umgeschrieben - sofern alle Voraussetzungen gegeben sind.
In this blog posting I'll write about a more exotic feature of the Oracle database. You can let the kernel rewrite SQL queries for you. This is based on the materialized view technology: When you have a materialized view for a specific (complex) SQL query and the optimizer "knows" about this, it will rewrite your query to use the materialized view instead of the original tables. In practice, it's not that easy - there are some requirements which the materialized views must meet. But that is another topic - I'll pick a particular feature here: the DBMS_ADVANCED_REWRITE (Documentation) package to create your own rewrites - even without materialized views.
Zusätzlich gibt es jedoch "für den fortgeschrittenen" Nutzer das PL/SQL-Paket DBMS_ADVANCED_REWRITE (Dokumentation) im Schema SYS. Damit können zwei SQL-Abfragen als gleich deklariert werden. Standardmäßig hat kein Datenbankuser Privilegien auf diesem Package - auch das Public Synonym fehlt. Also zuerst die Privilegien einräumen - neben dem EXECUTE-Privileg auf das Package wird auch das CREATE MATERIALIZED VIEW-Privileg benötigt ...
With this package you can instruct the optimizer to rewrite one SQL query to another. By default no database user can execute this package - and even the public synonym is missing. So in order to play with it you first need to grant the EXECUTE privilege to your database user (here: SCOTT). In addition to this, SCOTT needs the CREATE MATERIALIZED VIEW privilege.
grant execute on sys.dbms_advanced_rewrite to scott
/

grant create materialized view to scott
/
Nun könnt Ihr eine Query Rewrite Equivalence deklarieren. Das könnte zum Beispiel so aussehen (bitte fragt nicht nach dem Sinn der Beispiele) ...
Now you can create your first Query Rewrite Equivalence. An example might look like this (don't ask about the sense for these examples) ...
begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
    name             => 'MEIN_TEST',
    source_stmt      => 'select * from scott.emp',
    destination_stmt => 'select empno, '||
                        '       ename, '||
                        '       to_char(null) as job, ' ||
                        '       to_number(null) as mgr, ' ||
                        '       to_date(null) as hiredate, ' ||
                        '       to_number(null) as sal, '||
                        '       to_number(null) as comm, '||
                        '       to_number(null) as deptno '||
                        'from scott.emp',
    validate         => false,
    rewrite_mode     => 'TEXT_MATCH'
  );
end;
/
Die Parameter SOURCE_STMT und DESTINATION_STMT legen fest, welche SQL-Abfrage durch welche ersetzt werden soll. Der Parameter VALIDATE wird hier auf false gesetzt; Oracle soll also nicht prüfen, ob diese beiden Abfragen wirklich das gleiche Ergebnis zurückliefern. Würde man VALIDATE auf TRUE setzen, gäbe es eine Fehlermeldung, denn man sieht sofort, dass diese Abfragen nicht identisch sind. Die Anzahl der Ergebnisspalten und deren Datentypen müssen aber immer identisch sein. Der letzte Parameter legt fest, in welchen Fällen dieses Rewrite genutzt werden soll - hier nur dann, wenn die SQL-Abfrage exakt so wie deklariert ausgeführt wird (TEXT_MATCH). Die auf diese Art und Weise deklarierten Equivalences genießen beim Optimizer höchste Priorität - er geht davon aus, dass der Nutzer hier weiss, was er tut! Probieren wir es aus.
The parameters SOURCE_STMT and DESTINATION_STMT determine which SQL query is being rewritten to which other SQL statement. This is a declaration to the Oracle optimizer that those two SQL queries are equivalent and that the DESTINATION_STMT is more favorable. If the VALIDATE argument is set to TRUE the database will check whether the two SQL queries really return the same results. If set to false, the database will not perform that check. The last argument REWRITE_MODE determines when to use the query equivalence. The example here (TEXT_MATCH) requires the query text to be identical to the declaration. The equivalences declared with DBMS_ADVANCED_REWRITE have high priority - the optimizer will use them even if it considers the original query as more effective. Let's test the declaration.
SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.80   800           20
 7499 ALLEN      SALESMAN   7698 20.02.81  1600   300     30
 7521 WARD       SALESMAN   7698 22.02.81  1250   500     30
 7566 JONES      MANAGER    7839 02.04.81  2975           20
 7654 MARTIN     SALESMAN   7698 28.09.81  1250  1400     30
 7698 BLAKE      MANAGER    7839 01.05.81  2850           30
 7782 CLARK      MANAGER    7839 09.06.81  2450           10
 :    :          :             : :            :     :      :
Scheint nicht zu funktionieren ... der Grund ist der Session-Parameter QUERY_REWRITE_INTEGRITY, der defaultmäßig auf ENFORCED steht. Das bedeutet, die Datenbank führt generell nur solche Rewrites aus, die von ihr selbst als gleichwertig angesehen werden - und das sind die hier deklarierten nicht. Man muss der Datenbank nochmals sagen, dass sie auch solche Rewrites ausführen soll - auch wenn dadurch andere oder veraltete Daten zurückgegeben werden.
That seems not to work - we have forgotten to set the session parameter QUERY_REWRITE_INTEGRITY, which is set to ENFORCED by default. That means the database uses query rewrite only if the optimizer is sure that the same data is being returned. The parameter must be set to TRUSTED or STALE_TOLERATED in order to use the declared equivalences. STALE_TOLERATED means that query rewrite even may return stale or incorrect data.
SQL> alter session set query_rewrite_integrity = 'STALE_TOLERATED';

Session altered.

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7369 SMITH      
 7499 ALLEN      
 7521 WARD       
 7566 JONES      
 :    :          :             : :            :     :      :
Man sieht, dass die Abfrage intern umgeschrieben wurde. An der Tabelle wurde nix geändert - es ist auch keine View vorhanden. Man sieht schon, dass das ein sehr mächtiges Feature ist, mit dem man auch Kollegen in den Wahnsinn treiben kann. Daher sollte das natürlich vorsichtig eingesetzt werden. Den hier gezeigten Effekt kann man (wenn man das braucht) besser mit Views oder gar der Virtual Private Database realisieren.
You see that the query has been rewritten internally. The table has not been changed; there is also no view involved. It's obvious that this is a very powerful feature which can also lead to a lot of confusion - so use it with care. For the above example, Virtual Private Database or a View would, of course, have been the better choice.
Wenn es um Performance geht, hat das Feature aber absolut seine Berechtigung. Auch ohne den mitunter recht komplexen Setup von Materialized Views kann eine teure SQL-Abfrage auf einen einfachen Tabellen-SELECT umgeschrieben werden - selbst wenn die Tabelle gar keine Materialized View ist.
But DBMS_ADVANCED_REWRITE might be an approach to tackle performance problems. Think about a complex SQL query (Joins, Computations) which you like to have precomputed, but without having all the setup required for materialized views. The following example does a query rewrite from a query with a join to a simple select on a single table.
drop table empjoindept
/

create table empjoindept as 
  select e.empno, e.ename, e.sal, d.dname, d.loc 
  from emp e join dept d on (e.deptno = d.deptno)
/

begin
  sys.dbms_advanced_rewrite.drop_rewrite_equivalence(
    name => 'MEIN_TEST'
  );
end;
/

begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
    name             => 'MEIN_TEST',
    source_stmt      => 'select e.empno, e.ename, e.sal, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno',
    destination_stmt => 'select * from empjoindept',
    validate         => true,
    rewrite_mode     => 'GENERAL'
  );
end;
/
Führt man nun diesen SELECT (mit Join) aus, wird tatsächlich die Tabelle EMPJOINDEPT selektiert ...
Executing the query (with join) results in a single-table-select, as the execution plan indicates.
Ausführungsplan
----------------------------------------------------------
Plan hash value: 3055423993

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |    14 |   700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPJOINDEPT |    14 |   700 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Es gibt allerdings noch ein paar Einschränkungen ...
  • Bindevariablen werden nicht unterstützt. Man kann also kein generisches Rewrite deklarieren.
  • Ausdrücke wie SYSDATE, ROWNUM, USER und andere können ebenfalls nicht genutzt werden
  • Wenn die gleiche Session eine Änderung gemacht und das COMMIT noch nicht erfolgt ist, ziehen die Rewrites nicht.
  • Ein Join sollte beim Deklarieren des Rewrite mit der "alten" Join-Syntax gemacht werden; beim tatsächlichen Abfragen funktioniert auch die neue (natürlich darf REWRITE_MODE dann nicht auf TEXT_MATCH stehen - GENERAL wäre eine Alternative).
  • Nur SELECT-Abfragen können umgeschrieben werden - DML-Kommandos sind nicht unterstützt.
This feature has some restrictions ....
  • Bind variables are not supported. So you cannot declare a equivalence with e.g. a variable in the WHERE clause
  • Expressions like SYSDATE, ROWNUM, USER und others are not supported - ORA-30353
  • If the involved tables have uncommitted changes in your session, the query rewrite will not work.
  • When you declare a Query Rewrite Equivalence with a join, use the "old" join syntax - the ANSI syntax will not work. But you can use the ANSI syntax while querying - the previously declared equivalende with the old syntax will be used. Of course, you need to set REWRITE_MODE to another value then TEXT_MATCH - GENERAL would be an alternative.
  • Only SELECT queries are supported. DML statements will not work.
Man merkt schon, dass das Feature für den DWH-Bereich eingeführt wurde - die Anwendungsmöglichkeiten sind begrenzt - aber bei dem einen oder anderen komplexen SELECT kann es vielleicht gute Dienste leisten.
These restrictions make it obvious that the feature has been designed for Data Warehouse usage. The usage scenarios are limited - but in particular cases it might be a solution to tackle SQL performance problems.

24. November 2010

Kleine, nützliche Verbesserung in 11.2: CREATE OR REPLACE TYPE ... FORCE

Tiny useful change in 11.2: CREATE OR REPLACE TYPE FORCE
Erstellt Ihr öfter Objekttypen in eurem Datenbankschema. Wenn Ihr die Pipelined Table Functions verwendet, macht Ihr das, denn man braucht Objekttypen, um die zurückgegebene Tabelle zu beschreiben. Oracle 11.2 bringt eine kleine, aber nützliche Verbesserung mit, wenn die Objekttypen geändert werden müssen. Bislang musste man den Objekttypen löschen und neu anlegen - und wenn noch abhängige Typen da sind, müssen diese vorher auch gelöscht werden. Die die neue CREATE OR REPLACE TYPE ... FORCE-Option in der Version 11.2 erlaubt es, einen Objekttypen zu ändern, ohne die anhängigen Typen vorher löschen zu müssen. Eine solche Änderung geht nun also wesentlich leichter von der Hand ...
Do you frequently create object types in your database schema. You will do so if you use pipelined table functions since you need the types to describe the table returned by the function. Oracle 11.2 brings a tiny, but useful enhancement when it's about changing the type definitions. In previous versions you just can't change an object type's definition - you have to drop and recreate it. And when there are dependent types you have to drop all those types first. The new CREATE OR REPLACE TYPE ... FORCE syntax in 11.2 allows to change an object types' definition without looking at the dependent types. Changes are therefore much more easy to implement.
SQL> create type row_t as object(id number, col1 varchar2(2000));
  2  /

Typ wurde erstellt.

SQL> create type table_t as table of row_t;
  2  /

Typ wurde erstellt.

SQL> create or replace type row_t force as object(id number, col1 varchar2(2000), col2 date);
  2  /

Typ wurde erstellt.

SQL> desc table_t
 table_t TABLE OF ROW_T
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER
 COL1                                               VARCHAR2(2000)
 COL2                                               DATE
Die FORCE Option funktioniert nicht mehr, wenn der Typ als Datentyp in Tabellen verwendet wurde.
The FORCE option will not work when you have used the object type as the data type for a table column.
SQL> create table test_tab (col table_t) nested table col store as column_ntab;

Tabelle wurde erstellt.

SQL> create or replace type row_t force as object(
  2    id number, 
  3    col1 varchar2(2000),
  4    col2 date, 
  5    col3 timestamp
  6  );
  7  /

*
FEHLER in Zeile 1:
ORA-22866: cannot replace a type with table dependents
Eine kleine, aber manchmal nützliche Änderung ... und die muss es ja auch geben ...
A small, but sometimes useful change. And these are also very important ...

4. November 2010

ZIP Archive einpacken und auspacken: Das geht mit PL/SQL

Zipping and unzipping ... with PL/SQL!
Vor einiger Zeit veröffentlichte der Joel Kallman auf seinem Blog einen Tipp zum Erstellen von ZIP-Archiven mit anschließendem Versenden per Email. Er nutzte, und das ist ja auch eines meiner Hobbys, die Java-Engine in der Datenbank - diese ist ja von Haus aus mit ZIP-Funktionalität ausgestattet.
Some time ago there was a blog posting about creating zip files within an APEX application from Joel Kallman. He used the database-embedded JVM, since support for zip files is part of the java system library.
Dieses Posting habe ich dann als Grundlage für einen Tipp der deutschen APEX-Community genommen. Das musste ja "nur" noch um Funktionen zum Auspacken eines vorhandenen Archivs erweitert werden.
The database JVM is also one of my "hobbies" - so I very much liked this approach and thought how to extend this to also support unpacking existing zip archives. The result was a document in the german APEX-Community.
Kurz nach der Veröffentlichung bekam ich dann einen Hinweis, dass das Ganze nicht funktioniert, wenn ein ZIP-Archiv Dateien mit Umlauten im Dateinamen hat. Das ist ein alter, bekannter Bug in den Java-Systemklassen: Diese erwarten, dass ZIP-Archive in Unicode kodiert sind - WinZIP kodiert allerdings (hierzulande) in der "Codepage 850" - und sobald ein Dateiname im ZIP-Archiv einen Umlaut enthält, gibt es eine Fehlermeldung. Also muss man ein wenig mehr tun ...
Shortly after publishing this I got feedback from a german customer who had umlauts and other non ASCII characters as part of filenames within the ZIP archive. This lead to an java error and the whole approach did not work anymore. The reason for this is a very old bug in the java system library. Java expects filenames in the zip archive to be encoded in Unicode - but the popular Winzip utility uses a local codepage - "codepage 850" in germany. So there is a bit more work to be done ...
  1. Ladet euch die OpenSource-Nachbildung der ZIP-Funktionalität herunter: "Jazzlib". Grund hierfür ist, dass sie im Quellcode vorliegt und nicht mit Native-Code arbeitet. Hier lässt sich auch recht einfach eine Unterstützung für verschiedene Zeichensätze (Encodings) in ZIP-Archiven einbauen). Diese spielt Ihr dann mit dem Kommandozeilenwerkzeug loadjava in euer Datenbankschema (hier: SCOTT) ein.
    Download the open source implementation of the java.util.zip system package: "Jazzlib". This is provided as source code - so it is easy to extend the classes to suppert different character sets. You can load the file directly into your database schema (SCOTT) using the loadjava utility.
    loadjava -u scott/tiger -o -r -v jazzlib-binary-0.07.zip
    
  2. Ladet euch danach die Java-Klassen ZipInputStream.class und ZipOutputStream.class herunter. Dies sind die entsprechenden Klassen der "Jazzlib", aber jeweils erweitert um Methoden zur Unterstützung verschiedener Zeichensätze. Wenn euch der Quellcode interessiert, findet Ihr den hier für ZipInputStream.java und ZipOutputStream.java. Auch diese müssen mit dem loadjava-Werkzeug in das Datenbankschema geladen werden - verwendet bitte die .class-Dateien.
    Then download the java classes ZipInputStream.class and ZipOutputStream.class. These classes add support for different encodings and will replace the existing classes in the "jazzlib" package. You can have a look into sourcecode using the following links for ZipInputStream.java and ZipOutputStream.java. Use the loadjava utility again to load the .class files into the database schema.
    loadjava -u scott/tiger -o -r -v ZipInputStream.class ZipOutputStream.class
    
  3. Damit ist die ZIP-Implementierung jazzlib in die Datenbank geladen. Allerdings lässt sich die vorhandene Programmierschnittstelle nicht gut auf PL/SQL abbilden. Es wird also noch etwas mehr Java-Code benötigt - dieser hat nur die Aufgabe, die ZIP-Funktionalität in Java-Methoden, die sich leicht auf PL/SQL abbilden lassen, bereitzustellen.
    Now the (extended) ZIP implementation Jazzlib has been loaded into the database schema. But the API which has been designed to match the java.util.zip API cannot be mapped to a corresponding PL/SQL package, since the java types have no SQL pendants. So we need more java code: the following java class just creates java wrapper methods which can be easily mapped to PL/SQL.
    create or replace java source named "JavaZipCode" as
    
    import net.sf.jazzlib.ZipEntry;
    import net.sf.jazzlib.ZipOutputStream;
    import net.sf.jazzlib.ZipInputStream;
    import java.util.Vector;
    import java.io.OutputStream;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import oracle.jdbc.OracleDriver;
    import oracle.jdbc.OracleResultSet;
    import oracle.sql.BLOB;
    import oracle.sql.STRUCT;
    import oracle.sql.ARRAY;
    import oracle.sql.ArrayDescriptor;
    import oracle.sql.StructDescriptor;
    
    public class zip {
        static Connection con = null;
        static  BLOB zipLob = null;    
        static  InputStream lobIs    = null;
        static  ZipInputStream zipIs = null;
        static  ZipEntry zipFile = null;
        static boolean bFileIsOpen = false;
    
        static {
          try {
            con = DriverManager.getConnection("jdbc:default:connection");
          } catch (Exception e) {
            e.printStackTrace(System.out);
          }
        }
    
        public static void open(BLOB inLob, String encoding) throws Exception {
          if (bFileIsOpen) {
            throw new Exception ("ZIP File already open - call close() first");
          }
          zipLob = inLob;
          lobIs = inLob.getBinaryStream();
          zipIs = new ZipInputStream(lobIs, encoding);
          bFileIsOpen = true;
        }
      
        public static int getCurrentHandle() throws Exception {
          return (bFileIsOpen?1:0);
        }
    
        public static int next() throws Exception {
          zipFile = zipIs.getNextEntry();
          return (zipFile == null?0:1);
        }
    
        private static String getFileName (String sEntryName) {
          int iLastSlashPos = sEntryName.lastIndexOf("/");
          if (iLastSlashPos != -1) {
            return sEntryName.substring(iLastSlashPos + 1);
          } else {
            return sEntryName;
          }
        }
     
        public static STRUCT getEntry() throws Exception {
          if (!bFileIsOpen) {
            throw new Exception ("ZIP File is not open - call open() first");
          }
          StructDescriptor sDescr = 
            StructDescriptor.createDescriptor("ZIP_ENTRY_T", con);
          BLOB blobEntryContent = 
            BLOB.createTemporary(con, true, BLOB.DURATION_SESSION);
          OutputStream lobOs = blobEntryContent.getBinaryOutputStream(0L);
          int iChunkSize = blobEntryContent.getChunkSize();
          byte[] b = new byte[iChunkSize];
    
          int iBytesRead = 0;
    
          STRUCT oraZipEntry = null;
          Object[] oZipEntry = null;
    
          if (zipFile != null) {
           oZipEntry = new Object[6];
           oZipEntry[0] = zipFile.getName();
           oZipEntry[1] = getFileName(zipFile.getName());
           oZipEntry[2] = (zipFile.isDirectory()?"Y":"N");
           oZipEntry[3] = new java.math.BigDecimal(zipFile.getSize());
           oZipEntry[4] = new java.math.BigDecimal(zipFile.getCompressedSize());
           while ( (iBytesRead = zipIs.read(b, 0, iChunkSize)) != -1) {
            lobOs.write(b, 0, iBytesRead);
           }
           lobOs.flush();
           lobOs.close();
           oZipEntry[5] = blobEntryContent;
           oraZipEntry = new STRUCT(sDescr, con, oZipEntry);
          } else {
            throw new Exception ("End of zip file reached");
          }
          return oraZipEntry;
        }
    
        public static void close() throws Exception{
         lobIs.close();
         zipIs.close();
         lobIs = null;
         zipIs = null;
         bFileIsOpen = false;
        }
         
    
        public static ARRAY list (BLOB inLob, String encoding) throws Exception {
          InputStream lobIs = inLob.getBinaryStream();
          ZipInputStream zipIs = new ZipInputStream(lobIs, encoding);
          ZipEntry zipFile = null;
          boolean bEndOfArchive = false;
    
          ArrayDescriptor aDescr = ArrayDescriptor.createDescriptor("ZIP_ENTRY_CT", con);
          StructDescriptor sDescr = StructDescriptor.createDescriptor("ZIP_ENTRY_T", con);
    
          Object[] oZipEntry = new Object[6];
          STRUCT oraZipEntry = null;
          Vector vZipEntries = new Vector();
    
          while (!bEndOfArchive) {
            zipFile = zipIs.getNextEntry();
            if (zipFile != null) {
             oZipEntry[0] = zipFile.getName();
             oZipEntry[1] = getFileName(zipFile.getName());
             oZipEntry[2] = (zipFile.isDirectory()?"Y":"N");
             oZipEntry[3] = new java.math.BigDecimal(zipFile.getSize());
             oZipEntry[4] = new java.math.BigDecimal(zipFile.getCompressedSize());
             oZipEntry[5] = null;
             oraZipEntry = new STRUCT(sDescr, con, oZipEntry);
             vZipEntries.add(oraZipEntry);
            } else {
             bEndOfArchive = true;
            }
          }            
          lobIs.close();
          return new ARRAY(aDescr, con, vZipEntries.toArray());
        }
    
        public static BLOB zip(String query, String encoding) throws Exception {
            PreparedStatement pstmt = con.prepareStatement(query);
            BLOB result = zip(pstmt.executeQuery(), encoding);
            pstmt.close();
            return result;
        }
    
    
        public static BLOB zip(ResultSet   rset, String encoding) throws Exception {
            BLOB zipLob = BLOB.createTemporary(con, true, BLOB.DURATION_SESSION);
      
            OutputStream os = zipLob.setBinaryStream(1);
            ZipOutputStream zos = new ZipOutputStream(os, encoding);
    
            BLOB src = null;
            String filename = null;
    
            int chunksize = zipLob.getChunkSize();
            while (rset.next()) {
                filename = rset.getString( 1 );
                src = ((OracleResultSet)rset).getBLOB( 2);
    
                ZipEntry entry = new ZipEntry(filename);
                if (src != null) {
                  entry.setSize(src.length());
                } else {
                  entry.setSize(0);
                }
                zos.putNextEntry(entry);
                if (src != null) {
                  long len = src.length();
                  long offset = 1;
                  byte[] buffer;
                  while (offset < len) {
                     buffer = src.getBytes(offset, chunksize);
                     if (buffer == null) 
                       break;
     
                     zos.write(buffer, 0, buffer.length);  
                     offset += buffer.length;
                  }
                }
                zos.closeEntry();
            }
            zos.close();
            rset.close();
            return zipLob;
        }
    }
    /
    
    
    alter java source "JavaZipCode" compile
    /
    sho err
    
  4. Nun geht es an das PL/SQL-Paket. Zunächst benötigt Ihr Datenstrukturen - es braucht einen Datentypen für einen Eintrag (gepackte Datei) im ZIP-Archiv (ZIP_ENTRY_T) und einen für alle Dateien im Archiv (ZIP_ENTRY_CT).
    Now the PL/SQL part will be done. First we need data structures for dealing with ZIP archies. The following SQL script creates a type ZIP_ENTRY_T (which represents a file within a ZIP archive) and a collection ZIP_ENTRY_CT which is for all the files in a ZIP archive.
    drop type zip_entry_ct
    /
    drop type zip_entry_t
    /
    
    create type zip_entry_t as object(
      file_path       varchar2(4000),
      file_name       varchar2(4000),
      is_dir          char(1),
      file_size       number,
      compressed_size number,
      content         blob
    )
    /
    sho err
    
    create type zip_entry_ct as table of zip_entry_t
    /
    sho err
    
  5. Und schließlich wird das PL/SQL-Paket ZIP erstellt, mit dem die Java-Funktionen angesprochen werden. Wie Ihr sehen könnt, werden alle Prozeduren und Funktionen direkt auf Java-Methoden abgebildet. Die ganze Arbeit wird also mit Java gemacht.
    And finally the PL/SQL package ZIP is being created. Note that the various procedures and functions just map to java methods. So the actual work is being done by the database JVM.
    create or replace package zip is
      type cursor_t is ref cursor;
    
      function zip(p_cursor in cursor_t, p_encoding in varchar2) return BLOB;
      function zip(p_query in varchar2, p_encoding in varchar2) return BLOB;
      function list(p_zipfile in blob, p_encoding in varchar2) return ZIP_ENTRY_CT;
      procedure open(p_zipfile in blob, p_encoding in varchar2);
      function next return number;
      function get_Entry return ZIP_ENTRY_T;
      function is_open return number;
      procedure close;
    
    end zip;
    /
    sho err
    
    create or replace package body zip is
      function zip(p_cursor in cursor_t, p_encoding in varchar2) return BLOB
      as language java name 'zip.zip(java.sql.ResultSet, java.lang.String) return oracle.sql.BLOB';
    
      function zip(p_query in varchar2, p_encoding in varchar2) return BLOB
      as language java name 'zip.zip(java.lang.String, java.lang.String) return oracle.sql.BLOB';
    
      function list(p_zipfile in blob, p_encoding in varchar2) return ZIP_ENTRY_CT
      as language java name 'zip.list(oracle.sql.BLOB, java.lang.String) return oracle.sql.ARRAY';
    
      procedure open(p_zipfile in blob, p_encoding in varchar2)
      as language java name 'zip.open(oracle.sql.BLOB, java.lang.String)';
     
      function next return number
    
      as language java name 'zip.next() return int';
     
      function get_Entry return ZIP_ENTRY_T
      as language java name 'zip.getEntry() return oracle.sql.STRUCT';
    
      procedure close
      as language java name 'zip.close()';
    
      function is_open return number
    
      as language java name 'zip.getCurrentHandle() return int';
    end zip;
    /
    sho err
    
Nun könnt Ihr ein wenig testen. Ladet ein ZIP-Archiv in eine Tabelle. Eine Tabelle könnte bspw. so aussehen:
Now we can test a bit. Load a ZIP archive as a BLOB into a table. This might look as follows ...
SQL> select * from zip_archives;

NAME                           ZIPFILE
------------------------------ ----------------------------------------
jazzlib-binary-0.07.zip        504B03041400000008007B6DAD3002A3C98E8702
                               0000E20300001C0015006E65742F73662F6A617A
                               7A6C69622F41646C657233322E636C6173735554
                               0900037A5FA3407C5FA34055780400E803E80375


1 Zeile wurde ausgewählt.
Die Inhalte lassen wir uns nun mal anzeigen ...
You can then have a look into the ZIP archive as follows ...
SQL> select file_name, file_size from zip_archives, table(zip.list(zipfile, 'cp850'))

FILE_NAME                                 FILE_SIZE
---------------------------------------- ----------
Adler32.class                                   994
CRC32.class                                    1168
CheckedInputStream.class                       1302
CheckedOutputStream.class                       967
Checksum.class                                  274
DataFormatException.class                       440
Deflater.class                                 4419
DeflaterConstants.class                        1759
DeflaterEngine.class                           7281
DeflaterHuffman$Tree.class                     4677
:                                                 :

34 Zeilen ausgewählt.
In dieser Table-Funktion LIST ist das BLOB-Feld (CONTENT) übrigens stets auf NULL - andernfalls würden zuviele temporäre LOBs generiert. Wenn Ihr das ZIP-Archiv nun also tatsächlich auspacken wolltet (beispielsweise in die Tabelle MY_UNPACKED_FILES), ginge das mit dem folgenden PL/SQL-Code.
The LIST table function always returns SQL NULL for the CONTENT attribute. Populating it with the actual unpacked file would lead to too much open temporary lobs. If you want to unpack the archive and insert into unpacked files into another table (say: MY_UNPACKED_FILES) this would be done with the following Code ...
declare
  l_zipfile   blob;

  l_zipentry  zip_entry_t;
  l_next      pls_integer := 1;
begin
  select zipfile into l_zipfile
  from zip_archives
  where name = 'jazzlib-binary-0.07.zip';

  zip.open(l_zipfile, 'cp850');
  while l_next = 1 loop
   l_next := zip.next;

   if l_next = 1 then 
    l_zipentry := zip.get_entry;

    insert into my_unpacked_files
    values (zipentry.file_name, zipentry.content);

    if l_zipentry.content is not null then 
     dbms_lob.freetemporary(l_zipentry.content);
    end if;
   end if;
  end loop;

  zip.close;
end;
Die Funktion zum Erstellen eines ZIP-Archivs ist überladen: Sie kann mit einem Cursor und einer SQL-Abfrage als VARCHAR2 arbeiten. In beiden Fällen muss die Abfrage zwei Spalten zurückliefern - den Namen der Datei in der ersten, den Inhalt in der zweiten. Zurückgegeben wird ein BLOB, in dem das ZIP-Archiv enthalten ist.
The function to create a ZIP archive is pretty much the same as in Joels original posting. It has been overloaded. The query can be either SQL text in a VARCHAR2 variable or a REF CURSOR argument. In both cases the query must return two columns: the first one must be the filename, the second one must be the file content as a BLOB. The function will return the ZIP archive as a BLOB.
select zip.zip('select image_name, image_content from image_table', 'cp850') from dual;
Viel Spaß damit ...
Have fun!

12. Oktober 2010

Monate zu einem Datum hinzufügen. Da gibt's doch nix zu sagen, oder doch?

When it is about adding months ...
Das Thema DATE und TIMESTAMP hatte ich zwar schon, aber aus gegebenem Anlaß möchte ich heute doch noch ein kurzes Posting nachschieben. Es geht darum, wie man am effizientesten Monate zu einem Datum hinzufügt oder von diesem abzieht. Die eleganteste Variante scheint die INTERVAL-Syntax zu sein ...
DATE and TIMESTAMP and how to deal with these was already the topic of some blog postings. But today I'm talking again about this - and I have a particular issue: Adding or substracting months from a given date. The most elegant syntax seems to be the INTERVAL function ...
SQL> select to_date('2010-10-12', 'YYYY-MM-DD') - interval '1' month datum 
  2  from dual;

DATUM
----------
12.09.2010

1 Zeile wurde ausgewählt.
Sieht erstmal gut aus ... aber wenn man das mal mit dem 31.10. probiert, erlebt man eine Überraschung ...
This looks good at the first glance. But when you try this with the 31st of a month (say: October) you encounter an error ...
SQL> select to_date('2010-10-31', 'YYYY-MM-DD') - interval '1' month datum 
  2  from dual;

FEHLER in Zeile 1:
ORA-01839: Datum für angegebenen Monat nicht gültig
Was soll das? Naja, Oracle nimmt vom 31.10. einen Monat weg und kommt dann auf den 31.09. - den gibt es aber nicht, also wird ein Fehler ausgelöst. Das ist nicht so schön - aber nicht zu ändern. Es gibt zwei Möglichkeiten, mit dem Problem umzugehen. Zunächst könnte man mit dem "nächsten Ersten" arbeiten. Also nicht einen Monat vom 31.10. abziehen, sondern erstmal zum nächsten Ersten gehen (1.11.), dann den Monat abziehen und wieder zurückgehen. Das ist dann ein wenig Spielen mit TRUNC und ROUND. Alles in allem eher aufwändig.
What is that? Now, Oracle substracts a month from "October" and keeps the day (31st). The result is 31st of September - which does not exist, of course. This behaviour is odd - but it is as it is. So we have to figure how to deal with it. And there are two options: The first one would be to substract the month from the beginning of the next month. So we first "navigate" to November 1st, substract a month and a day. This is a bit of playing with ROUND and TRUNC functions for dates. But it is a quite cumbersome approach.
Oder man verwendet die "alte" Funktion ADD_MONTHS. Anders als der Name vermuten lässt, kann man damit auch Monate abziehen.
The old fashioned function ADD_MONTHS is much easier: It can not only add but also substract months.
SQL> select add_months(to_date('2010-10-31', 'YYYY-MM-DD'), -1) from dual;

ADD_MONTHS
----------
30.09.2010
Also: wenn es darum geht, "Monate" von einem Datum abzuziehen oder dazuzurechnen, ist ADD_MONTHS die einfachere Variante - sie erfordert keine Sonderbehandlung für die Tage ab dem 28. eines Monats. Sogar der 29. oder der 30. können zum beschriebenen Fehler führen - wenn man nach dem Abziehen oder Hinzufügen der Monate im Februar landen würde.
So when it is about adding or substracting months from a given date, the ADD_MONTHS function is the better approach since it also handles the described issue. And this applies particluarly when the given date is the end of a month, say: all days starting with the 28th. Yes, also the 29th and the 30th might be a problem, when your "target month" after adding or substracting is February.

27. September 2010

Inhaltsverzeichnis "SQL und PLSQL in Deutsch" ab sofort verfügbar!

Dieses Blog gibt es ja nun schon recht lange - und beim Durchstöbern meiner ganzen Postings ist mir aufgefallen, dass doch einiges an Information im Blog enthalten ist. Daher möchte ich heute keine neuen Inhalte einstellen, sondern euch eine Übersicht über die vorhandenen Postings zur Verfügung stellen. Schaut einfach mal rein.
Anhand der Zugriffsstatistiken sind mir auch einige Postings aufgefallen, die häufiger abgerufen werden. Das sind ...
Ein Posting, das schon sehr lange zurückliegt, nutze ich selbst sehr häufig beim Kunden. Ab Oracle11g Release 2 wird es zwar obsolet, weil es mit LISTAGG dann eine eingebaute Funktion gibt, aber für alle, die auf älteren Versionen laufen, ist es bestimmt oft hilfreich: Aggregatsfunktion für VARCHAR2.

9. September 2010

Information ist alles! DBMS_APPLICATION_INFO

Zum diesem Thema hatte ich vor langer Zeit zwar schonmal ein Blog-Posting, aber ich denke, dass es ein erneutes Posting durchaus wert ist.
Regarding today's topic I already had a blog posting long time ago. But this is IMHO so important that another posting is worth the effort.
Es geht darum, wie Ihr dem Datenbank-Administrator - schon während der Anwendungsentwicklung helfen könnt. Mit dem PL/SQL-Paket DBMS_APPLICATION_INFO. Das ist meiner Meinung nach eins der wichtigsten PL/SQL-Pakete für jeden Entwickler - egal ob man PL/SQL, Java, .NET, PHP oder andere Programmiersprachen verwendet.
I'll talk about DBMS_APPLICATION_INFO. With this package you can today - while developing your application - help the database administrator when it is about future debugging and tracing. This is one of the most important PL/SQL packages for application developers - for all developers regardless the programming language they use.
Mit DBMS_APPLICATION_INFO könnt Ihr eure Datenbanksitzung mit drei "Etiketten" versehen. Die Etikette heißen CLIENT_INFO, MODULE und ACTION. Was Ihr dort hineinschreibt, ist der Datenbank völlig egal. Die Informationen werden aber in allen Datenbank-Views, die der DBA zur Überwachung verwendet (also V$SQL, V$SESSION und andere) mit angezeigt. Dazu ein PL/SQL-Beispiel:
DBMS_APPLICATION_INFO allows you to set "markers" in the database session. Oracle provides three markers: CLIENT_INFO, MODULE and ACTION. You are allowed to set whatever value you want. The values you set are being recorded in all dynamic performance views the DBA uses (V$SQL, V$SQLAREA, V$SESSION and others. A PL/SQL example illustrates this.
begin
  dbms_application_info.set_client_info(
    client_info => 'MEINE_PLSQL_PROZEDUR'
  );
  dbms_application_info.set_module(
    module_name => 'MODUL 1',
    action_name => 'SCHLAFEN'
  );

  dbms_lock.sleep(100);

  dbms_application_info.set_client_info(
    client_info => ''
  );
  dbms_application_info.set_module(
    module_name => '',
    action_name => ''
  );
end;
/
sho err
Wenn der DBA nun (während diese Prozedur läuft), in die V$SESSION-View hineinsieht, werden diese Informationen mit angezeigt - in den Spalten CLIENT_INFO, ACTION und MODULE.
If the DBA (while this PL/SQL block is running) queries the V$SESSION view, he gets this information - it is contained in the columns CLIENT_INFO, ACTION and MODULE.
SQL> select sid, serial#, client_info, module, action
  2  from v$session where username='SCOTT'

       SID    SERIAL# CLIENT_INFO          MODULE     ACTION
---------- ---------- -------------------- ---------- ----------
        39       9780 MEINE_PLSQL_PROZEDUR MODUL 1    SCHLAFEN

1 Zeile wurde ausgewählt.
Man kann also sofort sehen, was eine Datenbanksitzung gerade tut. Das ist besonders wichtig, wenn Middleware im Einsatz ist, denn diese arbeitet normalerweise mit einem Connection-Pool, in dem alle Datenbanksitzungen mit dem gleichen Nutzerkonto arbeiten ... Und mit Java lässt sich DBMS_APPLICATION_INFO ebenfalls nutzen ...
The DBA can directly see what's going on in this very database session. This is particularly important when you have a three-tier-architecture with middleware involved. In those cases you have a connection pool where multiple database sessions are being opened with the same database user. Without DBMS_APPLICATION_USER the DBA cannot see which database session is doing which operation. DBMS_APPLICATION_INFO makes it transparent - and Java developers can (and should) also use it.
:
  Connection con = theDatasource.getConnection DBMS_APPLICATION_INFO();
  CallableStatement cstmt_ci = con.prepareCall("{call dbms_application_info.set_client_info(?)}");
  cstmt.setString(1, "JAVA_ANWENDUNG_1");
  ctsmt.execute();
  CallableStatement cstmt_mo = con.prepareCall("{call dbms_application_info.set_module(?,?)}");
  cstmt.setString(1, "MODUL 2");
  cstmt.setString(2, "JAVA BERECHNUNG");
  ctsmt.execute();
  :
Und mit allen anderen Programmiersprachen geht das analog. Die Nutzung dieser Informationen zieht sich übrigens durch alle Oracle-Werkzeuge - das folgende ist ein Screenshot aus dem Oracle Enterprise Manager. Dieses Werkzeug wird typischerweise vom DBA verwendet, um Informationen über die abgelaufenen SQL-Anweisungen zu erhalten. Und auch hier kann anhand MODULE und ACTION gesucht werden.
The same applies to all other programming languages. All Oracle tools use the Information provided with DBMS_APPLICATION_INFO. The following is a screenshot of Oracle's Enterprise Manager - the tool which many DBAs use the maintain the database. If they search for particular SQL statements Enterprise Manager allows to filter for ACTION or MODULE or both.

Ich kann nur wärmstens empfehlen, DBMS_APPLICATION_INFO immer und überall einzusetzen; wenn es mal später ans Tuning oder an den Betrieb der Applikation geht, wird der Aufwand doppelt und dreifach wieder zurückgezahlt - eben dadurch, dass sich Probleme schneller zuordnen und damit auch einfacher diagnostizieren lassen. Wenn Ihr das Package bislang also noch nicht genutzt habt: Fangt noch heute damit an.
If you're not using DBMS_APPLICATION_INFO - start using it today! You get additional efforts (if any) paid back doubled and tripled when it's about debugging, tracing or tuning the application.

6. September 2010

APEX für Alle! Webseminar zu den neuen APEX Websheets!

APEX-Interessierte aufgepasst. Am Mittwoch, den 8. September um 11:00 Uhr findet ein Webseminar zu den in APEX 4.0 neuen Websheets statt.
Mit den neuen Websheets kann APEX auch ohne SQL- oder PL/SQL-Kenntnisse genutzt werden; mit Berichte, Formularen, HTML-Editoren oder Data Grids können sogar Endanwender sehr schnell Daten ins Web bringen. Das Webseminar stellt vor, wie das funktioniert.
Also: Macht das Webseminar bei euren Fachabteilungen bekannt - vielleicht könnt Ihr das Interesse an APEX in eurer Firma erhöhen und den täglichen Spreadsheet-Wahnsinn mit einer vernünftige Lösung (APEX) anzugehen ...

27. August 2010

Performance von Spatial-Abfragen (DOAG SIG Spatial am 14.09.2010)

Dieses Posting ist mal zu einem etwas spezielleren Thema, in dem ich aber ebenfalls unterwegs bin: Oracle Spatial: Am 14. September veranstaltet die DIAG "Special Interest Group" Spatial ihre regelmäßige Veranstaltung. Ich werde dort einen Vortrag zum Thema Performance von Spatial-Abfragen halten.
Interessiert am Thema ... einfach auf die DOAG Webseite. Für APEX-Entwickler ist der darauffolgende Tag interessant: In einem eintägigen Workshop wird vermittelt, wie man Geodaten und Karten in APEX-Anwendungen verwenden kann.
Vielleicht sieht man sich ...
This posting is about a german-language event - so it is in german only.

3. August 2010

Römische Zahlen mit der Datenbank ...

English title: Roman Numbers with TO_CHAR

Vor kurzem ist mir nochmals was "kurioses" aufgefallen. Wusstet Ihr schon, dass Ihr römische Zahlen mit der Datenbank generieren könnt ...?
Shortly I found another nice feature of the Oracle database. You can generate roman numbers with just the TO_CHAR function.
SQL> select to_char(1997, 'RN') roemisch from dual;

ROEMISCH
---------------
       MCMXCVII

1 Zeile wurde ausgewählt.
Es funktioniert leider nur mit Zahlen zwischen 1 und 3999 - also für Jahres- oder Monatszahlen. Aber für was anderes braucht man sie ja auch normalerweise nicht ...
It only works for numbers between 1 and 3999 - so its usable for years and months ... but this should be sufficient for most cases.
SQL> select to_char(4001, 'RN')  roemisch from dual;

ROEMISCH
---------------
###############

1 Zeile wurde ausgewählt.

20. Juli 2010

"Rekursive" Table Functions: Ohne Objekttypen

English title: Recursive able Functions - without object types!

Erst vor kurzem hatte ich ein wenig was über die neue rekursive WITH-Klausel gepostet. Diese ist ab Version 11.2 verfügbar und erlaubt es, hierarchische Abfragen ohne START WITH - CONNECT BY zu formulieren.
Only a short time ago I posted about the new recursive subqueries, which are available in 11.2 and which allow to do recursive queries without START WITH - CONNECT BY.
Interessant ist, dass diese Technik es auch erlaubt, Table Functions auf eine andere Art zu bauen - man kann sich so eine SQL-Abfrage schreiben, die mit Hilfe der Rekursion zusätzliche Tabellenzeilen generieren kann. Hier ein Beispiel: Den Tilgungsplan, der hier als "klassische" Table Function bereitsteht, kann man auch mit einer rekursiven WITH-Klausel bauen. Das sähe dann so aus ...
The interesting bit is that this kind of query could also be used as a replacement for table functions - the recursion allows to create new, "table-independent" rows in a query result. And here is an example: I've done the table function for the "mortgage plan" as a recursive query. This query looks like this ...
col kapital format 999999990D00
col zinsen format 999999990D00
col tilgung format 999999990D00
col restwert format 999999990D00

with konstanten (datum, kapital, zinssatz, rate, rhythmus) as (
  select 
    trunc(sysdate, 'MONTH') datum,
    100000 kapital,
    5 zinssatz,
    600 rate,
    1 rhythmus
  from dual
), 
tilgungsplan (datum, kapital, zinsen, tilgung, restwert) as (
  select 
    to_char(k.datum) datum,
    k.kapital, 
    k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) tilgung,
    k.kapital - ( k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus))) restwert
  from konstanten k
  union all (
   select 
    to_char(add_months(to_date(s.datum),k.rhythmus)) datum,
    s.restwert kapital,
    s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    least(
      k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
      s.restwert
    ) tilgung,
    s.restwert - ( 
      least(
        k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
        s.restwert
      )
   ) restwert
   from konstanten k, tilgungsplan s
   where restwert > 0 
  )
)
select * from tilgungsplan
/
Der Vorteil ist, dass man keine Objekttypen zum Beschreiben der Ergebnismenge mehr braucht - man kann eine solche Abfrage mit nichts weiter als einem CREATE SESSION-Privileg ausführen. Allerdings sind die "klassischen" Table Functions (im Moment noch) wesentlich schneller - wer möchte, kann es ja vergleichen. Man kann auf jeden Fall festhalten, dass man mit der rekursiven WITH-Klausel mehr machen kann, als Parent-Child-Beziehungen abzufragen ...
As an advantage you don't need to create object types (which you need for pipelined table functions) - so this query could be executed with just the CREATE SESSION privilege. But (currently) this query is significantly slower than the pipelined function. And the result of all this ...? Using the new recursive WITH clause you can do much more than just querying parent-child relationships.

6. Juli 2010

Neues Feature für "classic" import: DATA_ONLY

English title: New Feature for "classic" imp: DATA_ONLY

In Oracle 11.2 gibt es beim "klassischen" Import-Werkzeug einen sehr nützlichen neuen Parameter: DATA_ONLY. Damit wird das Import-Utility angewiesen nur die Daten und keinerlei Metadaten wie Tabellen, Typen, PL/SQL-Prozeduren oder Trigger zu importieren (Dokumentation). Wenn die Tabellen, in die die Daten importiert werden sollen, nicht da sind, wird ein Fehler ausgelöst. Ruft man imp help=yes auf, so wird der neue Parameter angezeigt ...
In Oracle 11.2 there is a new feature for the "classic" import utility - and this is IMHO a very helpful one: DATA_ONLY. When specified as DATA_ONLY=Y the import utility processes only the data of an import file and skips all the metadata. That means, no tables, types, PL/SQL objects or other database objects are being created. When a table for the data is not present, import raises an error message. Calling import with help=yes shows the new parameter.
$ imp help=yes

:
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY                overwrite tablespace data file (N)
:
RESUMABLE_TIMEOUT      wait time for RESUMABLE
COMPILE                compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION  import streams general metadata (Y)
STREAMS_INSTANTIATION  import streams instantiation metadata (N)
DATA_ONLY              import only data (N)
VOLSIZE                number of bytes in file on each volume of a file on tape
:
Früher hat man sich, wenn die Tabellen schon da waren mit IGNORE=Y geholfen, DATA_ONLY=Y ist aber noch besser geeignet - insbesondere, wenn man die Datenbankobjekte ohnehin vorher per Skript eingespielt hat. Die Data Pump kann das natürlich auch - und vieles mehr - daher empfehle ich generell die Nutzung der Data Pump. Aber man kann es sich ja auch nicht immer aussuchen - ab und zu hat man eben ein altes, klassisches Export-File und muss das importieren. Vielleicht ist diese neue Option dann für den einen oder anderen nützlich ...
In the past we worked with IGNORE=Y when the tables were already present. But import then recreated all PL/SQL objects then. So DATA_ONLY is much better suited in cases where all the metadata is already present and we just want to import table rows. In generel I'd recommand to use Data Pump instead of the "classic" export and import utilities - with Data Pump you can adjust the export and import process in a much more fine granular manner. But sometimes we just have an "old" dumpfile and in those cases this new option might be helpful ...

15. Juni 2010

LOB (BLOB, CLOB) von entfernten Datenbanken lesen ...

English title: Retrieve Lobs from remote databases

Database Links haben, wie die meisten von euch wissen, so ihre Einschränkungen. So lassen sich BLOBS und CLOBs nicht so einfach über einen Database Link abfragen. Probiert man es, so kommt man nicht besonders weit ...
Database Links have, as most of us know, their limitations. One is that you cannot transport a LOB object. An attempt leads to an error message.
SQL> select content from dateien_tab@bloblink where file_name='04_XMLDB.pdf';
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables
Eine Lösung kann allerdings (wieder einmal) Java in der Datenbank sein. Denn in dieser könnte man eine normale JDBC-Verbindung zur entfernten Datenbank öffnen, den Blob selektieren, die Bytes per Java-Streaming lesen und dann lokal (wieder als BLOB) zurückgeben.
Java in the database clould be a solution (again). This is because in a java stored procedure one can open a plain JDBC connection to the remote database, execute a SQL query selecting the LOB column and retrieve the LOB bytes with java streaming. Those bytes could then returned to the SQL layer as a LOB object.
create or replace and compile java source named "JAVA_RemoteBlob" as
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.io.*;

  public class RemoteBlob {
    private static Statement  stmt = null;
    private static ResultSet  rs   = null;
    private static Connection remoteConn = null;
    private static Connection localConn =  null;

    static { 
      try {
         localConn = DriverManager.getConnection("jdbc:default:connection");
      } catch (Exception e) {}
    }

    public static void prepareCursor (String sUser, String sPass, String sConn, String sSql) throws Exception {
      String strUrl = "jdbc:oracle:thin:@" + sConn;
      remoteConn = DriverManager.getConnection( strUrl, sUser, sPass ); 
      stmt = remoteConn.createStatement();
      rs = stmt.executeQuery(sSql);
    }

    public static void closeCursor () throws Exception {
      rs.close();
      stmt.close();
      remoteConn.close();
    }
   
    public static Datum getBlob() throws Exception {
      Datum        localLOB = null;
      Datum        remoteLOB = null;
      InputStream  remoteBytes = null;    
      OutputStream localBytes = null;    
      Reader       remoteChars = null;
      Writer       localChars = null;

      byte[] bBuffer = null;
      char[] cBuffer = null;

      int iBytesRead = 0;

      if (rs.next()) {
  
        remoteLOB = ((OracleResultSet)rs).getOracleObject(1);  

        if (remoteLOB instanceof oracle.sql.BLOB) {
          localLOB = BLOB.createTemporary(localConn, true, BLOB.DURATION_CALL);
          remoteBytes = ((BLOB)remoteLOB).getBinaryStream(0L);
          localBytes = ((BLOB)localLOB).getBinaryOutputStream(0L);
          bBuffer = new byte[((BLOB)remoteLOB).getChunkSize()];
          while ((iBytesRead = remoteBytes.read(bBuffer, 0, bBuffer.length)) != -1) {
            localBytes.write(bBuffer, 0, iBytesRead);
          }
          localBytes.flush();
          localBytes.close();
          remoteBytes.close();
        } else {
          localLOB = CLOB.createTemporary(localConn, true, CLOB.DURATION_CALL);
          remoteChars = ((CLOB)remoteLOB).getCharacterStream(0L);
          localChars = ((CLOB)localLOB).getCharacterOutputStream(0L);
          cBuffer = new char[((CLOB)remoteLOB).getChunkSize()];
          while ((iBytesRead = remoteChars.read(cBuffer, 0, cBuffer.length)) != -1) {
            localChars.write(cBuffer, 0, iBytesRead);
          }
          localChars.flush();
          localChars.close();
          remoteChars.close();
        }
      } else { 
        throw new Exception ("ResultSet Exhausted");
      }
      return localLOB;
    }
  } 
/
sho err

create or replace package remote_lob is
  procedure prepare_cursor(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  );
  procedure close_cursor;
  function get_blob return blob;
  function get_clob return clob;

  function get_blob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return blob;

  function get_clob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return clob;
end remote_lob;
/
sho err

create or replace package body remote_lob is
  procedure prepare_cursor(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) as language java name 'RemoteBlob.prepareCursor(java.lang.String,java.lang.String,java.lang.String,java.lang.String)';

  procedure close_cursor
  as language java name 'RemoteBlob.closeCursor()';

  function get_blob return blob
  as language java name 'RemoteBlob.getBlob() return oracle.sql.BLOB';

  function get_clob return clob
  as language java name 'RemoteBlob.getBlob() return oracle.sql.CLOB';

  function get_blob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return blob is 
    v_blob blob;
  begin
    prepare_cursor(p_user, p_pass, p_conn, p_sql);
    v_blob := get_blob;
    close_cursor;
    return v_blob;
  end get_blob_simple;

  function get_clob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return clob is
    v_lob clob;
  begin
    prepare_cursor(p_user, p_pass, p_conn, p_sql);
    v_lob := get_clob;
    close_cursor;
    return v_lob;
  end get_clob_simple;

end remote_lob;
/
sho err
Nun habt Ihr das Package REMOTE_LOB, welches euch erlaubt, BLOBs oder CLOBS aus entfernten Datenbanken abzurufen. Der einzige Wermutstropfen ist, dass Username, Password und Connection-String hier nochmal übergeben werden müssen: die Informationen eines Database Link kann man nicht nutzen, da die das Passwort für die entfernte Datenbank nicht mehr aus den Dictionary Tabellen ausgelesen werden kann. Vor dem Test braucht Ihr noch Java-Netzwerkprivilegien - ohne schlägt der JDBC-Connect fehl ...
Now we have the package REMOTE_LOB which allows you to read BLOB oder CLOB objects from remote databases. The only disadvantage is that you need to pass username, password and the connect string to the java stored procedures. The information contained in an existing database link cannot be used since the remote users' password cannot be read from the dictionary tables. Before testing you need appropriate java privileges - without the JDBC connection will fail with the error message below.
ERROR in line 1:
ORA-29532: "Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.net.SocketPermission
sccloud033 resolve) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission',
'sccloud033', 'resolve' )
ORA-06512: in "SCOTT.REMOTE_LOB", line 2
ORA-06512: in line 2
Also Privilegien vergeben (als DBA) ...
So we grant the privileges (as DBA) ...
begin
  dbms_java.grant_permission( 
    GRANTEE => 'SCOTT', 
    PERMISSION_TYPE => 'SYS:java.net.SocketPermission',
    PERMISSION_NAME => '{remote-host}', 
    PERMISSION_ACTION => 'connect, resolve' 
  );
end;
/
.... und testen:
... finally we can test ...
SQL> exec remote_lob.prepare_cursor('test','test','sccloud033:1521/orcl','select content from dateien_tab');

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(remote_lob.get_blob) from dual;

DBMS_LOB.GETLENGTH(REMOTE_LOB.GET_BLOB)
---------------------------------------
                                 460800

1 row selected.

SQL> select dbms_lob.getlength(remote_lob.get_blob) from dual;

DBMS_LOB.GETLENGTH(REMOTE_LOB.GET_BLOB)
---------------------------------------
                                 751616

1 row selected.

SQL> select remote_lob.get_blob from dual;

GET_BLOB
--------------------------------------------------------------------------------
D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF09000600000000000000
00000000050000001502000000000000001000001702000001000000FEFFFFFF0000000025020000

1 Zeile wurde ausgewählt.

SQL> exec remote_lob.close_cursor;

PL/SQL procedure successfully completed.
Mit CLOBs funktioniert das analog. Die Funktionen GET_BLOB_SIMPLE und GET_CLOB_SIMPLE dienen der "Bequemlichkeit" und machen alle drei Schritte auf einmal. Das Grundprinzip könnte sogar verwendet werden, um LOBs aus einer anderen Datenbank auszulesen - schließlich kann man auch den JDBC-Treiber einer anderen Datenbank laden. Das kommt vielleicht noch etwas später - bis jetzt erstmal viel Spaß damit.
CLOBs also work. The functions GET_BLOB_SIMPLE and GET_CLOB_SIMPLE are "convenience functions" which do the three steps (prepare, get, close) at once. The basic principle of these functions could also be used to retrieve LOB objects from non-Oracle databases - a 3rd-party JDBC driver can easily be loaded into the database JVM. More about this later ... for now have fun with this package ...

2. Juni 2010

Enterprise Manager Alerts als RSS Feed bereitstellen - ein Beispiel

English title: Provide Enterprise Manager Alerts as RSS Feed: An Example

Wie die meisten von euch wissen, kann man mit dem Enterprise Manager Server-Generierte Alerts verwalten - bei bestimmten Ereignissen (die Metriken können definiert werden), wird ein solcher Alert ausgelöst. Loggt man sich in den Enterprise Manager Database Control ein - so sieht man die aktuellen Alerts direkt auf der Homepage. Ein Hinweis: Der folgende Tipp erfordert die Lizensierung des Diagnostic Pack, denn der Zugriff auf die View, mit der wir hier arbeiten, erfordert diese Lizenz.
In Oracle Enterprise Manager you can view and manage server-generated alerts - specific events, (you can define additional event and their metric yourself) trigger alerts. After logging into Database Control you can view see the current alerts on the homepage. Note: To use this tip you need to license the Oracle Enterprise Manager Diagnostic Pack sind the view we're using here is "protected" by that license.
Schade ist nur, dass man sich stets in die Web-Anwendung des Database Control (oder Grid Control) einloggen muss, damit man etwas sieht. Enterprise Manager erlaubt es zwar, Benachrichtigungen auch per Email versenden zu lassen - für den Nachrichtenaustausch gibt es jedoch noch eine weitere, sehr verbreitete Variante: RSS.
But you need to login into Enterprise Manager in order to see the alerts. You can register an email address within Enterprise Manager in order to be notified by mail - but for message exchange there is another very popular format: RSS. This posting shows how to provide an RSS feed containing the current Enterprise Manager alerts.
Die Alerts werden im EM Repository verwaltet. Dieses liegt im Schema SYSMAN der (bei Database Control) verwalteten Oracle-Datenbank oder (bei Grid Control) der Repository-Datenbank. Dieses Posting basiert auf Database Control - es sollte prinzipiell auch mit Grid Control funktionieren, aber dazu muss der Code wahrscheinlich an der einen oder anderen Stelle geändert werden. Außerdem basiert der Tipp auf einer 11g-Datenbank, da ich das PL/SQL Embedded Gateway als Webserver für den RSS Feed nutze. In 10g funktioniert es auch, man muss aber Apache Webserver mit mod_plsql (wie bei APEX) nutzen - darauf möchte ich hier aber nicht näher eingehen.
Mit den Mitteln der Datenbank könnte man einen RSS Feed bauen. Der RSS Feed soll die Alerts anzeigen und bei Klick auf einen der Alerts soll auf die Database Control-Seite dazu verzweigt werden. RSS-Feeds bestehen aus einem bestimmten XML-Format, welches über das Web bereitgestellt wird. Zum Erzeugen des XML können die XML-Funktionen der Datenbank dienen - diese habe ich in einem früheren Blog Posting schonmal vorgestellt. Nach ein wenig Suche hatte ich auch die Datenbanktabelle gefunden, in der die EM Alerts zu finden sind: MGMT$ALERT_CURRENT im Schema SYSMAN. Sie ist dokumentiert, kann von einem "normalen" Datenbankuser aber nicht ohne weiteres betrachtet werden.
The alerts are being managed in the EM Repository, which resides in the SYSMAN schema of (Database Control) the Oracle Database or (Grid Control) the repository database. This posting is based on Database Control - it should work for Grid Control also but you might need apply changes to the posted code. Furthermore I've used an 11g database here since I need the PL/SQL Embedded Gateway as the RSS feed's webserver. In 10g you would need to use the Apache Webserver with mod_plsql (as with APEX) - which is not explained here.
Using the database it is easy to build an RSS feed containing the alerts. Since RSS is an XML format it could be generated using the XML functions of the Oracle database. There was a blog posting about those in the past. After some research I found the database view containing the alert information: MGMT$ALERT_CURRENT in the schema SYSMAN. It is (as you can see) documented but cannot be selected by a "plain" database user.
Das läuft natürlich auf einen wichtigen Sicherheitsaspekt hinaus - und das möchte ich auch diesem Posting auch ganz offen voranstellen: Es ist von Oracle beabsichtigt, dass eben nicht jeder die Alerts betrachten kann (die Web-Oberfläche des Enterprise Manager ist ja durch Login geschützt). Dennoch möchte ich hier zeigen, wie Ihr sie als RSS bereitstellen könnt. Man kann es auf eine sichere Art und Weise implementieren und der eine oder andere von euch findet es vielleicht nützlich.
So I need to say something about the securiry implications: An RSS feed is visible to everyone who knows its URL - Enterprise Manager alerts are only visible to DBA's. That's the reason why there is no public database view. I'd like to show how to provide the RSS feed anyway: You can (as we'll see) implement it in a secure manner and some of you might find it useful.
Der Vorrede genug - hier ist die PL/SQL-Prozedur, welche die Alerts als RSS-Feed aufbereitet und dann mit dem PL/SQL Web Toolkit (HTP, HTF, OWA_UTIL) ausgibt. Für diese Prozedur legt Ihr euch (eben wegen der Sicherheit) am besten einen eigenen Datenbankuser an, (bspw. ALERTRSS) der nur ein Privileg hat: SELECT ON SYSMAN.MGMT$ALERT_CURRENT. Diese Prozedur wird dann als SYS ins Schema ALERTRSS eingespielt. Vor dem Einspielen solltet Ihr jedoch noch in Zeile 5 in Inhalt der Variablen v_em_url so um, dass die URL auf eure Database Control-Installation passt.
Here is the PL/SQL procedure which generates the RSS feed XML containing the contents of SYSMAN.MGMT$ALET_CURRENT. It's based on the PL/SQL Web Toolkit (OWA_UTIL, HTP, HTF etc.). For security reasons I'd recommend to use an own database schema for that procedure (ALERTRSS). Adjust the the content of the PL/SQL variable v_em_url to your EM installation before running the script.
create or replace procedure "ALERTRSS"."EM_ALERT_RSS"(
  p_target varchar2 default null
) is
  v_rssblob blob;
  v_em_url  varchar2(1000) := 'https://{your servername here}:1158/em';
begin
 select 
  xmlelement("rss", 
   xmlattributes('2.00' as "version"),
   xmlelement("channel",
    xmlelement("title", 'Oracle Enterprise Manager Alerts'),
    (
     select 
      xmlagg(
       xmlelement("item",
        xmlelement("title", message),
        xmlelement(
         "link", 
         v_em_url || '/console/monitoring/metricDetail$'||
         'type='||replace(target_type, '_', '*_')||'$'||
         'target='||replace(target_name, '_', '*_')||'$'||
         'pageType=byDay$'||
         'metricColumn='||replace(metric_column, '_', '*_')||'$'||
         'metric='||replace(metric_name, '_', '*_')||
          decode(key_value, null, '', '$keyValue='||replace(key_value, '_', '*_')) 
        ),
        XMLElement("pubDate", 
         substr(initcap(to_char(COLLECTION_TIMESTAMP, 'DAY','nls_date_language=''english''')), 1, 3)||
         ', '||
         initcap(to_char(COLLECTION_TIMESTAMP, 'DD MON YYYY HH24:MI:SS','nls_date_language=''english'''))||
         ' '||
         replace(SESSIONTIMEZONE, ':','')
        )
       )
      ) 
     from (
       select * from "SYSMAN"."MGMT$ALERT_CURRENT"
       order by collection_timestamp desc
     )
     where target_type=p_target or p_target is null
    )
   )
  ).getblobval(nls_charset_id('AL32UTF8')) into v_rssblob
  from dual;
  owa_util.mime_header('application/rss+xml', false);
  htp.p('Content-Length: '||dbms_lob.getlength(v_rssblob));
  owa_util.http_header_close;
  wpg_docload.download_file(v_rssblob);
  dbms_lob.freetemporary(v_rssblob);
end;
/
sho err
Damit die Prozedur im Browser aufgerufen werden kann, kommen (wie oben schon erwähnt) zwei Varianten in Frage. Ab Oracle11g steht das Embedded PL/SQL Gateway bereit - in 10g muss man den Apache Webserver mit mod_plsql nutzen. Das Embedded PL/SQL Gateway nutzt den Listener selbst als Webserver.
To call this procedure from the browser we need a webserver. There are basically two methods for this. In Oracle11g we have the Embedded PL/SQL Gateway, in Oracle10g we need to use Apache Webserver with mod_plsql - as described above. The Embedded Gateway uses the Oracle Listener as the HTTP server; I'll now describe how to configure it.
Damit das PL/SQL Embedded Gateway generell funktioniert, muss der HTTP-Protokollserver aktiviert sein - das erreicht Ihr mit den Skript catxdbdbca.sql im Verzeichnis $ORACLE_HOME/rdbms/admin. Die Portnummer könnt Ihr dabei frei wählen. Wenn der HTTP-Protokollserver läuft (erkennbar an der Ausgabe von lsnrctl status), könnt Ihr (als SYS) den Database Access Descriptor (DAD) für das Schema ALERTRSS einrichten. Damit der Web-Zugriff außerdem ohne separaten Login erfolgen kann, müsst Ihr noch den User ANONYMOUS entsperren. Das ist auch der Grund, warum der User ALERTRSS kein CREATE SESSION Privileg braucht. Es ist nur die Prozedur ALERTRSS.EM_ALERT_RSS zugänglich - und das auch nur für Anfragen per Browser.
Firstly you need to enable the HTTP protocol server (if not done already). There is a SQL script for that: catxdbdbca.sql in $ORACLE_HOME/rdbms/admin. You can choose the TCP/IP port number. Use lsnrctl status to check whether the listener has opened the HTTP port. After that you can configure a new Database Access Descriptor (DAD) in order to access the procedure. The following script does this. Finally unlock the database user ANONYMOUS which makes the procedure accessible without any login - that's the reason why the user ALERTRSS does not need the CREATE SESSION privilege. This setup makes only the procedure ALERTRSS.EM_ALERT_RSS accessible - and only for browser requests.
begin
  dbms_epg.drop_dad(
     dad_name => 'ALERTRSS_DAD'
  );
end;
/
sho err

begin
  dbms_epg.create_dad(
    dad_name => 'ALERTRSS_DAD',
    path     => '/alertrss/*'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'ALERTRSS_DAD', 
    attr_name => 'database-username', 
    attr_value => 'ALERTRSS'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'ALERTRSS_DAD', 
    attr_name => 'default-page', 
    attr_value => 'EM_ALERT_RSS'
  );
  DBMS_EPG.AUTHORIZE_DAD(
    dad_name => 'ALERTRSS_DAD',
    user => 'ALERTRSS'
  );
end;
/
sho err

alter user anonymous account unlock
/
Nun könnt Ihr den RSS-Feed bereits aufrufen und euch die Alerts ansehen - und zwar mit folgender URL:
From now on the URL path /alertrss matches to new procedure providing the RSS feed. Try it.
  http://{host}:{port}/alertrss
Im Firefox sollte das dann in etwa so aussehen ... ein Klick auf einen der Links verzweigt direkt auf die entsprechende Seite im Oracle Enterprise Manager. Da das RSS Format standardisiert ist, kommen nun natürlich auch andere Newsreader in Betracht: So kann auch der Thunderbird die Nachrichten darstellen. Und das RSS Format eignet sich sehr gut zur Integration mit anderen "Nachrichtendiensten" oder gar mit den Alert-RSS-Feeds anderer Datenbanken ...
In Firefox it should look as follows - clicking on a link branches directly to the alert's related EM database control page. Since RSS is a standardized format there are many newsreader applications available. The Oracle database alerts could then be displayed among other news (or alerts from other databases) - the DBA has a consistent view.

Beliebte Postings