Re: NULLS when col is NOT NULL ??

On Tue, 24 Aug 2004 14:21:00 -0500, Ed Stevens <nospam_at_noway.nohow> wrote:

>Oracle 8.1.7.4.1 on Win2k
>
>Developer brought this to me. Appears to be nulls in a column defined
>as NOT NULL.
>
>Here's what the column looks like:
>
>SQL> desc nlcdb.nlcprc
> Name Null? Type
> ----------------------------------------------------- --------
>------------------------------------
><snippage>
>
> NLCPRC_G2_LOAD_NBR NOT NULL
>CHAR(8)
>
><snippage>
>
>Total number of rows on the table:
>SQL> select count(*) from nlcdb.nlcprc;
>
> COUNT(*)
>----------
> 92290
>
>1 row selected.
>
>Total number of rows that are not null on this column (should be all
>of them)
>SQL> edit
>Wrote file afiedt.buf
>
> 1 select count(*)
> 2 from nlcdb.nlcprc
> 3* where nlcprc_g2_load_nbr is not null
>SQL> /
>
> COUNT(*)
>----------
> 92290
>
>1 row selected.
>
>So far, so good. Now, what values *do* we have in this column?
>SQL> edit
>Wrote file afiedt.buf
>
> 1 select distinct ascii(nlcprc_g2_load_nbr)
> 2 from nlcdb.nlcprc
> 3* where nlcprc_g2_load_nbr is not null
>SQL> /
>
>ASCII(NLCPRC_G2_LOAD_NBR)
>-------------------------
> 0
> 32
> 76
>
>Huh!?!? What's that acii zero?? Looks like nulls to me! How many of
>those do we have?
>
>SQL> edit
>Wrote file afiedt.buf
>
> 1 select count(*)
> 2 from nlcdb.nlcprc
> 3* where ascii(nlcprc_g2_load_nbr) = 0
>SQL> /
>
> COUNT(*)
>----------
> 283
>
>1 row selected.
>
>So, it appears we have 283 instances of nulls (ascii zero) in a column
>defined as NOT NULL. What am I missing here (besides my sanity, of
>course!)

Jim, Alan, Howard, Nuno ...

Thanks for setting me straight. Nuno was particularly onto the problem with his reference to other programming languages. The values for this column actually originate in a COBOL program running on the mainframe. It is brought to the client/server side, (where it is picked up by another COBOL program) then shoved into the Oracle table with no further data editing. Given that, I was looking at the data through my old COBOL programmer's eyes and seeing x'00' as the same as the COBOL reserved value LOW-VALUES, which in that environment is effectively the same as NULL. In this case, the originating program was not properly initializing a field to spaces and so was passing on whatever junk was in the field.

This also explains the behavior that brought it to the developer's attention in the first place. The reported that a SELECT on that column appeared to return spaces, but they knew (well, make that "they 'knew' ") that couldn't be right. So when they selected where col = ' ' (that's a space between the quotes) they got no hits, but if the did a select where col = '' (no space between the quotes) they got hits.

There's one other aspect of this that I realized right after my original posting, and I'm surprised no one picked up on. As I read the description of the ascii() function, it only returns the value of the first character of the evaluated value. So, even if x'00' was considered null by Oracle, it is quite possible those columns could have had something like x'0032323232' and the ascii() function would have only reported the x'00', throwing me a red herring. Unfortunately, by the time I thought of this and began to explore it, the developers had corrected the corrupt data.

So . . . to further clarify my understanding, given a column defined as CHAR(8) (fixed length, not VARCHAR -- and nulls allowed), if x'00' is not a 'representation' of nulls, what is physically stored in the 8 bytes reserved for that column? Received on Wed Aug 25 2004 - 08:11:10 CDT