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.
