Oracle Business Intelligence

Ez a két hibaüzenet kissé elkeserített, ugyanakkor megmagyarázza számomra, hogy az SQL Developer Migration Workbench miért több kurzor változó segítségével oldja meg az eredményhalmazok visszaadását a tárolt eljárásokból, és miért nem egy kurzor változókat tartalmazó tömb segítségével.

A válasz egyszerű: mert nem lehet kurzor változókat tartalmazó tömböt készíteni:

PLS-00990: Index Tables of Cursor Variables are disallowed
PLS-00989: Cursor Variable in record, object, or collection is not supported by this release

A "this release" pedig az Oracle Database 11g Enterprise Edition Release 11.1.0.6.0. Pedig milyen jó ötletnek tűnt.. :)

A hétvégén tovább folytattam az ANTLR-el való ismerkedést. Az ANTLR egy parser-generátor, ami egy nyelvtan alapján generál egy rekurzív leszálló (recursive descent) szintaktikai elemzőt.

Egy elemző alapesetben nem csinál mást, minthogy eldönti, hogy egy adott mondat része-e a nyelvnek, szintaktikailag értelmes-e. Az ANTLR segítségével viszont könnyedén készíthetünk olyan nyelvtanokat, amikből a generált parser egy bemenő mondatból kimenetként egy absztrakt szintaxis fát (AST) állít elő. Ezt a fát bejárva akár kiértékelhetjük a bemenetet (utasításokat), akár transzformációkat végezve átfordíthatjuk egy másik nyelvre.

Terence Parr a könyvében azt javasolja, hogy AST-k bejárásához is használjunk nyelvtanokat, pontosabban egy nyelvtan leírásából generált parsert, aminek a bemeneti mondatai az AST-k, kimenete meg mondjuk egy StringTemplate sablon megfelelő kitöltése.

Ezt a folyamatot szemlélteti - szintén Parr könyvéből származó - ábra:

A Code Generation honlapján 2005-ben megjelent egy cikk, "Language Translation Using ANTLR and StringTemplate" címmel, amiben Terence Parr bemutatja, hogyan lehet egy fordítót készíteni, ami egy C-szerű nyelvből Java, Python, vagy Java bytecode kódot állít elő. A példa nyelvtanok sajnos még az ANTLR 2-es verziójával készültek, amit azóta több szempontból is felülmúlt a legújabb, 3-as verzió. A hétvégén ezt alakítottam át, hogy működjön a 3-as verzióval, és először egy AST-t generáljon, majd az AST-t bejárva töltse ki a sablonokat.

A nyelvtanok itt találhatóak:

A tesztelést megvalósító Java kód:

Működése pedig:

A teszt bemenet legyen a honlapon is fentlevő kódrészlet:

 char c;
 int x;
 int foo(int y, char d) {
   int i;
   for (i=0; i<3; i=i+1) {
     x=3;
     y=5;
   }
 }

Ezt Python-ra fordítva az alábbi kimenetet kapjuk:

kelda@psycho:~/ANTLR/cminus$ cat input.txt | java Test
(GLOBALS (VAR char c)) (GLOBALS (VAR int x)) (FUNCTIONS
(FUNCTION int foo (PARAMS (PARAM int y) (PARAM char d))
(BLOCK (LOCALS (VAR int i)) (STATEMENTS (FOR (INIT (= i
0)) (COND (< i 3)) (STMT (= i (+ i 1))) (BLOCK LOCALS
(STATEMENTS (= x 3) (= y 5))))))))

def foo(y, d):
  i = 0
  while ( i < 3 ):
      x = 3
      y = 5
      i = i + 1

Az elején az AST látható (gyökér levél1 levél2 ... levéln) formában, utána pedig a kimenet.

Ez a bejegyzés igazából nem akart másról szólni, minthogy illusztrálja az ANTLR és a StringTemplate erejét, a részletek bemutatása nélkül. Később részletesen is bemutatom majd a működését, a nyelvtanok felépítését, és hogy az egész hogyan is illeszkedik a blogomba :)

Egy kis segítségként annyit elárulhatok, hogy az SQL Developer egyik könyvtárában van egy oracle.sqldeveloper.migration.translation.sqlserver.jar file, aminek a belsejében található egy generic.stg és egy tsql.stg StringTemplate sablon. Azaz az SQL Developer Migration Workbench is az ANTLR és a StringTemplate segítségével végzi a nyelvi fordítást.

Ebben a félévben főleg a diplomatervem elkészítésével fogok foglalkozni, melynek címe:

azaz az előző féléves önálló labor témámat folytatom. A diplomaterv kiírásom pontjai:

Az első pont egy kicsit trükkös, ugyanis eddig igen kevés releváns szakirodalmat sikerült találnom. Amit találtam, azok is főleg az adatok migrációjával foglalkozik. Ezek közül kiemelendő Erik Peter Bansleben diplomamunkája az esettanulmány hasonlósága miatt:

The source systems consisted of a combination of Microsoft SQL Server and Access databases, while the target platform was an Oracle 9i server which was to serve as the backend database for the new system.

Ha esetleg tud valaki a témába vágó irodalomról, megköszönném, ha jelezné akár hozzászólásban, akár e-mailben: orveny kukac gmail pont com. Nagy segítség lenne :)

Az előző félévben gyakorlatilag a második ponttal foglalkoztam, így úgy érzem, ez nem fog különösebb kihívást jelenteni.

Ami érdekesebb, az a 3. és 4. pont: gyakorlatilag egy Transact-SQL -> PL/SQL fordítót kell készítenem. Szerencsére rátaláltam az ANTLR framework-re, aminek segítségével egy nyelvtanból viszonylag könnyedén lehet szintaktikus elemzőket, fordítókat készíteni.

A honlapján megtalálható Dermott O'Neill-től, az SQL Developer egyik fejlesztőjétől egy idézet:

The decision to use Antlr and StringTemplate for Oracles next generation Migration and SQL Developer features was easy due to the fantastic support on the forums, extensive documentation and great tools. In particular, the ability to parse trees and define target languages using StringTemplate, provided the end to end language translation technology we required. Other parser generators left us high and dry with only half the solution.

Egyelőre még csak az irodalom feldolgozás fázisában tartok, de mindenesetre igen bíztató, hogy egy olyan eszköz használatát tanulom, amivel az SQL Developer Migration Workbench is működik :)

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.

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.

Folytatódik a munkahelyemen a migráció előkészítése: nézegetjük, hogy egyrészt mire képes a Migration Workbench, másrészt mik azok a lehetséges buktatók, amikre még nem gondoltunk.

Eközben találtam egy újabb bug-gyanús - igen idegesítő - hibát az OMWB-ben:

Ha valamilyen Oracle kulcsszó szerepel azonosítóként az SQL Server oldali kódban, azt okosan átalakítja, például:

select @something = date
from MyTable

ez a kód teljesen jó SQL Server alatt, ha a MyTable táblának van date nevű oszlopa. Ezt az alábbi kódra alakítja át:

SELECT date_
INTO v_something
FROM MyTable ;

A konvertálás előtt egy kollégám elkészített egy olyan sémát, ahol az összes oracle-s kulcsszó mögé egy _-t szúrt. Tehát már SQL Server oldalon az összes date-ből date_ lett, a type-okból type_, és így tovább. Viszont konvertálás után az összes type_ azonosítóra való hivatkozásból újra type lett. Ami SQL Server alatt:

select @something = type_
from MyTable

az konvertálás után:

SELECT type
INTO v_something
FROM MyTable ;

ami természetesen hibás. Ugyanez más kulcsszó esetén nem jött elő, tehát a date_-ek megmaradtak :)

A másik bug a következő: amit már egy korábbi bejegyzésben írtam is, az SQL Server megengedi, hogy egy tárolt eljárás bemenő paraméterét mezei változóként kezeljük, azaz állhat értékadás bal oldalán. Ezt az OMWB kezeli is, egy belső, rendesen deklarált változó segítségével. Ha a paraméterre való hivatkozás mindenhol pontosan ugyanaz, case sensitive-en:

create procedure foo (@bar int)
as
begin
select @Bar=5
end

itt ugye a hivatkozás megegyezik, leszámítva a kis/nagy B betűt. Az OMWB emiatt megzavarodik picit:


CREATE OR REPLACE PROCEDURE foo
(
v_bar IN NUMBER DEFAULT NULL
)
AS
BEGIN
v_Bar := 5;

END;

Sajnos ezek igen idegesítő hibák, remélem valahogy meg lehet majd őket kerülni, vagy ha nem, akkor hamar kijavítják.

UPDATE: visszaigazolt bug: 7332018: FORUMS: PROCEDURE ARGUMENTS NEED TO MATCH VARIABLE NAMES IN CASE.