If you ever have to start working with Oracle you have to keep in mind that NULLs and blank values don’t work exactly the same as in SQL Server. Let’s take a look at some examples

Create this table and insert some rows

create table TestNull(Col2 varchar(100));
insert into TestNull values(NULL);
insert into TestNull values('Bla');
insert into TestNull values('');
insert into TestNull values(' ');

As you can see we inserted four rows, one row is null, one row is blank, one row has a space and one row has Bla.

Now let’s run the following query

SELECT Col2,
  NVL(Col2,'EmptyOrNull') a,
  COALESCE(Col2,'EmptyOrNull') b,
  ascii(col2) c
FROM TestNull;

Here are the results in a html table

Here is an image of the same results

See what happened, Oracle changed the blanks to NULLs.

We can easily test this theory, let’s create a table with a column that has a not null constraint

create table TestNull2(Col2 varchar(100) not null);

Now of course if you try to insert a NULL it will blow up

insert into TestNull2 values(NULL);

Here is the error

_SQL Error: ORA-01400: cannot insert NULL into (“SYSTEM”.“TESTNULL2″.“COL2″)

1. 00000 – “cannot insert NULL into (%s)”_

Inserting Bla works without a problem

insert into TestNull2 values('Bla');

What about a blank, what will happen now?

insert into TestNull2 values('');

_SQL Error: ORA-01400: cannot insert NULL into (“SYSTEM”.“TESTNULL2″.“COL2″)

1. 00000 – “cannot insert NULL into (%s)”_

As you can see the blank gets converted to a NULL and you get the same error. This is very different from SQL Server.

Will a space succeed?

insert into TestNull2 values(' ');

A space is no problem.

Coalesce differences

Just be aware that coalesce won’t work the same either. Oracle doesn’t have isnull but it has the nvl function instead

Run the following two statements

select nvl('','No') as a
from dual;

select coalesce('','No') as a
from dual;

In both cases you are getting No back from the function, as you can see a blank is treated as null.

Be aware of these differences between Oracle and SQL Server, you could have some strange results back from queries if you assume it works the same

We will take a look at that strange table dual in another post