Problem:
--------
How to retrieve a row with a select which compares a column to a empty string

Solution:
---------

This article will explain how null values can be evaluated with host
variables.  Consider the following scenario.

  o First, I want to insert a string:
        INSERT INTO DEPT(DEPTNO, DNAME) VALUES (99, :my_string);

  o Then, I want to retrieve that same row:
        SELECT DEPTNO INTO :my_dno FROM DEPT WHERE DNAME = :my_string;

If my_string is a zero length (empty) string during the INSERT, a NULL
will be inserted into DNAME.  Consequently, the SELECT with the
comparison "DNAME = :my_string" will not return the row.  The reason
is that a NULL value does NOT equal another NULL value.  Since a NULL
represents a lack of data, a null value cannot be either equal or
unequal to any other value, even another NULL.  The only comparison
operators to use with null values are IS NULL and IS NOT NULL.

How can we work around this?  There are at least four ways:

1)  Use a special string to signify a zero length string.  For
    example,  use "empty" to signify that the string is zero length.
    This can be an awkward solution, since your application has to
    to treat the string "empty" specially.

2)  Check to see if the string is zero length.  If it is, do a
         SELECT .. WHERE DNAME IS NULL;

    otherwise do a
         SELECT .. WHERE DNAME = :my_string;

    This can be cumbersome, because if you reference multiple columns
    (say 6 columns) in your WHERE clause and each one might have a
    NULL value, then you would need 2^6 different statements.  That
    is 64 different WHERE clauses.

3)  Use the NVL function.
         SELECT .. WHERE nvl(DNAME, 'empty') = nvl(:my_string,'empty');

    Any function in a SQL statement will supress the use of available
    indexes.  If your table is indexed, using NVL here may impact
    performance.  This solution is viable for applications that don't
    need indexes.

4)  Use the straight forward method.
      SELECT .. WHERE ( (:my_string IS NULL AND DNAME IS NULL) OR
                        (DNAME = :my_string) )
    Again, indexes cannot be used with either IS NULL or IS NOT NULL
    phrase.

There may be other solutions for evaluating nulls, but those mentioned
here should be sufficient.

Achja die Abfrage NVL(spalte,chr(0)) bringt etwas zurück was nicht null ist,
aber auch nicht mit = '' klappt.

Naja, blöd ist halt wenn da ~ drinnen steht.