PL/SQL programátor už NEbloguje – vývoj aplikací pod databází Oracle

Tento blog je online od února 2011 a je na čase udělat nějaké to čištění. Od uvedeného data se mi podařilo ve wordpressu úspěšně rozepsat a nezveřejnit několik postů. A co je možné bych rád zpětně pročistil, dopsal či bez návratu odstranil. Důvody nedopsání jsou různé od časových až po takové, kde jsem zjistil, že to tak jednoduché nebude – napříkad mi tu straší článek o tom jak založit tabulku. Moje představa byla založení tabulky pomocí insertů do datového slovníku pod SYSem, modifikace datových bloků, dopočítání kontrolních součtů atd. A musím říct, že jsem nebyl úspěšný – tabulku se mi daří téměř správně založit, funguje describe, select a vše vypadá korektně, ale insert se prostě nedaří (internal error oracle). A od té doby jsem se k tomu nedostal. Rozepsaných a slíbených postů, které jsem nezveřejnil bych se rád postupně zbavil.

Před dvěma lety jsem měl  plánu napsat post o hintu IGNORE_ROW_ON_DUPKEY_INDEX, který patří mezi ty specifické, které mění chování a nevěnují se optimalizaci. Oracle ho uvádí společně tyto hinty: CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX a RETRY_ON_ROW_CHANGE. A když už jsem v tom, rovnou je zkusme všechny ;. A začněme hned tím prvním CHANGE_DUPKEY_ERROR_INDEX.

CHANGE_DUPKEY_ERROR_INDEX – Umí změnit typ chyby v případě, že dojde k porušení unikátnosti. Konkrétně z ORA-001 udělá ORA-38911. K čemu je to dobré? Například pokud jsou nad tabulkou dva různé unikátní sloupce a jednoduššímu zjištění ve kterém ze zmíněných došlo k porušení unikátnosti. Hint lze použít v INSERT a UPDATE statementu. Takže příklad:

create table tbl_unique_hint_test as
select rownum as first_unique,
rownum as second_unique
from dual connect by level<6;

Synaxe je následující:

Tedy takto:

insert /*+CHANGE_DUPKEY_ERROR_INDEX(tbl_unique_hint_test(first_unique)) */ into tbl_unique_hint_test values (1,1);
SQL Error: ORA-38913: Index zadaný v tipu k indexu není platný

Vyhučelo na chybě. I když jde zadat pro zjištění unikátnosti místo indexu sloupce, tak není možné zadat sloupce na kterých není unikátní index/constraint. Dokumentace píše, že je to nutné toto omezení mín, ale nepíše co se stane když není – odpověď je tedy ORA-38913. Takže založení unikátního indexu nad sloupcem first_unique:

create unique index inx_unique_first on tbl_unique_hint_test(first_unique);

A opět test hintu:

create unique index inx_unique_first on tbl_unique_hint_test(first_unique);
unique index INX_UNIQUE_FIRST created.
insert /*+CHANGE_DUPKEY_ERROR_INDEX(tbl_unique_hint_test(first_unique)) */ into tbl_unique_hint_test values (1,1);
SQL Error: ORA-38911: nesplněna podmínka jedinečnosti (APEX_WORKOUT.INX_UNIQUE_FIRST)

Fajn, číslo chyby je jiné. Ale daleko zajímavější je to takto:

create unique index inx_unique_second on tbl_unique_hint_test(second_unique);
unique index INX_UNIQUE_SECOND created.
insert /*+CHANGE_DUPKEY_ERROR_INDEX(tbl_unique_hint_test(second_unique)) */ into tbl_unique_hint_test values (1,1);
SQL Error: ORA-38911: nesplněna podmínka jedinečnosti (APEX_WORKOUT.INX_UNIQUE_SECOND)

Nyní když jsou nad tabulkou unikátní dva indexy má nová chyba ORA-38911 přednost ORA-001. Tedy místo toho, aby příkaz vyhuče na duplikaci nad sloupcem FIRST_UNIQUE, protože hodnota 1 již tam je, vyhučí na ORA-38911 pro sloupec SECOND_UNIQUE.

Kromě příkazu CONTINUE (a CONTINUE WHEN) se samozřejmě těším na to, až budu moci i na produkční databázi pracovat s ostatními novinkami a úpravami z Oracle 11g (a dalšími release).  Jednou z věcí, která nás na produkčním prostředí nyní trápí je nemožnost spouštět v EXECUTE IMMEDIATE a především v DBMS_SQL scripty a kódy delší než 32767 znaků (tzn. VARCHAR2).  Vadí nám to především u jobu, kterým ze selectu generujujeme scripty a pomocí kterých pak stahujeme data z okolních systémů před db link do naší databáze.  Obecně generované scripty jsou delší než ručně napsané a tedy granice 32kb je pro nás vcelku omezující.  Jediné řešení, které aktuálně aplikujeme je odstranění jakéhokoliv formátování, jakékoliv mezery, která tam být nemusí a jakéhokoliv přebytečného řádku.

Oracle 11R2 nabízí spouštění kódu v CLOBu pomocí jak EXECUTE IMMEDIATE, tak pomocí DBMS_SQL. Dost bylo odstraňování bílých znaků z SQL selectů a PL/SQL kódu.