Re: NULL vs. empty

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 Received on Thu May 27 2004 - 02:41:15 CDT