1. BLOB字段模糊查询报错

blob字段直接用 select * from table_name where column like ‘%%’查找的时候是不能实现的 ,主要是字段类型不符。不过我们可以用数据库自带的 utl_raw 函数进行 blob 字段的插入，查询和模糊匹配。

先介绍一下Oracle中RAW和 varchar2常用的两个转换函数

2. UTL_RAW.CAST_TO_RAW

该函数按照缺省字符集，将VARCHAR2字符串转换为RAW。

sys@ORCL>select utl_raw.cast_to_raw('shall') raw1,utl_raw.cast_to_raw('zhong') raw2 from dual;

RAW1 RAW2

-------------------- --------------------

7368616C6C 7A686F6E67

也可以用 rawtohex 函数实现：

sys@ORCL>select rawtohex('shall') raw1,rawtohex('zhong') raw2 from dual;

RAW1 RAW2

-------------------- --------------------

7368616C6C 7A686F6E67

3. UTL_RAW.CAST_TO_VARCHAR2

该函数按照缺省字符集合，将RAW转换为VARCHAR2。

sys@ORCL>select utl_raw.cast_to_varchar2('7368616C6C') var1,utl_raw.cast_to_varchar2('7A686F6E67') var2 from dual;

VAR1 VAR2

---------- ----------

shall zhong

其实RAW和VARCHAR是类似的,只是存储在RAW里的是二进制值,在任何时候不会做自动的字符集转换,这是RAW和VARCHAR的不同,RAW只是一种外部类型,其内部存储是VARRAW。

4. 实验：

----创建表

SQL> create table blob_test(id int,content blob);

Table created

----插入数据

sys@ORCL>insert into blob_test values(1,'shall zhong');

insert into blob_test values(1,'shall zhong')

*

ERROR at line 1:

ORA-01465: invalid hex number

由报错可以看出，无法直接往 blob字段中插入数据，下面用 UTL_RAW.CAST_TO_RAW 和 ,RAWTOHEX 函数转换一下再插入。

SQL> insert into blob_test values(1,UTL_RAW.CAST_TO_RAW('shall zhong'));

1 row inserted

SQL> insert into blob_test values(1,UTL_RAW.CAST_TO_RAW('这里是BLOB字段，数据zhong'));

1 row inserted

SQL> select * from blob_test;

ID CONTENT

---------- ------------------------------------------------------------

1 7368616C6C207A686F6E67

1 D5E2C0EFCAC7424C4F42D7D6B6CEA3ACCAFDBEDD7A686F6E67

----可以看到用两个函数转换，都可以插入成功，并且可以直接查询出来，但插入到数据库里面的结果为 16进制数据（注：11g 的数据库可以直接查出来， 10g 的无法直接查询 blob字段数据）。

----那么我们能直接往表中以 16进制的方式插入数据吗？

SQL> insert into blob_test values(2,'D5E2C0EFCAC7424C4F42D7D6B6CEA3ACCAFDBEDD7A686F6E67');

1 row inserted

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test;

ID VAR1

----------------- ---------

1 shall zhong

1 这里是BLOB字段，数据zhong

2 这里是BLOB字段，数据zhong

----可以看出是可以直接往数据库里插入16进制数据的，并且我们可以看到用两种方式插入的结果是一致的。

----下面是对 blob 字段的修改操作

SQL> update blob_test set content=rawtohex('当前数据库环境为11G') where id =1;

2 rows updated

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test;

ID VAR1

----------- ---------------------------------------

1 当前数据库环境为11G

2 这里是BLOB字段，数据zhong

SQL> update blob_test set content='7368616C6C207A686F6E67' where id =2;

1 row updated

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test;

ID VAR1

------------------- -----------

1 当前数据库环境为11G

2 shall zhong

----对 blob 字段的模糊查询。

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where content like '%shall%';

select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where content like '%shall%'

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where content like ' 7368616C6C207A686F6E67';

select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where content like ' 7368616C6C207A686F6E67'

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

----从上面的两个命令和报错可以看出，无法对 blob 字段进行模糊查询，即使用 16进制数据去匹配也不行，不过我们可以通过下面的方法实现对 blob 字段进行模糊匹配。

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where dbms_lob.instr(content,utl_raw.cast_to_raw('shall'),1,1) > 0;

ID VAR1

--------------------------------------- -----------

2 shall zhong

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where dbms_lob.instr(content,utl_raw.cast_to_raw('G'),1,1) > 0;

ID VAR1

------------------------------ -----------

1 当前数据库环境为11G

refencen：

http://blog.sina.com.cn/s/blog_ad6555610102v9q1.html

http://blog.csdn.net/springk/article/details/6866248

http://www.cnblogs.com/hellofei/archive/2010/03/25/1695297.html

http://www.cnblogs.com/hellofei/archive/2010/03/25/1696091.html