goldengate(5)--对大对象支持(LOB),insert、update、delete操作.
1.ogg支持大对象BLOB,CLOB,NCLOB
2.创建包含LOB字段的表test.xy108
create table test.xy108 (name varchar2(10),sex char(2), picture blob,text clob,description varchar2(200));
alter table test.xy108 add constraint pk_name primary key (name);
表结构如下:
-- Create table
create table TEST.XY108
(
NAME VARCHAR2(10) not null,
SEX CHAR(2),
PICTURE BLOB,
TEXT CLOB,
DESCRIPTION VARCHAR2(200)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST.XY108
add constraint PK_NAME primary key (NAME)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
源端操作:
3.在操作系统上创建临时存放blob大对象的图片文件的目录
SQL> create or replace directory BLOBDIR as '/tmp/lob'; (注意:/tmp/lob该目录必须存在)
Directory created.
4.授予权限访问该目录
SQL> grant read on directory BLOBDIR to test;
Grant succeeded.
5.创建存储过程
create or replace procedure test.db_blob(pname varchar2, pdesc varchar2) is
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('BLOBDIR', pname);
insert into test.xy108
(name, description, picture)
values
(pname, pdesc, empty_blob())
returning picture into dst_file;
select picture into dst_file from test.xy108 where name = pname 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 test.xy108 set picture = dst_file where name = pname;
dbms_lob.fileclose(src_file);
commit;
end db_blob;
6.执行存储过程db_blob
begin
test.db_blob('01.jpg','樱花');
end;
/
7.查看加载到数据库的blob图片文件
SQL> select name,description,picture,dbms_lob.getlength(picture) siz from test.xy108;
NAME DESCRIPTION PICTURE SIZ
---------- -------------------------------------------------------------------------------- ------- ----------
01.jpg 樱花 2872999
已经成功加载到数据库.
blob图片 的大小为2.73MB
SQL> select name,description,picture,dbms_lob.getlength(picture) siz from test.xy108;
NAME DESCRIPTION PICTURE SIZ
---------- -------------------------------------------------------------------------------- ------- ----------
01.jpg ó£?¨ 2872999
DESCRIPTION 字段乱码是因为源端与目标端的字符集不一致造成.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21266384/viewspace-756095/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21266384/viewspace-756095/