21. Oktober 2011

Ab morgen: DOAG2011

Bis zur DOAG2011 ist es ja nicht mehr solange hin - ich werde an allen drei Tagen dort sein und freue mich schon darauf, das eine oder andere Gesicht aus der Oracle-Community wiederzusehen ... Wie in den letzten Jahren habe ich auch dieses Jahr wieder ein paar Vorträge - hier sind sie ...
  • Demo-Kino: Transformers 4.1: Von Standard nach Cool
    15.11.2011 10:00 - 10:45 Uhr - Foyer Tokio

    Mit nur wenig Aufwand kann man einer APEX-Applikation moderne Elemente hinzufügen. Eine eher langweilige Geschäftsanwendung wird im Film transformiert - Elemente wie "Google Suggest", Karten, Volltextsuche und moderne Layoutelemente kommen hinzu. Die neue Anwendung holt den Nutzer dort ab, wo er steht, mit Anwendungsfeatures, die er aus dem Internet kennt ...

  • Das Navi in der Datenbank: Oracle11g has NAVTEQ on Board
    16.11.2011 10:00 - 10:45 Uhr - Raum Tokio

    Wussten Sie schon, dass Sie eine postalische Adresse mit der Oracle-Datenbank mit einer einfachen SQL-Abfrage in Koordinaten, also Längen- und Breitengrade, umwandeln können. Mit der Geocoding-Engine ist das überhaupt kein Problem - und durch die enge Integration mit der Datenbank kann dies Teil der normalen Geschäftslogik werden.
    Basis dafür sind NAVTEQ-Daten - Oracle has NAVTEQ on Board. Der Vortrag zeigt, was das NAVTEQ ODF Dataset (Oracle Data Format) beinhaltet, wie man es installiert und wie man damit nicht nur Geocoding, sondern auch Routing oder andere Operationen durchführen kann (diesen Vortrag mache ich gemeinsam mit Till Kreiler von NAVTEQ).

  • SQL oder NoSQL? Das ist hier die Frage! Die "Oracle NoSQL Datenbank"
    16.11.2011 14:00 - 14:45 Uhr - Raum Tokio

    Dieses Thema finde ich ziemlich spannend - ein wenig habe ich schon mit der neuen Oracle NoSQL Database herumgespielt - und ich muss sagen: Das ist wirklich ein neuer Ansatz - und auf die Reaktionen und Diskussionen bin ich auch schon sehr gespannt ...

    Bekannte Webseiten wie Twitter, Facebook oder URL-Verkürzer wie tinyurl.com setzen NoSQL-Datenbanken ein - und auf der OOW wurde jüngst die Oracle NoSQL Database angekündigt. Der Vortrag stellt die Oracle NoSQL Database vor, geht auf Unterschiede zwischen NoSQL-Datenbanken und den "klassischen" RDBMS ein und stellt mögliche Einsatzszenarien vor. NoSQL-Datenbanken werden genutzt, um extrem große Datenmengen extrem vieler User zu speichern. Im Gegensatz zu unserer "klassischen" Oracle-Datenbank spielen das Datenmodell, Transaktionskonzepte und Datenintegrität keine Rolle - das einzige, was zählt, ist die wirklich ständige Verfügbarkeit, die mit massiver Parallelisierung erreicht wird. Eine NoSQL-Datenbank richtet sich an Entwickler, denn es gibt keine Abfragesprache und keinen Query-Optimizer. Zugriffe erfolgen über eine Programmierschnittstelle (API) - alle Intelligenz steckt in der Anwendung.

  • apex.meinefirma.de: APEX Hosting im eigenen Unternehmen
    17.11.2011 13:00 - 13:45 Uhr - Raum Istabul

    Nahezu überall ändern sich Prozesse und Anforderungen immer schneller: gebraucht wird also eine Plattform, welche die Entwicklung neuer Anwendungen genauso schnell ermöglicht. Der öffentliche Demoserver "apex.oracle.com" zeigt, wie es geht: Entwickler können Ihre Workspaces selbstständig beantragen und verwalten, ohne manuelles des DBA Eingreifen sofort aktiv werden; neue Anwendungen stehen sofort bereit. Der Vortrag zeigt, wie ein Server "apex.meinefirma.de" aufgesetzt und betrieben werden kann, welche Hardware man braucht, wie Ressourcenkonflikte gelöst werden und worauf geachtet werden sollte. Das ist Cloud Computing "in Action".

Auf der ODTUG Kscope-Konferenz, an der ich im Juni teilgenommen hatte, wurde Twitter ganz massiv genutzt - Tweets über die einzelnen Vorträge und neue Ideen kamen quasi im Sekundentakt. Ich bin gespannt, wie das auf der DOAG2011 läuft. Meine Teamkollegen und ich werden euch über den Twitter Account @OracleBUDB und natürlich mit dem Hashtag #doag2011 auf dem Laufenden halten ... also dranbleiben ...
This is a posting about the DOAg2011 conference and therefore in german only.

4. Oktober 2011

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

From Java to SQL: Java objects and the database JVM
In der Vergangenheit hatte ich ja schon einige Blog-Postings zum Thema "Java in der Datenbank"; das letzte Posting zum Thema Twitter-Postings mit der Datenbank, die diversen Postings zum Thema Dateisystem-Zugriffe haben alle eines gemein: Sie verwenden die in der Datenbank enthaltene Java-Engine. Wenn es aber darum geht, Java-Funktionen aus PL/SQL oder SQL heraus zu nutzen, ist immer auch eine Parameter-Mapping gefragt. Und genau dem möchte ich mich heute widmen. Dabei geht es mir aber nicht um das Abbilden eines VARCHAR2, NUMBER oder DATE auf Java-Datentypen - das ist sehr einfach, wie man hier sehen kann ...
create or replace and compile java source named simple_test as

public class SimpleTest {
  public static String getString() {
    return "Hallo Welt";
  }
  public static java.sql.Timestamp getDate() {
    return new java.sql.Timestamp(new java.util.Date().getTime());
  }
  public static int getNumber() {
    return 4711;
  }
} 
/
sho err

create or replace package simple_test_plsql is
  function get_string return varchar2;
  function get_date return date;
  function get_number return number;
end simple_test_plsql;
/
sho err

create or replace package body simple_test_plsql is
  function get_string return varchar2 as 
  language java name 'SimpleTest.getString() return java.lang.String';
  function get_date return date as
  language java name 'SimpleTest.getDate() return java.sql.Timestamp';
  function get_number return number as
  language java name 'SimpleTest.getNumber() return int';
end simple_test_plsql;
/
sho err

select 
  simple_test_plsql.get_string,
  simple_test_plsql.get_date,
  simple_test_plsql.get_number
from dual
/

GET_STRING           GET_DATE            GET_NUMBER
-------------------- ------------------- ----------
Hallo Welt           21.09.2011 09:19:40       4711
Den Experten ist sicher schon aufgefallen, dass die Java-Methoden alle "static" sind; das ist auch logisch so, denn PL/SQL ist ja keine objektorientierte Sprache. Mit "statischen" Java-Methoden wird Java wie eine prozedurale Sprache genutzt - es entspricht eher dem Konzept von PL/SQL, daher können nur statische Java-Methoden auf PL/SQL-Pakete, Prozeduren oder Funktionen abgebildet werden.
Das einzige, wo man ein wenig aufpassen muss, sind DATE-Mappings - diese können auf die Klassen java.sql.Date und java.sql.Timestamp abgebildet werden. In Java bedeutet das aber etwas anderes als in SQL. Arbeitet man beim Mapping mit java.sql.Date, dann werden nur Tag, Monat und Jahr an die SQL-Ebene zurückgegeben - also das Datum - möchte man die Uhrzeit haben, sollte man mit java.sql.Timestamp arbeiten. Aber abgesehen davon ist das Mapping solcher Datentypen ja wirklich einfach - und wenn man Java-Bibliotheken in der Datenbank verwendet, sollte man sich am besten stets eine eigene "Schicht" mit Java-Methoden schreiben, die einfach auf SQL und PL/SQL abzubilden sind - wo die Methodensignaturen also am besten nur solche einfachen Datentypen nutzen und keine komplexen Objekte.
Aber jetzt geht's ans Eingemachte: Angenommen, wir haben eine Java-Bibliothek (und als Beispiel nehmen wir mal java.io.File), die ein komplexes Objekt repräsentiert. Und wir möchten nun eben nicht für jedes Attribut einen eigenen Call bauen, sondern alle relevanten Attribute mit einem einzigen Call abholen. Uns prinzipiell gibt es in der Datenbank ja auch Objekttypen bzw. User Defined Types, mit denen man sowas wie ein "File" modellieren kann. Also fangen wir mal damit an.
create type file_t as object(
  file_path      varchar2(4000),
  file_name      varchar2(4000),
  file_size      number,
  last_modified  date,
  is_dir         char(1),
  is_writeable   char(1),
  is_readable    char(1),
  file_exists    char(1)
)
/
Allerdings kann man nun keine direkte Verbindung zwischen dem SQL-Typen FILE_T und der Java-Klasse java.io.File herstellen - beide haben ja überhaupt nichts miteinander zu tun - ein Mapping ist aber mit Hilfe von einer "Java-Brückenklasse" möglich: Mit oracle.sql.STRUCT kann man Objekttypen aus Java heraus erstellen und an SQL bzw. PL/SQL zurückgeben. Was wir also brauchen, ist eine (statische) Java-Methode, die mit Hilfe von java.io.File die Information zu einer Datei holt, damit ein STRUCT für den FILE_T erzeugt und diese kann dann in die SQL-Ebene zurückgibt.
create or replace and compile java source named java_file as
import java.math.*;
import java.util.*;
import java.io.*;

import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JavaFile {
  public static STRUCT getFile(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    Object[]         o      = new Object[8];

    File f = new File(pFile);
    if (f.exists()) {
     o[0] = f.getPath();
     o[1] = f.getName();
     o[2] = new BigDecimal(f.length());
     o[3] = new java.sql.Timestamp(f.lastModified());
     o[4] = (f.isDirectory()?"Y":"N");
     o[5] = (f.canWrite()?"Y":"N");
     o[6] = (f.canRead()?"Y":"N");
     o[7] = "Y";
     return new STRUCT(sDescr, con, o);
    } else {
     return null;
    } 
  }
}
/
sho err

create or replace function get_file_atts (p_file in varchar2) return file_t
is language java name 'JavaFile.getFile(java.lang.String) return oracle.sql.STRUCT';
/
sho err
Hier muss der Java-Code mit der SQL-Ebene zusammenspielen - deshalb wird zuerst eine JDBC-"Verbindung" aufgebaut - das ist aber nichts weiter als eine Art "Pointer" auf die Datenbanksitzung, denn das Java läuft ja bereits in der Datenbank. Es wird ein StructDescriptor-Objekt erzeugt, welches auf den vorher erzeugten Typen FILE_T zeigt. Alle Attribute des FILE_T werden auf Java-Seite in einem Array der Klasse Object[] abgelegt. Hier muss man als Entwickler aufpassen, dass die verwendeten Java-Typen auf die Datentypen des SQL-Typen passen (siehe einfache Mappings oben). Mit diesem Array, dem StructDescriptor-Objekt und dem Connection-Objekt wird dann ganz zum Schluß ein STRUCT-Objekt generiert, welches genau auf den Typen FILE_T passt. Natürlich kann man auch komplexere Dinge bauen und ein "STRUCT in ein STRUCT" schachteln, man muss nur aufpassen, dass alles zur Definition der Objekttypen in SQL passt.
Zum Abschluß kommt wieder die PL/SQL-Definition der Funktion - in PL/SQL wird als IN Parameter ein VARCHAR2 und als Rückgabewert ein FILE_T deklariert. Folgerichtig passt das auf ein java.lang.String als Eingabe- und ein oracle.sql.STRUCT als Rückgabewert. Alle Objekttypen werden auf Java-Seite als oracle.sql.STRUCT abgebildet - die Verknüpfung mit dem konkreten Objekttypen erledigt der StructDescriptor ...
Alles klar? Dann können wir testen ...
SQL> select get_file_atts('/') from dual;
select get_file_atts('/') from dual
                               *
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.io.FilePermission /
read) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '/', 'read'
)
Ach ja: Zum Dateisystemzugriff braucht es Privilegien - die muss der DBA einräumen. Wenn Ihr vor diesen Meldungen Ruhe haben wollt, gebt eurem Datenbankschema das JAVASYSPRIV-Privileg, dann har er alle Rechte, die man haben kann (für Produktion nicht zu empfehlen). Alternativ könnt Ihr einfach den in der Fehlermeldung dargestellten Aufruf ausführen - der räumt genau das fehlende Privileg ein. Wenn Ihr das Privileg habt, probiert es nochmal ...
SQL> select get_file_atts('/tmp') from dual;

GET_FILE_ATTS('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR, IS
--------------------------------------------------------------------------------
FILE_T('/tmp', 'tmp', 126976, '21.09.2011 10:25:22', 'Y', 'Y', 'Y', 'Y')

1 Zeile wurde ausgewählt.
So weit so gut. Wir haben es also geschafft, ein strukturiertes Objekt von Java nach PL/SQL zu übertragen. Analog dazu kann man nun für alle Objekte vorgehen:
  • SQL-Objekttypen erzeugen
  • Java Methode erzeugen, die das eigentliche Java-Objekt auf eine STRUCT-Instanz abbildet, dabei mit dem StructDescriptor und dem Object[]-Array arbeiten
  • STRUCT-Instanz aus Java zurückgeben und in SQL übernehmen
  • PL/SQL Wrapper für die neue Java Stored Procedure erstellen
Bleibt die nächste (und im Datenbankumfeld spannende) Aufgabe: Ich möchte ein Directory-Listing abbilden, also eine ganze Reihe von strukturierten Objekten zurückgeben. Im reinen PL/SQL geht haben wir hierfür die Table Functions - und ein ähnliches Konzept nutzen wir auch in Java. Zunächst brauchen wir, wie bei der PL/SQL Table Function, einen Objekttypen, der die Dateiliste repräsentiert - das ist einfach:
create type file_ct as table of file_t
/
Und wieder gilt es, aus Java heraus eine Instanz dieses Typs FILE_CT zu erzeugen. Für Varray- oder Table Types gibt es jedoch eine andere Java-"Brückenklasse": oracle.sql.ARRAY. Der Umgang damit ist aber ganz ähnlich wie bei der Klasse oracle.sql.STRUCT. Es wird ein ArrayDescriptor-Objekt benötigt, der die Verbindung zum konkreten SQL-Typen herstellt, und mit diesem, dem Connection-Objekt und einem Standard-Java-Array wird die Instanz vom Typ oracle.sql.ARRAY erzeugt, die genau auf den FILE_CT passt. Das ganze als Code ...
public class JavaFile {
  public static ARRAY getFileList(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    ArrayDescriptor  aDescr = ArrayDescriptor.createDescriptor("FILE_CT", con);
    Object[]         o      = new Object[8];

    /* Array containing java File objects */
    File[]   f = new File(pFile).listFiles();

    /* Array containing SQL STRUCT objects */
    STRUCT[] a = new STRUCT[f.length];

    /* now loop through the File array and create a STRUCT instance for each file */
    for (int i=0;i<f.length;i++) {
     o[0] = f[i].getPath();
     o[1] = f[i].getName();
     o[2] = new BigDecimal(f[i].length());
     o[3] = new java.sql.Timestamp(f[i].lastModified());
     o[4] = (f[i].isDirectory()?"Y":"N");
     o[5] = (f[i].canWrite()?"Y":"N");
     o[6] = (f[i].canRead()?"Y":"N");
     o[7] = "Y"
     a[i] = new STRUCT(sDescr, con, o);
    } 
    /* Create and return the ARRAY object which maps to the SQL type */
    return new ARRAY(aDescr, con, a);
  }
}
/
sho err

create or replace function get_file_list (p_file in varchar2) return file_ct
is language java name 'JavaFile.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
sho err
Und das ist im Grunde genommen nur eine Erweiterung obigen Codes. Durch die Liste der File-Objekte, die von java.io.File.listFiles() zurückgegeben wird, laufen wir in einer Schleife durch, erzeugen für jedes File-Objekt eine STRUCT-Instanz und packen auch diese in ein Array. Das wird dann mit dem ArrayDescriptor auf den SQL-Typen FILE_CT abgebildet und als oracle.sql.ARRAY-Objekt zurückgegeben. Fertig - Test.
SQL> select file_name, last_modified, file_size from table(get_file_list('/'))

FILE_NAME                 LAST_MODIFIED        FILE_SIZE
------------------------- ------------------- ----------
wget-log                  27.04.2011 16:24:02        496
boot                      02.02.2011 13:22:58       1024
misc                      25.07.2011 09:29:09          0
stage                     04.04.2011 11:27:57          7
lib                       15.07.2011 11:06:27       4096
etc                       25.07.2011 09:29:07       4096
root                      15.07.2011 11:14:01       4096
bin                       02.02.2011 13:28:45       4096
:                         :                            :
Eigentlich ganz einfach, oder? Mit dem hier vorgestellten lässt sich nun jede beliebige Java-Bibliothek in der Datenbank nutzen - die einzige Voraussetzung ist, dass sie keinen Native-Code verwendet - nur 100%-Java-Bibliotheken laufen in der Datenbank. Zum Ansprechen aus SQL und PL/SQL überlegt man sich dann eine vernünftige Schnittstelle, erzeugt die passenden Java-Klassen, die entweder einfache Datentypen (String, Date, numerische) oder komplexe Datentypen als STRUCT oder ARRAY zurückgeben. Diese werden dann auf SQL-Ebene durch PL/SQL-Packages und Objekttypen repräsentiert. Die Code-Packages zum Dateisystem-Zugriff, zum Umgang mit einem POP3- oder IMAP-Mailserver oder zum Ein- und Auspacken von ZIP-Archiven arbeiten alle genau so - und wie gesagt: Jede andere Java-Bibliothek lässt sich genauso einbinden. Damit gibt es keine Grenzen in der Oracle-Datenbank.
In the past I had quite a number of postinhs in which I made use of the Java engine within the Oracle Database. Examples are the previous posting about automated tweets from PL/SQL (which was about using "twitter4j" in the database) or the postings about File- and operating system access. Today I'd like to elaborate a bit about a fundamental thing which one has to accomplish when using Java in the database: The parameter mapping. This posting is about how to map input or return paramaters in a java method to SQL and PL/SQL types in a package. And the focus will not be on the simple mapping of VARCHAR2, NUMBER or DATE datatypes ... these are rather simple, as we can see with this code example ...
create or replace and compile java source named simple_test as

public class SimpleTest {
  public static String getString() {
    return "Hello World";
  }
  public static java.sql.Timestamp getDate() {
    return new java.sql.Timestamp(new java.util.Date().getTime());
  }
  public static int getNumber() {
    return 4711;
  }
} 
/
sho err

create or replace package simple_test_plsql is
  function get_string return varchar2;
  function get_date return date;
  function get_number return number;
end simple_test_plsql;
/
sho err

create or replace package body simple_test_plsql is
  function get_string return varchar2 as 
  language java name 'SimpleTest.getString() return java.lang.String';
  function get_date return date as
  language java name 'SimpleTest.getDate() return java.sql.Timestamp';
  function get_number return number as
  language java name 'SimpleTest.getNumber() return int';
end simple_test_plsql;
/
sho err

select 
  simple_test_plsql.get_string,
  simple_test_plsql.get_date,
  simple_test_plsql.get_number
from dual
/

GET_STRING           GET_DATE            GET_NUMBER
-------------------- ------------------- ----------
Hello World          21.09.2011 09:19:40       4711
The "experts" might have notices that all java methods are "static" - that's evident. PL/SQL is a procedural, not an object-oriented language - so when we want to integrate PL/SQL with Java in the database we need to use the "procedural part" of Java which are static class methods. If you want to map java methods to procedures and functions in a PL/SQL package you must use static methods for that.
When mapping DATE values from Java to PL/SQL a bit attention is needed. Basically a date can be represented in java using java.sql.Date or java.sql.Timestamp. When those are being mapped to DATE in SQL or PL/SQL, the java.sql.Date class only maps Day, Month and Year - the time in the DATE would be set to midnight. For having the time component also you need to use java.sql.Timestamp. Apart from this the mapping of simple scalar datatypes from Java to SQL and PL/SQL is quite simple. So the first rule is to use simple types whenever possible. Complex objects should only be used when they're really needed ... because, as we will see, they require additional coding ...
And now we'll handle these: Let's assume we have a Java library doing some stuff and for this example we use the java.io.File class (which is part of standard Java). We'd like to access file attributes with SQL functions and we don't want to have a single call for each attribute. We need a SQL function which collects all attributes in one call - so we need a datatype containing all these attributes at the SQL side. We have Object types (or User-Defined-Types) for that purpose so the first thing we want to do is to create an object type representing a file.
create type file_t as object(
  file_path      varchar2(4000),
  file_name      varchar2(4000),
  file_size      number,
  last_modified  date,
  is_dir         char(1),
  is_writeable   char(1),
  is_readable    char(1),
  file_exists    char(1)
)
/
So far we have a Java representation for a file: java.io.File - and we have a SQL representation for a file: Our new FILE_T. But there is absolutly no connection between those. Of course, we cannot map the java.io.File class to our FILE_T type - the database has no clue how to map the attributes. We need to build a "bridge" between java.io.File and FILE_T. And this bridge is a special java class: oracle.sql.STRUCT. So we now need to implement a static Java method (we can only use static methods) which uses java.io.File to collect file attributes and builds a oracle.sql.STRUCT instance which can be mapped to FILE_T. This code goes here ...
create or replace and compile java source named java_file as
import java.math.*;
import java.util.*;
import java.io.*;

import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JavaFile {
  public static STRUCT getFile(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    Object[]         o      = new Object[8];

    File f = new File(pFile);
    if (f.exists()) {
     o[0] = f.getPath();
     o[1] = f.getName();
     o[2] = new BigDecimal(f.length());
     o[3] = new java.sql.Timestamp(f.lastModified());
     o[4] = (f.isDirectory()?"Y":"N");
     o[5] = (f.canWrite()?"Y":"N");
     o[6] = (f.canRead()?"Y":"N");
     o[7] = "Y";
     return new STRUCT(sDescr, con, o);
    } else {
     return null;
    } 
  }
}
/
sho err

create or replace function get_file_atts (p_file in varchar2) return file_t
is language java name 'JavaFile.getFile(java.lang.String) return oracle.sql.STRUCT';
/
sho err
The Java engine within the database needs to interact with the SQL layer - we need a JDBC database connection for that. This connection is not more than kind of a pointer to the actual database session in which the java code runs in. The StructDescriptor class is a utility which helps us to create a STRUCT object exactly matching FILE_T. Note the usage of the connection object and "FILE_T" when the StructDescriptor instance is being created. After that we collect all relevant file attributes in an Array of the fundamental Java class Object[]. The developer needs to take care about the order within that array: Our object type has eight simple, scalar attributes. The Java types in the Object array (String, int, java.sql.Date) must match the attributes of the object type (VARCHAR, NUMBER, DATE). With this array, the StructDescriptor instance, and the database connection the STRUCT instance is being created and returned in the last step of the program. This STRUCT instance exactly matches the FILE_T definition in the SQL layer.
The final step as (as always) the PL/SQL Wrapper for the static method. We create a function which takes a VARCHAR2 (containing the file path) and returns a FILE_T. These are being mapped to java.lang.String and oracle.sql.STRUCT . All object types are being encoded as oracle.sql.STRUCT - the StructDescriptor objects cares for the mapping to the correct SQL type.
Got it ...? Then try it!
SQL> select get_file_atts('/') from dual;
select get_file_atts('/') from dual
                               *
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.io.FilePermission /
read) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '/', 'read'
)
Ah, yes: You need Java privileges in order to access the file system with a Java stored porcedure. In the developing stage you might grant yourself the JAVASYSPRIV privilege - you will be a "Java superuser" then - on production systems this is not recommended you might grant individual privileges there. The practical bit is that Oracle not only throws the error message - it also gives the complete PL/SQL call to grant the required permission. So when you have the privilege, try again ...
SQL> select get_file_atts('/tmp') from dual;

GET_FILE_ATTS('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR, IS
--------------------------------------------------------------------------------
FILE_T('/tmp', 'tmp', 126976, '21.09.2011 10:25:22', 'Y', 'Y', 'Y', 'Y')

1 row selected.
So far - so good. We managed to create a structured object in java and pass it to PL/SQL. You can use this approach for any kind of structured object - and yes: You can nest one oracle.sql.STRUCT within another oracle.sql.STRUCT - just as you can nest object types in each other. The basic steps are:
  • Create the SQL object types
  • Create the static Java method which creates the STRUCT instance matching the object type using the Object[] array and the StructDescriptor class.
  • Return the STRUCT instance from java to SQL and PL/SQL
  • Create the PL/SQL wrapper for your Java stored procedure
Then we'll move on to the next (and more interesting) level: We want to pass not only a structured object but a list of structured objects from Java to PL/SQL. In the PL/SQL world we have Table Functions for that purpose - and as with these we now first create another object type representing our list of files ...
create type file_ct as table of file_t
/
And again: We now need to create an object within Java which maps to this FILE_CT type. But for Varray or table types there is another "Bridging class": oracle.sql.ARRAY. It's used the same way as oracle.sql.STRUCT. We first create an ArrayDescriptor instance using the JDBC connection and the type name. This helper object, the JDBC connection and a plain Java array are being used to create the actual instance of oracle.sql.ARRAY. And the code goes here ...
public class JavaFile {
  public static ARRAY getFileList(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    ArrayDescriptor  aDescr = ArrayDescriptor.createDescriptor("FILE_CT", con);
    Object[]         o      = new Object[8];

    /* Array containing java File objects */
    File[]   f = new File(pFile).listFiles();

    /* Array containing SQL STRUCT objects */
    STRUCT[] a = new STRUCT[f.length];

    /* now loop through the File array and create a STRUCT instance for each file */
    for (int i=0;i<f.length;i++) {
     o[0] = f[i].getPath();
     o[1] = f[i].getName();
     o[2] = new BigDecimal(f[i].length());
     o[3] = new java.sql.Timestamp(f[i].lastModified());
     o[4] = (f[i].isDirectory()?"Y":"N");
     o[5] = (f[i].canWrite()?"Y":"N");
     o[6] = (f[i].canRead()?"Y":"N");
     o[7] = "Y"
     a[i] = new STRUCT(sDescr, con, o);
    } 
    /* Create and return the ARRAY object which maps to the SQL type */
    return new ARRAY(aDescr, con, a);
  }
}
/
sho err

create or replace function get_file_list (p_file in varchar2) return file_ct
is language java name 'JavaFile.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
sho err
As you see: This code is basically just a small extension of the code above. We loop through the File[] array returned by java.io.File.listFiles(), create an oracle.sql.STRUCT instance for each java.io.File object and place it into a Java array (STRUCT[]). This java array is then being used to create the oracle.sql.ARRAY instance, which is finally passed back to PL/SQL. On the PL/SQL side we create the wrapper function and that's it. Try ...
SQL> select file_name, last_modified, file_size from table(get_file_list('/'))

FILE_NAME                 LAST_MODIFIED        FILE_SIZE
------------------------- ------------------- ----------
wget-log                  27.04.2011 16:24:02        496
boot                      02.02.2011 13:22:58       1024
misc                      25.07.2011 09:29:09          0
stage                     04.04.2011 11:27:57          7
lib                       15.07.2011 11:06:27       4096
etc                       25.07.2011 09:29:07       4096
root                      15.07.2011 11:14:01       4096
bin                       02.02.2011 13:28:45       4096
:                         :                            :
It's quite simpe, isn't it? Using this approach you can pass any data structure between Java and PL/SQL - you can use any Java library you want. The only prerequisite is that your Java library does not depend on native code - it must be pure Java. First you might design a simple interface consisting of static Java methods which can be easily mapped to PL/SQL calls. Use simple, scalar types whenever possible and use object types and arrays only when you really need them. Then you need to implement the static java methods (using STRUCT and ARRAY, if necessary), create the SQL object types and PL/SQL wrapper packages and you are done. The code packages for filesystem access, zum accessing POP3 or IMAP4 mail servers with PL/SQL or topack and unpack ZIP archives all work that way. And as said: You can use integrate any Java library doing interesting stuff into the Oracle database. There are no limits.

Beliebte Postings