Select po pliku tekstowym

Dobry trick przy maści wszelkiej integracji systemów. Przypuśćmy że jeden system ma gdzieś tam na dysku pliki CSV, aktualizuje je regularnie. Może być też tak że taki plik jest często eksportowany z innego miejsca, a my chcemy być z danymi zawartymi w tym pliku na bieżąco. Regularne importy nawet przy użyciu CRONa są nieco workaroundem bo przecież dane mogą się zmieniać co parę sekund, a poza tym import to spore obciążenie dla systemu.
W Oracle jest opcja stworzenia specjalnej tabeli będącej niejako nakładką na plik tekstowy. Sprawia ona że możemy korzystać z danych w pliku tekstowym prawie w taki sam sposób jakby znajdowały się one bezpośrednio w tabeli. Pisząc prawie mam na myśli to, że możemy je czytać ale nie możemy do nich pisać.

Aby móc korzystać ten sposób z pliku tekstowego, Oracle musi mieć zmapowany katalog w którym owy plik się znajduje. Zaloguj się jako SYS i wykonaj poniższe polecenia:

create directory loader as 'c:\andrzej\loader'
grant read, write on directory loader to hr

Oczywiście wpisz adres katalogu na swoim dysku. Katalog musi fizycznie istnieć na dysku, a użytkownik systemowy Oracle (z którego baza korzysta w systemie) musi mieć do niego dostęp. Jeśli pracujesz w Linuksie, nadaj odpowiedni chmod. Druga linia powyższego kodu nadaje uprawnienia do zapisu i odczytu ale użytkownikowi bazodanowemu a nie systemowemu.

Zaloguj się jako user któremu nadałeś uprawnienia do tego katalogu (tutaj HR) , a następnie uruchom poniższy kod.


CREATE TABLE regiony_plik (
region_id number,
region_name varchar2(100)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY loader
ACCESS PARAMETERS(
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(region_id, region_name)
)
LOCATION ('regiony.csv')
)
REJECT LIMIT UNLIMITED;

Zauważ że jest to zwyczajna definicja tabeli z pewnymi dodatkami.
Type oracle_loader oznacza że jest to tabela z której będziemy korzystać w sposób opisany na początku tego posta.
Default directory loader - tutaj podajemy nazwę katalogu pod jaką funkcjonuje on w Oracle - nie podajemy tutaj żadnej ścieżki. Po to tworzyliśmy wcześniej mapowanie katalogu.
Fields terminated by ',' określa znacznik jaki rozdziela poszczególne kolumny w pliku csv.
Missing field values are null mówi oraclowi że puste wartości pomiędzy znakami rozdzielającymi to nie błąd tylko po prostu pusta wartość w kolumnie.
(region_id, region_name) to nazwy kolumn do jakich mają w podanej kolejności być ładowane dane.
Location('regiony.csv') określa nazwę pliku w zmapowanym katalogu.
Zrób sobie jakiegoś selecta na stworzonej tabeli by upewnić się że wszystko gra. Niektóre błędy mogą pojawić się dopiero teraz.

I voila!