DBMS_METADATA - generowanie skryptów DDL

Bardzo ciekawym pakietem dostarczonym z bazą danych jest DBMS_METADATA – jego głównym zadaniem jest tworzenie skryptów DDL dla różnych obiektów bazodanowych..

Dokumentacji nie będę przepisywać, ale przedstawię kilka zastosowań

Należy zauważyć, że wywołania w instrukcjach SQL umożliwiają ograniczone możliwości wykorzystania API, skrypty PL/SQL pozwalają na bardziej zaawansowane mozliwości tworzenia skryptów DDL

• Skrypt do tworzenia listy uprawnień dla bieżącego użytkownika:

SELECT

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USER)

            ||

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USER)

            ||

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USER)

FROM DUAL

• Skrypt do tworzenia indeksów w bieżącym schemacie

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, USER) ||';' FROM USER_INDEXES WHERE VISIBILITY = 'VISIBLE'

• Skrypt do tworzenia bieżącego użytkownika

SELECT DBMS_METADATA.GET_DDL('USER', USER)

FROM DUAL – pod warunkiem, że użytkownik nie jest SYS/SYSDBA

• Przeniesienie tabel  ze schematu SOURCE_SCHEMA  do schematu DESTINATION_SCHEMA wszystkich z tabel z nazwami zaczynającymi się na B, z pominięciem atrybutów storage i  kluczy obcych…

zacznijmy od  stworzenia tabeli na wynikowy skrypt
CREATE  TABLE TBL_DDL_DATA(
DDL_SCRIPT CLOB DEFAULT EMPTY_CLOB()
);

poniżej skrypt

DECLARE

v_hdl NUMBER;

v_th1 NUMBER;

v_th2 NUMBER;

v_doc sys.ku$_ddls := sys.ku$_ddls();

   BEGIN

      delete from T_BLOB;

      v_hdl := DBMS_METADATA.OPEN('TABLE', 'LATEST');

      DBMS_METADATA.SET_FILTER (v_hdl,'SCHEMA_EXPR','

IN( ''SOURCE_SCHEMA'')');

      DBMS_METADATA.SET_FILTER (v_hdl,'NAME_EXPR','LIKE ''B%''');

      v_th1 := DBMS_METADATA.ADD_TRANSFORM (v_hdl,'MODIFY', NULL, 'TABLE');

      DBMS_METADATA.SET_REMAP_PARAM(v_th1,'REMAP_SCHEMA', 'SOURCE_SCHEMA', 'DESTINATION_SCHEMA');

      v_th2:=DBMS_METADATA.ADD_TRANSFORM(v_hdl,'DDL');

      DBMS_METADATA.SET_TRANSFORM_PARAM(v_th2,    'SQLTERMINATOR', TRUE);

DBMS_METADATA.SET_TRANSFORM_PARAM(v_th2,    'SEGMENT_ATTRIBUTES', FALSE);

DBMS_METADATA.SET_TRANSFORM_PARAM(v_th2,'REF_CONSTRAINTS', FALSE, 'TABLE');

DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_QUERY(v_hdl));

LOOP

   

    v_doc := DBMS_METADATA.FETCH_DDL(v_hdl);

    IF v_doc IS NOT EMPTY THEN

        IF v_doc.COUNT >0 THEN

            INSERT INTO TBL_DDL_DATA(DDL_SCRIPT)

            VALUES (v_doc(v_doc.last).DDLTEXT);

        END IF;

    END IF;

EXIT

WHEN v_doc IS NULL;

END LOOP;

COMMIT; 

DBMS_METADATA.CLOSE(v_hdl);

END;

Jeśli chcemy wykonać skrypt, zaleca się posiadać rolę CREATE TABLE do schematu docelowego

Moje uwagi:

• z poziomu SQL nie ma możliwości parametryzacji skryptów - pominięcia parametrów storage, ustawienia filtra, zmiany schematu - to wszystko można zrobić w PL/SQL
• Skrypty  dla lokalnych dla tabeli partycjonowanych działają dobrze pod warunkiem, że owe tabele są puste(zgodnie z założeniem pakiety – tworzenie  struktur, a nie ich rozbudowywanie)
• Skrypty DDL dla tabel partycjonowanych  interwałowo niekiedy są generowane błędnie
• Dłuższe skrypty opłaca się zapisywać do pól CLOB
• Pakiet jest dość powolny

Walidacja NIP, PESEL i REGON - im mniej kodu tym lepiej

W poniższym niewielkim pakiecie chciałem pokazać zastosowanie niektórych rzadziej wykorzystywanych możliwości języka PL/SQL, a mianowicie:

• kolekcji
• parametrów domyślnych
• typu SIMPLE_INTEGER ( ze względów wydajnościowych)

Zauważmy, że wszystkie trzy algorytmy walidacji dla NIP, PESEL i REGON  mają następujące podobne cechy:

• tekst na wejściu ma mieć długość o jedną cyfrę dłuższą niż długość tablicy wag
• w jednakowy sposób obliczamy sumę kontrolną - różnica jest w dzielniku
• walidacja sumy kontrolnej z ostatnim cyfrą wprowadzonego literału występuje w dwóch wersjach dla PESEL oraz dla NIP i REGON

Inne założenia i uwagi:

•  każda z funkcji zwraca jeden jeśli parametr jest poprawny i zero w przeciwnym przypadku
• pragma WNDS umozliwia wywołanie funkcji na poziomie SQL
• argument może być dowolnej długości i zawierać dowolną liczbę znaków nie będących cyframi za pomocą wyrażeń regularnych usuniemy je
• deklaracja tablic wag w części deklaratywnej ciała pakietu poprawiło wydajność o około 20% w porównaniu z deklaracja tablic wag wewnątrz ciał funkcji. Tablice tworzone są podczas pierwszego wywołania w danej sesji bazodanowej
• jest to kod szybki, ale stanowi pewien kompromis pomiędzy wydajnością a czytelnością
• kompilacja za pomocą poniższych parametrów (do kodu maszynowego z zaawansowaną optymalizacją) zwiększa wydajność około dwukrotnie: ALTER PACKAGE PCKG_WALIDUJ_IDS COMPILE PLSQL_CODE_TYPE = NATIVE  PLSQL_OPTIMIZE_LEVEL = 3
• jest to krótki protest  przeciwko kodowaniu metodą kopiuj & wklej
• wymagany jest Oracle 11g

CREATE OR REPLACE PACKAGE PCKG_WALIDUJ_IDS AS
    FUNCTION    FDB_Waliduj_NIP(pc_NIP VARCHAR2) RETURN INTEGER;
    FUNCTION    FDB_Waliduj_PESEL(pc_PESEL VARCHAR2) RETURN INTEGER;
    FUNCTION    FDB_Waliduj_REGON(pc_REGON VARCHAR2) RETURN INTEGER;
    PRAGMA RESTRICT_REFERENCES(FDB_Waliduj_NIP, WNDS);
    PRAGMA RESTRICT_REFERENCES(FDB_Waliduj_PESEL, WNDS);
    PRAGMA RESTRICT_REFERENCES(FDB_Waliduj_REGON, WNDS);
END;
/

CREATE OR REPLACE PACKAGE BODY PCKG_WALIDUJ_IDS IS
------------------------------------------------------
TYPE t_Wagi IS TABLE OF SIMPLE_INTEGER;
vt_NIP t_Wagi := t_Wagi(6, 5, 7, 2, 3, 4, 5, 6, 7 );
vt_PESEL t_Wagi := t_Wagi(1, 3, 7, 9, 1, 3, 7,9,1,3);
vt_REGON_9 t_Wagi := t_Wagi(8, 9, 2, 3, 4, 5, 6, 7);
vt_REGON_14 t_Wagi := t_Wagi( 2, 4, 8, 5, 0, 9, 7, 3, 6, 1, 2, 4, 8);
----------------------
FUNCTION PDB_Waliduj( pc_Literal VARCHAR2, pt_Wagi IN t_Wagi,
    pn_Modulo SIMPLE_INTEGER, pb_PESEL BOOLEAN :=FALSE) RETURN NUMBER
IS
    vc_Literal VARCHAR2(14 CHAR);
    vn_SumaKontr SIMPLE_INTEGER:=0;
BEGIN
    IF NVL(REGEXP_COUNT(pc_Literal,'[[:digit:]]'),0) <> pt_Wagi.COUNT + 1 THEN        
        RETURN 0;
     END IF;
---Suma kontrolna
    vc_Literal := REGEXP_REPLACE( pc_Literal,'[^[:digit:]]','');                      
    FOR i IN 1..pt_Wagi.COUNT
    LOOP
        vn_SumaKontr := vn_SumaKontr + TO_NUMBER(SUBSTR( vc_Literal, i,1)) * pt_Wagi(i);
    END LOOP;
---
    vn_SumaKontr := MOD(vn_SumaKontr, pn_Modulo);
--  Dla NIP poniższy przypadek nie powinien wystąpić, dla PESEL nie wystąpi
    IF vn_SumaKontr = 10 THEN
        vn_SumaKontr := 0;
    END IF;  
--  PESEL    
    IF pb_Pesel THEN
        IF vn_SumaKontr + TO_NUMBER(SUBSTR( vc_Literal, Length(vc_Literal),1)) IN ( 0, 10)  THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END IF;
-- NIP, REGON
    IF vn_SumaKontr = TO_NUMBER(SUBSTR( vc_Literal, Length(vc_Literal),1)) THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
----
FUNCTION  FDB_Waliduj_NIP(pc_NIP VARCHAR2) RETURN INTEGER
IS   
BEGIN    
    RETURN PDB_Waliduj(pc_NIP, vt_NIP, 11);           
END;
------------------------------------------------------  
FUNCTION  FDB_Waliduj_PESEL(pc_PESEL VARCHAR2) RETURN INTEGER
IS  
BEGIN
    RETURN PDB_Waliduj(pc_Pesel, vt_PESEL, 10, TRUE);
END;
------------------------------------------------------
FUNCTION  FDB_Waliduj_REGON(pc_REGON VARCHAR2) RETURN INTEGER
IS  
BEGIN           
    RETURN PDB_Waliduj(pc_REGON, vt_REGON_9,  11) +
    PDB_Waliduj(pc_REGON, vt_REGON_14, 11);
END;
END;
/

Niektóre parametry bazy a możliwośc kompilacji i poprawność kodu

Przenosząc kod PL/SQL pomiędzy serwerami można się natknąć na kilka ciekawych zagwozdek..
Skupiujemy schematy, uprawnienia, a pomimo tego kod może działać źle lub nie działać wcale
Ze względu  na rożne ustawienia serwerów bazy na takiej samej wersji bazy danych kod może nie chcieć się skompilować lub działać inaczej..
compatible - jeśli mamy ustaloną zgodność z poprzednią wersją bazy danych, a korzystamy z  dobrodziejstw najnowszej, mamy gwarantowany błąd kompilacji, np. kod zawierający instrukcję CONTINUE w pętlach przestanie się kompilować
open_links, open_links_per_instance - oba parametry ustalają  maksymalną lczbę połączeń do innych baz danych per sesję/instancję. domyślna wartość dla obu parametrów to 4.  Jeśli mamy kod pobierający dane z większej ilości zewnętrznych baz, nie zostanie on skompilowany..
remote_depedencies_mode - jeśli w złożonych aplikacjach PL/SQL korzystających z wywołań RPC zmienia się wartość parametru z signature na timestamp -może to być źródłem błędów kompilacji i wykonania
plsql_ccflags - są to flagi kompilacji warunkowej.  Wg mnie to potencjalne źródło mnóstwa kłopotów.. Rozważmy poniższy przykład
CREATE OR REPLACE PROCEDURE PDB_Conditional_Compilation IS
BEGIN
 DBMS_OUTPUT.PUT_LINE('START');
 $IF $$SPECIAL_COMPILATION $THEN
  DBMS_OUTPUT.PUT_LINE('Special compilation');
 $END
 DBMS_OUTPUT.PUT_LINE('STOP');
END;
Jeśli podczas kompilacji procedury nie ustawimy parametru $$SPECIAL_COMPILATION w opcji plsql_ccflags, i zmienna nie będzie ustawiona na poziomie sesji, kod wykona się bez instrukcji
DBMS_OUTPUT.PUT_LINE('Special compilation');
Można oczywiście z wykorzystaniem kompilacji warunkowej napisać  wyjątkowo złośliwy kod jak poniżej, ale jest to przypadek teoretyczny:
   $IF dbms_db_version.ver_le_11 $THEN
           ....kod....
   $ELSE
       RAISE_APPLICATION_ERROR(-20100, 'Kto mnie wykryje ??');
   $END

nls_length_semantics - problem został dokładniej omówiony w poniższym linku
Semantyka bajtowa i znakowa a poprawność kodu
Ustawienie zmiennej na semantykę bajtową może powodować błędy kompilacji np. poniższa deklaracja zmiennej:
 vc_Test VARCHAR2(4) :=  'kość';
wewnątrz bloku stanie się przyczyną błędu:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Taki sam błąd (ale już wykonania, nie kompilacji) wygeneruje poniższy blok:

DECLARE

 vc_Test VARCHAR2(4);

BEGIN

  vc_Test  :=  'kość';
END;

Prosta namiastka mutexu na bazie dla funkcjonalności PL/SQL

CEL:

Chcemy, aby na bazie , dla pewnej grupy funkcjonalności  PL/SQL była uruchomiona w danej chwili co najwyżej jedna instancja przetwarzania…

Propozycja rozwiązania:

  Wykorzystamy pakiet  SYS.DBMS_APPLICATION_INFO ( ma GRANT EXECUTE TO PUBLIC), tabelę TBL_MUTEX , w której będziemy zapisywać parametryzację orazpakiet PCKG_MUTEX

Wymagane uprawnienia:

CREATE TABLE, CREATE PROCEDURE,  odczyt z perspektywy systemowej v$session

Elementy DDL:

Szczegóły implementacyjne:

CREATE TABLE TBL_MUTEX

(

  PROCESS_NAME         VARCHAR2(32 CHAR)        NOT NULL,

  DESCRIPTION          VARCHAR2(50 CHAR)  NOT NULL,

  F_EXCLUSIVE_RUNNING  VARCHAR2(1 CHAR)    DEFAULT 'Y'  NOT NULL

);

CREATE UNIQUE INDEX IDX_MUTEX ON TBL_MUTEX

(PROCESS_NAME);

ALTER TABLE TBL_MUTEX ADD (

  CHECK ("F_EXCLUSIVE_RUNNING" IN('Y','N')),

  CONSTRAINT PK_MUTEX

 PRIMARY KEY

 (PROCESS_NAME));

----------------- PAKIET ---------

CREATE OR REPLACE PACKAGE PCKG_MUTEX IS

 PROCEDURE PDB_SetMutex(pc_MutexName VARCHAR2);

 PROCEDURE PDB_ClearMutex;

 FUNCTION  FDB_IsMutexUsed(pc_MutexName VARCHAR2) RETURN NUMBER;

 FUNCTION  FDB_GetMutexInfo(pc_MutexName VARCHAR2)  RETURN VARCHAR2;

  PRAGMA RESTRICT_REFERENCES (FDB_IsMutexUsed, WNDS, RNPS);

  PRAGMA RESTRICT_REFERENCES (FDB_GetMutexInfo, WNDS, RNPS);

END;

/

CREATE OR REPLACE PACKAGE BODY PCKG_MUTEX IS

CURSOR C_Mutex(pc_MutexName VARCHAR2, pn_CheckSID NUMBER) IS

SELECT

         'Na komputerze ' || TRIM(s.terminal) || ' użytkownik '||

         TRIM(s.osuser) || ' zalogowany do Oracle jako ' || s.username ||' korzysta z aplikacji ' ||

          LTRIM(RTRIM(s.module)) AS Line

FROM v$session s

   WHERE

      (s.username IS NOT NULL)

     AND (NVL (s.osuser, '!!!!!!!!') NOT IN ( 'SYSTEM', 'SYS'))

     AND (s.TYPE <> 'BACKGROUND')

     AND ((SID <> sys_context('userenv','sessionid') AND pn_CheckSID >0)

        OR pn_CheckSID =0 )

     AND s.module  =

     ( SELECT process_name FROM TBL_MUTEX WHERE F_EXCLUSIVE_running = 'Y' AND process_name = pc_MutexName

     )

;

----------------------------------

 FUNCTION  FDB_IsMutexUsed(pc_MutexName VARCHAR2) RETURN NUMBER

 IS

  BEGIN

   FOR EL IN C_Mutex(pc_MutexName, 1)

     LOOP

        RETURN 1;

     END LOOP;

    RETURN 0;

  END;

PROCEDURE PDB_SetMutex(pc_MutexName VARCHAR2)

 IS

  BEGIN

    IF   FDB_IsMutexUsed(pc_MutexName) =1 THEN

     RAISE_APPLICATION_ERROR(-20100, FDB_GetMutexInfo(pc_MutexName) );

    END IF;

    DBMS_APPLICATION_INFO.SET_MODULE(pc_MutexName, NULL);

  END;

----------------------------------

 PROCEDURE PDB_ClearMutex

  IS

  BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE( NULL, NULL);

  END;

 FUNCTION  FDB_GetMutexInfo(pc_MutexName VARCHAR2)  RETURN VARCHAR2

 IS

  BEGIN

   FOR EL IN C_Mutex(pc_MutexName,0)

     LOOP

       RETURN EL.LINE;

     END LOOP;

    RETURN NULL;

  END;

END;

/