我有一个表tbl，其中包含以下各列：
create table tbl (id number,
colA BLOB,
ColB BLOB,
Insert_time timestamp(0)
)
当我在查询下面运行时，它工作正常：
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')
但是当我在下面运行时会引发错误：
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')
我的意思是说，如果我仅选择1个blob列来读取，则日期过滤器可以正常工作。但是，当我在where子句中选择两个带日期过滤器的BLOB列时，它将引发以下错误：
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.
这种简单的方式（作为例外建议）表示该列ColLB中的减压值无效。
测试显示这很可能是带有NULL值的列-请参见下面的脚本。
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
请注意，如果您存储了一个NULL已损坏的非值（即未采用压缩格式），则会看到另一个异常
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
因此，作为一种解决方法，您应该使用CASE针对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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句