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