Oracle Business Intelligence

A slide-okat most is feltöltöttem a netre, a jelszó a szokásos (titok), a szokásos okból.

Habár a téma nem a legegyszerűbb, és én is belemegyek majd technikai részletekbe, remélem a sok T-SQL és PL/SQL kód között azért majd lesz idő egy koccintásra is :) És nagyon remélem, hogy ezúttal látszódni fognak a screenshot-jaim a projektoron, nem úgy, mint az egyetemi önálló labor előadáson..

Az Oracle Relational Migration Maps (röviden: Migration Maps) az Oracle által használt módszertan egy meglévő adatbázis Oracle adatbáziskezelőre migrálásához.

Nem tudom, hogy ez mennyire aktuális, ugyanis a honlapja jól láthatóan egy régebbi design-ba illeszkedik, viszont az OTN-ről jelenleg is elérhető, ezért én úgy veszem, hogy az ott leírtak még ma is tükrözik az Oracle álláspontját :) De igazából nem is a konkrét részletek az érdekesek, hanem az eddig leírt részletes technikai problémák és megoldási javaslatok helyett három lépést hátralépve madártávlatból is megnézzük egy migráció folyamatát.

A folyamatban négy fő szerepkör lett definiálva:

• Ügyfél
• Migrációs projekt menedzser
• Adatbázis migrációs mérnök
• Alkalmazás migrációs mérnök

Az ügyfél feladata, hogy technikai segítséget nyújtson a migráció során az alkalmazással kapcsolatban felmerülő kérdésekben, ugyanis ebben a módszertanban a migrációt alapvetően az Oracle szakemberei végzik az ügyfél aktív közreműködésével. Ezen kívül a projekt elején ki kell tölteni egy úgynevezett Assessment Questionnaire-t, aminek célja az alapvető követelmények meghatározása, a szükséges erőforrások felbecsülése, a jelenlegi alkalmazás bemutatása. A kérdőívben olyan kérdések szerepelnek, mint például: "What business problems does your application system solve?" vagy "What is the timeframe for your current migration project?". Talán a "Select all of the technologies and languages used in your application system." kérdésre adható válaszlehetőségekből is látszik, hogy ezt a kérdőívet bizony nem ma írták, ugyanis a listában nem szerepel a C# .NET, sőt, a .NET egyedül ASP.NET kontextusban merül fel.

Az általános kérdések közé becsempésztek pár igen komoly - technológiai különbségekre utaló - kérdést is, ezek:

• "Does your database or application system require any precision past seconds in the DATETIME column?": ez azért érdekes, mert az Oracle DATETIME adattípusa csak másodperc pontossággal képes az időt tárolni, míg SQL Serverben ez 3.33 ms.
• "Does your database or application system evaluate empty strings ('') and NULLs as the same?": ugyanis Oracle alatt az üres string az NULL.

A kitöltött kérdőíven felül el kell küldenie az alkalmazás forráskódját is a migrációs projekt menedzsernek.

Igazából ezek egyszerű igen/nem kérdéseknek tűnnek, de ha mondjuk nekem kéne megválaszolnom őket, nem tudom, hány napig tartana a helyes válasz kiderítése.. Persze, ha valakinek olyan rendszerrel van dolga, ahol a legutolsó kis részlet is végletekig dokumentálva van, akkor más a helyzet.

A migrációs projekt menedzser áll a középpontban, ő biztosítja a magas minőségű munkát az Oracle részéről, illetve kezeli az ügyfél igényeit. Rajta keresztül zajlik a kommunikáció, ő tervezi meg a feladatokat, illetve azok időzítését. Magyarul ő a projekt menedzser, ennek minden feladatával és felelősségével együtt :)

Az adatbázis migrációs mérnök az Oracle Migration Workbench segítségével (akkor, amikor ezt a módszertant készítették, a Migration Workbench még egy különálló alkalmazás volt, most már az SQL Developer-be integrálták) migrálja a forrás adatbázist, és teszteli, hogy valóban úgy működik, mint az eredeti verzió.

Ehhez hasonlóan, az alkalmazás migrációs mérnök az alkalmazás megfelelő módosításaiért felelős.

A migráció 6 fő lépésből áll, melyeket az alábbi ábra szemléltet:


A definíciós fázisban kell elvégezni a meglévő rendszer részletes vizsgálatát, felmérni a feladat komplexitását, hogy meg lehessen becsülni a migrációs feladatokat, azok időtartamát.

Az analízis fázisban kezdődik az igazi munka. Egyeztetni kell az ügyféllel a definíciós fázisban felderített kérdéseket, problémákat, javaslatokat. Egy migráció során nem triviális, hogy az ügyfél mennyire ismeri a célrendszert, annak sajátosságait, a forrásrendszerrel szembeni különbségeit, ezért fontos feladat az oktatás. Az analízis fázisban kell meghatározni, hogy az ügyfélnek milyen témákban van szüksége oktatásra.

Ezen felül szükséges még az adatbázis biztonsági, adminisztrációs követelményeinek illetve biztonsági mentések és visszaállítások stratégiájának vizsgálata.

A tervezési fázis arról szól, hogy a definíciós és analízis fázis során szerzett ismeretek alapján megtervezzük a felmerült feladatok, problémák megoldását, azaz elkészítjük az oktatási tervet, a célrendszer hardver, hálózati és szoftver architektúráját, a felmerült architektúrális problémákra a megoldási terveket (például: ideiglenes táblák, séma-objektum nevekben történő változások, stb.), az adatok migrációjának a stratégiáját, átállási stratégiát, stb.

A migrációs fázis célja, hogy tömör, megbízható script-ek és riportok álljanak az ügyfél rendelkezésére, amikből felépítheti a célrendszer alatt az adatbázis sémát. Ebben a fázisban történik a tesztelés is.

Az átállás fázis - mint ahogy a nevéből is következik - az átállást hivatott megvalósítani, azaz a migrált rendszer feltelepítését, ügyfél általi tesztelését, adatkonverziót illetve élesbe állást.

Az utolsó, üzemeltetési fázisban szükség lehet, hogy a már feltelepített, éles rendszerben kell támogatást nyújtani az ügyfélnek.

Tranzakciókezelés

Microsoft SQL Server 2000 alatt három féle módon lehet tranzakciót kezdeni:

Explicit tranzakciók esetén a programozó határozza meg a tranzakció elejét és végét is, Transact-SQL scriptek esetén BEGIN TRANSACTION / COMMIT / ROLLBACK utasításokkal, ADO.NET esetén pedig az SqlConnection objektum BeginTransaction() illetve Commit() / Rollback() metódusaival.

A BEGIN TRANSACTION utasítás 1-el növeli a @@TRANCOUNT változó értékét (alapesetben a változó 0), a COMMIT 1-el csökkenti, míg a ROLLBACK 0-ra állítja. Ez azért van így, mert SQL Server alatt úgy lehet tranzakciókat egymásba ágyazni, hogy a beágyazott tranzakciók esetében az adatbázis a COMMIT utasításokat figyelmen kívül hagyja, csak a legkülső COMMIT után lesznek a módosítások véglegesítve. A ROLLBACK utasítás viszont minden aktív tranzakciót visszagörget.

Ezzel szemben Oracle adatbáziskezelő alatt egy tranzakció az első végrehajtható SQL utasítással kezdődik, és vagy akkor ér véget, amikor explicit módon kiadunk egy COMMIT illetve ROLLBACK utasítást, vagy implicit módon, pl.: egy DDL utasítás végrehajtásával, vagy az adatbázisból történő kilépéskor (disconnect).

Léteznek még úgynevezett autonóm tranzakciók (autonomous transactions), amik teljesen függetlenek a "hívó" tranzakciótól. Például, ha egy tárolt eljárás az AUTONOMOUS_TRANSACTION direktívával lett lefordítva, akkor, amikor meghívódik egy külső tranzakcióból, nem látja annak a még nem véglegesített adatait, illetve tőle függetlenül lesz véglegesítve vagy visszagörgetve. Ez például naplózást megvalósító eljárások esetén lehet hasznos.

Az Oracle Migration Workbench a BEGIN TRANSACTION utasításokat a SET TRANSACTION READ WRITE utasítássá fordítja át, ami az aktuális tranzakciót úgy állítja át, hogy utasítás szintű konzisztenciát biztosítson (statement-level read consistency).

Ez annyiban nem szerencsés, hogy míg a BEGIN TRANSACTION tetszőleges helyen szerepelhet egy T-SQL blokkban, addig Oracle alatt a tranzakció tulajdonságait csak az első utasításban lehet beállítani. Azaz például, ha egy INSERT szerepel a BEGIN TRANSACTION előtt (az explicit tranzakción kívül, alapértelmezetten autocommit módban), akkor ezt a Migration Workbench segítségével PL/SQL-re fordítva valami ilyesmit kapunk:

CREATE OR REPLACE PROCEDURE sp_something
AS
BEGIN
INSERT INTO proba
VALUES ( 1, SYSDATE );

SET TRANSACTION READ WRITE;

INSERT INTO proba
VALUES ( 2, SYSDATE );

ROLLBACK;

END;

ami - habár szintaktikailag helyes, azaz le fog fordulni - futáskor hibát fog adni:

ORA-01453: SET TRANSACTION must be first statement of transaction

Gond van még a @@TRANCOUNT változóval is, mert a dokumentáció szerint hiába támogatja a Migration Workbench a globális változók migrációját, nekem összesen annyit sikerült elérnem, hogy a @@TRANCOUNT-ot v_transcount-ra cserélte. A SwisSQL (a Migration Workbench konkurrenciája) ezt a kérdést úgy oldja meg, hogy egy package-ben deklarál egy változót, amit minden egyes tranzakciókezelő utasításkor növel/csökkent, azaz BEGIN TRANSACTION helyett növeli eggyel, COMMIT után csökkenti eggyel, és ROLLBACK után pedig 0-ra állítja.

Ma délután kértek fel arra, hogy holnap tartsak egy előadást az önálló labor témámról (migráció), mivel többen visszamondták a sajátjukat. Gyorsan összedobtam egy pár slide-ból álló prezentációt, aztán majd lesz, ami lesz :) Elvégre benne vagyok a témában, tehát olyan nagy meglepetés nem érhet..

A .rar archívumhoz a jelszó: titok, csak azért védtem így le, mert benne van az email-címem, aztán a csúnya spam-robotok nehogy megszeressenek.

Október 27-én egy Oracle Blogger Dinner keretén belül egy másik előadást is fogok tartani, szintén migráció témában, de az picit nagyobb lélegzetvételű lesz: kb. 1 óra a mostani 10-15 perc helyett. Ha elkészül majd - terveim szerint a jövő heti hosszú hétvége alatt - akkor azt is közzé fogom tenni.

November 6-án, csütörtökön 9 órától lesz az Oracle Business Intelligence Fóruma, ahol bemutatják az Oracle üzleti intelligencia és adattárház megoldását, annak újdonságait és terveit.

A rendezvény ingyenes, regisztrációhoz kötött. Én megpróbálok ott lenni, mindenesetre már regisztráltam :)

Az előző dimenzionális modellezésről szóló bejegyzésben megemlítettem, hogy a csillag sémának az egyik előnye a jó lekérdezési teljesítmény. Akkor ezt hagytam lógni a levegőben, nem indokoltam meg, hogy miért. Ez a bejegyzés viszont arról szólna, hogy csillag séma esetén milyen technikák léteznek a lekérdezések optimalizálására.

Az indexek a lekérdezésekben szereplő szelekciós feltételek kiértékelésénél használatosak (pl.: WHERE T.A = 1000). Általános esetben egy lekérdezés végrehajtási ideje az indexek feldolgozásának és az adatok kinyerésének ideje. Ha egy lekérdezés eredményhalmazának mérete jelentős a tábla teljes méretéhez viszonyítva, akkor az adatelérés ideje megközelítheti egy teljes "table scan" (amikor a tábla minden során egyesével végigmegyünk) idejét. Ilyen esetekben az indexek használata épphogy lassítaná a lekérdezést.

Erre a problémára az egyik elterjedt megoldás az ún. bitmap indexek, vagy magyarra fordítva bittérképes indexek használata. Bitmap indexekből is többféle létezik, a család legegyszerűbb tagja az egyszerű bitmap indexek (simple bitmap indexes). Például, ha az indexelni kívánt attribútum a nemek (ami tipikusan két értéket vehet fel: Férfi, Nő), akkor az index egy két bitből álló vektor lenne, az egyik bit a férfiak esetén lenne 1-es, a másik pedig nők esetében:

gender='M'     gender='F'

cust_id 70     0    1
cust_id 80     0    1
cust_id 90     1    0
cust_id 100    0    1
cust_id 110    0    1
cust_id 120    1    0
cust_id 130    1    0
cust_id 140    1    0

A fenti táblázat minden sora az Ügyfél (Customer) tábla egyik rekordjához tartozik. Az egyszerű bitmap indexek mérete az indexelendő attribútum kardinalitásának és a tábla méretének lineáris függvénye, és az index feldolgozás ideje az index méretének lineáris függvénye. Emiatt rögtön látszódik az egyszerű bitmap indexek egyik hátrányos tulajdonsága: ha az indexelendő attribútum sokféle értéket vehet fel, akkor a táblázat igen ritka lesz (sparsity problem), egy sorhoz tipikusan csak egy darab 1-es fog tartozni, a többi bit értéke 0 lesz. Ez pedig nem nevezhető hatékony tárhely-kihasználásnak. Erre megoldás lehet a bittérképek tömörítése (pl.: futáshossz kódolással), de így elveszítenénk a bitmap indexek egy igen kellemes tulajdonságát: ha több feltétel egyszerre történő teljesülését akarjuk vizsgálni, akkor a bittérképek adott oszlopainak logikai ÉS kapcsolatát kell csak kiszámolnunk:

        gender='M'  region='central'

cust_id 70     0          1           0
cust_id 80     1          1           1
cust_id 90     1    AND   0    =      0
cust_id 100    0          0           0
cust_id 110    1          1           1

azaz a 80-as és a 110-es ügyfél tartozik a központi régióban levő férfiak közé. Ming-Chuan Wu a Query Optimization for Selections using Bitmaps című munkájában két olyan bitmap indexet is javasol, amik megőrzik a fent bemutatott előnyöket és mégis hatékonyabb tárhelykihasználtságot tesznek lehetővé.

Bit-sliced indexing

Az első az ún. bit-sliced indexelés (ezt inkább nem próbálom meg magyarra fordítani, úgyis csak valami zagyvaság lenne belőle:) ). Egy attribútum bit-sliced indexe az attribútum értékének bitenkénti leképezése. Például, ha egy A attribútum egy 16-bites egész szám, és értékei 100 és 900 közötti értékeket vehetnek fel, akkor a hozzá tartozó index így nézhet ki:

A        b15...b10 b9 b8 b7 b6 b5 b4 b3 b2 b1 b0
201         0...0    0  0  1  1  0  0  1  0  0  1
100         0...0    0  0  0  1  1  0  0  1  0  0
900         0...0    1  1  1  0  0  0  0  1  0  0

A bitvektorok száma megegyezik az attribútum típusának méretével, a vektorok hossza pedig az indexelt tábla kardinalitásával.

Egy bit-sliced indexnek nem feltétlen kell bináris alapúnak lennie, elképzelhető például decimális alapú bit-sliced index is. Az előző példában szereplő A attribútum tizes alapú indexe három komponensből állna, a helyiértékeknek megfelelően.

A     b9 b8 b7 b6 b5 b4 b3 b2 b1 b0    b9 b8 b7 b6 b5 b4 b3 b2 b1 b0    b9 b8 b7 b6 b5 b4 b3 b2 b1 b0
124     0  0  0  0  0  0  0  0  1  0     0  0  0  0  0  0  0  1  0  0     0  0  0  0  0  1  0  0  0  0
      harmadik komponens                  második komponens               első komponens

Ilyen típusú indexek esetén a kiválasztás a megfelelő bitvektorok kiolvasásával és össze-ÉS-eléssükkel történik. Például az A=124 szűrőfeltétel esetén a b1 b2 b4 vektorokat olvassuk ki rendre a harmadik, második illetve első komponensekből, és logikai ÉS kapcsolatba hozzuk őket. Az eredmény pedig azon sorokat tartalmazza, ahol az A értéke 124.

Az alap kiválasztása a tárhelykövetelményeket és a feldolgozási sebességet befolyásolja. A fenti példában a bináris alapú index 16 bitvektorból áll, míg a tizes alapú 30-ból. Egy szűrő feltétel kiértékelése (pl.: A=124) bináris alapon 16 bitvektor végigpásztázását jelenti, decimális alapon viszont csak háromét.

Encoded Bitmap Indexing

Egy másik bitmap indexelést kódolt bitmap indexelésnek neveznek (encoded bitmap indexing, EBI). Az EBI az attribútum értelmezési tartományát leképezi egy kódoló függvény segítségével, és a kódolt értékeken felépít egy bináris alapú bit-sliced bittérképet. A bináris alap és kódolás segítségével hatékony tárhelykihasználtságot tesz lehetővé, ugyanakkor viszont megtartja a lekérdezés-optimalizálás lehetőségét jóldefiniált kódolás segítségével (well-defined encoding). Például, ha egy B attribútum értelmezési tartománya {a, b, c, d, e, f, t, u, v, w}, akkor egy M függvényt az alábbi módon definiálhatunk:

B      M(B)
void    0000
NULL    0001
a      0010
b      0011
c      0100
d      0101
e      0110
f      0111
t      1000
u      1001
v      1010
w      1011

a void az adatbázisból törölt, a NULL pedig a NULL értékeket reprezentálja. A fenti táblázathoz tartozó minterm-ek:

f_void=/b3/b2/b1/b0    f_b = /b3/b2b1b0
f_null=/b3/b2/b1b0     f_c = /b3b2/b1/b0
f_a   =/b3/b2b1/b0     f_d = /b3b2/b1b0

f_e = /b3b2b1/b0       f_u = b3/b2/b1b0
f_f = /b3b2b1b0        f_v = b3/b2b1/b0
f_t = b3/b2/b1/b0      f_w = b3/b2b1b0

EBI segítségével egy szűrés az alábbi módon hajtható végre:
szűrőfeltétel: B eleme {a, b, e, f}
Ezekhez az elemekhez tartozó minterm-ek egy Boole függvényt alkotnak: f_a + f_b + f_e + f_f, amit tovább lehet egyszerűsíteni /b3b1-re. Azaz, azon értékek elégítik ki a szűrőfeltételt, amik b3 bitjét negálva majd b1 bitjükhöz ÉS-elve 1-et kapunk.

Az EBI valójában nem más, mint egy bináris alapú bit-sliced bitmap index egy attribútum kódolt értelmezési tartományán. Az EBI-knek két előnyük van a bit-sliced indexek felett:

• a bitvektorok száma nem több, mint a bit-sliced esetében, hiszen a szükséges bitvektorok száma az adott attribútum számosságának kettes alapú logaritmusa (+2 a törölt és NULL értékek miatt, és ennek a felsőegészrésze).
• EBI esetében több optimalizálási lehetőség van "megfelelő" kódoló függvény kiválasztása esetén. Ilyen függvényeket Wu: Encoded Bitmap Indexing for Data Warehouses című írásában mutat be.

Csillag transzformáció bitmap indexekkel

Ezen kitekintő után térjünk vissza a csillag sémánkhoz. Például, egy szokásos értékesítési adatokat tartalmazó "sales" tény-tábla esetén dimenziók lehetnek: idő, ügyfél, termék, értékesítési csatorna.

Tekintsük az alábbi lekérdezést:

SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND   s.cust_id = c.cust_id
AND   s.channel_id = ch.channel_id
AND   c.cust_state_province = 'CA'
AND   ch.channel_desc in ('Internet','Catalog')
AND   t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

Oracle adatbáziskezelő alatt a csillag transzformáció feltétele, hogy minden illesztéshez használt oszlopon definiálva legyen egy bitmap index, ebben az esetben cust_id, time_id, channel_id. A lekérdezést két menetben hajtja végre az adatbáziskezelő motor. Az alábbi lekérdezés végrehajtásával csak azokat a sorokat gyűjti ki a tény táblából, amik valóban szükségesek:

SELECT ... FROM sales
WHERE time_id IN
(SELECT time_id FROM times
WHERE calendar_quarter_desc IN('1999-Q1','1999-Q2'))
AND cust_id IN
(SELECT cust_id FROM customers WHERE cust_state_province='CA')
AND channel_id IN
(SELECT channel_id FROM channels WHERE channel_desc IN('Internet','Catalog'));

Ebben a lekérdezésben a tény táblán a time_id-re definiált bitmap index segítségével kiválasztjuk azokat a sorokat, amik 1999. első negyedévére vonatkoznak. Ez valójában annyit jelent, hogy azokat a sorokat választjuk ki, amiknél a bittérképben az 1999-Q1 bit értéke 1-es értékű. Hasonlóan történik a második negyedév adatainak kiválasztása. A kettő bitenkénti VAGY kapcsolatba hozásával kapjuk meg a kívánt időszakot.

Ugyanilyen módon történik az ügyfelekre és értékesítési csatornákra történő szűrőfeltételek kiértékelése. A kapott három bittérképet majd logikai ÉS kapcsolatba hozva csupán azon sorok esetén kapunk 1-es értéket, amik az összes feltételt kielégítik.

A második lépésben történik meg a tény tábla kiválasztott sorainak a dimenzió táblákhoz történő illesztése. Mivel a dimenzió táblák relatíve kis méretűek a tény táblákhoz viszonyítva, az Oracle általában teljes "table scan"-t használ az elérésükhöz.

Források:

• Wu, Ming-Chuan: Query Optimization for Selections using Bitmaps (1999)
• Oracle® Database Data Warehousing Guide 11g Release 1 (11.1) Part Number B28313-02: Optimizing Star Queries

Visszatérési értékek

A visszatérési értékek okozzák a legizgalmasabb problémákat a migráció során. SQL Server alatt egy tárolt eljárás háromféle módon képes adatokat visszaadni a hívója felé:

• kimenő paraméterekkel: CREATE PROCEDURE sp_demo (@something int OUT)
• visszatérési értékkel: return @someValue
• úgynevezett eredményhalmazokkal (result set-ekkel): egy T-SQL tárolt eljárás képes visszaadni egy tetszőleges SELECT utasítás által visszaadott eredményhalmazt

Kimenő paraméterekből és visszaadott eredményhalmazokból - ha jól tudom - tetszőleges számú lehet, persze elképzelhető, hogy van valami igen magas felső korlát.

Egy példa ezen módok bemutatására:

CREATE PROCEDURE sp_demo (@something int out)
AS
begin
set @something = 5
select 7
return 9
end

Az eljárást futtatva az alábbi eredményt kapjuk:


Az első eredményhalmazt a tárolt eljárás adta vissza (SELECT 7), a második pedig az @i, @j változók tartalmát mutatja.

Mint azt már egy korábbi, migrációról szóló bejegyzésben írtam, a migráció egyik célja az lenne, hogy a .NET-es vastag kliens lényegében kizárólag tárolt eljárás hívásokkal kommunikál az adatbázissal. Mind a módosító, mind a lekérdező funkciókat tárolt eljárások valósítják meg: az eljárás által visszaadott eredményhalmazt a kliens valahogy feldolgozza (pl.: megjeleníti táblázatos formában). De természetesen vannak olyan eljárások is, amik nem adnak vissza semmit sem, hanem csak az adatbázisban módosítanak valamit.

Oracle adatbáziskezelő alatt viszont egy tárolt eljárás csak kimenő paraméterekkel képes adatokat visszaadni. Nincs sem visszatérési érték, sem eredményhalmaz. Eredményhalmazokat kurzor referenciákkal (REF CURSOR) lehet emulálni. Az Oracle Migration Workbench is ezt a módszert követi: ha egy T-SQL tárolt eljárás eredményhalmazokat adna vissza, azt úgy fordítja át PL/SQL-re, hogy felvesz annyi darab kurzor referencia kimenő paramétert, ahány eredményhalmazt az eljárás visszaad. Például, a fenti sp_demo eljárás PL/SQL-es változata így nézne ki:

CREATE OR REPLACE FUNCTION sp_demo
(
v_something OUT NUMBER,
cv_1 IN OUT SYS_REFCURSOR
)
RETURN NUMBER
AS
BEGIN
v_something := 5;

OPEN cv_1 FOR
SELECT 7
FROM DUAL ;

RETURN 9;
END;

Hoppá! Az OMWB függvényt csinált az eljárásunkból! Ez azért van, mert visszatérési értéke csak és kizárólag függvényeknek lehet PL/SQL-ben. Viszont egy függvény és egy tárolt eljárás között számos lényegi különbség van, amik miatt nem szerencsés csak úgy egyikből a másikat csinálni. Egy függvény nem végezhet adatmódosító (DML) utasításokat SQL utasításokban. Csupán azért alakította a migráló eszköz függvénnyé, mert csak azoknak lehet visszatérési értéke.

Tehát annyit már most is kijelenthetünk, hogy ha vannak olyan T-SQL tárolt eljárásaink, amiknek van visszatérési értékük, akkor célszerű elgondolkozni azon, hogy hogyan alakítsuk át őket. Például felvehetünk kimenő paramétereket, vagy, ha eddig valójában függvényként használtuk az eljárást, akkor alakítsuk is át függvénnyé.

Az eredményhalmazok kérdése viszont trükkösebb. Ugyanis a kimenő paramétereket az eljárás hívásakor explicit fel kell venni. Ha egy eljárás hív egy másikat, akkor a hívó eljárásban is deklarálni kell egy kurzor változót, amit paraméterül adunk a másik eljárásnak. Ha viszont .NET alól szeretnénk ilyen eljárásokat meghívni, akkor a paraméterlistába kell felvenni a kimenő kurzor típusú paramétert:

cmd.Parameters.Add("cv_1", OracleType.Cursor).Direction =
ParameterDirection.Output;

Ha ezt elmulasztjuk, akkor a hívás exception-t fog dobni, PLS-00306 hibaüzenettel: "wrong number or types of arguments in call to '*spname*'". Ugyanez igaz fordítva is, azaz ha olyan eljárásnál próbálnánk hozzáadni, aminek valójában nincs ilyen paramétere.

És ezzel eljutottunk egy igen kemény problémához: a migráció célja ugye az, hogy ugyanaz a kliens legyen használható SQL Server és Oracle adatbáziskezelő esetén is. SQL Servernek nem kell explicit megmondani, hogy a tárolt eljárás fog-e visszaadni eredményhalmazt, Oracle-nek viszont igen. Egy T-SQL tárolt eljárás tetszőleges számú eredményhalmazt is visszaadhat, egy PL/SQL-es viszont pontosan annyit, ahány kimenő kurzor változó paramétere van.

Mivel a migráció során úgyis hozzá kell nyúlni minden egyes tárolt eljárás kódjához (ha másért nem, akkor azért, hogy megnézzük, úgy működik-e, ahogy szeretnénk), ezért arra a kompromisszumos megoldásra jutottunk, hogy:

• minden tárolt eljárásnak kötelezően van egy cv_1 nevű kimenő paramétere, a paraméterlista első helyén, attól függetlenül, hogy visszaad-e eredményhalmazt, vagy sem
• azon tárolt eljárások esetében, amik több eredményhalmazt is visszaadnának, újragondoljuk a funkciójukat, és szükség esetén feldaraboljuk több eljárássá, azaz eljárásonként pontosan egy visszaadott eredményhalmaz.

Az eljárások egymást is hívják, ezért a kódot ilyen esetekben is módosítani kell. Mit nyerünk ezzel? Azt, hogy a kliens kód megtarthatja az univerzális jellegét, és nem kell kliens oldalon is minden egyes eljáráshíváshoz hozzányúlni. (Pl.: ott is megmondhatnánk explicit módon, hogy a hívott eljárás vissza fog-e adni eredményhalmazt, vagy sem, és ennek hatására SQL Server esetén nem történne semmi, Oracle esetén viszont felkerülne a paraméterlistába a kimenő kurzor változó.)

Az MSDN-en szerepel egy viszonylag részletes leírás arról, hogy hogyan kell .NET alatt PL/SQL tárolt eljárásokat hívni, illetve érdekes olvasmány még Vadim Tropashko kurzorokról szóló blogbejegyzése. Ennek a cikknek a végén szerepel egy kemény kijelentés:

It seem that the genesis of “a better cursor demand” is a stream of programmers from SQL Server world, where a procedure which doesn’t even bother to declare its outputs as a cursor like this

CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO

Its a pity that TSQL designers give an impression of not understanding the difference between a procedure and a query.

Hát, ez kicsit úgy hangzik, mintha az Oracle megoldása lenne az egyetlen járható út.

Készítettem egy buta kis Windows-os alkalmazást, ami azt próbálja demonstrálni, hogy a kliens oldalon milyen változtatások szükségesek ha SQL Server helyett Oracle adatbáziskezelőt akarunk használni. Az egyedüli különbség (a használt osztályokat leszámítva) a kurzor paraméter deklarációja. A kód letölthető innen.