Re: NULL vs. empty

Steffen Conrad <steffen.conrad_at_gmx.de> wrote in message news:<2hlk7gFe6grcU1_at_uni-berlin.de>...
> Ed prochak wrote:
> > I understand your issue with the empty string and the NULL. But how
> > are number fields affected? If the field is numeric and the input file
> > has no value a NULL is loaded. When you dump the table to a file the
> > numeric field for null values will be blanks, right?
> >
> > I just don't see the problem on numbers that there is on character
> > data.
> >
> > Ed
>
> Ok, I will explain a little bit further to come to the point:
>
> We are loading ASN.1 coded data (mostly all telecommunication operators
> use ASN.1 coded data for billing purposes) into Oracle, do updates on it
> and unload it to encode it back to ASN.1 coded data.
>
> ASN.1 is a tag/length/value oriented binary data format. If (for
> example) a integer is coded by tag 01, a number '10' would be coded as
> '01010A' (tag '01 for int, length 01 and value '0A'H).
>
> If a field is described in the ASN.1-specification and the according tag
> is not found in the binary data a 'NULL' value is loaded in the
> according column on DB. If tag is found the value is loaded into the
> according column.
>
> But the problem is:
>
> What to do on '0100' (tag 01 for int, length 00) - this is a valid ASN.1
> coded int with _NO_ value.
>
> To preserve binary compatibility to the original loaded files we have to
> load this field onto DB with _NO_ value, because when unloading the data
> back to encode to ASN.1 we have to write '0100' back to the binary file.
>
> Therefore we _must_ divide between 'NULL' and _empty_ values, because
> the binary representation in ASN.1 would differ when doing nothing about it.
>
> What I want to know is a way to decide between these two states (NULL
> vs. empty) without creating some meta infos for each column because it
> would break up the design and would cause a very expensive redevelopment
> of our DB scripting.
>
> Therefore every way to store a non-NULL value in a number field that is
> not a representation of a common NUMBER value would solve the problem,
> because SQLLDR stops working when trying to store a non-NUMBER value in
> a NUMBER column.
>
> Bye,
> Steffen

Yes it's been awhile since i've ever seen ASN.1 data, and even then I didn't have to deal with it directly.

Okay, I think your problem is in throwing away the length information. Your current design is basically a lossy compression algorithm.

Instead, consider two tags, one a character type and one an integer type. To load them in ORACLE (or about any RDBMS) I'd make a table with 4 columns:

create table sample (
quantity integer,
qnty_len integer,
product_desc VARCHAR2(200),
product_len integer) ;

then in loading you can store the tag length in the length columns, making the distinction between <no value> and NULL trivial on dumping the data back out. You wouldn't have to use that kludge of "<tab>" on the character data as you proposed. (in the long run, it will bite you!)

so try adding the lenghts. You be much happier.

HTH
  ed Received on Thu May 27 2004 - 13:14:49 CDT