Re: differences between null and empty varchar2

I wouldn't say that inserting "" in pro-c gives stranges results. In fact, it produces results which are absolutely consistant within the framework of Oracle.

If you read the documentation, when you insert a string of "" into a column you are required to set the indicator variable to -1 to indicate an empty string. When you do this Oracle will insert the column as null. Works every time.

The problem here is that C programmers have become so used to the concept of a null terminated string that they are tempted to say that Oracle is wrong for the way that they treat strings. In fact, there is no such concept as a null terminated string in Oracle because it simply would not make any sense.

Now, the ISO standard does state that '' is to be treated different than NULL and some time in the future Oracle has said that they will make changes to treat them differently, but until then oracle developers simply have to program by the rules that are laid out in Oracle documentation. I've been doing it for years and I've never had any unpredictable results.

By the way, a sring can never "contain" an ASCII null. That would be silly. In C language an ASCII NULL terminates array of characters so that it can be treated as a string, but the ASCII NULL is not actually a part of the string - that would not make any logical sense.

Ken

Ed Prochak wrote:

> SQL's NULL may be the same as an empty string "" in ORACLE, but the
> empty string is not the same as a string containing an ASCII nul (X'00
> or "\0" in C style). Since Oracle uses counted strings, the empty string
> is truely empty (length is zero). This differs from a string containing
> one nul character.
>
> I mention C style because Pro*C programmers may get burned by this. If
> you assign a string containing the nul ('\0') character to a VARCHAR2
> variable in embedded SQL, you can get some wheird results. In SQL "" is
> empty, but in C "" actually is an array of one character '\0'.
>
> So there is some logic to ORACLE's position/implementation.
>
>
Received on Sat Aug 07 1999 - 07:52:42 CDT