Blog programisty w Oracle PL/SQL

Jest to blog eksperymentatora programisty w PL/SQL dla Oracle. Wszystkie kody tutaj zamieszczone mogą być dowolnie wykorzystywane i zmieniane. A jeśli Ktoś z Gości znajdzie błąd, będę niezwykle wdzięczny...
Zapisz

Szukaj na tym blogu

piątek, 13 kwietnia 2012

Unikalne nazwy dla obiektów

Poniższy trigger wynika z własnych przemyśleń na temat nazywania obiektów w bazie danych i mnóstwa problemów, jeśli w różnych schematach są obiekty o tej samej nazwie np.
  • problemy ze scalaniem schematów, występujące w wyniku rotacji użytkowników baz danych
  • redundancja danych i problemy związane z identyfikacją biznesowego znaczenia obiektów o takiej samej nazwie  
  • kłopoty z utrzymaniem spójności kodu i odwołaniami do obiektów powtarzających się w wielu schematach
  • nadmierne zużycie przestrzeni dyskowej
Poniżej przykłady  problematycznych instrukcji
CREATE TABLE DUAL AS
SELECT * FROM DUAL
        UNION
SELECT * FROM DUAL

RENAME myTable TO DUAL
 
Trigger TRG_CHECK_DUPLICATES jest przykładem rozwiązania omawianego problemu. Został napisany przy następujących założeniach:
  • nie działa dla użytkowników SYS i SYSTEM
  • działa przed operacjami CREATE i RENAME
  • dla operacji RENAME dokonujemy prostej analizy polecenia DDL, dla polecenia CREATE nazwa obiektu pobierana jest z predefiniowanej zmiennej ORA_DICT_OBJ_NAME
  • w komunikacie wyjątku podawane są nazwy już istniejących obiektów dla instrukcji DDL
  • jest instalowany przez DBA w schemacie SYS


CREATE OR REPLACE TRIGGER SYS.TRG_CHECK_DUPLICATES
   BEFORE CREATE
   OR RENAME
   ON  DATABASE
DECLARE
    CURSOR
C (pc_ObjectName VARCHAR2) IS
        SELECT OWNER|| '.' || OBJECT_NAME || ' typu '   || OBJECT_TYPE  AS MSG FROM DBA_OBJECTS
          WHERE OBJECT_NAME = pc_ObjectName
              AND OWNER <> ORA_DICT_OBJ_OWNER;
    vc_ErrorMsg VARCHAR2(500 CHAR);
    vc_SQLStatement VARCHAR2(200 CHAR);
    vc_ObjectName VARCHAR2(30 CHAR);
    vt_List DBMS_STANDARD.ORA_NAME_LIST_T := DBMS_STANDARD.ORA_NAME_LIST_T();
    vn_Size    PLS_INTEGER;
BEGIN
    IF USER IN ('SYS', 'SYSTEM') THEN
          RETURN;
    END IF;
    BEGIN

        IF ORA_SYSEVENT = 'RENAME' THEN
            vn_Size := ora_sql_txt (vt_List);
            FOR i IN 1 .. vn_Size
            LOOP
                vc_SQLStatement := vc_SQLStatement || vt_List (i);
            END LOOP;
           
            vc_ObjectName := REGEXP_SUBSTR(TRIM(vc_SQLStatement), '[[:alnum:]]+[[:cntrl:]]*$',1,1);
            vc_ObjectName := REGEXP_SUBSTR(TRIM(vc_ObjectName), '[[:alnum:]]+',1,1);
         ELSIF ORA_SYSEVENT = 'CREATE' THEN
            vc_ObjectName:= ORA_DICT_OBJ_NAME;
        END IF;
       
        FOR EL IN C(vc_ObjectName)
         LOOP
           IF vc_ErrorMsg IS NULL THEN
                vc_ErrorMsg := 'istnieje już ' || CHR(13) || CHR(10) || EL.MSG;
            ELSE
                vc_ErrorMsg := vc_ErrorMsg || CHR(13) || CHR(10) || EL.MSG ;
            END IF;
         END LOOP;
       
     EXCEPTION WHEN OTHERS THEN
        NULL;
     END;
     IF  vc_ErrorMsg IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20101, 'Zdublowane nazwy obiektów: ' || vc_ErrorMsg );
     END IF;
END;
/

niedziela, 19 lutego 2012

Kiedy serwer śpi

Istotnym problemem jest wykrywanie obciążenia na serwerach bazodanowych i szukanie dziur czasowych na uruchomienie dodatkowych funkcjonalności. Niektóre moje funkcjonalności  nie mają kluczowego znaczenia, natomiast nie powinny być uruchamianie, kiedy instrukcja top pokazuje pełne wykorzystanie zasobów serwera.. W tym celu napisałem sobie funkcję zwracającą wartość IDLE z  polecenia top.  funkcja działa poprawnie na serwerach jedno i wieloprocesorowych dla pojedyńczej instancji danej bazy, nie była testowana w konfiguracjach  wieloinstancyjnych z przełączaniem.. Rozbieżnośc między top a poniższą funkcją była mniejsza niż 8 %...

CREATE OR REPLACE FUNCTION  Fdb_GetIdlePercent(pn_Seconds NUMBER DEFAULT 5) RETURN NUMBER
AS
 vn_IdleTime    NUMBER;
 vn_CPUCount NUMBER;BEGIN
    SELECT VALUE INTO vn_IdleTime FROM V$OSSTAT WHERE STAT_NAME = 'IDLE_TIME';
    SELECT VALUE INTO vn_CPUCount FROM V$OSSTAT WHERE STAT_NAME = 'NUM_CPUS';
    SYS.DBMS_LOCK.SLEEP( pn_Seconds);
  SELECT VALUE - vn_IdleTime    INTO vn_IdleTime FROM V$OSSTAT WHERE STAT_NAME = 'IDLE_TIME';  
    RETURN ROUND(vn_IdleTime/(vn_CPUCount *pn_Seconds*100)  *100, 2 );   
    EXCEPTION WHEN OTHERS THEN
           RETURN 100;
END;

Funkcja wymaga następujących niestandardowych uprawnień:
GRANT EXECUTE ON   SYS.DBMS_LOCK TO :current_user;
GRANT SELECT ON SYS.V$OSSTAT TO :current_user;

Istotnym parametrem jest czas pomiaru - 5 sekund lub dłużej powinno wystarczyć..

 Poniższy  blok czeka, aż parametr idle spadnie do 50% na podstawie  sprawdzania przedziałów czasowych o długości 300 sekund.
DECLARE
      vn_Idle NUMBER;
      vn_Delay NUMBER :=  300;
BEGIN
    LOOP
         vn_Idle := Fdb_GetIdlePercent(vn_Delay);
         EXIT WHEN vn_Idle <= 50;
    END LOOP; 
--- Insert your code here 
END;

wtorek, 27 grudnia 2011

Kto koduje ten błądzi

Zdecydowaną większość z poniższych błędów i ja sam popełniałem

1.Niewłaściwa obsługa wyjątków

BEGIN
....
.....

EXCEPTION
WHEN OTHERS THEN
COMMIT ;

END;

Wprawdzie kompilator PL/SQL  w wersji 11g wykrywa instrukcje WHEN OTHERS THEN NULL, ale traktowane są jako ostrzeżenia i to przy włączonej opcji PLSQL_WARNINGS='ENABLE:ALL'. W skrajnych przypadkach dla zaharmonogramowanych jobów; brak obsługi wyjątków jest w stanie w wiecznej pętli uruchamiać błędny kod i skonsumować istotną część zasobów bazy
 
2. Brak NVL w algorytmach obliczeniowych
DECLARE
a INTEGER;
b INTEGER :=1;
BEGIN
DBMS_OUTPUT.PUT_LINE(a + b);
END:
inna sprawa, że wg mnie pola używane do obliczeń powinny być DEFAULT 0 NOT NULL. Nawet jeśli się to przestrzega, warto wstawiać NVL dla świętego spokoju..

Nieznajomość faktu, że niektóre funkcje agregujące np AVG zadziałają inaczej dla wartości NULL i NOT NULL
SELECT AVG(pole_liczbowe) FROM tabela
zadziała inaczej
SELECT AVG( NVL(pole_liczbowe,0)) FROM tabela
jeśli pole_liczbowe bedzie przyjmować wartości NULL

3 Nadużywanie DBMS_OUTPUT w  pętlach
4. Warunek LENGTH( zmienna_tekstowa) = 0 zamiast zmienna_tekstowa IS NULL

5. Deklarowanie zmiennych odpowiadających polom w tabeli bez korzystania z zakotwiczeń %TYPE. Szczególnie ciekawym przypadkiem jest inna maksymalna długość VARCHAR2 w polu tabeli i jako zmienna w kodzie

6. W obrębie bazy deklarowanie tabel z polami VARCHAR2 ( size CHAR)i VARCHAR2( size BYTE)

7. Nadużywanie dynamicznego SQL - chociaż czasami bywa niezbędny. Szczególnie niebezpieczny jest SQL wykonujący operacje DDL, np.  TRUNCATE TABLE, CREATE INDEX, DROP INDEX... Polecenia DDL wywołują niejawne zatwierdzanie transakcji, co może w sposób bardzo skuteczny zniszczyć logike transakcyjną aplikacji w PL/SQL

8. Nadawanie id rekordom w inny sposób niż przy użyciu sekwencji
9. Dokonywanie konwersji ze stringów na liczby i na daty bez podania masek - taki kod jest nieprzenośny zupełnie
10. Stosowanie zmiennych typu CHAR

11. Niepodawanie obiektom baz danych w kodzie PL/SQL nazw schematów. I to wszystkim bez wyjątku !!! Czasami w dużej bazie może pojawić się kilka tabel klienci w rożnych schematach i mamy pewne źródło problemów. Przy migracji i scalaniu baz podawanie ownerów mocno ułatwia życie... Dlatego też sądzę, że dla kluczowych tabel należy tworzyć publiczne synonimy coby mniej rozgarnięci userzy nie popełnili ich w swoich schematach...

12. Stosowanie wskazówek w zapytaniach.
SELECT /*+ INDEX( t idx_klienci_nazwa) */ FROM schemat.tbl_klienci t WHERE t.nazwa = vc_Nazwa
Po mojemu hintów już od bazy 10g nie powinno się używać - wbudowany optymalizator radzi sobie z optymalizacją dobrze. Zaszycie hinta niesie za sobą niebezpieczeństwa związane z przebudową, usunięciem, rozszerzeniem indeksów, przeprowadzką rzadkich indeksów na bitmapowe przy migracji na enterprise, zmianą liczebności zbiorów danych i sposobem ich przechowywania( np.partycjonowanie tabel) - co może spowodować spory spadek wydajności... Lepiej jest więcej czasu poświęcić na rozpracowanie opornego zapytania niż umieszczać wskazówki w kodzie...

13. Pogrzebmy w bebechach
select UPPER(username), UPPER (osuser), UPPER (program), UPPER( machine)
into vc_UserName, vc_OSUser, vc_Program, vc_Machine
from v$session
where sid = USERENV('sid');
Staranie się, aby unikać odwołań do tabeli słownikowych, a jeśli się tego nie uda to takowe wywołania wsadzić do osobnego pakietu i korzystać tylko i wyłącznie z funkcji pakietowych... Tak samo w przypadku pakietów systemowych - wywołania należy opakować

14. Uprawnienia
GRANT SELECT, INSERT, DELETE, UPDATE on fk.tbl_faktury TO PANI_BASIA;
GRANT SELECT ON fk.tbl_faktury TO PANI_JOLA.
Wszelakie uprawnienia do obiektów bazodanowych rozdzielać wyłącznie za pomocą ról, użytkownikom nie wolno nadawać ról bezpośrednio, to prowadzi do trudnego do opanowania chaosu w kodzie. Bywają czasami pojętni użytkownicy piszący własne procedury, dla nich można zrobić wyjątek, dla pozostałych lamerów nie

15.Przedrostki
select * from schemat.klienci... Stosowanie standaryzowanych przedrostków (tbl_, vw_, seq_, mv_, pdb_, pckg_ ) do obiektów baz danych - aby w wypadku choroby genialnego kodera lub błędów runtime dało się cokolwiek zrozumieć, co genialny twórca miał na myśli
16. Nadużywanie zapytań z dual. Widziałem złośliwy test:
CREATE TABLE DUAL AS
SELECT * FROM DUAL
  UNION ALL
SELECT * FROM DUAL
17. Używanie w kodzie funkcji  SYSDATE ( za wyjątkiem pieczątek czasowych), zamiast tego należy użyć parametrów 

piątek, 1 lipca 2011

Algorytmy rekurencyjne w SQL

 Możliwe?? Tak, wystarczy polubić klauzulę model..
Poniżej w dwóch zapytaniach są  algorytmy zapisane w wersji rekurencyjnej
  • ciąg Fibonacciego
  • silnia
  • algorytm Euklidesa do wyznaczania największego wspólnego podzielnika
Przedstawione są dwie techniki:
  •  modele iteracyjne -znakomicie nadają się do implementacji jednoargumentowych funkcji rekurencyjnych, a ich zapis jest niemal intuicyjny. Niestety przy większej liczbie parametrów pojawiają się problemy cyklicznego wyliczania komórek
  • modele nieiteracyjne  - formuły są nieco bardziej zawiłe, ale można obsłużyć funkcje wieloargumentowe
    SELECT                                                    
          d  , f AS FIBBONACCI,
          S AS SILNIA
      FROM   (SELECT   0 d  FROM DUAL)
    MODEL
       DIMENSION BY (0 d)
       MEASURES (0 f, 0 s)
       RULES
          ITERATE (10000000) UNTIL (ITERATION_NUMBER = 50)
           (f [ITERATION_NUMBER] =
                                CASE ITERATION_NUMBER
                                    WHEN 0 THEN 0
                                    WHEN 1 THEN 1
                                  ELSE
                                    f[ITERATION_NUMBER - 2] + f[ITERATION_NUMBER - 1]
                                END,
          s [ITERATION_NUMBER] =
                      CASE ITERATION_NUMBER
                        WHEN 0 THEN 1
                        ELSE           
                        ITERATION_NUMBER * s[ITERATION_NUMBER - 1]
                      END)




    Poniższy przykład można jeszcze nieco uprościć :)

    SELECT   L1, L2, NWD
      FROM   (SELECT   L1, l2
                FROM   (    SELECT   LEVEL - 1 L1
                              FROM   DUAL
                        CONNECT BY   LEVEL <= 20),
                       (    SELECT   LEVEL - 1 L2
                              FROM   DUAL
                              CONNECT BY   LEVEL <= 20))
    MODEL
       DIMENSION BY (L1, L2)
       MEASURES (0 NWD )
       RULES SEQUENTIAL ORDER
          (NWD [ANY, ANY] =
                CASE
                   WHEN CV (l2) = 0
                   THEN
                      CV (l1)
                   WHEN CV (l2) <= CV (l1)
                   THEN
                      NWD[CV (l2), MOD (CV (l1), CV (l2))]
                END,
          NWD [ANY, ANY] =
                CASE
                   WHEN CV (l2) > CV (l1) THEN
                            NWD[CV (l2), CV (l1)]
                   ELSE NWD[CV (l1), CV (l2)]
                END)

    niedziela, 29 maja 2011

    Trzy pułapki w PL/ SQL

    1. Modyfikator result_cache dla funkcji niedeterministycznych przekształca je na deterministyczne
    Funkcja deterministyczna jest to funkcja zwracająca dla takiego samego zbioru parametrów identyczny  wynik. Deterministyczności wymaga sie  od wyrażeń w indeksach  funkcyjnych i polach wyliczeniowych dla tabel.. Deterministyczność wyklucza użycie funkcji SYSDATE, funkcji losowych...

    Rozważmy poniższy przykład:
     Skompilujmy poniższą funkcję:
     CREATE OR REPLACE FUNCTION Fdb_Test RETURN DATE
    RESULT_CACHE
        IS
    BEGIN
        RETURN SYSDATE;
    END;




    Następnie wywołajmy pięć  razy w odstępach kilkusekundowych:
    SELECT Fdb_Test  FROM DUAL
    O ile bufor dla result cache jest nieprzepełniony, to funkcja zwróci nam za każdym wywołaniem taką samą wartość. Poniższe sprawdzenie pokaże czterokrotne użycie bufora(za pierwszym razem rzeczywiście wywoła się SYSDATE):
    SELECT SCAN_COUNT
       FROM  V$RESULT_CACHE_OBJECTS WHERE  NAMESPACE ='PLSQL' AND NAME LIKE '%FDB_TEST%'

    2. Struktury zakotwiczone %ROWTYPE zapewniają uproszczenie wstawiania..
    I z reguły jest to prawda
    Stwórzmy tabelę:
    CREATE TABLE TBL_TEST
          (ID NUMBER(4),
            OPIS VARCHAR2(50 CHAR)
           )
    Można wówczas dokonać wstawiania w bardzo elegancki sposób bez podawania listy kolumn...
    DECLARE
      vr_Test TBL_TEST%ROWTYPE;
    BEGIN
      vr_Test.ID  := 1;
      vr_Test.Opis := 'Opis';
      INSERT INTO TBL_TEST VALUES vr_Test;
      COMMIT;
    END;Zakotwiczenie przy wstawianiu będzie  dalej działało  przy dodaniu nowych pól, chyba, że to będą pola obliczeniowe..

    Po dodaniu formuły
    ALTER TABLE TBL_TEST  ADD FORMULA VARCHAR2(100 CHAR)  GENERATED ALWAYS  AS ( TO_CHAR(ID) || ' ' || OPIS)
    przy próbie wykonania powyższego bloku pojawi się komunikat
    ORA-54013: INSERT operation disallowed on virtual columns
    Szkoda, że obie możliwości nie mogą działać jednocześnie (:

    3, Triggery DML blokują jedynie operacje DML
    Jeśli chcemy zablokować  w sposób dynamiczny np. usuwanie z tabel, to nie można użytkownikom końcowym nadawać żadnych uprawnień DDL do tabeli..
    Poniższy trigger
    CREATE OR REPLACE TRIGGER .TRG_TBL_TEST__NO_DELETE
    BEFORE DELETE
    ON
    TBL_TEST
    --w celu lepszej wydajności powinien to być triger  on statement, a nie FOR EACH ROW
    BEGIN
      IF EXTRACT
    ( HOUR FROM LOCALTIMESTAMP ) BETWEEN 8 AND 18 THEN
        RAISE_APPLICATION_ERROR (- 20100, 'Usuwanie w godzinach pracy z tabeli jest niedozwolone' );
      END IF;
    END TRG_TBL_TEST__NO_DELETE;
    nie zabezpieczy przed instrukcją DDL 
     TRUNCATE TABLE TBL_TEST
    jeśli użytkownik końcowy posiadać będzie bezpośrednio nadane uprawnienie ALTER TABLE