Member Avatar for sandy_b76

Hi,

How to check if BLOB field is NULL or Not in a Table though other data is available in a selected row ? I need to do this in a sql statement.

Thanks
Sandy

  • 2 Contributors
  • 3 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by sandy_b76

Dani AI

Generated

Good question, and nice progress by . One subtlety to keep in mind is that Oracle distinguishes between a NULL LOB locator and an initialized-but-empty LOB created with EMPTY_BLOB(). Those two cases behave differently in SQL and should be tested separately. In short: NULL means no locator at all; EMPTY_BLOB() means a zero-length, non-NULL value. See Oracle’s SQL Reference on EMPTY_BLOB/EMPTY_CLOB.

If you want a pure-SQL check that categorizes each row, this pattern avoids surprises and matches what hinted at with DBMS_LOB:

SELECT
  CASE
    WHEN blob_col IS NULL THEN 'NULL'
    WHEN DBMS_LOB.GETLENGTH(blob_col) = 0 THEN 'EMPTY'
    ELSE 'HAS DATA'
  END AS blob_state
FROM your_table
WHERE <your_condition>;

DBMS_LOB.GETLENGTH returns NULL when the LOB is NULL and 0 when the internal LOB is empty, so the CASE above covers all three states cleanly. DBMS_LOB.GETLENGTH docs. For a quick filter to find rows that actually contain bytes, you can simply use WHERE DBMS_LOB.GETLENGTH(blob_col) > 0.

Two extra gotchas:

  • Use IS NULL / IS NOT NULL to test nullness; equality comparisons with NULL do not work as expected. Oracle nulls documentation.
  • Avoid LENGTH(blob_col) for BLOBs. LENGTH is defined for character datatypes (and CLOB/NCLOB), not for BLOB; for binary LOBs, stick with DBMS_LOB.GETLENGTH. LENGTH function reference.

Recommended Answers

All 3 Replies

Member Avatar for sandy_b76

I solved this problem with following.

SELECT nvl(length(BLOB_Field),0) INTO variable
FROM <<Table_Name>>
WHERE <<Condition>>

IF variable > 0 THEN
strMessage := 'BLOB Field has data';
ELSE
strMessage := 'BLOB Field is empty';
END IF;


Hope This will help others.


Thanks
Sandy

Member Avatar for debasisdas

try using the predefined package dbms_lob to get some info regarding that.

Member Avatar for sandy_b76

Thanks a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.