31. Juli 2007

Verzeichnisinhalte anzeigen ... mit SQL!

Heute hatte ich eine interessante Frage:Wie kann die Dateiliste eines Verzeichnisses (auf dem Server) mit SQL selektieren?
Da PL/SQL-Pakete wie UTL_FILE dazu keine Unterstützung anbieten, liegt, wie beim Ausführen von Betriebssystem-Kommandos aus der Datenbank die Nutzung von Java in der Datenbank nahe. Das folgende Skript legt eine Java-Klasse und eine PL/SQL-Funktion GET_FILE_LIST an.
drop type varchar2_array
/

create or replace java source named "FileHelper" as
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;

public class FileHelper {
  public static ARRAY getFileList(String sDirName) throws Exception{
    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    ArrayDescriptor descr = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", con);

    File f = new File(sDirName);
    String[] sFileList = f.list();
    return new ARRAY(descr, con, sFileList);
  }
}
/

alter java source "FileHelper" compile
/

create type VARCHAR2_ARRAY as table of VARCHAR2(4000)
/

create or replace function get_file_list(p_directory in varchar2) return VARCHAR2_ARRAY
  is language java name 'FileHelper.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
Eine Dateiliste kann man sich nun ganz einfach anzeigen mit ...
select * from table(get_file_list('/'))
/

COLUMN_VALUE
---------------------------------------
lost+found
ds
export
var
usr
bin
:
Damit das funktioniert, benötigt der aufrufende Datenbankuser allerdings noch einige Java-Spezifische Privilegien erforderlich. Um ein bestimmtes Verzeichnis freizugeben (bspw. "/") muss als User SYS folgender Call abgesetzt werden.
begin 
  dbms_java.grant_permission( '[DB-Schema]', 'SYS:java.io.FilePermission', '/','read');
end;
/
Im PL/SQL-Paket zur Interaktion mit dem Betriebssystem findet Ihr eine vollständige Implementierung; die darin enthaltene Funktion FILE_PKG.GET_FILE_LIST listet neben den reinen Dateinamen auch die Attribute und erlaubt darüber hinaus auch Aktionen mit den einzelnen Dateien wie Auslesen, Hineinschreiben und vieles mehr ...

30. Juli 2007

Skripte generieren: DBMS_METADATA!

Zum Auslesen von Metadaten aus der Datenbank gibt es seit einiger Zeit ein PL/SQL-Paket, welches diese Aufgabe sehr einfach löst. Der einfachste Weg ist die Funktion GET_DDL - Ein Beispiel:
select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;

DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
 (    "EMPNO" NUMBER(4,0),
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9),
      "MGR" NUMBER(4,0),
      "HIREDATE" DATE,
      "SAL" NUMBER(7,2),
      "COMM" NUMBER(7,2),
      "DEPTNO" NUMBER(2,0),
       CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE,
       CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
        REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
Es geht aber noch weiter. Das folgende Beispiel nimmt noch einige Einstellungen vor. So soll das DDL-Skript ...
  • ... ein anderes Schema verwenden (MYSCHEMA)
  • ... ohne Storage-Klausel generiert werden
  • ... einen SQL Terminator enthalten
create or replace function mymetadata return sys.ku$_ddls is
  md_handle number;
  tr_handle number;
  dl_handle number;

  result_array sys.ku$_ddls;
begin
  md_handle := dbms_metadata.open('TABLE');
  -- Transformationen TABLESPACE und SCHEMA
  tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_TABLESPACE', 'USERS', 'EXAMPLE');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'SCOTT', 'MYSCHEMA');

  -- Einstellungen für das DDL
  dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
  dbms_metadata.set_transform_param(dl_handle, 'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);

  -- Welche Objekte?
  dbms_metadata.set_filter(md_handle, 'NAME', 'EMP');

  result_array := dbms_metadata.fetch_ddl(md_handle);
  dbms_metadata.close(md_handle);
  return result_array;
end;
/
Das Ergebnis:
select ddltext from table(mymetadata);

DDLTEXT
---------------------------------------------------------------------------
CREATE TABLE "MYSCHEMA"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "MYSCHEMA"."DEPT" ("DEPTNO") ENABLE
   ) ;
Mit DBMS_METADATA kann die DDL-Generierung beliebig angepasst werden. Auch das Generieren der Metadaten als XML-Dokument ist möglich. Mit XSLT-Stylesheets kann die Ausgabe dann beliebig formatiert werden. HTML, PDF oder andere Formate sind dann kein Problem mehr ...

19. Juli 2007

Bilder in der Oracle-Datenbank: ORDIMAGE

Die Oracle-Datenbank bietet auch einiges an Funktionalität zum Umgang mit Bildern an. So ist der Datentyp OrdImage speziell für die Speicherung von Bildern vorgesehen. Gegenüber einem einfachen BLOB bietet das den Vorteil, dass man Attribute wie die Höhe, die Breite oder die Farbtiefe abgerufen oder einfache Bildoperationen (Skalieren, Umwandeln in Schwarz/Weiss) durchgeführt werden können.
Recht eindrucksvoll ist die Funktion "Metadatenextraktion", die mit nahezu allen Digitalbildern ohne großen Aufwand ausprobiert werden kann (Fast alle Digitalkameras schreiben die sog. EXIF-Metadaten in die Bilddatei). Die Vorgehensweise ist ganz einfach: Zunächst wird ein DIRECTORY-Objekt erzeugt, damit die Datenbank Zugriff auf die Bilddateien hat (Selbstverständlich können diese auch in Tabellen liegen; der Einfachheit halber nutzen wir hier jedoch das Dateisystem).
create directory bilderdir as '/path/to/bilder/directory'
/
Dann können die Metadaten einfach mit der Funktion ORDIMAGE.GETMETADATA wie folgt abgerufen werden. Die Rückgabe erfolgt als XML-Dokument.
select * from table(ordimage.getmetadata(bfilename('BILDERDIR','004.jpg')))
/

<ordImageAttributes xmlns="http://xmlns.oracle.com/ord/meta/ordimage" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/ordimage http://xmlns.oracle.com/ord/meta/ordimage">
  <height>853</height>
  <width>1280</width>
  <contentLength>1184957</contentLength>
  <fileFormat>JFIF</fileFormat>
  <contentFormat>24BITRGB</contentFormat>
  <compressionFormat>JPEG</compressionFormat>
  <mimeType>image/jpeg</mimeType>
</ordImageAttributes>

<exifMetadata xmlns="http://xmlns.oracle.com/ord/meta/exif" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/exif http://xmlns.oracle.com/ord/meta/exif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TiffIfd>
    <Make tag="271">Canon</Make>
    <Model tag="272">Canon PowerShot SD700 IS</Model>
    <Orientation tag="274">top left</Orientation>
    <XResolution tag="282">300</XResolution>
    <YResolution tag="283">300</YResolution>
    <ResolutionUnit tag="296">inches</ResolutionUnit>
    <Software tag="305">Adobe Photoshop CS2 Windows</Software>
    <DateTime tag="306">2007-06-18T14:51:13.000000</DateTime>
    <YCbCrPositioning tag="531">centered</YCbCrPositioning>
  </TiffIfd>
  <ExifIfd tag="34665">
    <ExposureTime tag="33434">.002</ExposureTime>
    <FNumber tag="33437">2.8</FNumber>
    <ExifVersion tag="36864">0220</ExifVersion>
    <DateTimeOriginal tag="36867">2007-06-18T16:21:55.000000</DateTimeOriginal>
    <DateTimeDigitized tag="36868">2007-06-18T16:21:55.000000</DateTimeDigitized>
    <ComponentsConfiguration tag="37121">YCbCr</ComponentsConfiguration>
    <CompressedBitsPerPixel tag="37122">3</CompressedBitsPerPixel>
    <ShutterSpeedValue tag="37377">8.96875</ShutterSpeedValue>
    <ApertureValue tag="37378">2.96875</ApertureValue>
    <ExposureBiasValue tag="37380">0</ExposureBiasValue>
    <MaxApertureValue tag="37381">2.96875</MaxApertureValue>
    <MeteringMode tag="37383">Pattern</MeteringMode>
    <Flash tag="37385">
      <Fired>No</Fired>
      <Return>No strobe return function</Return>
      <Mode>Compulsory suppression</Mode>
      <Function>Yes</Function>
      <RedEyeReduction>No</RedEyeReduction>
    <FocalLength tag="37386">6.63</FocalLength>
    :

12. Juli 2007

Oracle11g: Es geht los ...

Nachdem der offizielle "Launch" von Oracle11g gestern in New York City stattfand, sind heute die ersten technischen Informationen zu Oracle11g im OTN verfügbar. Viel Spaß beim Lesen!

8. Juli 2007

(Auch große) BLOB's ins Dateisystem schreiben ...

Recht häufig bekomme ich die Frage, wie man denn einen BLOB ins Dateisystem schreiben kann. BFILE hilft nicht weiter - die kann man nur lesen, nicht schreiben. Fertige Funktionen in DBMS_LOB oder einem anderen Package gibt es nicht - insofern bleiben nur zwei Möglichkeiten:
  • UTL_FILE
  • Java in der Datenbank
Im folgenden ist ein Beispiel für UTL_FILE dargestellt. UTL_FILE kann nur mit RAW oder VARCHAR2 umgehen, also muss der Blob in "Chunks" von 32.000 Byte geschrieben werden ... ist aber nicht wirklich ein Problem:
... geht natürlich auch mit CLOB; anstelle von UTL_RAW.PUT_RAW muss dann allerdings UTL_FILE.PUT und VARCHAR2 anstelle von RAW verwendet werden ...

3. Juli 2007

Dictionary Abkürzungen: Wenn's mal schnell gehen muss ...

Fast jeder kennt die Abfrage SELECT * FROM TAB; damit sieht man die Tabellen eines Schemas. Solche kurzen Namen für Dictionary Views gibt es nicht nur für Tabellen (die Alternative wäre USER_TABLES), sondern auch für andere Dictionary Views. Manche sind zwar veraltet, andere aber nicht. Man spart beim Tippen einfach Zeit ...
SYNON OWNER                          TABLE_NAME
----- ------------------------------ ------------------------------
TAB   PUBLIC                         TAB
COL   PUBLIC                         COL
CAT   PUBLIC                         USER_CATALOG
CLU   PUBLIC                         USER_CLUSTERS
IND   PUBLIC                         USER_INDEXES
OBJ   PUBLIC                         USER_OBJECTS
SEQ   PUBLIC                         USER_SEQUENCES
SYN   PUBLIC                         USER_SYNONYMS

Beliebte Postings