--------------20140220------------------

--------------oracle与sqlserver数据对大对象的解析区别

第一步：构建测试数据，这里使用现成公司数据，数据通过ODBC导入，个别数据会进行相关处理，但并不会影响结果

测试表分别为：EMR_BL_BL01/EMR_BL_BLXG/EMR_KBM_BLLB/GY_KSDM/GY_YGDM,其中EMR_BL_BLXG的栏位

HJNR为BLOB类型，为测试对象

第二步：oracle解析，操作大对象需要使用到oracle dbms_lob.sub大对象截取/dbms_lob.instr大对象定位(这个跟instr函数有点小区别)

转换函数utl_raw.cast_to_raw及utl_raw.cast_to_varchar2.

a)创建测试视图jeevan_test_01供后期使用：

create view jeevan_test_01 as

select * from (

select a.BLBH,a.JZHM,a.BRBH,a.BLLX,a.BLLB,a.BLMC,a.DLLB,

a.MBLB,a.MBBH,a.ZXSJ,a.CJSJ,a.WCSJ,a.SXYS,

(SELECT YGXM FROM GY_YGDM where YGDM=a.SXYS) SXYSXM,a.BRKS,

(SELECT KSMC FROM GY_KSDM where KSDM=a.BRKS) BRKSMC,

a.CJKS, (SELECT KSMC FROM GY_KSDM where KSDM=a.CJKS) CJKSMC,

a.BLZT,a.BRXM,a.BRZD,a.SSYS,

(SELECT YGXM FROM GY_YGDM where YGBH=a.SSYS) SSYSXM,

a.SYBZ,a.BZMBBH,a.BLYM,

b.JLXH,b.XGGH,

(SELECT YGXM FROM GY_YGDM where YGBH=b.XGGH) XGGHXM

,b.XGSJ,b.HJNR

from

(select * from EMR_BL_BL01 where MBLB=20) a

left join

EMR_BL_BLXG b

on a.BLBH=b.BLBH

) t;

b)解析脚本：

select

BLBH

,JZHM

,BRBH

,BLLX

,BLLB

,BLMC

,DLLB

,MBLB

,MBBH

,ZXSJ

,CJSJ

,WCSJ

,SXYS

,SXYSXM

,BRKS

,BRKSMC

,CJKS

,CJKSMC

,BLZT

,BRXM

,BRZD

,SSYS

,SSYSXM

,SYBZ

,BZMBBH

,BLYM

,JLXH

,XGGH

,XGGHXM

,XGSJ

,

-------------我是分隔符------------

case when dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院日期'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院日期：'))>0 then

replace(utl_raw.cast_to_varchar2(dbms_lob.substr(HJNR,

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院日期'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院日期：')),

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院日期：')))),

'入院日期：'

)

else

''

end 入院日期,

-------------我是分隔符------------

case when dbms_lob.instr(HJNR,utl_raw.cast_to_raw('住院总计'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院日期：'))>0 then

replace(utl_raw.cast_to_varchar2(dbms_lob.substr(HJNR,

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('住院总计'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院日期：')),

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院日期：')))),

'出院日期：'

)

else

''

end 出院日期,

-------------我是分隔符------------

case when dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院诊断'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院情况：'))>0 then

replace(utl_raw.cast_to_varchar2(dbms_lob.substr(HJNR,

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院诊断'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院情况：')),

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院情况：')))),

'入院情况：'

)

else

''

end 入院情况,

-------------我是分隔符------------

case when dbms_lob.instr(HJNR,utl_raw.cast_to_raw('治疗经过'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院诊断：'))>0 then

replace(utl_raw.cast_to_varchar2(dbms_lob.substr(HJNR,

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('治疗经过'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院诊断：')),

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('入院诊断：')))),

'入院诊断：'

)

else

''

end 入院诊断,

-------------我是分隔符------------

case when dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院时情况'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('治疗经过：'))>0 then

replace(utl_raw.cast_to_varchar2(dbms_lob.substr(HJNR,

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('出院时情况'))-dbms_lob.instr(HJNR,utl_raw.cast_to_raw('治疗经过：')),

dbms_lob.instr(HJNR,utl_raw.cast_to_raw('治疗经过：')))),

'治疗经过：'

)

else

''

end 治疗经过

from jeevan_test_01

;

c)测试截图：

全部执行抛出错误

错误代码：ORA-06502 PL/SQL数字或值错误，原始变量长度太大 ORA-06512：在line 1

查阅了相关资料发现是大对象操作函数dbms_log.substr()只能截取2000长度的数据，

我需要的结果集中出现了超过2000以上的。所以抛出以上错误。在网上找了很久也没找到解决方案

最后被迫转换测试环境，在sqlserver上处理。

sqlserver 2005后的版本有支持到varchar(max),这个支持文本到2G，能满足一些超过4000个字节的

业务。oracle为什么这个不跟随sqlserver的节奏也支持大一些呢？

-------------------下面出现的都为sqlserver数据库代码

创建数据：建一个视图供后期分析使用

create view jeevan_test_02 as

select * from (

select a.BLBH,a.JZHM,a.BRBH,a.BLLX,a.BLLB,a.BLMC,a.DLLB,

a.MBLB,a.MBBH,a.ZXSJ,a.CJSJ,a.WCSJ,a.SXYS,

(SELECT YGXM FROM [GY_YGDM] where YGDM=a.SXYS) SXYSXM,a.BRKS,

(SELECT KSMC FROM [GY_KSDM] where KSDM=a.BRKS) BRKSMC,

a.CJKS, (SELECT KSMC FROM [GY_KSDM] where KSDM=a.CJKS) CJKSMC,

a.BLZT,a.BRXM,a.BRZD,a.SSYS,

(SELECT YGXM FROM [GY_YGDM] where YGBH=a.SSYS) SSYSXM,

a.SYBZ,a.BZMBBH,a.BLYM,

b.JLXH,b.XGGH,

(SELECT YGXM FROM [GY_YGDM] where YGBH=b.XGGH) XGGHXM

,b.XGSJ,b.HJNR

from

(select * from EMR_BL_BL01 where MBLB=51 ) a

left join

EMR_BL_BLXG_TMP b

on a.BLBH=b.BLBH

--住院患者疾病诊断证明

) t-- where t.HJNR not like '%出(转)院记录%'

GO

sqlserver解析数据脚本，sqlserver封装的比较好，易用性方便性很明显，先接触sqlserver后接触oracle的

程序员都会大骂oracle不人性化，但依然改变不了他是数据库老大的地位，关键是sqlserver它不支持linux操作系统

oracle就不一样了，它基本上能在所有市面上能见到的操作系统上运行。题外话了

sqlserver操作大对象跟常规对象都使用一样的函数 patindex类似oracle中的instr函数，不过patindex函数只能定位

第一次出现的地址，但它支持通配符。instr可以定位第一次乃至第n次出现的地址，不能使用通配符。

substring函数截取函数，类似oracle中的dbms_lob.substr与substr函数。cast及convert函数，

类似oracle中的to_number/to_date/to_char/utl_raw.cast_to_raw/utl_raw.cast_to_varchar2函数，

这点上也能体现出sqlserver封装的可用性。解析代码如下：

select

[BLBH]

,[JZHM]

,[BRBH]

,[BLLX]

,[BLLB]

,[BLMC]

,[DLLB]

,[MBLB]

,[MBBH]

,[ZXSJ]

,[CJSJ]

,[WCSJ]

,[SXYS]

,[SXYSXM]

,[BRKS]

,[BRKSMC]

,[CJKS]

,[CJKSMC]

,[BLZT]

,[BRXM]

,[BRZD]

,[SSYS]

,[SSYSXM]

,[SYBZ]

,[BZMBBH]

,[BLYM]

,[JLXH]

,[XGGH]

,[XGGHXM]

,[XGSJ]

,

-------------我是分隔符------------

case when patindex('%出院日期%',HJNR)-patindex('%入院日期：%',HJNR)>0 then

replace(substring(HJNR,

patindex('%入院日期：%',HJNR),

patindex('%出院日期%',HJNR)-patindex('%入院日期：%',HJNR)),

'入院日期：',''

)

else

''

end 入院日期,

-------------我是分隔符------------

case when patindex('%住院总计%',HJNR)-patindex('%出院日期：%',HJNR)>0 then

replace(substring(HJNR,

patindex('%出院日期：%',HJNR),

patindex('%住院总计%',HJNR)-patindex('%出院日期：%',HJNR)),

'出院日期：',''

)

else

''

end 出院日期,

-------------我是分隔符------------

case when patindex('%入院诊断%',HJNR)-patindex('%入院情况：%',HJNR)>0 then

replace(substring(HJNR,

patindex('%入院情况：%',HJNR),

patindex('%入院诊断%',HJNR)-patindex('%入院情况：%',HJNR)),

'入院情况：',''

)

else

''

end 入院情况,

-------------我是分隔符------------

case when patindex('%治疗经过：%',HJNR)-patindex('%入院诊断：%',HJNR)>0 then

replace(substring(HJNR,

patindex('%入院诊断：%',HJNR),

patindex('%治疗经过：%',HJNR)-patindex('%入院诊断：%',HJNR)),

'入院诊断：',''

)

else

''

end 入院诊断,

-------------我是分隔符------------

case when patindex('%出院时情况%',HJNR)-patindex('%治疗经过：%',HJNR)>0 then

replace(substring(HJNR,

patindex('%治疗经过：%',HJNR),

patindex('%出院时情况%',HJNR)-patindex('%治疗经过：%',HJNR)),

'治疗经过：',''

)

else

''

end 治疗经过

from jeevan_test_02

GO

这样通过转换平台解决掉了这个问题，也不晓得后期oracle是否会支持到更大的文本，

oracle的做法应该是考虑到性能上的问题，文本太大了会影响数据库的执行效率。

可能是oracle公司基于效率及业务实际情况最后确定4000作为文本的临界点。

ITPub博客

oracle与sqlserver数据对大对象的解析 ORA-06502