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

图片.jpg

复制到目标端的图片.jpg