• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

Determine NULL vs Empty

How would i distinguish between a row containing a NCLOB column with NULL value vs zero length column?
0
xoxomos
Asked:
xoxomos
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
NULL is NULL ;-)
You have to use empty_clob to check if empty or not (size = 0), see here:  http://www.sqlines.com/oracle/functions/empty_blob
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't have a oracle db under my hands right now, but I think that LENGTH will return NULL vs 0 ...
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions076.htm
0
 
xoxomosAuthor Commented:
I see empty_blob(), empty_clob() but nowhere is empty_nclob().
For some reason, vendor made this column NCLOB.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
This is important:
http://nuijten.blogspot.de/2009/11/empty-clob-is-not-null-its-not-null.html

so: clob_column with NULL value = NULL, but clob_column with NULL value != empty
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
you can use empty_clob also for NCLOB columns, see official docs here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/lobs.htm#ADDCI4370
0
 
xoxomosAuthor Commented:
Tried duplicating http://nuijten.blogspot.de/2009/11/empty-clob-is-not-null-its-not-null.html except using NCLOB instead of CLOB.



Connected.
SQL> create table testNCLOB
  2  (columna number,
  3   somecharacters nclob
  4  );

Table created.

SQL> insert into testNCLOB values (100, null);

1 row created.

SQL> insert into testNCLOB values (200, 'neils stuff');

1 row created.

SQL> insert into testNCLOB values (300, empty_clob());

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testNCLOB where somecharacters is NULL;

   COLUMNA
----------
SOMECHARACTERS
--------------------------------------------------------------------------------
       100



SQL> select * from testNCLOB;

   COLUMNA
----------
SOMECHARACTERS
--------------------------------------------------------------------------------
       100


       200
neils stuff

       300



SQL> select 'empty' from testNCLOB where somecharacters = empty_clob();
select 'empty' from testNCLOB where somecharacters = empty_clob()
                                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NCLOB



SQL> select somecharacters from testNCLOB;

SOMECHARACTERS
--------------------------------------------------------------------------------

neils stuff


SQL>
0
 
johnsoneSenior Oracle DBACommented:
Given your sample setup, I found that this seems to work the best:

SELECT columna 
FROM   testnclob 
WHERE  dbms_lob.Getlength(somecharacters) = 0; 

Open in new window


I tried casting the EMPTY_CLOB call to NCLOB using the TO_NCLOB call like this:

SELECT columna 
FROM   testnclob 
WHERE  somecharacters = To_nclob(Empty_clob()); 

Open in new window


But that didn't seem to work either.  It seems that the SOMECHARACTERS column is being converted to some other type for the comparison.  I would stick with the GETLENGTH method.  That will give you ones that are initialized and have a 0 length, but not ones that are NULL.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
This "direct" compare just works within PL/SQL. If you need the comparison in SQL, you'll need to use DBMS_LOB, like this:
select 'empty' 
from testNCLOB 
where dbms_lob.compare(somecharacters, empty_clob() ) = 0

Open in new window


select 'not empty' 
from testNCLOB 
where dbms_lob.compare(somecharacters, empty_clob() ) = 1

Open in new window

0
 
xoxomosAuthor Commented:
OK, say i get = 0 for empty_clob.  Would I not get = 0 regardless of whether it's null or just empty (uninitialized)?
0
 
johnsoneSenior Oracle DBACommented:
If it is null, then the length returned is null.  If it is initialized and empty then the length returned is 0.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
0
 
xoxomosAuthor Commented:
Thanks all :-)
Finally got it.
SQL> select length(main_data) from course_contents where pk1 = 4100231;

LENGTH(MAIN_DATA)
-----------------
                0
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now