Solved

Determine NULL vs Empty

Posted on 2014-01-08
12
517 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
  • 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 142

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now