Steffen Conrad wrote:
> 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
What Ed suggested, or just load as character data: '0100 ';
and do some ETL.
'0100 'would be an empty field,
NULL would be just that - no data at all,
and '01000A' would be decimal 10.
--
Regards,
Frank van Bortel
Received on Thu May 27 2004 - 14:22:42 CDT
