Reading BLOB column in oracle table

I have a table tbl which has below columns:

create table tbl (id number,
                  colA BLOB,
                  ColB BLOB,
                  Insert_time timestamp(0)
                  )

When I run below query it works fine:

select id,
substr(substr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.colA), 2000, 1) ),instr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColA), 2000, 1) ),'MeterReadingReasonCode',1),25),24,2) first
--substr(substr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColB), 2000, 1) ),instr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColB), 2000, 1) ),'MeterReadingTypeCode',1),23),22,2) second
from tbl 
where
tbl.INSERT_TIME >=  To_Date('04-01-2020 12:00:00 AM' ,'dd-mm-yyyy hh12:mi:ss AM')
and tbl.INSERT_TIME <=  To_Date('04-08-2020 11:00:00 PM' ,'dd-mm-yyyy hh12:mi:ss AM')

But when I run below it throws error:

select id,
substr(substr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.colA), 2000, 1) ),instr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColA), 2000, 1) ),'MeterReadingReasonCode',1),25),24,2) first
substr(substr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColB), 2000, 1) ),instr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColB), 2000, 1) ),'MeterReadingTypeCode',1),23),22,2) second
from tbl 
where
tbl.INSERT_TIME >=  To_Date('04-01-2020 12:00:00 AM' ,'dd-mm-yyyy hh12:mi:ss AM')
and tbl.INSERT_TIME <=  To_Date('04-08-2020 11:00:00 PM' ,'dd-mm-yyyy hh12:mi:ss AM')

I mean to say, if I select only 1 blob column to read , then date filter works fine. But when I select both BLOB columns with date filter in where clause, it throws below error:

ORA-29261: bad argument
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 60
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 230
ORA-06512: at "SYS.UTL_COMPRESS", line 89
29261. 00000 -  "bad argument"
*Cause:    A bad argument was passed to the PL/SQL API.
*Action:   Check the arguments passed to the PL/SQL API and retry the call.

Answer

This simple means (as the exception suggest) that in the column ColLB contains not a valid value for the decompression.

The test shows that is is most probably a column with a NULL value – see script below.

create table tbl (ColB BLOB);
                  
insert into tbl ( colb)
values(null);

select  
   utl_compress.lz_uncompress(tbl.ColB)
from tbl  
;

ORA-29261: bad argument
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 56
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 226
ORA-06512: at "SYS.UTL_COMPRESS", line 89

Note that if you have stored a non NULL value that is corrupted (i.e. not in a compressed format) you would see a different exception

insert into tbl ( colb)
values(HEXTORAW('cafe'));

select  
   utl_compress.lz_uncompress(tbl.ColB)
from tbl  
;

ORA-29294: A data error occurred during compression or uncompression.
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 56
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 226
ORA-06512: at "SYS.UTL_COMPRESS", line 89

So as a workaround you should protect your expression with a CASE statment testing for NULL

select 
  case when colB is not NULL then 
    substr(substr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColB), 2000, 1) ),instr(utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(tbl.ColB), 2000, 1) ),'MeterReadingTypeCode',1),23),22,2) 
  end as  second
from tbl