Re: Oracle NULL vs '' revisited

On Aug 17, 10:49 am, Matthew Harrison <m.harris..._at_craznar.com> wrote:
> Ok, I'm developing another oracle application where the distinction
> between NULL and '' will be important.
>
> Given Oracle doesn't adhere to SQL standards for the distinction, is
> there any best practices for comparing, and storing blank strings in a
> table where the field is conceptually NOT NULL.
>
> Thank you.
>
> --
> Pinging self [127.0.0.1] with 32 bites of banana cake:
>
> Ping statistics for 127.0.0.1:
> Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),

I do not believe, one has to set default values or some other value in the field to treat as null. Any value you put in the field/variable is not same thing as null. One has to store information whether a value is null outside of field (such as in an indicator variable). Oracle took care of this long time ago. When one writes code, e.g., in PRO*C, Oracle has host variables and indicator variables. Host variable stores the actual value of the field and indicator variables store the fact whether the field is null or not. So when you want to save a null in database, you set indicator variable, e.g., to -1. When you get a value from Oracle it will set indicator variable to -1 if value was null. Any time you do a check in your code to find whether a value is null or not, you look at the indicator variable. Oracle provides similar concepts in other labguages such Java. In PL/SQL, internally Oracle perhaps uses same approach but Oracle has built short cuts in PL/SQL language. For example to find whether i is null, one writes code:
If (i is null). To insert null, insert table (col1) values (null);

Any other mechanism used such as empty value for Strings , 0 or - 32767 for integer, or using default values are kludges which get you in trouble sooner or later because null is not same as any of these values. As long one follows Oracle's approach, there will not be any problems. In my view, Oracle's approach is the right approach. Received on Fri Aug 17 2007 - 16:14:40 CDT