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.