Press "Enter" to skip to content

Oracle PL/SQL: Check If BLOB or CLOB is Empty

In Oracle PL/SQL, to check if BLOB or CLOB is empty or not, use the dbms_lob.getlength() function or dbms_lob.compare() function. Here are the examples:

1. Using dbms_lob.getlength() Function

declare
  vblob blob;
  Cursor c_blob
  is
  select content into vblob
      from employee_docs
      where employee_id = 101;
begin
      
    open c_blob;
    fetch c_blob into vblob;
    close c_blob;
    
    /* if the vblob is empty then the length would be 0 */
    if dbms_lob.getlength(vblob) = 0 then
       raise_application_error(-20001, 'Blob is empty.');
    end if;
    -- do anything with vblob
    
end;

2. Using dbms_lob.compare() Function

declare
   vblob blob;
   Cursor c_blob
   is
   select content into vblob
      from employee_docs
      where employee_id = 101;
begin
      
    open c_blob;
    fetch c_blob into vblob;
    close c_blob;
    
    /* if vblob is equal to an empty_blob, means it is empty */
    if dbms_lob.compare(vblob, empty_blob()) = 0 then
       raise_application_error(-20001, 'Blob is empty.');
    end if;
    -- do anything with vblob
    
end;

Similarly, to check for empty CLOB, change the variable type to clob and replace the empty_blob() function with empty_clob() function in the above PL/SQL code.

Related Tutorials:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Comments are closed.