Solved

Determine NULL vs Empty

Posted on 2014-01-08
12
540 Views
Last Modified: 2014-01-08
How would i distinguish between a row containing a NCLOB column with NULL value vs zero length column?
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
ID: 39766325
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 39766337
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
 

Author Comment

by:xoxomos
ID: 39766361
I see empty_blob(), empty_clob() but nowhere is empty_nclob().
For some reason, vendor made this column NCLOB.
0
Industry Leaders: 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!

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39766373
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39766402
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
 

Author Comment

by:xoxomos
ID: 39766414
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 39766529
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39766531
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
 

Author Comment

by:xoxomos
ID: 39766703
OK, say i get = 0 for empty_clob.  Would I not get = 0 regardless of whether it's null or just empty (uninitialized)?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39766750
If it is null, then the length returned is null.  If it is initialized and empty then the length returned is 0.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39766761
0
 

Author Closing Comment

by:xoxomos
ID: 39766822
Thanks all :-)
Finally got it.
SQL> select length(main_data) from course_contents where pk1 = 4100231;

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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 57
Pivoting oracle table 9 72
PL/SQl Expanding the WHERE statement in query 3 33
Oracle SQL Developer - SubString 2 23
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question