21. Dezember 2007

Kurz vor Weihnachen noch ein Tipp ...

English title: Just before Christmas one tip for application developers ...

Heute (kurz vor Weihnachten) noch das für den Anwendungsentwickler recht wichtiges Kommando der Oracle-Datenbank. Auf der Entwicklungsumgebung sollte man es (als SYS) ruhig absetzen:
Today (just before christmas) I have an important Oracle command for application developers: I'd recommand to issue this on a development environment as SYS
SQL> shutdown immediate
In der Produktionsumgebung vielleicht eher nicht ... für den Anwendungsentwickler ist es allerdings - gerade jetzt - eine hervorragende Gelegenheit, in der kurzen "staden Zeit" zu entspannen und neue Ideen zu sammeln. In diesem Sinne wünsche ich euch und euren Lieben ...

Frohe Weihnachten und einen guten Rusch ins neue Jahr 2008

... und dann geht's hier auch weiter ...
It's not a good idea for the production environment ... but for the application developer this is now a good opportunity to "step back" for a short while, relax and start again with many creative ideas in the next year. And with this in mind I wish every reader ...

Merry Christmas and a happy new year 2008 for you and your family

... and in 2008 I'll continue with the next post ...

17. Dezember 2007

Relationale Daten als XML per FTP abrufen: Und das nur mit der Datenbank

English title: Get relational data as XML via FTP: Just with the database!

Heute befassen wir uns nochmal mit dem Thema XML. Vor einiger Zeit habe ich ja schon ein wenig über die Methoden zum Generieren von XML geschrieben - heute wenden wir das ganze praktisch an. Wir erzeugen mit den Inhalten der Tabellen DEPT und EMP für jedes Department ein XML-Dokument (mit den SQL/XML Funktionen) und stellen diese direkt als FTP-Download bereit. Wie im letzten Post beschrieben, kann man Pointer (REF) auf Zeilen einer Tabelle OF XMLTYPE erzeugen und mit diesen Pointern Einträge im virtuellen Dateisystem anlegen.
Heute machen wir das wieder genauso, nur wird heute keine TABLE OF XMLTYPE erstellt, sondern eine VIEW OF XMLTYPE.
Today we'll focus again on XML: Some time ago I wrote something about the different methods for generating XML in the Oracle database (The post is here, but it's not translated so far). Now we'll use it in practice: Based on the well-known tables EMP and DEPT we'll create an XML document for each Department using the SQL/XML functions. These "virtual" XML documents will then be made available for download via FTP. As described in the previous post one can create pointers (REF's) on rows in a table OF XMLTYPE and with these pointers entries in the virtual filesystem (the "XML DB Repository") can be created.
Today we'll do the same with just one little difference: We'll not create a TABLE OF XMLTYPE but a VIEW OF XMLTYPE
create view V_DEPT_XML of XMLTYPE
with object oid (extract(sys_nc_rowinfo$, '/department/@id').getnumberval())
as
select 
 XMLElement("department",
  XMLAttributes(d.deptno as "id"),
  XMLElement("name", d.dname),
  XMLElement("location", d.loc),
  XMLElement("employees",
   (
    select 
     XMLAgg(
      XMLElement("employee", 
       XMLAttributes(e.empno as "id"),
       XMLElement("name", e.ename),
       XMLElement("hiredate", e.hiredate),
       XMLElement("payment-info",
        XMLForest(e.sal as "salary", e.comm as "commission")
       )
      )
     )
     from emp e where e.deptno = d.deptno
   )
  )
 )
from dept d
/
Von besonderer Bedeutung ist die zweite Zeile WITH OBJECT OID .... Da dies eine View und keine Tabelle ist, kann auch die Objekt-ID nicht automatisch generiert werden - vielmehr muss man der Datenbank sagen, wie ein "Objekt" (hier: ein XML-Dokument) identifiziert werden soll bzw. wie die Objekt-ID aus dem XML-Dokument abgeleitet werden soll. In diesem Beispiel wird die Objekt-ID aus der DEPTNO abgeleitet.
Nun schauen wir wiederum im Data Dictionary nach den Spalten der View
The second line WITH OBJECT OID ... in the above code is very important. Since this is a view and not a table, the object id cannot be generated automatically - the user has to tell the database how the objects (the generated XML documents) are identified and therefore how to derive the object id from the XML content. In this example the object id is derived from the DEPTNO column.
Now we look into the data dictionary to get information about the view columns.
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='V_DEPT_XML'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
SYS_NC_ROWINFO$                XMLTYPE                        NO  NO
SYS_NC_OID$                    RAW                            NO  YES

2 Zeilen ausgewählt.
Die View hat zwei Spalten: Die Spalte SYS_NC_ROWINFO$ enthält das generierte XML Dokument, die Spalte SYS_NC_OID$ die Objekt-ID. Das entspricht in etwa dem Aufbau der TABLE OF XMLTYPE (letzter Post), diese hatte nur eine zusätzliche, versteckte Spalte mit den eigentlichen XML-Daten - da wir nun eine View haben, wird diese nicht benötigt. Nun kann man sich die Pointer (REF) ausgeben lassen.
This view has two columns: SYS_NC_ROWINFO$ is for the generated XML document, SYS_NC_OID contains the (generated) object id. This is similar to a TABLE OF XMLTYPE (previous post); the table had another additional column for the XML data itself - since this is a view such a column is not needed. Now we can get the pointers (REF) to the virtual XML documents.
SQL> select ref(e) from v_dept_xml e;

REF(E)
--------------------------------------------------------------------------------
00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C10B0000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C1150000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C11F0000000000000000000000000000000000000000

00004A038A0046417A36A09945F1DBE040A8C08C0211B20000001426010001000100290000000000
090602002A00078401FE0000000A02C1290000000000000000000000000000000000000000
Nun erzeugen wir mit folgendem PL/SQL-Code die Einträge im virtuellen Dateisystem:
Now we'll create the virtual files with the following PL/SQL code:
declare
  v_res boolean;
begin
  for i in (
    select 
      ref(e) as reference,
      extractvalue(object_value, '/department/@id') as id
    from v_dept_xml e
  ) loop
    v_res := dbms_xdb.createresource(
      abspath => '/public/department-'||i.id||'.xml',  
      data    => i.reference,                    
      sticky  => true                     
    );
  end loop;
end;
/
sho err
  
commit
/
Anschließend sieht es im Ordner /public des virtuellen Dateisystems in etwa so aus (wenn der FTP-Zugriff noch nicht funktioniert, spielt als SYS das Skript $ORACLE_HOME/rdbms/admin/catxdbdbca.sql ein):
After running this code the folder /public in the virtual filesystem (if FTP does not work, run the script $ORACLE_HOME/rdbms/admin/catxdbdbca.sql as SYS) looks like the following
220- vmware.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 vmware.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-10.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-20.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-30.xml
-rw-r--r--   1 SCOTT    oracle         0 DEC 17 11:16 department-40.xml
226 ASCII Transfer Complete
ftp: 292 bytes received in 0,00Seconds 292000,00Kbytes/sec.
ftp>
Laden Sie nun eine Datei herunter und öffnen Sie diese mit einem Editor - sie sehen das von der View erzeugte XML-Dokument. Und jetzt kommt's: Ändern Sie mal mit einem SQL UPDATE die Tabellen EMP bzw. DEPT (COMMIT nicht vergessen) und laden Sie das XML-Dokument nochmal herunter - Sie bekommen sofort aktuelle Daten per FTP ... und das alles mit den Bordmitteln der Datenbank!
Download a file, open it using a text editor and you'll see the XML document generated by the view. And this is the trick: Issue a SQL UPDATE command for the table EMP or DEPT and download the same file again (don't forget the COMMIT. You'll always get the most current data via FTP ... and all this without additional tools or software - just with the database!

16. Dezember 2007

English ... German ... Deutsch .. . Englisch

In der Vergangenheit habe ich immer häufiger festgestellt, dass auch Nachfrage nach den Posts in Englisch besteht. Daher habe ich mir vorgenommen, künftig eine übersetzte Version anzubieten: Ihr seht die Posts (soweit übersetzt) in Englisch, wenn Ihr an die URL ein lang=en anhängt. Probiert es aus: Englisch - Deutsch
During the last months I recogized some demand for english versions of my posts. So I plan to meet this requirement and provide translated versions. You get the english versions (if available) by appending "lang=en" to the Blog or Post URL ("lang=de" gets you the german versions, of course). Try it: English - German

7. Dezember 2007

TABLE OF XMLTYPE ... oder "normale" Spalte: Was ist der Unterschied ...?

English title: TABLE OF XMLTYPE ... or "normal" column: What's the difference?

This post is about a "very special" Oracle XML feature: Most people know that there are two ways to create a table for XML documents
Heute geht es wieder mal um das Thema XML und dabei um eine "Spezialität" der objektrelationalen Features in Oracle. Schaut man sich in der Dokumentation um, dann kann man feststellen, dass man eine XML-Tabelle auf zweierlei Art und Weise anlegen kann:
  1. CREATE TABLE XMLTAB1 (xml_document XMLTYPE);
  2. CREATE TABLE XMLTAB2 OF XMLTYPE;
Beyond the fact that the latter variant allows only one column: What's the difference ...?
Mal abgesehen davon, dass man bei der zweiten Variante keine weiteren Spalten mehr angeben kann: Was ist eigentlich der Unterschied ...?
The documentation of the object-relational features states that the first table stores the XML documents as column objects, the second table as row objects. But what are the consequences ...? To find out more we dig into the oracle data dictionary:
Wenn man in die Dokumentation zu den objektrelationalen Features, dann stellt man fest, dass die XML-Dokumente im ersten Fall als Column Objects und im zweiten Fall als Row Objects gespeichert werden. Aber was bedeutet das nun im Detail ...? Dazu sehen wir uns ein wenig im Data Dictionary um:
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='XMLTAB1'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
XML_DOCUMENT                   XMLTYPE                        YES NO
SYS_NC00002$                   CLOB                           NO  YES
The table XMLTAB1 ("column objects") contains XML_DOCUMENT as specified in the CREATE TABLE statement. But this is a virtual (derived) column. In addition there is the hidden column SYS_NC00002$ of type CLOB. When there are no storage parameters for XML, Oracle chooses the text-based storage, as explained more detailed in the post XML Speicherungsformen (german). Therefore this hidden column SYS_NC00002$ does actually store the XML text. The column XML_DOKUMENT is just derived (BTW: Here we can see that Virtual Columns in Oracle11g are not really a "new feature": the Oracle database did use them in previous versions; but Oracle11g exposes them externally.)
Die Tabelle XMLTAB1 mit den "Column Objects" enthält die im CREATE TABLE-Kommando angegebene Spalte XML_DOCUMENT als virtuelle, also "abgeleitete" Spalte und zusätzlich die versteckte (Hidden) Spalte SYS_NC00002$ vom Typ CLOB. Wie bereits im Post über die Speicherungsformen geschrieben, wird ein XMLTYPE als CLOB gespeichert, wenn man bei Tabellenerstellung nix anderes angibt. Und diese versteckte Spalte SYS_NC00002$ nimmt das eigentliche XML-Dokument auf. Die sichtbare Spalte XML_DOKUMENT ist dagegen eine daraus abgeleitete virtual Column. (Übrigens: Daran ist erkennbar, dass Virtual Columns in Oracle11g kein wirklich neues Feature ist - es wurde lediglich für den Anwender nutzbar gemacht. Virtuelle Spalten gab's auch schon vorher.)
But what about the table OF XMLTYPE ("row objects")?
Aber wie sieht es nun mit der Tabelle OF XMLTYPE, also den "Row Objects" aus?
SQL> select COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN, HIDDEN_COLUMN from user_tab_cols where TABLE_NAME='XMLTAB2'

COLUMN_NAME                    DATA_TYPE                      VIR HID
------------------------------ ------------------------------ --- ---
SYS_NC_OID$                    RAW                            NO  YES
SYS_NC_ROWINFO$                XMLTYPE                        YES NO
XMLDATA                        CLOB                           NO  YES
Again, there is a hidden column (XMLDATA), which contains the actual XML data, and a derived (virtual) column. The virtual column is now named SYS_NC_ROWINFO$, as we did not specify a column name during table creation (CREATE TABLE ... OF XMLTYPE). Compared with the first table there is an additional column: SYS_NC_OID$. This column contains a unique object id for each XML document. This object id allows to create a pointer for each XML document.
Wiederum haben wir eine versteckte Spalte (XMLDATA), welche die eigentlichen XML-Daten aufnimmt und eine abgeleitete (virtuelle) Spalte, die hier SYS_NC_ROWINFO$ heißt (bei Tabellenerstellung haben wir hier ja keinen Spaltennamen angegeben). Soweit ist es wie bei der ersten Tabelle. Neu ist die Spalte SYS_NC_OID$, welche eine eindeutige Object-ID für jedes XML-Dokument enthält. Diese Object ID macht es nun möglich, dass man einen Pointer auf das XML-Dokument erzeugen kann:
SQL> insert into xmltab2 values ('<xml-dokument>Ein Text</xml-dokument>');

1 row created.

SQL> select ref(e) from XMLTAB2 e;

REF(E)
--------------------------------------------------------------------------------------
000028020940B3A963EF38DC29E040A50A86F8248140B3A963EF37DC29E040A50A86F824810141DA550000
This reference pointer can now be used to create an entry for the particular XML document in Oracle XML DB's virtual file system (the XML DB Repository). The following example illustrates this using the FTP access (in most cases this must be configured with the script $ORACLE_HOME/rdbms/admin/catxdbdbca.sql):
Und eine solche Referenz könnte man nun nutzen, um das eingefügte XML-Dokument im virtuellen Dateisystem der XML Datenbank erscheinen zu lassen. Im folgenden sei dies anhand des FTP-Zugriffs (ggfs. einrichten mit dem Skript $ORACLE_HOME/rdbms/admin/catxdbdbca.sql) erklärt:
declare
  v_ref ref xmltype;
  v_res boolean;
begin
  select ref(e) into v_ref from xmltab2 e;
  v_res := dbms_xdb.createresource(
    abspath => '/public/testdatei.xml',  -- Pfad, unter dem die virtuelle Datei erzeugt werden soll
    data    => v_ref,                    -- Pointer auf die Tabellenzeile
    sticky  => true                      -- "Klebrig"; beim Löschen der Tabellenzeile verschwindet
                                         -- auch die virtuelle Datei
  );
end;
/
sho err
  
commit
/
Now we browse the virtual file system with a command line FTP client
Nun schauen wir uns das virtuelle Dateisystem mit FTP an
ftp> open host 2100
Connected to host.mydomain.com.
220- host
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 host FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott tiger
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   2 SCOTT    oracle         0 DEC 12 13:03 testdatei.xml
226 ASCII Transfer Complete
ftp> get testdatei.xml
226 ASCII Transfer Complete
ftp: 37 bytes received in 0,05Seconds 1298,24Kbytes/sec.
After "downloading" the file testdatei.xml can be opened using a normal text editor and it contains the XML text which was inserted in the table. Deleting the file via the FTP DELETE command leads the table row also to be deleted. Changing the XML content with a SQL UPDATE command leads the file content to be updated accordingly.
Access to the virtual filesystem is not only possible with FTP but also with HTTP and WebDAV - so this feature is very feasible for data integration scenarios: The next post will show not only how to create a XML view on top of some relational tables, but also how to expose these virtual XML documents as files in the virtual filesystem - clients can get the data via FTP or HTTP.
Schaut man in die Datei hinein, so findet man den eben in die Tabelle eingefügten XML-Text wieder. Löscht man die Datei via FTP DELETE-Kommando, dann verschwindet sie auch aus der Tabelle. Ändert man die Tabellenzeile, so ist die virtuelle Datei sofort mitgeändert.
Wie man beim Einspielen des o.g. Skripts $ORACLE_HOME/rdbms/admin/catxdbdbca.sql feststellt, steht neben FTP auch HTTP zur Verfügung - und da gelten genau die gleichen Bedingungen.
Dieses Feature lässt sich sehr gut zum Datenaustausch nutzen - beim nächsten Mal werde ich zeigen, wie man es zuammen mit XML-Views auf relationale Tabellen einsetzen kann. Man erzeugt dann nicht nur eine XML-Sicht über die relationalen Tabellen, sondern macht die Daten auch gleich per FTP oder HTTP verfügbar. Aber dies ... später ...

5. Dezember 2007

Umfrage zum Thema XML in der Datenbank ...

Zum Thema XML in der Oracle-Datenbank habe ich ja schon den einen oder anderen Post in diesem Blog geschrieben ... nun würde ich gerne ein Bild über die tatsächlichen XML-Themen, die so auf eurem Schreibtisch liegen, bekommen. Stimmt einfach ab: Habt Ihr mit XML in der Datenbank zu tun? Und wenn ja: Was ist die Anforderung ...?

4. Dezember 2007

Emails aus einem Postfach abrufen ... wieder mal mit SQL!

English title: Retrieving mails from an IMAP server - again: with SQL!

Emails aus der Datenbank heraus zu versenden, ist keine allzu kompilizierte Aufgabe - es wird tagtäglich mit Hilfe der bekannten PL/SQL-Pakete wie UTL_SMTP oder UTL_MAIL gemacht. Eine interessante Aufgabe wäre dagegen das Abrufen von Emails aus einem Postfach. Wozu kann sowas gut sein - man könnte bspw. Prozesse durch Emails antriggern - die Datenbank holt die Mails ab und starten dann PL/SQL-Logik ...
It is no problem to send emails from the database - with the provided PL/SQL packages UTL_SMTP or UTL_MAIL this is done day by day without problems. But the other way around is an interesting challenge: to retrieve mails from an mail account using SQL or PL/SQL. What's this good for? This is obvious: The process of retrieving emails could be triggered by a database event - some PL/SQL collects the email and processes them further.
Doch wie macht man das? Schließlich gibt es kein PL/SQL-Pakete zum Abrufen von Emails. Die Lösung lautet Java in der Datenbank. Die Java Mail API ist in der Java Welt sehr bekannt und kann nicht nur Mails senden, sondern auch aus Postfächern (POP3 und IMAP) abrufen. Also schreiben wir eine Java Stored Procedure: Diese verbindet sich auf den Mailserver, ruft die Mails ab und gibt Sie als Array zurück. Das Array wird dann auf ein PL/SQL VARRAY abgebildet, so dass die Java Stored Procedure wie eine Table Function verwendet werden kann. Doch genug der Vorrede - hier kommt der Code (dieses Codebeispiel ist nur mit IMAP-Mailservern getestet - eim Umschreiben auf POP3-Postfäche sollte aber nicht so kompliziert sein)
But how to do this? There is no PL/SQL package for retrieving Mails - with pure PL/SQL this simply cannot be done. The solution is (as in many other cases) java in the database. The java mail API is very well known (in the java programmers' world) and it can not only send mail but also retrieve it via POP3 as well as via the IMAP protocol. So we'll create a java stored procedure: This procedure connects to the mail server, retrieves the mail headers and returns them as an array - this array must then be converted to a PL/SQL VARRAY. This java stored procedure can finally be accessed like a table function. But this is enough talking - here's the code (this example is for IMAP email servers - changing it to POP3 should be an easy task)
set define off

drop procedure get_mail_headers
/
drop type mail_header_ct
/
drop type mail_header_t
/

create or replace type mail_header_t as object(
  subject       varchar2(4000),
  sender        varchar2(100),
  sender_email  varchar2(100),
  sent_date     date,
  deleted       char(1),
  read          char(1),
  recent        char(1),
  answered      char(1)
)
/

create or replace type mail_header_ct as table of mail_header_t
/


create or replace java source named "GetMailHeaders" as 
import java.sql.Connection;
import java.sql.DriverManager;

import java.util.Properties;
import java.util.Vector;

import javax.mail.Folder;
import javax.mail.FetchProfile;
import javax.mail.Message;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.Flags;
import javax.mail.internet.InternetAddress;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;


class getMails {

  public static ARRAY getMails(String host, int port, String user, String pass, int maxHeaders) 
  throws Exception {
    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    ArrayDescriptor aDescr = ArrayDescriptor.createDescriptor("MAIL_HEADER_CT", con);
    StructDescriptor rDescr = StructDescriptor.createDescriptor("MAIL_HEADER_T", con);
    Object[] mailHeader = new Object[8];
    STRUCT oraMail = null;
    Vector vMails = new Vector();
      
    Properties props = new Properties();
    props.setProperty("mail.store.protocol", "imap");
    Session s = Session.getDefaultInstance(props);
    Store store = s.getStore();
    store.connect(host, port, user, pass);
    Folder folder = store.getFolder("INBOX");
    folder.open(Folder.READ_ONLY);
    Message message[] = folder.getMessages();
    FetchProfile fp = new FetchProfile();
    fp.add(FetchProfile.Item.ENVELOPE);
    fp.add(FetchProfile.Item.FLAGS);
    folder.fetch(message, fp);
  
    String sPersonal = null;

    for (int i=message.length-1; i>=0 && ((i>=(message.length - maxHeaders)) || maxHeaders == -1);i--) {
      mailHeader[0] = new String(message[i].getSubject());
      sPersonal = ((InternetAddress)(message[i].getFrom()[0])).getPersonal();
      if (sPersonal == null) {
        mailHeader[1] = new String("");
      } else {
        mailHeader[1] = new String(sPersonal);
      }
      mailHeader[2] = new String(((InternetAddress)(message[i].getFrom()[0])).getAddress());
      mailHeader[3] = new java.sql.Timestamp(message[i].getSentDate().getTime());
      mailHeader[4] = (message[i].isSet(Flags.Flag.DELETED)?"Y":"N"); 
      mailHeader[5] = (message[i].isSet(Flags.Flag.SEEN)?"Y":"N"); 
      mailHeader[6] = (message[i].isSet(Flags.Flag.RECENT)?"Y":"N"); 
      mailHeader[7] = (message[i].isSet(Flags.Flag.ANSWERED)?"Y":"N"); 
      oraMail = new STRUCT(rDescr, con, mailHeader);
      vMails.add(oraMail);
    }
    folder.close(false);
    store.close();
    return new ARRAY(aDescr, con, vMails.toArray());
  }
}
/

alter java source "GetMailHeaders" compile
/
sho err
Hier ist der PL/SQL Wrapper, welcher die Java-Klasse auf eine PL/SQL Table Function abbildet.
This is the "PL/SQL wrapper" which makes the java method available to PL/SQL.
create or replace function get_mail_headers(
  p_mailhost in varchar2,
  p_mailport in number,
  p_username in varchar2,
  p_password in varchar2,
  p_max_mails in number 
) return MAIL_HEADER_CT
is language java name 'getMails.getMails(
  java.lang.String, 
  int, 
  java.lang.String, 
  java.lang.String, 
  int
) return oracle.sql.ARRAY';
/
Die Funktion GET_MAIL_HEADERS hat folgende Parameter:
  1. p_mailhost: Der Mailserver-Hostname oder die IP-Adresse
  2. p_mailport: Der TCP/IP-Port des Mailservers (normalerweise 143 für IMAP-Postfächer)
  3. p_username: Username zum Login in den Mailserver
  4. p_password: Passwort zum Login in den Mailserver
  5. p_max_mails: Maximal abzurufende Mails, "-1" ruft alle ab.
Und wie immer, wenn man mit Java Stored Procedures Netzwerkverbindungen nach "draußen" öffnen möchte, benötigt man Privilegien: Der folgende Code muss als SYS abgesetzt werden und räumt dem User SCOTT das Privileg ein, Netzwerkverbindungen zu mailserver.mydomain.com zu öffnen.
The function GET_MAIL_HEADERS is called with the following parameters:
  1. p_mailhost: The Mailserver hostname or its IP address
  2. p_mailport: TCP/IP port of the mailserver (in most cases "143" for IMAP servers)
  3. p_username: mailserver username
  4. p_password: mailserver password
  5. p_max_mails: maximum number of mails to be retrieved; "-1" gets all
And ... as always when opening network connections with java ... some privileges are needed. The following call grants the privilege to connect to mailserver.mydomain.com and must be issued as SYS.
begin
  dbms_java.grant_permission( 
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.net.SocketPermission',
    permission_name   => 'mailserver.mydomain.com', 
    permission_action => 'connect,resolve' 
  );
end;
/

commit
/
Ausprobieren ist dann ganz einfach ...
Testing is very simple ...
select sender, subject from table(
  get_mail_headers(
    'mailserver.domain.com', 
    143, 
    'carsten.czarski',
    'ganzgeheim', 
    -1
  )
)
/

SENDER                                  SUBJECT
--------------------------------------  ----------------------------------------
Max.Muster@maildomain.com               Hallo Carsten
:                                       :
Und was bringt das? Sowie die Mail-Header als (virtuelle) Tabelle mit SQL abrufbar sind, kann man jedes beliebige Web-Framework nutzen, um sie als Tabelle auf eine Webseite zu bringen - Ein Beispiel für die Darstellung mit Application Express findet sich in der Application Express-Community - einfach mal reinschauen.
And what's this good for ...? As the mail headers are available as a virtual table (via the table function) one can use every web framework to get them onto a web page in tabular format ("report"). A (german) example describing how this is used with Oracle Application Express can be found in the german Application Express community.

27. November 2007

Installierte Oracle Software ermitteln ... doch, das geht mit SQL!

Die Tage hatte ich mit Volker Solinus eine sehr interessante Diskussion. Was dabei herausgekommen ist, ist eine ganz nette Anwendung der XML-Technologie in der Datenbank. Bekanntlich kann der DBA sich mit der View DBA_REGISTRY recht einfach die in der jeweiligen Datenbank konfigurierten Komponenten ansehen. Schwieriger stellt es sich dar, wenn man wissen möchte, welche Software im $ORACLE_HOME des Datenbankservers installiert ist - Da treten schon öfter Fragen auf wie ...
  • Ist die Companion CD installiert oder nicht?
  • Welche Einzelpatches sind eigentlich installiert?
Die Antwort auf diese Fragen lässt sich auch mit SQL ermitteln: Basis dafür ist die Tatsache, dass der Oracle-Installer eine Datei namens comps.xml im Verzeichnis $ORACLE_HOME/inventory/ContentsXML pflegt. In dieser XML-Datei sind alle installierten Softwarekomponenten enthalten - Das OPatch-Werkzeug pflegt auch die installierten Einzelpatches ein.
Und da es eine XML-Datei ist, kann sie mit den hier schon öfter genutzten SQL-Funktionen wie EXTRACTVALUE ausgelesen werden.
Um zu beginnen, erzeugen wir zunächst ein Verzeichnis-Objekt auf besagtes Verzeichnis mit der Datei comps.xml:
create or replace directory COMPSXMLDIR as '/oracle/u01/app/oracle/product/11.1.0/inventory/ContentsXML';
Anschließend kann man sich die Datei im SQL*Plus schonmal ansehen:
select 
  xmltype(
      bfilename('COMPSXMLDIR', 'comps.xml'),
      nls_charset_id('WE8ISO8859P1')
  ).getclobval()
from dual;
Nun geht's aber los: Wir verwenden sie Funktionen EXTRACTVALUE zum Ausschneiden einzelner Tags bzw. Attribute und nutzen das TABLE(XMLSEQUENCE(EXTRACT(...)))-Konstrukt zum "Flachklopfen" der hierarchischen XML-Struktur. Die erste Frage wäre die nach den installierten CD-Sets:
col comp_name format a50
col comp_version format a20

select 
  extractvalue(value(c), '/COMP/EXT_NAME') comp_name,
  extractvalue(value(c), '/COMP/@ACT_INST_VER') comp_version
FROM table(
     XMLSequence(
         extract(
             xmltype(
                 bfilename('COMPSXMLDIR', 'comps.xml'),
                 nls_charset_id('WE8ISO8859P1')
             ), 
             '/PRD_LIST/TL_LIST/COMP'
         )
     )
 ) c
/ 

COMP_NAME                                          COMP_VERSION
-------------------------------------------------- --------------------
Oracle Database 11g                                11.1.0.6.0
Auf einer anderen Datenbank sieht es u.U. so aus:
COMP_NAME                                          COMP_VERSION
-------------------------------------------------- --------------------
Oracle Database 11g                                11.1.0.6.0
Oracle Database 11g Examples                       11.1.0.6.0
Gehen wir ein wenig tiefer hinein: Welche Komponenten machen denn die Oracle Database 11g aus?
select 
  extractvalue(value(c), '/COMP/EXT_NAME') comp_name,
  extractvalue(value(c), '/COMP/@ACT_INST_VER') comp_version
FROM table(
     XMLSequence(
         extract(
             xmltype(
                 bfilename('COMPSXMLDIR', 'comps.xml'),
                 nls_charset_id('WE8ISO8859P1')
             ), 
             '/PRD_LIST/COMP_LIST/COMP'
         )
     )
 ) c
/

COMP_NAME                                          COMP_VERSION
-------------------------------------------------- --------------------
Sun JDK                                            11.1.0.6.0
Installer SDK Component                            11.1.0.6.0
Oracle One-Off Patch Installer                     11.1.0.6.0
Oracle Universal Installer                         11.1.0.6.0
LDAP Required Support Files                        11.1.0.6.0
SSL Required Support Files for InstantClient       11.1.0.6.0
Oracle Net Required Support Files                  11.1.0.6.0
Buildtools Common Files                            11.1.0.6.0
:                                                  :
Und schließlich die Frage nach den installierten Einzelpatches
SELECT to_number(   extractValue(value(oneOffList), '/ONEOFF/@REF_ID')              ) oneoff_id,
       substr   (   extractValue(value(oneOffList), '/ONEOFF/@ACT_INST_VER')  , 1,20) db_version,
       substr   (   extractValue(value(oneOffList), '/ONEOFF/@INSTALL_TIME')  , 1,30) install_time
FROM table(
     XMLSequence(
         extract(
             xmltype(
                 bfilename('COMPSXMLDIR', 'comps.xml'),
                 nls_charset_id('WE8ISO8859P1')
             ), 
             '/PRD_LIST/ONEOFF_LIST/ONEOFF'
         )
     )
 ) oneOffList
/

 ONEOFF_ID DB_VERSION           INSTALL_TIME
---------- -------------------- ------------------------------
   5557962 10.2.0.3.0           2007.Mar.12 14:03:52 CET
   5556081 10.2.0.3.0           2007.Mar.12 14:04:03 CET
   6010833 10.2.0.3.0           2007.Jul.10 11:10:29 CEST
   6069085 10.2.0.3.0           2007.Jul.10 11:16:35 CEST
         : :                    :

Bitte achtet auf den Zeichensatz, den Ihr bei NLS_CHARSET_ID angibt - es sollte der sein, in dem die Datei comps.xml vorliegt - hängt mitunter vom Betriebssystem ab. Nochmals Danke an Volker für die Idee und die Hilfe bei der Umsetzung.

26. November 2007

Case- und Umlaut-"insensitive" Suche ... und das mit Index!

Erst kürzlich wurde ich von gefragt, wie man denn in der Oracle-Datenbank eine Case-Insensitive Suche durchführen kann - schließlich möchte man dem Endanwender nicht zumuten, den Kundennamen immer in der richtigen Schreibweise eingeben zu müssen. Ein erster Ansatz wäre das Verwenden einer Funktion wie UPPER oder LOWER und dann die Nutzung eines function based Indexes:
create index on kunden_tab (upper(name))
Allerdings greift dieses Beispiel (speziell im deutschsprachingen Raum) etwas zu kurz. Sobald Umlaute ins Spiel kommen, braucht man noch etwas mehr Funktionalität. So wird der Name Müller gerne auch mal als MULLER gesucht. Man bräuchte also eine Suchabfrage, welche u.U. auch Umlaute ignoriert.
Speziell hierfür gibt es seit Oracle10g eine Erweiterung des NLS_SORT-Parameters. Während NLS_SORT=GERMAN erstmal nur eine andere Sortierreihenfolge für die Umlaute wählt, sorgen NLS_SORT=GERMAN_CI dafür, dass Groß- und Kleinschreibung und NLS_SORT=GERMAN_AI dafür, dass die "diakritischen Zeichen" ignoriert werden. Und das besondere ist: Setzt man NLS_COMP=LINGUISTIC, dann gelten diese Einstellungen nicht nur für das Sortieren, sondern auch für das Filtern mit der WHERE-Klausel. Das folgende Beispiel zeigt die Funktionalität:
create table daten (col varchar2(200))
/

insert into daten values ('heinz');
insert into daten values ('Heinz');
insert into daten values ('Müller');
insert into daten values ('Muller');
insert into daten values ('Klara');
insert into daten values ('Fleiß');

commit
/

create index idx_daten on daten (col)
/

-- Einstellen auf Case-Insensitive Suche

alter session set nls_comp=linguistic
/
alter session set nls_sort=german_AI
/

select * from daten where col='MULLER'
/

COL
--------------------------------------------------------------------
Müller
Muller
Allerdings zeigt ein Blick auf den Ausführungsplan, dass der vorher erzeugte, "normale" Index nun nicht mehr zieht ...
explain plan for
select * from daten where col='MULLER'
/

select * from table(dbms_xplan.display())
/

Plan hash value: 914527404

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |   102 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DATEN |     1 |   102 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("COL",'nls_sort=''GERMAN_AI''')=HEXTORAW('50734B4B28640001010101010100') )

Also wird der Index als linguistischer Index neu gebaut:
drop index idx_daten
/

create index idx_daten on daten (NLSSORT(col, 'NLS_SORT = German_AI'))
/
Erneuter Test ...
select * from daten where col='MULLER'
/

COL
---------------------------------------------------------------------

Müller
Muller

explain plan for
select * from daten where col='MULLER'
/

select * from table(dbms_xplan.display())
/

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   909 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DATEN     |     1 |   909 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DATEN |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NLSSORT("COL",'nls_sort=''GERMAN_AI''')=HEXTORAW('50734B4B28640001010101010100'))
Mehr Informationen zum Thema "linguistische Indizes" finden sich in der Dokumentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch5lingsort.htm#i1006421.

19. November 2007

XML-Dokumente speichern: Was tun, wenn "Query Rewrite" mal nicht geht ...

Nun folgt der fünfte und letzte Teil der Posting-Reihe zum Thema XML in der Oracle-Datenbank und Performance. In den letzten Posts wurden die verschiedenen Speicherungsformen, das Query Rewrite und die Auswirkungen auf den Ausführungsplan betrachtet. Insbesondere die objektrelationale Speicherung hängt ganz massiv vom Query Rewrite ab: Wenn die XPath-Abfrage erfolgreich auf die objektrelationalen Strukturen umgeschrieben werden kann, wird sie mit annährend relationaler Performance ausgeführt. Wie gesehen, kann die Performance dann durch Tuning-Maßnahmen wie Indizes, Partitionierung oder andere nun noch weiter verbessert werden. Basis ist jedoch ein funktionierendes Query Rewrite!.
Manchmal stellt man jedoch fest, dass man zwar die objektrelationale Speicherung verwendet (man hat ein XML Schema registriert), die Abfrageperformance jedoch trotzdem nicht richtig stimmt ... Hierzu ein Beispiel: Auf die Tabelle XML_OR wird die folgende XPath-Abfrage abgesetzt (mit der XPath-Funktion contains machen wir eine Teilstring-Suche) - Trotz objektrelationaler Speicherungsform wird die Performance eher schlecht sein. Zur Diagnose fangen wir mit einem Ausführungsplan an ...
explain plan for
select * from xml_or e
where existsnode(value(e), '//name[contains(text(), "Kunde #2285")]')=1
/

select * from table(dbms_xplan.display());

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |  5000 |   258K|    32   (7)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | SYS_IOT_TOP_72422 |    10 |   400 |     3   (0)| 00:00:01 |
|*  2 |  FILTER            |                   |       |       |            |          |
|   3 |   TABLE ACCESS FULL| XML_OR            |  5000 |   258K|    29   (7)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("NESTED_TABLE_ID"=:B1)
   2 - filter(
         EXISTSNODE(
           SYS_MAKEXML('3EB815A238894F4EE040A8C08C021394',4753,"E"."XMLEXTRA","E"."XMLDATA"),
           '//name[text()="Kunde #2285"]'
         )=1
       )
Der XPath-Ausdruck findet sich im Ausführungsplan wieder - im letzten Post trat dies nur bei der textbasierten Speicherung auf. Dieser XPath-Ausdruck konnte jedoch offensichtlich auf die zweifellos vorhandenen objektrelationalen Strukturen umgeschrieben werden.
Doch warum ...?
Um diese Frage zu beantworten, müssen wir ein wenig tiefer in die Datenbank schauen. Das folgende Kommando aktiviert das Tracing für Query Rewrite:
alter session set events='19027 trace name context forever, level 0x2000'
Anschließend führen wir das EXPLAIN PLAN nochmals durch. Wichtig ist jedoch, dass das SQL für die Datenbank neu sein muss - wir fügen also einen Kommentar mit irgendeinem wirren Text dazu.
explain plan for
select /* asdkljkjf */ * from xml_or e
where existsnode(value(e), '//name[contains(text(), "Kunde #2285")]')=1
/

EXPLAIN PLAN ausgeführt.
Die Informationen zum Query Rewrite befinden sich nun in der Tracedatei. Man benötigt also spätestens jetzt Zugriff auf das Dateisystem des Datenbankservers: Bis Oracle10g befinden sich diese im udump-Verzeichnis, ab Oracle11g unter $ORACLE_BASE/diag/.../trace. Schaut man in die generierte Tracedatei hinein, so sollte sie in etwa so aussehen ...
NO REWRITE
        XPath ==> /child::test-xml/child::name[contains(text(), "Kunde #2285")]
        Reason ==> predicate not rewritable
        Current Location Path ==>
                axis:child
                nodetest:wildcard name
                strval:name
                slash:slash
Das Predicate ist der Teil innerhalb der eckigen Klammern. Schaut man nun in der XML DB Dokumentation nach, welche XPath-Konstrukte durch Query Rewrite auf objektrelationale Strukturen umgeschrieben werden können, so fällt auf, dass nur bestimmte XPath-Funktionen unterstützt werden: not, floor, ceiling, substring, string-length und translate. contains gehört nicht dazu. Die XML-Abfrage wird durchaus ausgeführt, allerdings wird sie nicht auf objektrelationale Strukturen umgeschrieben - die Performance entspricht also eher der textbasierten Speicherungsform.
Doch wie lösen wir das Problem?
Es gibt zwei Varianten:
  1. Verwenden der Funktion ora:contains
  2. Nutzen der SQL-Funktion LIKE
explain plan for
select /* asdkljdasjhklkjf */ * from xml_or e
where existsnode(
  value(e), 
  '//name[ora:contains(text(), "Kunde #2285") > 0]',
  'xmlns:ora="http://xmlns.oracle.com/xdb"'
)=1
/

select * from table(dbms_xplan.display())
/

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |   262 | 13886 |    23   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | SYS_IOT_TOP_72422 |    10 |   400 |     3   (0)| 00:00:01 |
|*  2 |  FILTER            |                   |       |       |            |          | 
|   3 |   TABLE ACCESS FULL| XML_OR            |  5000 |   258K|    23   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | SYS_IOT_TOP_72422 |     1 |    40 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("NESTED_TABLE_ID"=:B1)
   2 - filter(SYS_XMLCONTAINS("E"."SYS_NC00009$",'Kunde #2285')>0 AND
              SYS_XMLCONV("E"."SYS_NC00007$",0,32,'3EB815A238894F4EE040A8C08C021394',1,4748,1)
               IS NOT NULL OR  EXISTS (SELECT 0 FROM "SCOTT"."STUECKE_OCT" "SYS_ALIAS_1"
              WHERE "NESTED_TABLE_ID"=:B1 AND SYS_XMLCONTAINS("SYS_ALIAS_1"."name",'Kunde
              #2285')>0 AND SYS_XMLCONV("SYS_ALIAS_1"."SYS_XDBPD$",0,32,'3EB815A238894F4EE040A
              8C08C021394',1,4750,1) IS NOT NULL))
   4 - access("NESTED_TABLE_ID"=:B1)
       filter(SYS_XMLCONTAINS("SYS_ALIAS_1"."name",'Kunde #2285')>0 AND
              SYS_XMLCONV("SYS_ALIAS_1"."SYS_XDBPD$",0,32,'3EB815A238894F4EE040A8C08C021394',1,4750,1) IS NOT NULL)
Der XPath-Ausdruck findet sich nicht mehr wieder, also hat das Query Rewrite funktioniert. Die Frage ist nur, ob man mit dem Ergebnis so richtig glücklich wird. ora:contains ist wesentlich mehr als ein einfacher Zeichenkettenvergleich - die Funktion macht eine Volltextrecherche im angesprochenen XML-Knoten. Um in großen Datenmengen performant suchen zu können, würde auf jeden Fall ein Volltextindex benötigt - mehr zu diesem Thema findet sich in der Oracle-Dokumentation. Gefragt war vielleicht eher eine einfache Wildcard-Suche, wie beim SQL-Like ... und auch dies ist erreichbar:
explain plan for
select /* asdkljdasjhklkjf */ * 
from xml_or e, table(xmlsequence(extract(value(e), '//stueck/name'))) n
where extractvalue(
  value(n), 
  '/name'
) like '%Kunde #2285%' or
extractvalue(
  value(e), 
  '/name'
) like '%Kunde #2285%'
/

select * from table(dbms_xplan.display())
/

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   244 | 22692 |   167   (2)| 00:00:03 |
|*  1 |  INDEX RANGE SCAN     | SYS_IOT_TOP_72422 |    10 |   400 |     3   (0)| 00:00:01 |
|*  2 |  HASH JOIN            |                   |   244 | 22692 |   167   (2)| 00:00:03 |
|*  3 |   INDEX FAST FULL SCAN| SYS_IOT_TOP_72422 |   244 |  9760 |   144   (2)| 00:00:02 |
|   4 |   TABLE ACCESS FULL   | XML_OR            |  5000 |   258K|    23   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("NESTED_TABLE_ID"=:B1)
   2 - access("NESTED_TABLE_ID"="E"."SYS_NC0001100012$")
   3 - filter(("SYS_ALIAS_1"."name" LIKE '%Kunde #2285%' OR NULL LIKE '%Kunde #2285%') 
        AND SYS_XMLCONV("SYS_ALIAS_1"."SYS_XDBPD$",0,32,'3EB815A238894F4EE040A8C08C021394',1,4750,1) IS NOT NULL)
Man sieht, dass auch in diesem Beispiel kein XPath-Ausdruck mehr im Ausführungsplan enthalten ist. In diesem Beispiel haben wir die Abfrage zwar gehörig umgeschrieben ... das Ziel (funktionierendes Query Rewrite) ist es jedoch auf jeden Fall wert.
Bitte behaltet stets im Hinterkopf: Ohne Query Rewrite wird die Abfrage trotz objektrelationaler Strukturen durch XML-Parsing ausgeführt - Indizes, Partitionierung und alle anderen Mittel der Datenbank zur Abfrageoptimierung bleiben ungenutzt. Das Sicherstellen von Query Rewrite ist bei der XML DB also eine fundamentale Tuning-Maßnahme!

Wenn sich also bei einer XPath-Abfrage herausstellt, dass Query Rewrite nicht funktioniert, dann sollte die erste Maßnahme stets sein, eben dieses sicherzustellen - im Notfall auch durch Umschreiben der eigentlichen Abfrage.

13. November 2007

Sehen wir uns auf der DOAG ...?

Alle Jahre wieder steht die DOAG Jahreskonferenz vor der Tür... Das Thema XML ist ja in diesem Blog schon recht tief behandelt worden - die 11g-News gibt es in einem Vortrag auf der Konferenz: am 2. Tag (22. November) von 12:00 bis 12:45 - also vormerken. Allgemeine SQL und PL/SQL News gibt es am 1. Tag (21. November) von 15:00 bis 15:45 Uhr. Mehr Info ... Vielleicht sieht man sich ...

12. November 2007

XML in der Datenbank: Performance, SQL und EXPLAIN PLAN

Nach dem kurzen Ausflug zum CSV-Parser geht es nun weiter mit der Speicherung von XML-Dokumenten in der Oracle-Datenbank. Teil 1 zeigte, wie man XML-Dokumente grundsätzlich speichert und anschließend relational aufbereitet - Teil 2 stellte die verschiedenen Speicherungsformen vor und Teil 3 zeigte, was bei einer SQL-Abfrage auf die XML-Dokumente tatsächlich in der Datenbank passiert ist.
Heute beschäftigten wir uns mit XML-Abfragen und Ausführungsplänen - insbesondere für die objektrelationale Speicherungsform. Im Gegensatz zu den letzten Posts beschäftigen wir uns nun nicht mehr damit, alle XML-Inhalte relational aufzubereiten, vielmehr werden nun bestimmte XML-Dokumente mit der SQL WHERE-Klausel gesucht. Basis sind wieder die in Teil 2 erzeugten Tabellen. Beginnen wir mit einem einfachen Beispiel auf die objektrelational gespeicherten Dokumente. Das Dokument für Kunde #756 soll gefunden werden:
explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml[name="Kunde #756"]'
)=1
/

select * from table(dbms_xplan.display())
/

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    57 |   112K|   136   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| XML_OR |    57 |   112K|   136   (0)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("XML_OR"."SYS_NC00009$"='Kunde #756')

Note
-----
   - dynamic sampling used for this statement
Interessant am Ausführungsplan ist die Predicate Information. Sie zeigt, dass mit einem Filter auf die Spalte "XML_OR"."SYS_NC00009$" gearbeitet wurde. Dies ist der Beweis, dass die Datenbank ein Query Rewrite durchgeführt hat: Der XPath-Ausdruck '/test-xml[name="Kunde #756"]' wurde umgeschrieben, so dass tatsächlich eine relationale Abfrage ausgeführt wurde.
Wenn wir das gleiche mit textbasiert gespeicherten XML-Dokumenten versuchen, sieht der Ausführungsplan etwas anders aus:

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    51 |   100K|   371   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| XML_TEXT |    51 |   100K|   371   (1)| 00:00:05 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXISTSNODE(SYS_MAKEXML("XML_TEXT"."XMLDATA"),'/test-xml[na
              me="Kunde #756"]')=1)

Note
-----
   - dynamic sampling used for this statement
Der XPath-Ausdruck aus der SQL-Abfrage findet sich genau so im Ausführungsplan wieder; das bedeutet, dass kein Query Rewrite stattgefunden hat - die Datenbank wird die XML-Dokumente zur Laufzeit der Abfrage parsen, was insbesondere bei großen Dokumentmengen eine Zeitlang dauern dürfte. Ein kleines Berechnungsbeispiel hierzu: Angenommen, ein Server schafft es, ein XML-Dokument in einer Millisekunde zu parsen. Liegen nun 1 Million Dokument in der Tabelle, dann ergeben sich (die Datenbank parst ja alle Dokumente zur Laufzeit) 1000 Sekunden, also in etwa 15 Minuten. Allein hierdurch wird deutlich. Greift man "in die XML-Dokumente hinein", so kommt die textbasierte Speicherung nicht mehr in Frage.
Der Vollständigkeit halber nun noch der Ausführungsplan für die Speicherung der XML-Dokumente als Binary XML:

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    32 | 64448 | 16633   (1)| 00:03:20 |
|*  1 |  FILTER             |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL | XML_BINARY |  4311 |  8478K|   102   (0)| 00:00:02 |
|   3 |   NESTED LOOPS      |            |     2 |    12 |     5   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |            |       |       |            |          |
|*  5 |    XPATH EVALUATION |            |       |       |            |          |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM XPATHTABLE('/test-xml' PASSING :B1 COLUMNS 
              "C_00$" XMLTYPE PATH '.')  "P",XPATHTABLE('/test-xml/name'
              PASSING SYS_XQSEQ2CON("P"."C_00$",1) COLUMNS "C_00$" XMLTYPE PATH '.',
              "C_01$" XMLTYPE PATH '/name')  "P1" WHERE
              SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P1"."C_01$",1,50),50,1,0)='Kunde #756'))

   5 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P1"."C_01$",1,50),50,1,0)='Kunde #756')

Note
-----
   - dynamic sampling used for this statement
Auch hier sieht man, dass die Abfrage intern umgeschrieben wurde. Ausgeführt wird eine Abfrage, die auf die Strukturen der Speicherungsform Binary XML optimiert ist. Sie ist (wie bereits in Teil 2 dargestellt wurde) schon wesentlich schneller als die textbasierte Speicherung - an die objektrelationale Speicherung mit annährend relationaler Performance kommt sie jedoch nicht heran.
Im folgenden gehen wir noch ein wenig tiefer in die objektrelationale Speicherung hinein: Betrachten wir eine Suche in der XML-Hierarchie; es wird also nicht mehr nach einem bestimmten Kunden, sondern nach einem gekauften Stück gesucht ...
explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml/gekaufte-stuecke/stueck[name="Stueck #1782"]'
)=1
/

select * from table(dbms_xplan.display())
/

---------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |    32 | 60480 |  9305   (1)| 00:01:52 |
|*  1 |  FILTER                            |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL                | XML_OR |  5653 |    10M|   136   (0)| 00:00:02 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH|        |       |       |            |          |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$" WHERE
              SYS_OP_ATG(VALUE(KOKBF$),3,4,2)='Stueck #1782'))
   3 - filter(SYS_OP_ATG(VALUE(KOKBF$),3,4,2)='Stueck #1782')

Note
-----
   - dynamic sampling used for this statement
Auch die Suche in der XML-Hierarchie wurde komplett durch ein Query Rewrite umgesetzt - der XPath-Ausdruck findet sich im Ausfürhungsplan nicht mehr wieder.
Nun zur Indizierung von XML-Dokumenten: Bei großen Dokumentmengen wollen wir nicht unbedingt jedesmal einen Full Table Scan machen. Zunächst ein Beispiel für die Indizierung des Kundennamens (die erste Abfrage):
drop index idx_or_kundename
/

create index idx_or_kundename 
on xml_or (extractvalue(object_value, '/test-xml/name'))
/

explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml[name="Stueck #1782"]'
)=1
/

select * from table(dbms_xplan.display())
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |    57 |   112K|     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OR_KUNDENAME |    57 |   112K|     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("XML_OR"."SYS_NC00009$"='Stueck #1782')

Note
-----
   - dynamic sampling used for this statement
Man sieht am Ausführungsplan sofort, dass zur Ausführung nun ein Index genutzt wird; das gute an der objektrelationalen Speicherung ist, dass man normale, relationale Hilfsmittel zum Datenbanktuning nutzen kann. Versuchen wir als nächstes, die zweite Abfrage nach den gekauften Stücken mit einem Index zu unterstützen ...
... aber auf Anhieb wird das nicht funktionieren: Die "gekauften Stücke" sind eine 1:n-Beziehung - es gibt also mehrere gekaufte Stücke pro XML-Dokument. Beim Erstellen der Tabelle in Teil 2 wurden keine besonderen Angaben gemacht, also werden alle gekauften Stücke als Array zusammen mit dem Rest des XML-Dokumentes in eine Tabellenzeile gepackt. Nun gilt für einen relationalen Index "Indexblatt = Tabellenzeile": Wir wären also in der Situation, viele Indexblätter (gekaufte Stücke) auf ein- und dieselbe Tabellenzeile zeigen zu lassen ... das geht nicht.
Die Lösung des Problems: Wir legen die Tabelle etwas anders an und sorgen dafür, dass die "gekauften Stücke" in eine eigene Tabelle ausgelagert werden:
drop table xml_or
/

create table xml_or of xmltype
xmlschema "http://sql-plsql-de.blogspot.com/xml-tests/schema.xsd"
element "test-xml"
varray xmldata."gekaufte-stuecke"."stueck" store as table STUECKE_OCT ((
  primary key (nested_table_id, array_index)) organization index)
/

insert into xml_or (select * from xml_text)
/
In der Storage Clause geben wir an, dass das Array der gekauften Stücke ('varray XMLDATA."gekaufte-stuecke"."stueck"' in die index-organisierte Tabelle STUECKE_OCT ausgelagert werden soll. Nun kann darin das Element "name" indiziert werden.
desc STUECKE_OCT

 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------

 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 id                                                 VARCHAR2(4000 CHAR)
 name                                               VARCHAR2(4000 CHAR)
Probieren wir es: Der Index wird nun auf die Tabelle STUCKE_OCT erzeugt.

drop index idx_or_stueckname
/

create index idx_or_stueckname 
on STUECKE_OCT ("name")
/

explain plan for 
select rowid from xml_or
where existsnode(
  object_value,
  '/test-xml/gekaufte-stuecke/stueck[name="Stueck #1782"]'
)=1
/

select * from table(dbms_xplan.display())
/

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |  2034 |     3  (34)| 00:00:01 |
|   1 |  NESTED LOOPS      |                   |     1 |  2034 |     3  (34)| 00:00:01 |
|   2 |   SORT UNIQUE      |                   |     1 |  2012 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| IDX_OR_STUECKNAME |     1 |  2012 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN| SYS_C009741       |     1 |    22 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SYS_ALIAS_1"."name"='Stueck #1782')
   4 - access("NESTED_TABLE_ID"="XML_OR"."SYS_NC0001100012$")

Note
-----
   - dynamic sampling used for this statement
Wie man sieht, hat die Speicherungsform und u.U. das Tabellenlayout doch massiven Einfluß auf den Ausführungsplan einer SQL-Abfrage auf XML-Dokumente. Insbesondere bei großen Datenmengen oder performancekritischen Abfragen lohnt sich eine genauere Betrachtung des Themas.
Im nächsten Post werden wir uns ansehen, was man tun kann, wenn man zwar scheinbar alles beachtet hat ... es aber trotzdem nicht performant läuft: also Troubleshooting. Bis dahin ... Stay tuned!

6. November 2007

CSV-Dateien einlesen mit SQL: Easy CSV Parser!

Heute (so zwischendurch) mal ein ganz anderes Thema: Wenn es darum geht, Kommaseparierte Dateien in die Datenbank zu laden, gibt es zahlreiche Wege: SQL*Loader, External Tables, Application Express, 3rd-Party Tools und zahlreiche Varianten mehr ... Alle erfordern mehr oder weniger Aufwand - Um eine external table anzulegen, muss man die SQL*Loader-Syntax schon gut kennen, auch wenn die Datei noch so einfach strukturiert ist.
Ich habe mich schon länger gefragt, ob die im Post LDAP Server abfragen mit SQL verwendeten völlig dynamischen Table Functions hier Verwendung finden können. Hintergrund ist ja, dass, verwendet man für Table Functions den sog. Interface Approach, die Struktur der zurückgegebenen Tabelle völlig dynamisch sein kann.
Also habe ich es mal probiert ... der Code ist zwar ein wenig länger geworden, aber eine erste Version ist fertig. Es funktioniert wie folgt:
select * from table (easy_csv_parser.parse(
  'CSV_DIR',     -- Directory-Objekt, in welchem die Datei liegt
  'file.txt',    -- Dateiname
  ',',           -- Feld-Delimiter
  '"',           -- Felder optional umschlossen von
  1,             -- 1: Es soll versucht werden, die Datentypen selbst zu erkennen
  1              -- 1: Spaltennamen aus erster Zeile entnehmen
))
/
Insbesondere wenn man mit dem CSV-Format noch experimentiert, sollte man stets noch einen SQL-Kommentar hinzufügen, um das SQL-Kommando für die Datenbank neu zu machen. Das Directory-Objekt muss auf ein Verzeichnis im Betriebssystem zeigen und ggfs. zunächst mit CREATE DIRECTORY erzeugt werden. Das Ergebnis mit bspw. dieser Datei hier ...
NAME,VORNAME,ORT,GEHALT,DATUM
Czarski,Carsten,München,400099,01-DEC-01
Franz,Muster,Trier,3000,01-JAN-01
"Mustermann, uu","Karl", Bad Homburg, 500, NODAT
... sieht dann etwa so aus:
NAME            VORNAME         ORT                 GEHALT DATUM
--------------- --------------- --------------- ---------- --------------------
Czarski         Carsten         München             400099 01-DEC-01
Franz           Muster          Trier                 3000 01-JAN-01
Mustermann, uu  Karl             Bad Homburg           500

3 Zeilen ausgewählt.
Probiert es einmal aus - Feedback ist erwünscht. Nächstes mal geht es wieder mit XML in der Datenbank, Ausführungsplänen und Performance weiter. Und hier kommt nun endlich der Code:
CREATE type easy_csv_parser as object(
  row_types          anytype,

  utlfile_id         number,
  utlfile_datatype   number,
  ecp_delimiter      varchar2(10),
  ecp_enclosed       varchar2(10),
  ecp_sample         number,

  static function parse(
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1, 
    p_cols_firstline   in number default 1 
  ) return anydataset pipelined using easy_csv_parser,

  static function ODCITableDescribe(
    record_table       out anytype,
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1, 
    p_cols_firstline   in number default 1 
  ) return number,

  static function ODCITablePrepare (
    sctx               out easy_csv_parser, 
    tab_func_info      in sys.ODCITabFuncInfo,
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number,

  static function ODCITableStart   (
    sctx               in out easy_csv_parser, 
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number, 

  member function ODCITableFetch   (
    self               in out easy_csv_parser,
    nrows              in number, 
    record_out         out anydataset
  ) return number,
  
  member function ODCITableClose   (
    self               in easy_csv_parser 
  ) return number
)
/
sho err

CREATE or replace type BODY easy_csv_parser as
 static function ODCITableDescribe(
    record_table       out anytype,
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number as
    v_record_structure anytype; 
    v_field_name       varchar2(4000); 
    v_field_data       varchar2(4000); 

    v_filehandle       utl_file.file_type;
    v_line_fields      varchar2(32767);
    v_line_data        varchar2(32767);

    v_f_ep             pls_integer := -1;
    v_f_sp             pls_integer := 1;
    v_d_ep             pls_integer := -1;
    v_d_sp             pls_integer := 1;
    v_enc_p            pls_integer := 1;
    v_field_pos        pls_integer := 1;

    v_typecode         pls_integer := dbms_types.typecode_varchar2;
    v_typecode_prec    pls_integer;
    v_data_date        date;
    v_data_number      number;
  begin 
    anytype.begincreate(dbms_types.typecode_object, v_record_structure);
    v_filehandle := utl_file.fopen(p_dir, p_filename, 'R'32767);

    utl_file.get_line(v_filehandle, v_line_fields, 32767);
    if p_cols_firstline = 1 then 
      utl_file.get_line(v_filehandle, v_line_data, 32767);
    else 
      v_line_data := v_line_fields;
    end if;

    while v_f_ep != 0 loop
     v_f_ep := instr(v_line_fields, p_delimiter, v_f_sp);
     v_d_ep := instr(v_line_data, p_delimiter, v_d_sp);
     if v_f_ep > 0 then 
      v_enc_p := instr(
       replace(v_line_fields, p_enclosed||p_enclosed, ''), 
       p_enclosed, 
       v_f_sp
      );
      if v_enc_p > 0 and v_enc_p < v_f_ep then 
       v_enc_p := instr(
        replace(v_line_fields, p_enclosed||p_enclosed, ''), 
        p_enclosed, 
        v_enc_p + 1
       );
       if v_enc_p > 0 then
        v_f_ep := instr(v_line_fields, p_delimiter, v_enc_p + 1);
       else 
        v_f_ep := instr(v_line_fields, p_delimiter, v_f_sp);
       end if; 
      end if;
     end if;
     if v_d_ep > 0 then 
      v_enc_p := instr(
       replace(v_line_data, p_enclosed||p_enclosed, ''), 
       p_enclosed, 
       v_d_sp
      );
      if v_enc_p > 0 and v_enc_p < v_d_ep then 
       v_enc_p := instr(replace(
        v_line_data, p_enclosed||p_enclosed, ''), 
        p_enclosed, 
        v_enc_p + 1
       );
       if v_enc_p > 0 then
        v_d_ep := instr(v_line_data, p_delimiter, v_enc_p + 1);
       else 
        v_d_ep := instr(v_line_data, p_delimiter, v_d_sp);
       end if; 
      end if;
     end if;
     if v_f_ep = 0 then 
       v_field_name := substr(v_line_fields, v_f_sp);
       v_field_data := substr(v_line_data, v_d_sp);
     else  
       v_field_name := substr(v_line_fields, v_f_sp, (v_f_ep - v_f_sp));
       v_field_data := substr(v_line_data, v_d_sp, (v_d_ep - v_d_sp));
     end if;
     if p_enclosed is not null then 
      v_field_name := regexp_replace(v_field_name, '^'||p_enclosed, '');
      v_field_name := regexp_replace(v_field_name, p_enclosed||'$''');
      v_field_data := regexp_replace(v_field_data, '^'||p_enclosed, '');
      v_field_data := regexp_replace(v_field_data, p_enclosed||'$''');
      v_field_data := rtrim(ltrim(v_field_data));
     end if;
     v_typecode := dbms_types.typecode_varchar2;
     if p_sample = 1 and v_field_data is not null then 
       begin
         v_data_date := to_date(v_field_data);
         v_typecode := dbms_types.typecode_date;
       exception when others then
         begin
           v_data_number := to_number(v_field_data);
           v_typecode := dbms_types.typecode_number;
         exception when others then null;
         end;
       end;
     end if;
     v_record_structure.addattr(   
       ANAME     => case 
                      when p_cols_firstline = 1 then upper(replace(v_field_name,' ','_')) 
                      else 'COLUMN_'||lpad(v_field_pos, 3'0') 
                    end,
       TYPECODE  => v_typecode,
       PREC      => null,
       SCALE     => null,
       LEN       => case 
                      when v_typecode = dbms_types.typecode_varchar2 then 4000 
                      else null 
                    end,
       CSID      => null,    
       CSFRM     => null,
       ATTR_TYPE => null
     );
     v_f_sp := v_f_ep + 1;
     v_d_sp := v_d_ep + 1;
     v_field_pos := v_field_pos + 1;
     v_enc_p := -1;
    end loop;
    
    v_record_structure.endcreate();

    anytype.begincreate(dbms_types.typecode_table, record_table); 

    record_table.setinfo(
      nullnullnullnullnull, v_record_structure, dbms_types.typecode_object, 0
    ); 
    record_table.endcreate(); 
    utl_file.fclose(v_filehandle);

    return odciconst.success;
  
  -- exception when others then  
  --  return odciconst.error;
  end;   

  static function ODCITablePrepare (
    sctx               out easy_csv_parser, 
    tab_func_info      in sys.ODCITabFuncInfo, 
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number is
    prec         pls_integer; 
    scale        pls_integer; 
    len          pls_integer; 
    csid         pls_integer; 
    csfrm        pls_integer; 
    record_desc  anytype; 
    aname        varchar2(30); 
    dummy        pls_integer; 

  begin 
      -- insert into log values ('ODCITablePrepare entered') ; commit;
    dummy := tab_func_info.RetType.GetAttrElemInfo(
      null, prec, scale, len, csid, csfrm, record_desc, aname
    ); 
    sctx := easy_csv_parser(
      record_desc, nullnull, p_delimiter, p_enclosed, p_sample
    ); 
    return odciconst.success; 
  end; 

  static function ODCITableStart   (
    sctx               in out easy_csv_parser, 
    p_dir              in varchar2,
    p_filename         in varchar2,
    p_delimiter        in varchar2 default ',',
    p_enclosed         in varchar2 default '', 
    p_sample           in number default 1 ,
    p_cols_firstline   in number default 1 
  ) return number is
    v_filehandle       utl_file.file_type;
    v_line       varchar2(32767);
  begin 
    v_filehandle := utl_file.fopen(p_dir, p_filename, 'R'32767);
    if p_cols_firstline = 1 then 
      utl_file.get_line(v_filehandle, v_line);
    end if;
    sctx.utlfile_id := v_filehandle.id;
    sctx.utlfile_datatype := v_filehandle.datatype;
    return odciconst.success; 
  end; 
 

  member function ODCITableFetch   (
    self               in out easy_csv_parser,
    nrows              in number, 
    record_out         out anydataset
  ) return number is
    v_filehandle       utl_file.file_type;

    v_field            varchar2(4000);
    v_line             varchar2(32767);
    v_f_ep             pls_integer := -1;
    v_f_sp             pls_integer := 1;
    v_enc_p            pls_integer := -1;
  
    v_field_pos        pls_integer := 1;

    d_prec             PLS_INTEGER; 
    d_scale            PLS_INTEGER;
    d_len              PLS_INTEGER;
    d_csid             PLS_INTEGER;
    d_csfrm            PLS_INTEGER;
    d_schema_name      VARCHAR2(30); 
    d_type_name        VARCHAR2(30); 
    d_version          varchar2(100);
    d_count            PLS_INTEGER;
    d_desc             pls_integer;
    d_attr_elt_type    anytype;
    d_aname            varchar2(30);
  begin 
    v_filehandle.id := self.utlfile_id;
    v_filehandle.datatype := self.utlfile_datatype;
 
    d_desc := self.row_types.getinfo(
      d_prec, d_scale, d_len, d_csid, d_csfrm, 
      d_schema_name, d_type_name, d_version, d_count
    );

    record_out := null;
    anydataset.begincreate(dbms_types.typecode_object, self.row_types, record_out); 
    record_out.addinstance;
    record_out.piecewise(); 
    begin
     utl_file.get_line(v_filehandle, v_line, 32767);
     while v_f_ep != 0 and v_field_pos <= d_count loop
      v_f_ep := instr(v_line, ecp_delimiter, v_f_sp);
      if v_f_ep > 0 then 
       v_enc_p := instr(
        replace(v_line, self.ecp_enclosed||self.ecp_enclosed, ''), 
        self.ecp_enclosed, 
        v_f_sp
       );
       if v_enc_p > 0 and v_enc_p < v_f_ep then 
        v_enc_p := instr(
         replace(v_line, self.ecp_enclosed||self.ecp_enclosed, ''), 
         self.ecp_enclosed, 
         v_enc_p + 1
        );
        if v_enc_p > 0 then
         v_f_ep := instr(v_line, ecp_delimiter, v_enc_p + 1);
        else 
         v_f_ep := instr(v_line, ecp_delimiter, v_f_sp);
        end if; 
       end if;
      end if;
      if v_f_ep = 0 then 
       v_field := substr(v_line, v_f_sp);
      else 
       v_field := substr(v_line, v_f_sp, (v_f_ep - v_f_sp));
      end if;
      if self.ecp_enclosed is not null then 
       v_field := regexp_replace(v_field, '^'||self.ecp_enclosed, '');
       v_field := regexp_replace(v_field, self.ecp_enclosed||'$''');
      end if;
      d_desc := self.row_types.getattreleminfo(
       v_field_pos, d_prec, d_scale, d_len, d_csid, d_csfrm, d_attr_elt_type, d_aname
      );
      if d_desc = dbms_types.typecode_varchar2 or self.ecp_sample = 0 then 
        record_out.setvarchar2(v_field);
      elsif d_desc = dbms_types.typecode_date then 
       begin
        record_out.setdate(to_date(ltrim(rtrim(v_field))));
       exception when others then 
        record_out.setdate(to_date(null));
       end;
      elsif d_desc = dbms_types.typecode_number then 
       begin
        record_out.setnumber(to_number(v_field));
       exception when others then 
        record_out.setnumber(to_number(null));
       end;
      end if;
      v_f_sp := v_f_ep + 1;
      v_field_pos := v_field_pos + 1;
     end loop;
     while v_field_pos <= d_count loop
      d_desc := self.row_types.getattreleminfo(
       v_field_pos, d_prec, d_scale, d_len, d_csid, d_csfrm, d_attr_elt_type, d_aname
      );
      if d_desc = dbms_types.typecode_varchar2 or self.ecp_sample = 0 then 
       record_out.setvarchar2(null);
      elsif d_desc = dbms_types.typecode_date then 
       record_out.setdate(to_date(null));
      elsif d_desc = dbms_types.typecode_number then 
       record_out.setnumber(to_number(null));
      end if;
      v_field_pos := v_field_pos + 1;
     end loop;
     record_out.endcreate;
    exception 
     when NO_DATA_FOUND then 
      begin
       record_out.endcreate;
      exception when others then null;
      end;
      record_out := null;
    end;
    return odciconst.success; 
  end; 

  
  member function ODCITableClose   (
    self               in easy_csv_parser 
  ) return number is
   v_filehandle     utl_file.file_type;
  begin
    v_filehandle.id := self.utlfile_id;
    v_filehandle.datatype := self.utlfile_datatype;
    utl_file.fclose(v_filehandle);
    return odciconst.success; 
  end;
end;
/
sho err

Beliebte Postings