Re: the difference between NULL and ' ':an interseting result

From: <feenan_at_nova.enet.dec.com>
Date: 3 Jan 1994 14:38:21 GMT
Message-ID: <2g9akt$3v6_at_jac.zko.dec.com>


The following scenario does not show the difference between NULL and ' '. The problem is that (as shown by this thread) many applications/users don't like NULL. When unloading data into a flat file representation most database managers recognize this and have a method of representing NULL with some value (usually blank for text and 0 for numeric). Anyways try the following using this mechanism in Oracle and see what happens...I think it is SET NULL in Oracle. The default value for this I bet is a blank...thus the difference between the two files is by default the, none.

Jay Feenan
Digital Equip. Corp

***opinions expressed here are totally my own and not those of my employer***

|>
|>Hello folks,
|>An interesting fact about NULLS in Oracle.This is a sequel
|>to Mr.David Moore's post about SQL blanks where he was wondering
|>about the difference between '' and ' '.Of course we had concluded
|>that '' was NULL and whereas ' ' was a blank space.Now i have done
|>a small experiment whose results i want to report to the net.
|>First create a table like this
|>----> Create Table temp (col1 char(1));
|>Now Insert a null into this table like this
|>----> Insert into temp values(NULL);
|>Now spool the contents of this table to a file called one.lst
|>----> spo one
|>----> select * from temp;
|>----> spo off
|>Now delete this entry(row) from this table.
|>delete from temp;
|>Now insert a blank into the table like this
|>----> Insert into temp values(' ');
|>Now spool the contents of this table to a file called two.lst
|>---->spo two.lst
|>---->select * from temp;
|>---->spo off
|>Now we have two files one.lst and two.lst.
|>
|>One would conclude that if u use the diff command in unix
|>on this two files it would be different But if u run the
|>command
|>$ diff one.lst two.lst
|>the diff does not report any
|>difference.Strange isn't it.
|>I look forward to hearing from the net.
|>
|>HAPPY NEW YEAR AND THREE CHEERS TO ORACLE.
|>
|>Regards,
|>Mahesh Vallampati
|>
|>____________________________________________________________________
|>Mahesh Vallampati,(HOME) | Mahesh Vallampati(Office) |
|>301 Ball St., # 1086, | 244,WERC,Texas A & M Univ. |
|>College Station, Tx - 77840 . | College Station,Tx 77840 |
|>Ph : ( 409 ) 268 1489 . | Ph :(409 ) 845 6189 |
|>____________________________________________________________________
|>I PROMISE TO USE THE KEY ,THE | THY SHALL
|>WHOLE KEY AND NOTHING BUT THE KEY | INHERIT AND
|>SO HELP ME CODD | DISTRIBUTE
|>____________________________________________________________________
|>
|>
Received on Mon Jan 03 1994 - 15:38:21 CET

Original text of this message