Om snel een csv bestand te koppelen een Oracle database, gebruik ik external tables. Hier een quick note hoe dit aan te maken:
Bepaal op de server een directory waar de te koppelen bestanden terecht gaan komen. In mijn geval is dat /var/oracle/import01. Zorg dat deze dir voldoende lees/schrijf rechten heeft. Alles wordt uitgevoerd als oracle:dba op mijn systeem. (OpenSuse 11.3 met Oracle 10XE).
Log nu in als iemand die rechten heeft om Oracle directory’s aan te mogen maken. En maak de directory en grant deze aan diegene die het schrijven en lezen gaat uitvoeren (knilluz in mijn geval dus).
create or replace directory import01 as '/var/oracle/import01'; grant read, write on directory import01 to knilluz;
Plaats nu het bestand in de dir (/var/oracle/import01/ext_tab_test.txt in mijn geval).
En creëer de tabel:
create table ext_table_test
( voornaam varchar2(255)
, achternaam varchar2(255)
, voorvoegsels varchar2(255)
, geboortedatum varchar2(255)
, straat varchar2(255)
, huisnummer varchar2(255)
, postcode varchar2(255)
, plaats varchar2(255)
)
organization external
( default directory import01
access parameters
( records delimited by newline
characterset 'UTF8'
fields terminated by ';'
missing field values are null
)
location ('ext_tab_test.txt')
);
Let er goed op dat de layout van het bestand de volgorde heeft van de volgorde van de kolommen in het create table gedeelte. Omdat ik lui ben neem ik voor het creeeren van de velden ‘onzinnig’ grote datatypes allen van varchar2(255). In het verwerkingsproces ga ik hier wel string functies op los laten. Omdat het bestand in UTF8 is gecodeerd, heb ik dit als characterset clausule opgenomen. Omdat anders lettertekens malle dingen gaan doen met namen als Nedžija, Henriëtte of José
Zo zit in dit bestand alleen maar de voornamen van de personen. En daar zitten juwelen tussen Hermina Dirkina Maria Barbara Geertruida Bernardina bijvoorbeeld. Hier wil ik echter alleen voorletters van hebben (H.D.M.B.G.B.). Daar had ik het volgende stukje sql voor geschreven (met uitleg, want :
— voorletters van voornamen maken (voorletters is van datatype varchar2(10))
— 1. vervang alle niet hoofdletters door ‘niks’ [REGEXP_REPLACE(voornamen,'([^A-Z])’,”)] (Hermina Dirkina Maria Barbara Geertruida Bernardina wordt dan (HDMBGB)
— 2. neem alleen de eerste 5 voorletters [substr(‘[result stap 1]’,1,5)] (HDMBGB wordt dan HDMBG)
— 3. vervang iedere occurrence van de huidige string (.) door zichzelf met een puntje erachter [REGEXP_REPLACE(‘result stap 2′,'(.)’,’\1.’)]. (HDMBG wordt nu H.D.M.B.G.
REGEXP_REPLACE(SUBSTR(REGEXP_REPLACE(voornamen,'([^A-Z])',''),1,5),'(.)','\1.') voorletters
Oh en nog een laatste vingeroefeningetje. Zoek alle voornamen waar malle tekens in voorkomen (dus sluit die namen uit waar alleen maar a-z’en, A-Z’en en spaties in voorkomen.
SELECT * FROM table WHERE REGEXP_LIKE(voornamen,'[^a-z,A-Z, ]')
—