注册

使用存储过程UPDATE操作BLOB字段,调用dbms_lob包报错(Oracle可以,请教DM8实现方式)

阿彬 2024/08/28 484 1

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:银河麒麟v10(基本等同于CentOS 7)
【CPU】:Hygon Dhyana Processor
【问题描述】*:在Oracle数据库11g中,执行如下操作可以成功,在DM8数据库中执行报错 第 1 行, 第 49 列[,]附近出现错误[-2007]:,请指导适配。 目的为,通过存储过程,向含有BLOB字段的表,更新BLOB字段值为.png文件。
(表述不清楚的地方,请指出,我随时补充说明)
【一】在Oracle中的执行情况
SQL> create or replace directory BLOBDIR as '/home/oracle/';

Directory created.

SQL> CREATE TABLE eygle_blob (fid number,fname varchar2(50),fdesc varchar2(200),fpic BLOB);

Table created.

SQL> create sequence S_EYGLE_SEQ start with 1 increment by 1;

Sequence created.

SQL> CREATE OR REPLACE PROCEDURE eygle_load_blob(pfname VARCHAR2,pdesc varchar2)
2 IS
3 src_file BFILE;
4 dst_file BLOB;
5 lgh_file BINARY_INTEGER;
6 BEGIN
7 src_file := bfilename('BLOBDIR', pfname);
8
9 INSERT INTO eygle_blob (fid,fname,fdesc,fpic)
10 VALUES (S_EYGLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
11 RETURNING fpic INTO dst_file;
12
13 SELECT fpic INTO dst_file
14 FROM eygle_blob WHERE fname = pfname FOR UPDATE;
15
16 dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
17 lgh_file := dbms_lob.getlength(src_file);
18 dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
19
20 UPDATE eygle_blob SET fpic = dst_file
21 WHERE fname = pfname;
22
23 dbms_lob.fileclose(src_file);
24 commit;
25 END eygle_load_blob;
26 /

Procedure created.

SQL> exec eygle_load_blob('123.png','ksjfd');

PL/SQL procedure successfully completed.

SQL>
image.png
【二】在DM8中执行情况
1.创建directory并授权
create or replace directory BLOBDIR as '/home/dmdba/dmdbms/bin/';
grant read on directory BLOBDIR to SYSDBA;
2.创建测试表
CREATE TABLE SYSDBA.eygle_blob(fid number,fname varchar2(50),fdesc varchar2(200),fpic BLOB);

create sequence S_EYGLE_SEQ start with 1 increment by 1;
3.创建存储过程
CREATE OR REPLACE PROCEDURE eygle_load_blob(pfname VARCHAR2,pdesc varchar2)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('BLOBDIR', pfname);

INSERT INTO eygle_blob (fid,fname,fdesc,fpic)
VALUES (S_EYGLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
RETURNING fpic INTO dst_file;

SELECT fpic INTO dst_file
FROM eygle_blob WHERE fname = pfname FOR UPDATE;

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

UPDATE eygle_blob SET fpic = dst_file
WHERE fname = pfname;

dbms_lob.fileclose(src_file);
commit;
END eygle_load_blob;
/

col segment_name for a30;
select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='SYSDBA';

行号 SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024


9 TESTBLOB TABLE 0

4.加载Blob对象
exec eygle_load_blob('PIC.png','ASDFG');

image.png

回答 0
暂无回答
扫一扫
联系客服