gateway dblink transport mssql image datatype to oracle blob datatype

在异构RDBMS之间传送数据，有时会面临数据类型的转换问题，必要时要用到第三方，比如SQL SERVER中的image 二进制图像类型，转到oracle 对应blob,sybase system 10 还是 image(因为她们有血缘关系)
下面我想演示一下，用oracle 的透明网关来同步mssql的image 数据类型，刚好这也是昨天网友问的一问题
首先在mssql insert image类型，本次是导入一个png图片

create database zhang;
use zhang;
create table imgtest(id int,img image);
insert into imgtest(id,photo)select 1,* from Openrowset(bulk N'D:\\t1\\1.png',single_blob) as img
--这是sql server 2005后的简单方法，mssql 2000方法不在讨论范围内
select * from imgtest
------------------------
1	0x89504E470D0A1A0A0000000D494844520000023B0000019B0802000000AD40C67F0.....
select datalength(img) from imgtest
-------------------
184946
复制

下面开始在oracle 端建立透明网关，（创建方法本站也有请在右上角搜索以前的blog）

利用to_log函数，限制很多，有兴趣可以查看yangtingkun的blog,
--to_lob 只能用在CTAS,和 insert select 中
SQL> l
  1  declare
  2  v_id int;
  3  v_img raw;
  4  begin
  5  select "id","img" into v_id,v_img from imgtest@lnk_mssql;
  6  insert into anbob.imgtest values(v_id,to_lob(v_img));
  7  commit;
  8  dbms_output.put_line('ok!');
  9* end;
SQL> /
v_img raw;
      *
ERROR at line 3:
ORA-06550: line 3, column 7:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 6, column 39:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got BINARY
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
raw类型固定长类型还好有明确提示range 32767 下面换long raw
SQL> l
  1  declare
  2  v_id int;
  3  v_img long raw;
  4  begin
  5  select "id","img" into v_id,v_img from imgtest@lnk_mssql;
  6  insert into anbob.imgtest values(v_id,to_lob(v_img));
  7  commit;
  8  dbms_output.put_line('ok!');
  9* end;
SQL> /
insert into anbob.imgtest values(v_id,to_lob(v_img));
                                      *
ERROR at line 6:
ORA-06550: line 6, column 39:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got BINARY
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
用insert values 看来是失败的
换insert select 试试
SQL> l
  1  declare
  2  v_id int := 1;
  3  begin
  4  execute immediate 'insert into anbob.imgtest
  5                select "id",to_lob("img")
  6                 from imgtest@lnk_mssql
  7                   where "id"=:id' using v_id;
  8  --commit;
  9  dbms_output.put_line('ok!');
 10* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
ORA-06512: at line 4
只在ORACLE中执行测试一下
create table gtt ( id int, x blob );
create table t ( id int primary key, x long raw );
insert into t values( 1, rpad( 'a', 2000, 'a' ) );
declare
         l_id number := 1;
         l_blob blob;
 begin
         execute immediate '
         insert into gtt
         select :x, to_lob(x)
           from t
          where id = :x' using l_id, l_id;
 end;
PL/SQL procedure successfully completed.
SQL> select dbms_lob.getlength(x) from gtt;
DBMS_LOB.GETLENGTH(X)
---------------------
         1000
复制

--tom--
use INSERT INTO select ....
do not fetch out, put into plsql only to send back.
insert into select
no plsql, just sql.
to_lob is not implemented by mssql - it would be the thing that would have to convert the long raw into a lob.
long raws are not going to be dblink friendly at all. Your best bet will be to write an external application that selects from mssql, inserts into Oracle.
Or dump the data out into a flat file and load that.

那我们可以换一种思路，可以先通过透明网关insert到一个long raw 本地表中，再insert into to_lob到blob字段中(先不考虑长度问题)
 declare
 v_img long raw;
 begin
 select "img" into v_img from imgtest@lnk_mssql;
 insert into t values (1,v_img);
 dbms_output.put_line('ok!');
 end;
 ok!
PL/SQL procedure successfully completed.
SQL> insert into gtt select id ,to_lob(x) from t;
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_lob.getlength(x) from gtt;
DBMS_LOB.GETLENGTH(X)
---------------------
                32760
复制

然后我把blob这部分打开，哇，只有上半张图片（存进去的是png格式），原文件为180K,现在为32k,为什么呢？
原因是long raw 在做为 columns datatype是max size 2G,而在PL/SQL中最大是32767 bytes，lob 类型在dblink不无传输而long可以，lob在10G及sqlplus 中无法显示，在11G做了增强也可以显示，所以如果单个图片大于32K，就可以考虑用java或其它OCI 程序来解决了