利用ORACLE JAVA存储过程实现BLOB图片导出

最新推荐文章于 2022-01-11 16:55:18 发布

fengwen2003

最新推荐文章于 2022-01-11 16:55:18 发布

阅读量403

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE 文章标签： Java Oracle SQL SQL Server QQ

本文链接：https://blog.youkuaiyun.com/fengwen2003/article/details/83534302

本文介绍了如何在Oracle 9i中解决使用DBMS_LOB和UTL_FILE导出BLOB图片时遇到的问题。由于9i的限制，只能通过编写Java存储过程利用强大的IO操作来替代PL/SQL包。通过创建Java程序并在数据库中编译，实现了从BLOB字段正确导出图片的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前几天有朋友让解决个问题:一个XX收单系统,需要的交易情况统计来自另外一套系统,以图片方式进行存储和导出查看.图

片以BLOB类型直接存在数据库中.本来以为就是个JAVA操作LOB的问题,但是因为是2次开发,所以必须遵循以前的规定,即所

有业务由存储过程实现.他们的选择是用DBMS_LOB和UTL_FILE包,导入图片到数据库没问题,但是在导出时始终不能正确

显示,发现导出后文件的大小与导入的图片不一致.

其实这又是9i的一个老问题了(为什么我又要说又呢),在Oracle DBA Tips Corner 中说明如下:

It should be noted that in Oracle9i this PL/SQL procedure does not work with all binary files. This is due to Oracle bug (BUG#: 2883782). The PL/SQL procedure that I use to write binary (raw) data out is UTL_FILE.PUT_RAW. This procedure, along with UTL_FILE.GET_RAW, was introduced in Oracle 9i Release 2 as previous versions of UTL_FILE only worked with TEXT files.

In Oracle9i there is currently a restriction of a maximum of 32k that can be written with PUT_RAW unless you insert new line characters in between the data. In Oracle10g there is a new binary mode. When files are opened with this mode, "wb", any amount of raw data can be written without the need for new lines. In short, this is a bug that can bite you if your binary files do not have a new line character within the RAW data in your MAX_LINESIZE buffer. If you do get bit by this bug in Oracle9i, there is no sol