Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: NULL vs. empty
Steffen Conrad wrote:
> Hi all,
>
> I have the following problem:
>
> We are loading ASN.1 coded data into 8i, runnning updates etc. on the
> loaded data and unloading it. When doing this we have to assure handling
> of empty fields in the ASN.1-coded data.
>
> Explanation:
>
> (1) no data in the ASN.1 coded data -> save it as NULL on DB
> (2) data in ASN.1 -> save value on DB
> (3) only tags without data in ASN.1 coded data -> save as 'empty' on DB,
> but not NULL
>
> We are using SQLLDR to load data onto DB, for strings we are able to
> divide between NULL and 'empty' fields by passing ,, (for NULL ASN.1
> fields) and ,"\t", (for empty ASN.1 fields). This is a workaround
> because when unloading the data will be parsed as ASCII and blanks/tabs
> will be not read when working on unloaded data.
>
> But how to do this for NUMBERS/INTEGER? Is there a way for passing a
> special value to the SQLLDR like 'NaN' or 'inf' etc. for determining
> empty (but not NULL) fields.
>
> SQLLDR stops working when passing "" or "\t" for empty fields.
>
> What is the best way to handle this problem on DB and for SQLLDR?
> Please do not mention about an additional column for each value... ;-)
>
> Bye,
> Steffen
Oracle treats empty strings as NULLs (OK Daniel...), at least for character columns (CHAR, VARCHAR, VARCHAR2, NVARCHAR, NVARCHAR2):
SQL> create table bla (col1 varchar2(10)); Table created.
SQL> insert into bla values ('');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from bla where col1 is null;
COUNT(*)
1
SQL> select count(*) from bla where col1 is not null;
COUNT(*)
0
SQL> select nvl('','Is NULL') from dual;
NVL('','ISNULL')
SQL> select nvl(' ','Is NULL') from dual; NVL
--- SQL> -- Regards, Frank van BortelReceived on Mon May 24 2004 - 14:19:45 CDT
![]() |
![]() |