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

My apologies for jumping in feet first: I've missed some of this thread.

feenan_at_nova.enet.dec.com wrote:
> 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.

The above is true, but the default display value of null is an empty string. The problem with
  SELECT NULL FROM DUAL
vs
  SELECT ' ' FROM DUAL (which is essentially what the query posted by Mahesh Vallempati did) is that SQL*Plus pads the output to the page width, so that when you spool the output to a file (with linesize set to the default value of 80), the NULL query returns nothing, and then pads the line with 80 spaces, whilst the space query returns 1 space, and then pads with another 79, so the result looks the same. (Actually, thinking about it, the padding happens within the column: you get nothing padded to the column width, then padded to the page width. But the effect is the same.)

This is of course a separate problem from that of Forms stripping trailing spaces from strings :-)

--
  _________________________   _________________________
 /  Tommy Wareing          \ /                         \
|  p0070621_at_brookes.ac.uk   X   'Bugger!' said Piglet   |
 \  0865-483389            / \                         /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~