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 

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)

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

NULL a poprawnośc algorytmów obliczeniowych..

Wartość NULL bywa przyczyną wielu błędów w algorytmach  obliczeniowych - większość  z nich wynika  braku inicjacji zmiennych:
 Rozważmy trzy przykłady:
1.Nie działa inicjalizacja dla typów zakotwiczonych w PL/SQL
CREATE TABLE TBL_TEST
 (FIELD NUMBER DEFAULT 0 NOT NULL);

 DECLARE
   vn_Number TBL_TEST.FIELD%TYPE;
 BEGIN
   IF vn_Number IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Inicjalizacja nie zadziałała');
   END IF;
 END;
 Powyższy przypadek jest dość podstępny- zakotwiczenia pobierają tylko informację o typie bez checków...
2.Funkcje agregujące

 CREATE TABLE TBL_TEST
 (FIELD NUMBER  );


ciąg instrukcji insert, takze z wartością NULL;
INSERT INTO TBL_TEST (FIELD) VALUES (NULL);
INSERT INTO TBL_TEST (FIELD) VALUES (1);
INSERT INTO TBL_TEST (FIELD) VALUES (8); 
COMMIT;
Wykonajmy teraz zapytanie:
SELECT SUM(FIELD), AVG(FIELD), AVG(NVL(FIELD,0)) FROM TBL_TEST
Otrzymane wyniki to
9        4.5         3
Powyższy wynik świadczy o tym, że funkcja AVG do liczenia średniej wybiera wartości tylko  NOT NULL.  Dlatego warto dokładnie przestudiować dokumentację opisującą działanie funkcji agregujących..

3. Operatory arytmetyczne

DECLARE
 vn_A NUMBER(2) :=1;
 vn_B NUMBER(2) ;
 vn_C NUMBER(3);
BEGIN
  vn_C := vn_A + vn_B;
  IF vn_C IS NULL THEN 
    DBMS_OUTPUT.PUT_LINE('RESULT IS NULL');
   END IF;
END; 
Dla operatorów arytmetycznych i wbudowanych funkcji matematycznych np. ABS i SIN, jeśli co najmniej jeden parametr jest NULL, to zwracana jest także wartość NULL..

Rozpatrując powyższe przykłady, nasuwa się oczywisty wniosek - należy unikać wartości NULL w algorytmach obliczeniowych.... są one źródłem bardzo złośliwych i podstępnych błędów
Proponuję zastosowanie poniższych zaleceń:

• na etapie projektowania struktury baz danych dla pól tabel wykorzystywanych w algorytmach numerycznych powinno się wymuszać wartość  NOT NULL.  Wartość domyślna jest niekonieczna, niech lepiej zostanie bezpośrednio zainicjowana w kodzie
•  należy zamiast zmiennych typu NUMBER, BINARY_INTEGER stosować zmienne typu SIMPLE_DOUBLE, SIMPLE_INTEGER, SIMPLE_FLOAT oraz inne z przedrostkiem SIMPLE, które wymagają inicjacji na poziomie deklaracji.. Wykonanie poniższego bloku

       DECLARE
           vn_Test SIMPLE_INTEGER;
       BEGIN
           vn_Test := 0;
       END;
zakończy się błędem: PLS-00218: a variable declared NOT NULL must have an initialization assignment. oprócz tego jakiekolwiek przypisanie wartości NULL do zmiennej vn_Test też wygeneruje wyjątek... Wyjątki wyłowią niebezpieczne fragmenty kodu w algorytmach..

• Jeśli musimy użyć typu NUMBER(12,2) to wówczas najlepiej stworzyć  nagłówek pakietu do definicji własnych typów

     CREATE OR REPLACE PACKAGE PCKG_MyTypes IS
          SUBTYPE t_Number_12_2 IS NUMBER(12,2) NOT NULL;
     END;
     wówczas wykonanie poniższego bloku
     DECLARE
        vn_Test PCKG_MyTypes.t_Number_12_2;
     BEGIN
        vn_test := 0;
   END;
   także zakończy się wyjątkiem : PLS-00218: a variable declared NOT NULL must have an initialization assignment

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;

/