Problem z dynamicznym SQL

Cześć,

Mam taki problem dotyczący NDSa w Oracle. Chciałbym napisać funkcję, która by sprawdzała czy dany rekord należy to tabeli czy nie. Problem jest taki, że jedna z kolumn (ApartmentNumber) może byc NULLem, więc postanowiłem skleić zapytanie żeby działało w taki sposób, że jak parametr nie będzie NULLem to w porówwnaniu będzie użyte AND ApartmentNumber = :bind_apartment_number a jak NULLem to będzie AND ApartmentNumber IS NULL:

FUNCTION fbCheckIfLocationExist(
    pv_CountryName     IN VARCHAR2
   ,pv_CityName        IN VARCHAR2
   ,pv_StreetName      IN VARCHAR2
   ,pn_StreetNumber    IN NUMBER 
   ,pn_ApartmentNumber IN NUMBER DEFAULT NULL 
   ,pv_ZipCode         IN VARCHAR2 
) RETURN BOOLEAN
IS
    vv_SQLStatement VARCHAR2(4000 CHAR);
    vv_ExistOutput  VARCHAR2(1 CHAR);
BEGIN 
    vv_SQLStatement := 'SELECT CASE'
                    || '        WHEN EXISTS (SELECT NULL'
                    || '                     FROM tLMLocations'
                    || '                     WHERE CountryName = :bind_country_name'
                    || '                     AND   CityName = :bind_city_name'
                    || '                     AND   StreetName = :bind_street_name'
                    || '                     AND   StreetNumber = :bind_street_number'
                    || '                     AND   ApartmentNumber' || CASE WHEN pn_ApartmentNumber IS NOT NULL THEN ' = :bind_apartment_number' ELSE ' IS NULL' END
                    || '                     AND   ZipCode = :bind_zip_code)'
                    || '            THEN ''Y'''
                    || '        ELSE'
                    || '            ''N'''
                    || '       END'
                    || 'FROM dual';
    EXECUTE IMMEDIATE vv_SQLStatement INTO vv_ExistOutput
    USING   IN  pv_CountryName
    ,       IN  pv_CityName
    ,       IN  pv_StreetName
    ,       IN  pn_StreetNumber
    ,       IN  pn_ApartmentNumber
    ,       IN  pv_ZipCode;

    RETURN CASE vv_ExistOutput
             WHEN 'Y' 
                THEN TRUE 
             WHEN 'N'
                THEN FALSE
           END;
                      
                      
END fbCheckIfLocationExist;

I tutaj pojawiają się dwa pytania:

1. Po pierwsze mam problem z samym NDSem, kiedy usunę linijkę związaną z ApartmentNumber i wszystkimi powiązanymi zmiennymi/parametrami dostaję odpowiedź, że brakuje mi keyworda, sprawdzałem parę razy zapytanie, nawet wyświetlałem to za pomocą DBMS_OUTPUT.PUT_LINE i wszystko wyglądało OK, nic tam chyba nie brakowało.
2. Czy będzie się to w ogólę tak dało zrobić, chodzi o ten kod? Teoretycznie wiem, że mógłbym sprawdzić na samym początku czy pn_ApartmentNumber jest NULLem i przygotować dwa odrębne SELECTy, ale chciałbym to w jednym zrobić

To pokaz to zapytanie wyplute przez put line. Powinno działać ale ciężko tak jak nie wiadomo gdzie jest błąd.

SELECT NULL FROM costam

Czy to zawsze nie będzie zwracać nulli? Może 0, może wiele wierszy, ale będzie jedna kolumna a w niej wartość null?

S4t napisał(a):

To pokaz to zapytanie wyplute przez put line. Powinno działać ale ciężko tak jak nie wiadomo gdzie jest błąd.

OK, właśnie jak wklejałem tutaj co znajduje się w zmiennej vv_SQLStatement zauważyłem, że END nie był oddzielony z FROM dual i ostatecznie baza próbowała odpalić ENDFROMdual i sypała błędem, że brakuje keyworda :) Widzę, że funkcja działa jeżeli ApartmentNumber nie jest NULLem, ale dla NULLa rzuca invalid relational operator, będę musiał jeszcze na to popatrzeć.

Co do

KamilAdam napisał(a):

SELECT NULL FROM costam

Czy to zawsze nie będzie zwracać nulli? Może 0, może wiele wierszy, ale będzie jedna kolumna a w niej wartość null?

Oj zawsze, zwraca NULLa czyli to co tygryski lubią najbardziej

Co to znaczy "problem dotyczący NDSa w Oracle" ? (skrót "NDS") Jakoś nie kojarzę tego skrótu :)

1. Dlaczego dynamic sql ? (= która wersja oracle?)
2. Jak już PL/SQL to po co dynamiczny SQL? Ja bym kombinował w ten sposób:

FUNCTION fbCheckIfLocationExist(
    pv_CountryName     IN VARCHAR2
   ,pv_CityName        IN VARCHAR2
   ,pv_StreetName      IN VARCHAR2
   ,pn_StreetNumber    IN NUMBER 
   ,pn_ApartmentNumber IN NUMBER DEFAULT NULL 
   ,pv_ZipCode         IN VARCHAR2 
) RETURN BOOLEAN
IS  
  vv_ExistOutput BOOLEAN := FALSE;
BEGIN 

  FOR x IN (
  	SELECT 
      1 
    FROM tLMLocations 
    WHERE 
        CityName     = pv_CityName  
    AND StreetName   = pv_StreetName
    AND StreetNumber = pn_StreetNumber
    AND ZipCode      = pv_ZipCode
    AND (pn_ApartmentNumber IS NULL OR ApartmentNumber=pn_ApartmentNumber)
    AND rownum<=1
  ) LOOP
    vv_ExistOutput := TRUE;
  END LOOP; 
  
  RETURN  vv_ExistOutput;

END fbCheckIfLocationExist;
/

Ewentualnie jakaś modyfikacja typu ApartmentNumber=nvl(pn_ApartmentNumber,-999999) (gdzie -999999 jest spoza zakresu numeracji ApartmentNumber).