Det er typisk dumps af større felttyper (10K+) fra et andet system, hvor det har været besværligt at flytte data via alm. databasedrivere, så vi i stedet får en zip-fil fuld af tekstfiler, og en beskrivelse af, at navnet på filerne fortæller f.eks., hvilket kundenummer data skal påhæftes i Oracle databasen.
Frem for rent faktisk at loade disse data ind, er det ofte nemmere at tilgå dem direkte fra Oracle, og i ét trin at flytte data hen til deres slut destination med almindelige insert update kolonner. Dette kan f.eks. gøres med external tables.
En external table i Oracle er i virkeligheden blot en pegepind, der peger ud af databasen og hen på en tekstfil, og en beskrivelse af, hvordan tekstfilen skal læses. Dette er en super effektiv måde at tilgå en datafil i en kort periode mens man henter og bearbejder dens indhold.
Men der er også mulighed for intelligent at tilgå mange datafiler på én gang. Dette kan hjælpe i situationer som den der er beskrevet ovenfor, hvor f.eks. 10.000 filer indeholder én lang kommentar hver, hvor filnavnet er det tilhørende kundenummer, og hvor man gerne vil have opdateret et CLOB felt i databasen ud fra dette kundenummer. Tricket er at bruge COLUMN TRANSFORMS og FROM LOBFILE mulighederne i external table. I det følgende eksempel er dette gjort på en Windows server, men tricket kan også bruges på Linux.
Lad os sige at vi har 10.000 små datafiler i et bibliotek på databaseserveren. Det første vi skal gøre at give adgang til dem fra databasen. Dette gøres med et directory:
create directory ext_files as ‘d:\lots_of_files_dir’;
Det næste vi skal gøre er at sørge for er, at vi har én fil vi kan tage udgangspunkt i. Det har vi ikke til at starte med, da de 10.000 filer ikke kender noget til hinanden, men det kan heldigvis nemt klares med en alm DIR kommando, hvor outputtet kanaliseres over i en output fil:
dir /b >file_names.txt
Det giver et indhold i output-filen der svarer til dette:
Kunde00001.txt Kunde00013.txt Kunde00017.txt … Kunde18212.txt Kunde19231.txt file_names.txt
Da batch kommandoer ikke kender noget til transaktioner eller ACID-tankegang, vil navnet på den nye fil vi skaber også komme med i outputtet, så den skal vi huske at slette i filen.
Den nye fil benytter vi så som LOCATION fil i vores external table, og henter dens data ind i den første kolonne i vores external table.
Men samtidigt definerer vi en CLOB kolonne, som skal gives os indholdet af den tilhørende fil.
Så toppen af vores external table definition ser således ud:
CREATE TABLE lob_tab ( filename VARCHAR2(100), clob_content CLOB ) ORGANIZATION EXTERNAL
Dernæst angiver vi type og, hvilket directory, der skal benyttes for at nå filerne:
TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_files
Så kommer mapningen af, hvordan data skal forstås, inkl. angivelse af en log fil, hvor evt. ulæselig data skal logges, en fake field terminator, der aldrig skal bruges da hver record kun har én værdi, og en NULL angivelse:
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE ext_files:'lob_tab_%a_%p.bad' LOGFILE ext_files:'lob_tab_%a_%p.log' FIELDS TERMINATED BY '¤' MISSING FIELD VALUES ARE NULL ( filename CHAR(100) )
Dernæst følger selve tricket, hvor vi fortæller clob_content kolonnen skal “transformers” ud fra en selvstændig fil i directoriet med det navn der står filename kolonnen:
COLUMN TRANSFORMS (clob_content FROM LOBFILE (filename) FROM (ext_files) CLOB) )
Endelig fortæller vi hovedfilens navn og, at der blot skal køres videre ligegyldig, hvor mange fejl vi evt. støder på:
LOCATION ('file_names.txt')
)
REJECT LIMIT UNLIMITED
Alt I alt kommer CREATE kommandoen så til at se således ud:
CREATE TABLE lob_tab (
filename VARCHAR2(100),
clob_content CLOB
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_files
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE ext_files:'lob_tab_%a_%p.bad'
LOGFILE ext_files:'lob_tab_%a_%p.log'
FIELDS TERMINATED BY '¤'
MISSING FIELD VALUES ARE NULL
(
filename CHAR(100)
)
COLUMN TRANSFORMS (clob_content FROM LOBFILE (filename) FROM (ext_files) CLOB)
)
LOCATION ('file_names.txt')
)
REJECT LIMIT UNLIMITED
/
Efter denne kommando er eksekveret er både filnavne og indhold så umiddelbart tilgængelige med en almindelig SELECT, og vi kan flytte på data som vi ønsker det:
SELECT * FROM lob_tab
FILENAME CLOB_CONTENT: Kunde00001.txt Her starter en lang kommentar om… Kunde00013.txt Dette er en beskrivelse af hvordan kunden… Kunde00017.txt Denne kunde bør altid… … 10000 rows returned
