Kolejne artykuły na nowym blogu

Witajcie,
jeśli macie ochotę zapoznać się z nowymi artykułami z zakresu Oracle, PostgreSQL, SQL Server i innych technologii, zapraszam na nowego bloga: https://blog.jsystems.pl którego współtworzę wraz z kolegami z pracy :)

Czuwaj! ;)

Odtwarzanie pliku kontrolnego bez kopii w autobackupie

Jeśli zdarzy się tak, że padnie nam baza a nie mieliśmy włączonego autobackupu pliku kontrolnego, możemy posłużyć się poniższą metodą. W moim przypadku padł system, nie miałem wcześniej włączonego autobackupu i zostałem z zawartością FRA. Ponieważ po padzie systemu i instalacji bazy od nowa, musiałem odtworzyć starą bazę na nowej, pojawił się problem z różnymi DBID. DBID (czyli taki unikalny identyfikator bazy) przechowywany jest w pliku kontrolnym, więc ten musiałem odzyskać w pierwszej kolejności. Podczas zwykłych backupów również jest robiony backup pliku kontrolnego, trzeba go będzie tylko wskazać przy odtwarzaniu. Ja znalazłem właściwy po nazwach katalogów (w FRA jest katalog backuppiece a w nim podkatalogi z datami i w nim są malutkie pliki tak po ok 1 MB zawierające właśnie backup pliku kontrolnego). Uruchamiamy RMANa, kładziemy bazkę do NOMOUNTa i wydajemy polecenie:

restore controlfile from 'ścieżka do backupu pliku kontrolnego w FRA';

Plik kontrolny mamy odzyskany. Przechodzimy więc do mounta:

alter database mount;

Musimy teraz zrobić porządek z położeniem plików backupów z których chcemy odzyskiwać bazę. Repo backupów znajduje się w pliku kontrolnym, tak więc mamy tam informacje o starych położeniach backupów. Dajemy więc:

CROSSCHECK BACKUP;
DELETE EXPIRED BACKUP;
CATALOG START WITH 'ŚCIEŻKA DO katalogu z backupami i archivelogami (nadrzędny katalog)';

Pierwsze dwie komendy służą wywaleniu informacji o backupach których fizycznie już nie ma wg. repo. Trzecia powoduje zarejestrowanie nowego położenia backupów i archivelogów. Dalej odtwarzanie przebiega jak zawsze:

RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

Ostatnia komenda do otwarcie bazy, ale w związku z odtwarzaniem pliku kontrolnego musimy otworzyc bazę z użyciem resetlogsa. Warto pamiętać też o zrobieniu jakiegoś backupu, byśmy w razie czego mieli z czego otwarzać w ramach nowej inkarnacji która powstaje przy resetlogsie ;) Proponowałbym też zadbać o właściwe ustawienie parametru db_recovery_file_dest, bo nowe położenie FRA nie musi pokrywać się ze starym. Tą metodę możemy wykorzystać również przy duplikacji opisanej tutaj: http://andrzejklusiewicz.blogspot.com/2013/01/gotuj-z-oraclem-odtworzenie-bazy-w.html

Kurs Oracle PL/SQL. Funkcje strumieniowe

Istnieje możliwość tworzenia w PL/SQL na których wyniku możemy operować tak jak na tabeli – mam na myśli stosowanie SELECT, warunków WHERE i sortowania. Jest możliwość zastosowania funkcji zwracającej tablicę typu obiektowego, jednak stosowanie takich typów nie należy do wygodnych. Ponadto trzeba będzie poczekać na wynik do czasu aż wygeneruje się cały. Przypuśćmy, że chcemy dostać pierwsze X wierszy jak najszybciej. Reszta może zostać pobrana nieco później. Interesuje nas działanie podobne do wykonania zapytania SELECT na bardzo dużej tabeli w narzędziu takim jak SQL Developer. Stosunkowo szybko (o ile nie zastosowaliśmy np. sortowania) dostaniemy pierwsze 50 wierszy (tylko one zostały pobrane z bazy). Kolejne zostaną zfetchowane dopiero gdy przesuniemy suwak przy wyniku. Aby uzyskać taki efekt, możemy zastosować funkcję strumieniową – pipelined. Wiersze będą zwracane z funkcji jeden po drugim w takim tempie w jakim będą pobierane / generowane. Nie będziemy musieli czekać na wygenerowanie całego wyniku. Moim zdaniem ciekawa funkcjonalność w optymalizacji PL/SQL. Nic nie stoi też na przeszkodzie by użyć funkcji strumieniowych w połączeniu z typem obiektowym.

Zaczniemy od najprostszego przykładu. Stworzyłem funkcję zwracającą kolejne potęgi liczby 2. Funkcja będzie zwracać tablicę elementów typu number element po elemencie. Na wyniku tej funkcji wykorzystamy SELECT.

W pierwszej kolejności tworzę typ tablicowy elementów typu number, widoczny w całym schemacie. Dalej tworzę funkcję która zwróci tyle kolejnych potęg liczby 2 ile podamy przez parametr.

Różnicę w stosunku do zwykłych funkcji zauważyć można w liniach 3, 7 i 9. W linii 3 zauważymy deklarację "PIPELINED", jest ona wymagana jeśli chcemy wykorzystywać funkcje w sposób opisanywcześniej. W linii 7 znajdziemy "pipe row". Oznacza on po prostu zwrot kolejnego elementu z funkcji. W linii 9 znajdziemy klauzulę return która jednak nic nie zwraca... Musi ona być z powodów formalnych. Sam zwrot danych zrealizowaliśmy już wcześniej z użyciem PIPE ROW :)

W liniach 12 i 13 zobaczymy sposób wykorzystania naszej nowej funkcji. Klauzula TABLE umożliwia nam stosowanie SELECT na wyniku funkcji. Oczywiście moglibyśmy tutaj użyć również WHERE czy ORDER BY.

Teraz nieco skomplikujemy sytuację. Funkcja nie będzie zwracała tablicy elementów typu prostego, a tablicę elementów typu rekordowego.

W pierwsze kolejności muszę zdeklarować sam zwracany typu rekordowy. Ponieważ nie mogę go zdeklarować w schemacie, deklaruję go w pakiecie. Przy okazji w tym samym pakiecie deklaruję również typ tablicowy złożony z elementów właśnie utworzonego typu rekordowego. Tutaj wymagane jest pewne wyjaśnienie z moje strony. Czemu w typie rekordowym mam same pola typu varchar2? Nie jest to w żaden sposób wymagane. Po prostu wszystkie typy proste inne niż grupa LOB czy typ LONG są konwertowalne do varchar2, a ja jeszcze nie wymyśliłem zapytania któego użyję :) To sprawia też że ten typ rekordowy będzie wielorazowego użytku na potrzeby ewentualnych innych zapytań.

Dalej tworzę funkcję wykorzystującą ten typ tablicowy. Konstrukcyjnie niewiele tutaj zmian w stosunku do poprzedniej funkcji. Tyma razem jednak przetwarzam wynik zapytania. Może rzucić się w oczy linia 29 z deklaracją pojedynczego elementu do którego wrzucam zaczytany z kursora wiersz. Po co mi on potrzebny jeśli funkcja ma zwracać tablicę? Zwracać będzie tablicę, ale wierszo po wierszu. W linii 35 wywołuję PIPE ROW która nie może przecież zwrócić nam tablicy. Dlatego potrzebuję takiego elementu jako kontenera na kolejne zwracane wiersze.

Kurs administracji Oracle. Tryb Flashback

Tryb flashback umożliwia oglądanie danych w bazie, w takim stanie w jakim były w określonym punkcie w czasie. Stan danych będzie pochodził z przestrzeni UNDO, a to oznacza że dostęp do stanu z przeszłości będzie możliwy tylko wtedy,gdy oryginalne postaci danych nie zostaną nadpisane.

Aby wykorzystać tę funkcjonalność użytkownik musi mieć nadane przez administratora uprawnienia do pakietu dbms_flashback:

grant execute on dbms_flashback to hr;

Z poziomu użytkownika HR odpytuję tabelkę EMPLOYEES wybierając kilka wierszy.

Następnie dokonuję zmiany na tych wierszach i zatwierdzam ją.

Zarówno moja, jak i każda inna sesja będą widziały dane po zmianie:

Następnie komendą

execute dbms_flashback.enable_at_time(to_date('29-11-2015 12:00:00','dd-mm-yyyy

hh24:mi:ss'));

sprawiam, że widzę dane w stanie w jakim były dwie godziny przed zmianami.

I przeglądam dane:

Baza danych dla mojej sesji jest jednak tylko do odczytu. Pozostając w trybie flashback nie mam możliwości dokonywania jakichkolwiek zmian na niej. Aby wyłączyć tryb flashback stosuję polecenie:

execute dbms_flashback.disable;

Kurs Oracle PL/SQL. Ukrywanie implementacji

Oracle udostępnia nam możliwość ukrycia implementacji kodu PLSQL. Czasem chcemy wdrożyć system, ale w taki sposób by nikt niepowołany nie mógł przeglądać jego źródeł.

Możemy tego dokonać w kilku prostych krokach. Najpierw zapisujemy do pliku tekstowego kod programu:

Następnie używamy programu WRAP który służy do zamiany tego kodu na postać zaszyfrowaną:

Dalej logujemy się sqlplusem lub innym klientem i wywołujemy ten zaszyfrowany kod tak jak normalny:

Kodu takiej procedury nie da się przejrzeć:

Natomiast bez problemu można ją wywołać i wszystko będzie działać jak zawsze:

Mój bezpłatny kurs programowania na platformę Android

Stworzyłem bezpłatny kurs programowania na platformę Android :) Znajdziecie go pod tym linkiem: http://andrzejklusiewicz-android.blogspot.com/p/bezpatny-kurs-programowania-android-java.html

Jak zawsze zapraszam do oceny i komentowania ;)

Klauzula NOCOPY w PL/SQL

Klauzulę NOCOPY możemy stosować przy parametrach OUT oraz IN OUT . Sprawia ona że wartość przekazana przez parametr nie jest kopiowana (jak to się dzieje domyślnie) a przekazywana jest referencja. To oznacza, że przekazywany jest wskaźnik do obszaru pamięci, a nie wartość jako taka. Efekt jest taki, że zarówno procedura z parametrem jak i blok (lub inna procedura czy funkcja) ją wywołujący działają na TYCH SAMYCH danych, bo chodzi o tą samą przestrzeń w pamięci operacyjnej.

Z jednej strony stosowanie klauzuli NOCOPY może nam pomóc – zwłaszcza przy przekazywaniu przez parametr typu OUT dużych wartości np. długich tablic albo dużych obiektów. Z drugiej strony, jeśli np. w procedurze nastąpi jakiś wyjątek, to w naszej przekazanej zmiennej może znaleźć się coś innego niż byśmy się spodziewali. Poniżej przedstawiam przykład takiej sytuacji:

Jak widzimy, w wyniku wywołanego wyjątku, przetwarzanie w procedurze nie dobiegło do końca. W efekcie w zmiennej y bloku wywołującego znalazł się tekst „W trakcie przetwarzania” zamiast „Oryginalny oryginał”

Parametry typu IN, OUT, IN OUT w procedurach i funkcjach PL/SQL

Dla procedur oraz funkcji możemy definiować opcjonalne parametry. Mogą występować w trzech typach:

IN – parametr tylko do odczytu, poprzez który dane zostają przekazane do podprogramu.

OUT – Służy do zwracania wartości z podprogramu. Ma wartość NULL do momentu kiedy zostanie zainicjalizowana.

IN OUT – Połączenie dwóch powyższych typów. Podczas wywoływania programu tym parametrem przekazywane są do niego wartości, a po zakończeniu wykonywania zwracane. Stosuje się go gdy dane wejściowe mają zostać zmienione podczas działania programu.

Podczas deklarowania parametrów dla procedury lub funkcji nie ma sztywnych ograniczeń co do ich ilości oraz kolejności. Parametry mogę zadeklarować na kilka różnych sposobów i poniżej omawiam to na przykładach:

wejsciowy1 – parametr wejścia typu varchar2, nie posiada wartości domyślnej i dlatego przy wywoływaniu podprogramu będę zmuszony ją podać.

wejsciowy2 oraz wejsciowy3 - dwa sposoby zadeklarowania wartości domyślnej dla parametru. Jeśli nie przypiszę żadnej wartości, przypisana zostanie automatycznie wartość występująca po DEFAULT lub znaku przypisania. Podczas wywoływania podprogramu nie muszę podawać wartości do tego parametru.

wejsciowy4 – ponieważ nie określiłem jaki ma to być typ parametru, Oracle domyślnie przyjmuje że jest ot parametr typu IN.

wejsciowy5 – parametr typu wejściowego numerycznego który będę musiał uzupełnić przy wywoływaniu procedury.

wyjsciowy – parametr wyjściowy typu number. Nie mogę określić dla niego wartości domyślnej, ani wartości wejściowej przy wywołaniu programu. Mogę to zrobić jedynie wewnątrz podprogramu.

dwustronny – parametr do którego nie mogę przypisać wartości domyślnej przy wywoływaniu podprogramu. Mogę podać do niego wartość podczas wywoływania podprogramu.

Mogę określać typ parametru, nie mogę natomiast długości. Zamiast więc stosować varchar2(243) muszę zastosować samo varchar2. 

Przykład wzajemnego wywoływania procedur oraz praktycznego przekazywania parametrów.

1. Tworzę procedurę o nazwie „wypisywacz”, która po otrzymaniu danych w parametrach wejściowych (imię i nazwisko są domyślnie IN) ma wypisać na ekranie powitanie. Ponadto do parametru wyjściowego wzrost ma przypisać wartość 178.
   
   
   
   
   
   
   
   
   
   2. Z bloku anonimowego wywołuję przed momentem stworzoną procedurę podając wartości dla parametrów wejściowych, oraz nazwę zmiennej do której ma zostać przypisana wartość wyjściowa.
   
   
   
   
   
   
   
   
   
   Jak widać, po wywołaniu procedury „wypisywacz” wartość zmiennej do której została przypisana wartość wewnątrz procedury „wypisywacz” uległa zmianie.
   
   
   Parametrów wcale nie muszę wypisywać w dokładnie takiej kolejności w jakiej są zdeklarowane w definicji funkcji/procedury. Jedynym warunkiem jest określenie podczas wywoływania podprogramu do jakiej zmiennej przypisuję jaką wartość.
   
   
   
   
   
   
   

Klauzula RETURNING w PL/SQL

W PL/SQL istnieje opcjonalna klauzula RETURNING INTO pozwalająca na zapisanie do zmiennej wartości pochodzących z rekordu wstawianego przez polecenie INSERT lub modyfikowanego przez UPDATE. Taka możliwość staje się bardzo użyteczna, gdy zechcemy uzyskać ID pochodzącego z sekwencji właśnie wstawionego rekordu, lub generowanej dynamicznie innej wartości (np. jeśli wstawiamy sysdate).

W powyższym przykładzie wstawiłem nowy wiersz do tabeli jobs, a przy pomocy klauzuli RETURNING INTO uzyskałem ID wstawionego wiersza. Wartość ID została przypisana do zmiennej ID której typ został zdeklarowany na podstawie typu kolumny job_id z tabeli jobs. Taka możliwość nabiera ogromnego znaczenia, jeśli wartość wstawiana do kolumny klucza głównego pochodzi z sekwencji, a mamy zamiar operować na właśnie wstawionych danych. Oczywiście w takim przypadku można by również zastosować odwołanie do currval wykorzystywanej sekwencji, jednak nie mamy żadnej gwarancji, że w międzyczasie ktoś inny nie skorzystał z tej samej sekwencji i nie zmienił jej wartości.

Klauzula FOR UPDATE

Przy użyciu klauzuli FOR UPDATE możemy zablokować wiersze do edycji przez inne sesje. Działa to na zasadzie transakcyjnej blokady zasobów. Jeśli my wykonamy jakiś UPDATE lub DELETE, wiersze których te polecenia zostaną zablokowane do czasu zatwierdzenia lub wycofania transakcji. W tym czasie inne sesje usiłujące dokonać jakiejkolwiek zmiany będą musiały oczekiwać na zwolnienie zasobów przez nas. Najniższy poziom blokady to wiersz, tak więc nawet jeśli zmienimy zawartość jednej kolumny, nikt nie będzie mógł zmienić również pozostałych kolumn w tych wierszach.
Klauzulę tę możemy wykorzystywać zarówno w SQL, jak i w kursorach w PL/SQL. Przykład użycia w SQL:

Wyświetlam 3 osoby z departamentu nr 90 , jednocześnie blokując te wiersze do edycji przez inne sesje. Teraz z innej sesji usiłuję te wiersze zmodyfikować:

Zauważ że modyfikuję inną kolumnę, niż te które wyświetlałem z pierwszej sesji. Sesja czeka na zwolnienie zasobów. Możemy teraz swobodnie dokonać zmian, bez obawy że ktoś inny w międzyczasie dokona jakichś zmian na „naszych” wierszach.

Dopiero po wydaniu polecenia „COMMIT”, wiersze zostają odblokowane i sesja która oczekiwała na odblokowanie zasobów może dokonać zmian:

Klauzulę FOR UPDATE możemy wykorzystywać również w PL/SQL w kursorach. Samo zadeklarowanie kursora nie spowoduje jednak blokady wierszy, jak się za chwilę przekonamy.
Uruchomiłem blok anonimowy z samą deklaracją kursora:

Aktualizacja z innej sesji przebiegła bez żadnych problemów:

Aby wiersze zostały zablokowane , kursor trzeba przynajmniej otworzyć:

Nie koniecznie musi to być otwarcie jawne, może być to również automatyczne otwarcie kursora które
następuje w pętli kursorowej, tak jak to widać poniżej:

Z wykorzystaniem klauzuli for update wiąże się również klauzula „WHERE CURRENT OF” która pozwala aktualizować lub kasować wiersze zablokowane przez kursor.

Istotna uwaga: klauzula WHERE CURRENT OF odnosi się do wiersza który właśnie został zfetchowany z kursora.

Niezależnie od ilości wierszy w kursorze, klauzula WHERE CURRENT OF odnosi się do ostatnio pobranego z kursora wiersza. Poniżej zastosowałem pętle kursorową, i jak widzimy zawsze ilość zaktualizowanych wierszy wynosi 1.

W przypadku próby wykorzystania klauzuli WHERE CURRENT OF bez uprzedniego fetcha, dostajemy błąd :

Funkcje deterministyczne

Najpierw wyjaśnijmy, co to znaczy że funkcja jest lub nie jest deterministyczna. Funkcja jest deterministyczna wtedy, kiedy dla takich samych parametrów zwróci zawsze ten sam wynik. To oznacza, że taka funkcja musi działać zawsze w ten sam sposób, a na wynik nie powinny wpływać żadne czynniki zewnętrzne tj. funkcja nie powinna korzystać z żadnych zmiennych pakietowych ani innych źródeł zewnętrznych. Taka funkcja nie może też zmieniać żadnych danych w bazie (w tabelach ani pakietach). W niektórych sytuacjach wymagane jest by funkcja była deterministyczna. Przykładowo jeśli zechcemy użyć własnej funkcji w indeksie funkcyjnym, to funkcja ta musi być deterministyczna. Nie tylko spełniać warunek jako taki, ale też musi to być jasno określone w treści funkcji.
Poniżej przykład. Tworzę zwykłą funkcję, której już konstrukcja jasno wskazuje że funkcja jest deterministyczna (wartość parametru zawsze zostanie podzielona przez 12 i zaokrąglona do 2 miejsca po przecinku). Nie jest to jednak określone specjalną klauzulą DETERMINISTIC.
Przy próbie wykorzystania takiej funkcji w indeksie funkcyjnym dostajemy błąd
„ ORA-30553 The function is not deterministic”

Teraz dodaję klauzulę DETERMINISTIC (ponadto nic się nie zmienia), i przebudowuję funkcję, a następnie ponownie próbuję stworzyć indeks funkcyjny w oparciu o tę funkcję:

Tym razem obyło się bez problemów.

Pakiet DBMS_SQL

Pakiet DBMS_SQL możemy wykorzystywać alternatywnie do klauzuli EXECUTE IMMEDIATE. Każdą z czynności : otwarcie kursora, parsowanie zapytania, wykonanie zapytania i zamknięcie kursora wykonujemy tutaj ręcznie i osobno. To ma swoje zady i walety. Z jednej strony jest więcej pisania i ilość kodu gwałtownie wzrasta. Z drugiej strony mamy większą kontrolę nad wszystkim co się dzieje, kod jest nieco bardziej przejrzysty. Przeanalizujmy teraz porównianie obu tych technik.
Użycie EXECUTE IMMEDIATE :

To samo z użyciem pakietu DBMS_SQL:

W obu przypadkach musiałem konkatenować zapytanie, z tym że w pierwszym wykonanie całości sprowadza się do polecenia EXECUTE IMMEDIATE. W drugim mamy kilka innych poleceń, wywołań procedur i funkcji z pakietu DBMS_SQL. Przeanalizujmy je po kolei.



dbms_sql.open_cursor
To funkcja alokująca przestrzeń na przetwarzanie kursora. Zwraca numeryczą referencję do kursora (taki identyfikator, dzięki któremu wiadomo o który kursor chodzi).



Dbms_sql.parse
Procedura ta sprawdza poprawność semantyczną zapytania (sprawdza czy wpisałeś zapytanie czy np. „Suchą szosą szedł sobie Sasza” ). W przypadku operacji DDL procedura ta wykonuje polecenie! W takim przypadku dzieje się to już tutaj i polecenie execute nie jest obowiązkowe.
Pierwszy parametr to identyfikator kursora (uzyskany przy wywołaniu funkcji open_cursor), drugi to zapytanie które będzie wykonywane, trzeci to wersja SQL. Można w trzecim parametrze ustawić zgodność SQL z wersją np. 6.












dbms_sql.execute
Funkcja wykonuje zapytanie. Jest zbędna w przypadku operacji DDL. W przypadku operacji DML zwraca ilość wierszy które uległy zmianie. W przypadku klauzuli EXECUTE IMMEDIATE również moglibyśmy się dowiedzieć ilu wierszy dotyczyła zmiana, ale musielibysmy dodatkowo użyć klauzuli SQL%ROWCOUNT.



dbms_sql.close
Dealokuje przestrzeń w pamięci operacyjnej wykorzystywaną na potrzeby przetwarzania kursora.



Możemy też wykorzystać zmienne bindowane w obu przypadkach. Wersja z EXECUTE IMMEDIATE:

Wersja z DBMS_SQL:



declare
dep integer:=90;
mana integer:=100;
podwyzka number:=400;
cu_id integer;
ile integer;
begin
cu_id:=dbms_sql.open_cursor;
dbms_sql.parse(cu_id, 'update employees set salary=salary+:p where department_id=:d and manager_id=:m',dbms_sql.native);
dbms_sql.bind_variable(cu_id,':p',podwyzka);
dbms_sql.bind_variable(cu_id,':d',dep);
dbms_sql.bind_variable(cu_id,':m',mana);
ile:=dbms_sql.execute(cu_id);
dbms_sql.close_cursor(cu_id);

end;

To co się tutaj pojawiło nowego, to wywołanie procedury bind_variable. Służy ona do podawania wartości jakie mają trafić do zmiennych bindowanych. Pierwszy parametr to identyfikator kursora, drugi zmienna bindowana użyta w zapytaniu, trzeci wartość do wstawienia do tej zmiennej. W zasadzie w obu przypadkach zamiast zmiennych dep,mana i podwyżka mógłbym użyć równie dobrze wartości bezpośrednio.

Wyrażenia regularne w Oracle (REGEXP)

Wyrażenia regularne

Funkcje

Wyrażenia regularne pozwalają nam wynajdywać w tekście fragmenty wg wzorców. Związane z regexp jest 5 funkcji:

REGEXP_COUNT

funkcja dostępna od wersji 11g. Zwraca ilość wystąpień elementów pasujących do wzorca. Poniższy przykład zwraca ilość liczb występujących w tekście:



select regexp_count('fff4ff563fffff','[[:digit:]]')
from dual;

REGEXP_REPLACE

Funkcja zamienia elementy pasujące do wzorca na podany zamiennik. Na poniższym przykładzie widzimy zamianę wszystkich cyfr na ciąg XXX.



select regexp_replace('fff4ff563fffff','[[:digit:]]','XXX')

from dual;

REGEXP_SUBSTR

Funkcja zwraca z podanego tekstu pierwszy element pasujący do wzorca. Poniżej widzimy wycięcie pierwszego przynajmniej 3 elementowego zbioru cyfr z tekstu:


select regexp_substr('eeeeee23ee44445eee444445','[[:digit:]]{3,}')

from dual;

REGEXP_INSTR

Funkcja zwraca pozycję ciągu tekstowego pasującego do wzorca. Poniżej określenie pozycji elementu składającego się z przynajmniej 4 cyfr:



select regexp_instr('eeeeee23ee44445eee444445','[[:digit:]]{4,}')

from dual;

REGEXP_LIKE

Służy sprawdzaniu czy podany tekst pasuje do wzorca:



select * from employees where
regexp_like(phone_number,'[[:digit:]]{3}\.[[:digit:]]{2}\.[[:digit:]]{4}\.[[:digit:]]{6}');

Wzorce w wyrażeniach regularnych

.	Dowolny znak
+	Jeden lub więcej wystąpień
?	Zero lub jedno wystąpienie
*	Dowolna ilość wystąpień (w tym zero)
{3}	Dokładnie trzy wystąpienia
{3,}	Przynajmniej trzy wystąpienia
{3,6}	Od trzech do sześciu wystąpień
|	Lub
[[:digit:]]	Cyfra
[[:alpha:]]	Litera
[[:alnum:]]	Znak alfanumeryczny
\	Wyłączenie znaku jako specjalnego. Np. jeśli zechcemy użyć kropki jako w kontekście znaku tekstowego, a nie znaku specjalnego oznaczającego dowolny znak

To oczywiście nie wszystkie możliwe wzorce, jest ich znacznie więcej. Wzorce dotyczące ilości wystąpień dotyczą zawsze poprzedzającego elementu. Kilka przykładów:

'[[:digit:]]{4,}'	Przynajmniej 4 cyfry
([[:alnum:]]| |\.|-){8,14}	Ciąg składający się z od 8 do 14 elementów wśród których mogą wystąpić dowolne litery i cyfry, spacje, kropki lub myślniki
[[:alnum:]]{1,}(\.[[:alnum:]]{1,})?@[[:alnum:]]{1,}\.[[:alpha:]]{2,4}	Przynajmniej jednoelementowy wyraz składający się z liter lub cyfr, po którym może nastąpić ciąg rozpoczynający się od kropki i składający się z przynajmniej jednej litery lub cyfry, po których nastąpi znak @, po których nastąpi przynajmniej jednoelementowy ciąg alfanumeryczny, po którym nastąpi kropka i ciąg składający się z samych tylko liter o całkowitej długości od 2 do 4 znaków. Czyli znajdziemy element wyglądający jak adres email :)

Tabele zewnętrzne (External Table) typu DATA_PUMP

Tabele zewnętrzne typu Data_Pump

Od wersji 10g istnieje możliwość szybkiego utworzenia pliku na dysku na podstawie danych z zapytania. Taki plik jest w formacie Oracle Data Pump i może być przez to narzędzie odczytywany. Plik taki możemy też przenieść do innego systemu i podpiąć go jako tabelę zewnętrzną.

Nie możemy wyrzucić danych do dowolnego katalogu na dysku, a jedynie do tych które zostały zamapowane i mamy uprawnienia do zapisu w nich. W pierwszej kolejności mapujemy więc istniejący katalog i nadajemy użytkownikowi stosowne uprawnienie (jako administrator):

create directory temp as 'c:\temp';
grant read, write on directory temp to hr;

Następnie przystępujemy do eksportu:

create table lista_plac

organization external

( type oracle_datapump

default directory temp

location ('lista_plac.dmp')

) as select last_name,first_name,salary from employees;

Parametr default directory określa alias katalogu w którym ma się znaleźć nasz plik eksportu. Location służy do podania nazwy pliku do jakiego dane mają zostać wyeksportowane. Dane jakie mają zostać wyeksportowane są określane przez zapytanie na końcu instrukcji.

Po takiej tabeli możemy wywoływać select'y jak po każdej innej:

nie możemy jednak niestety aktualizować danych w takiej tabeli. Pozostanie ona tylko do odczytu.

Plik taki możemy za to przenieść do innego systemu i podpiąć go pod inną bazę.

Po stronie drugiej bazy musimy zamapować katalog w którym plik z danymi się znajdzie, oraz nadać do niego odpowiednie uprawnienia:

create directory dane as 'c:\temp\imporciki';

grant read,write on directory dane to hr;

Następnie tworzymy tabelę:

create table zarobki

(

first_name varchar2(50),

last_name varchar2(50),

salary number

)

organization external

( type oracle_datapump

default directory dane

location ('lista_plac.dmp')

) ;

Nazwy kolumn w takiej tabeli nie mogą być przypadkowe, muszą odpowiadać nazwom pod jakimi zostały wyeksportowane. Definiujemy katalog w którym znajduje się taki plik, oraz jego nazwę. Z takiej tabeli korzystamy tak jak i wcześniej: