Solved

Determine NULL vs Empty

Posted on 2014-01-08
12
545 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
Independent Software Vendors: 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 57
plsql job on oracle 18 104
Oracle Errors 11 79
Convert summed columns to Rows 6 16
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

740 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