Oracle SQL und PL/SQL ...: November 2010

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 ...

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.

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 ...

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.

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.

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 ...

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
```
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
```

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.

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

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
```

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.

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:

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 ...

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.

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.

select zip.zip('select image_name, image_content from image_table', 'cp850') from dual;

Viel Spaß damit ...

24. November 2010

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

4. November 2010

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

Beliebte Postings

Links ...

Blogroll ....

Deutschsprachige APEX und PL/SQL Community

Google+ Followers

Carsten Czarski

Disclaimer

Label Cloud

Blog-Archiv