Oracle Business Intelligence

Idén először vettem részt a Magyarországi Oracle Felhasználók Konferenciáján, melyet április 6-9. között a siófoki Hotel Azúrban tartottak.

Én szerda reggel érkeztem meg, kiváncsian vártam Kardkovács Zsolt és Éberhardt Péter előadását a Közel valós idejű, adaptív műsorajánló digitális televíziózáshoz címmel, amikor is a terem előtt hallottam, hogy sajnos elmarad. Helyettük Sárecz Lajos (akinek a jóvoltából vehettem részt a konferencián) tartott előadást Real Application Testing témában.

A szerdai nap további részét főleg az Üzleti intelligencia és az Adatbiztonság szekciók előadásain töltöttem, illetve befurakodtam Czinkóczki László - szokásosan - teltház előtt tartott SQL érdekességek, újdonságok előadására.

Az előadásokat egy számomra rendkívül érdekes panelbeszélgetés zárta szintén adatbiztonság témában, ahol a meghívott vendégek Kirner Attila (PSZÁF), Jakab Péter (MKB Zrt.), Dr. Suba Ferenc (Magyar Kormány Informatikai Biztonsági Incidenskezelő Központ), Antal Lajos (PwC Kft.), Bártfai Attila (kancellár.hu Zrt.), Keleti Arthur (KFKI Zrt.) voltak.

A beszélgetésről talán mindent elmond az, hogy az első kérdés megválaszolása, kivesézése 50 percig tartott.

Az esti vacsorát látványos zenés programok, sör, beszélgetések színesítették, majd egy csocsó-"bajnokság" :)

A csütörtöki nap számomra továbbra is BI + SQL/PLSQL témában telt, az adatbiztonság helyét Tóth Balázs kiváló Change Management a 11g Oracle adatbázisban előadása vette át.

Én hasznosnak éreztem a konferenciát, csak néha tényleg a bőség zavarával kűzködtem: túl sok jó előadás volt :)

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.

Identity/Szekvencia

Microsoft SQL Server alatt az automatikus azonosító generálásra az IDENTITY típusú oszlopok szolgálnak. A típus megadására a CREATE TABLE illetve ALTER TABLE utasításokkal van lehetőség:


ahol a seed a legelső táblába beszúrt értéket, az increment pedig a növekményt jelöli. Az IDENTITY típusú oszlopokhoz kapcsolódnak a @@IDENTITY, IDENT_CURRENT() illetve SCOPE_IDENTITY() függvények. Az IDENT_CURRENT() egy adott táblára mondja meg, hogy mi volt az utolsó IDENTITY érték, a SCOPE_IDENTITY az adott hatáskörben teszi meg ugyanezt, az @@IDENTITY pedig hatáskörtől függetlenül a legutolsó értéket adja vissza. A hatáskör lehet tárolt eljárás, trigger, függvény vagy batch.

Például ha van két tábla IDENTITY mezőkkel, T1 és T2, és a T1-be történő beszúrás hatására lefut egy trigger, ami a T2 táblába szúr be egy sort, akkor az INSERT utáni @@IDENTITY és SCOPE_IDENTITY() más értékeket fog visszaadni: az @@IDENTITY a legutolsó beszúrás eredményét, tehát a T2-be beszúrt identity értéket, a SCOPE_IDENTITY() pedig a T1-be beszúrtat.

Oracle alatt az IDENTITY oszlopok helyett szekvenciák vannak. A szekvenciák önálló séma objektumok, amik automatikusan generálnak egyedi értékeket. Az IDENTITY-hez hasonló működést szekvenciák és triggerek segítségével lehet elérni. Erről Pém Gábor is írt egy bejegyzést a blogjában. Viszont az @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT() függvényekkel ekvivalens nincs Oracle alatt, ezeket a szekvenciákhoz tartozó CURRVAL, NEXTVAL pszeudo-oszlopokkal lehet emulálni.

Az SQL Developer Migration Workbenchje az IDENTITY oszlopokat úgy fordítja át, hogy meggenerálja a hozzájuk tartozó triggereket és szekvenciákat, valamint beállítja az sqlserver_utilities PL/SQL package identity nevű változóját, amivel a @@IDENTITY működését próbálja szimulálni. Ha a T-SQL kódban használjuk a SCOPE_IDENTITY() vagy az IDENT_CURRENT() függvényeket, akkor minden esetben manuálisan kell átírni a logikát PL/SQL-re.

Azonosító nevek, fenntartott kulcsszavak

A kulcsszavak és fenntartott szavak (keywords and reserved words) problémájáról már írtam egy bejegyzést, amit valahogy úgy lehetne összefoglalni, hogy ne használj kulcs- és fenntartott szavakat adatbázis objektumok nevekként.

Ha az ember a nulláról indul egy adatbázis megtervezésekor, akkor természetesen ez az ajánlott út. Viszont ha már van egy kész séma, többszáz táblával, amikre többezer hivatkozás is van, akkor bizony kénytelenek leszünk kompromisszumokat kötni. Egy fontos szabály, amit nem lehet áthágni: azonosító név maximum 30 karakter (30 byte) hosszú lehet (kivéve adatbázis név - 8 byte, adatbázis-link név - 128 byte). Ha van olyan táblánk, oszlopnevünk, T-SQL változónk, függvényünk, stb., ami 30 karakternél hosszabb, akkor azt át kell nevezni (a Migration Workbench egyszerűen levágja a végét, ha névütközés van, akkor sorszámmal látja el az azonos neveket, pl: myverylongtable_1, myverylongtable_2, stb.).

A dokumentáció szerint "-k között szereplő fenntartott szavak lehetnek azonosító nevek, pl.: "DATE", de - mint már írtam - ebből lehetnek még problémák. Feltehetően egy bug miatt ha még SQL Server oldalon átnevezzük a fenntartott szavakat úgy, hogy egy _ postfixet teszünk a végükre, pl.: Type_, akkor a Migration Workbench "visszanevezi" őket, azaz leveszi a postfixünket. Ezt eddig csak a Type-nál vettem észre.

A kívánatos út szerintem az, ha még migráció előtt SQL Server oldalon átnevezünk minden 30-nál hosszabb illetve fenntartott azonosító nevet, illetve az azokra történő hivatkozást. Nagy adatbázis esetén nagy munka, de megkímélhetjük magunkat későbbi kellemetlen meglepetésektől. Például, ha van egy tárolt eljárásunk, ami visszaad valamilyen recordsetet, ahol bizonyos oszlopnevek vagy alias-ok 30 karakternél hosszabbak, akkor a kliens oldalon külön kell kezelni, hogy az eredményt T-SQL eljárás adta-e vissza (30+ hosszú is lehet egy oszlopnév ill. alias), vagy PL/SQL (max 30 karakter) ha szerepelnek a kliens-kódban oszlopnévre történő hivatkozások. Ez nem járható út.

Jelenleg van egy .NET-es, C#-ban írt alkalmazás (kliens), ami Microsoft SQL Server-t használ adatbázisként. A kliens oldalon szinte kizárólag tárolt eljárás hívások szerepelnek. A cél az, hogy ugyanaz a kliens szoftver képes legyen SQL Server-t is és Oracle Database-t is használni adatbázisként, azaz egy klienshez lenne két külön adatbázis-oldali rész. Ezért a migrációnak csak minimális mértékben kéne visszahatnia a kliens oldali részre. Sajnos ez közel sem olyan egyszerű. Lássuk a problémás területeket!

Adattípusok

Habár az Oracle és az SQL Server által támogatott adattípusok különböznek egymástól, szerencsére az esetek igen nagy százalékában megfeleltethetőek egymásnak. Egy Oracle white paper szerint is: "Microsoft SQL Server and Oracle9i Database data types differ. However these differences are usually small enough to have little or no impact on your applications."

Az SQL Developerbe integrált Migration Workbench (OMWB) a konvertáláskor automatikusan leképezi az SQL Server adattípusokat Oracle adattípusokra. Számunkra lényeges eltérés a (N)VARCHAR típus maximális hosszában van: míg SQL Server alatt maximum 8000 karaktert képes tartalmazni, addig Oracle alatt ez a szám 4000. De szerencsére az OMWB a 4000-nél hosszabb (N)VARCHAR típusokat automatikusan (N)CLOB-okká konvertálja.

Különböznek még a dátum típusok pontosságai: míg SQL Server alatt a DATETIME 3.33 mikroszekundum pontosságú, addig Oracle alatt másodperc pontosságú a DATETIME-nak megfelelő DATE típus. Ahol ez nem elég, ott érdemes a TIMESTAMP típust használni, ami viszont már 1 mikroszekundum pontosságú. Persze kérdés az, hogy az adatbázisunkban kismillió helyen használt DATETIME-ok közül hol elég a másodperces felbontás, és hol nem. Ebből még lehetnek bonyodalmak..

Az adattípusok konvertálásáról bővebben lehet még olvasni az SQL Developer dokumentációjában és a Migrating Applications from Microsoft SQL Server to Oracle9i Database című white paper-ben.

Implicit/explicit konverzió

Az adattípusokhoz erősen kapcsolódó téma a típusok közötti implicit vagy explicit konverziók kérdése.

SQL Server alatt az explicit típuskonverziót a CAST és CONVERT függvények valósítják meg:


A CONVERT függvény SQL Server specifikus, és nagyobb rugalmasságot biztosít dátumok és tört számok konvertálásakor. A CAST pedig inkább ANSI-szerű, ezért többé-kevésbé ugyanolyan szintaxissal lehet használni a különböző adatbázis szerverek alatt.

Implicit konverzió például akkor történik, amikor két különböző típusú adatot hasonlítunk össze, vagy ha egy tábla adott oszlopába szúrunk be egy attól különböző típusú értéket. Az implicit konverzió a felhasználó elől rejtve marad.

SQL Server alatt a lehetséges implicit és explicit típuskonverziók mátrixát az alábbi kép tartalmazza:



Ugyanez az ábra Oracle alatt picit máshogy néz ki, implicit konverziók:



explicit konverziók:



Habár implicit típuskonverzió alkalmazása több okból is helytelen (a kód nehezebben értelmezhető, nem feltétlen optimális az utasítások végrehajtása performancia szempontból, a különböző verziók során változhatnak a konverziós szabályok, stb.) mi mégis több helyen alkalmazzuk, egész egyszerűen azért, mert kényelmes. Például SQL Server a képen látható módon többféle dátumformátumot is képes kezelni:


Kavarodás itt is történhet (ÉV-HÓNAP-NAP, NAP-HÓNAP-ÉV, stb.), de ha az ember ragaszkodik valami megszokott formátumhoz, nem érheti baj. Kivéve, ha a kódot más adatbázis szerver alatt is szeretné használni :)

Oracle alatt az alapértelmezett formátumot az NLS_DATE_FORMAT paraméter tartalmazza, ami az én szerveremen:

SELECT Value FROM v$nls_parameters WHERE parameter = 'NLS_DATE_FORMAT'

VALUE
-----
RR-MON-DD

Azaz a '2008-01-01' például csak explicit módon konvertálható át DATE típusúvá. Ilyen esetekben célszerűbb a TO_DATE függvény használata. A mi esetünkben tehát minden ilyen esetben kézzel át kell írni minden implicit konverziót tartalmazó kódot explicit konverzióvá.

Ideiglenes táblák, táblaváltozók

Ideiglenes táblák SQL Server és Oracle alatt is léteznek, a különbség annyi, hogy míg SQL Server alatt a táblában tárolt adatok és maga a struktúra is ideiglenes, addig Oracle alatt a struktúra (séma) állandó.

A Migration Workbench kiszedi a tárolt eljárások kódjából az ideiglenes táblákat létrehozó DDL utasításokat, és külön hozza létre őket:

/* Translation Extracted DDL For Required Objects */
CREATE GLOBAL TEMPORARY TABLE tt_foo
(
...
);

Az ideiglenes táblákat törlő DROP utasításokból pedig ez lesz:

EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_foo ';

Azaz csak a tartalmát törli a táblának, magát a táblát nem.

Egyelőre úgy tűnik, hogy számunkra ez tökéletesen megfelelő. Egyedül az zavaró, hogy (hanyagságból) rengeteg #temp nevű táblát használunk SQL Server oldalon, és mivel Oracle alatt a séma nem ideiglenes, ezért ahány helyen #temp táblát használunk, annyi darab TT_TEMP_n jön létre Oracle oldalon. Az n jelenleg olyan 30 körüli (TT_TEMP_1, TT_TEMP_2, ... TT_TEMP_30, ..).

Táblaváltozók viszont nincsenek Oracle alatt, ezek kiváltására vagy ideiglenes táblákat, vagy PL/SQL collection-öket kell majd használnunk. Azaz itt is marad a kézi átírás.

A migráció kapcsán rögtön belefutottunk egy érdekes problémába: SQL Server oldalon több olyan oszlopnevet is használunk, ami Oracle alatt kulcsszó vagy fenntartott szó: name, text, comment, date, number, stb.

Úgy gondoltuk, hogy ha ezeket az oszlopneveket nagybetűsen és ""-k között hozzuk létre (és így is hivatkozunk rájuk) akkor működhet. A táblák létre is jöttek, viszont több esetben egyéb gondok léptek fel. Pl.:

CREATE TABLE asdf (
"DATE" date
);

Select * from asdf;

Ez tökéletesen lefut, viszont:

declare
v_temp number;
begin

select 1 into v_temp from dual where exists (select 1 from asdf);
end;

ez már nem:

ORA-06550: line 5, column 61:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 2:

Gondoltuk, hogy baj van, akkor át kell nevezni az oszlopokat. Elkezdtem próbálkozni, hogy milyen kulcsszavakkal van még gond, és nem az összes reserved word-del van baja:

CREATE TABLE asdf (
"AUDIT" date
);

Elvileg az audit is reserved word mégsem fut hibára a fenti kódrészlet.

A következő gondolat az volt, hogy a típusnevekkel van csak gondja, de ez sem teljesen igaz, ugyanis a lenti esetben:

CREATE TABLE asdf (
"DATE" int
);

Szintén hiba nélkül lefut a PL/SQL kód, ellenben, ha "NUMBER" int van (tehát nem "NUMBER" number), akkor megint csak az említett hibára fut.

Maradt még egy utolsó kombináció, méghozzá a kulcsszavak kisbetűsen, ""-k között:

CREATE TABLE asdf (
"number" number
);

Így jelenleg minden (date, number, stb.) működik. Egyelőre. Már csak az a költői kérdés maradt hátra, hogy ebben így mi a logika?