.NET Core NuGet程序包:Oracle.ManagedDataAccess.Core 查询Oracle BLOB字段报错:TTC Error解决方案
报错截图如下:
报错语句如下,其中PHOTO为BLOB字段:
select PHOTO from employee_photo
此类语句在.NET FrameWork下并无异常,但用.NET Core NuGet程序包:Oracle.ManagedDataAccess.Core访问Oracle数据库时爆发:TTC Error 异常
后查找资料,官方并无解决方案,最后在:https://cloud.tencent.com/developer/ask/202816 找到相关答案
修改后SQL语句如下
WITH
INFO
AS
(
SELECT
dbms_lob.getlength(A.PHOTO) AS FILE_CONTENT_LENGTH,
MOD(dbms_lob.getlength(A.PHOTO),2000) AS MOD,
CASE
WHEN MOD(dbms_lob.getlength(A.PHOTO),2000) > 0 THEN TRUNC((dbms_lob.getlength(A.PHOTO)/2000) + 1)
ELSE TRUNC(dbms_lob.getlength(A.PHOTO)/2000)
END INTERATION_COUNT,
a.PHOTO as FILE_CONTENT
FROM employee_photo A WHERE A.employee_no = '123456'
)
,OFFSETS AS
(
SELECT
(2000 * (ROWNUM-1)) + 1 AS OFFSET,
I.MOD,
I.FILE_CONTENT_LENGTH,
I.INTERATION_COUNT,
i.FILE_CONTENT
FROM INFO I
CONNECT BY LEVEL <= I.INTERATION_COUNT
)
,RESULT AS
(
SELECT
DBMS_LOB.SUBSTR(O.FILE_CONTENT, 2000, O.OFFSET) AS CONTENT,
O.OFFSET,
O.MOD,
O.FILE_CONTENT_LENGTH,
O.INTERATION_COUNT
FROM OFFSETS O
)
SELECT * FROM RESULT R ORDER BY R.OFFSET ASC
查询结果会把BLOB字段拆分为多行,每行2000个字节,程序按字段 OFFSET 顺序拼接起来即可