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------------------------10x89504E470D0A1A0A0000000D494844520000023B0000019B0802000000AD40C67F0.....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 BINARYORA-06550: line 6, column 1:PL/SQL: SQL Statement ignoredraw類型固定長類型還好有明確提示range 32767 下面換long rawSQL> 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 BINARYORA-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 datatypeORA-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  , to_lob(x)           from t          where id =  ' 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 程式來解決了