Gateway dblink transport MSSQL image ype to Oracle blob datatype

when data is transferred between heterogeneous RDBMS systems, data type conversion is sometimes required, for example, if the Image Binary Image Type in SQL Server is converted to the Blob corresponding to Oracle, Sybase system 10 or image (because of their kinship)
I want to demonstrate how to use the transparent gateway of Oracle to synchronize the image data type of MSSQL, this is also a question asked by netizens yesterday.

First, in the MSSQL insert image type, this is to import a PNG Image

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 -- this is a simple method after SQL Server 2005. The MSSQL 2000 method is not in the scope of the discussion. Select * From imgtest -------------------- explain ..... select datalength (IMG) from imgtest ------------------- 184946

The following describes how to create a transparent gateway on the Oracle end. (You can also search for the previous blog in the upper right corner.)

The to_log function has many restrictions. If you are interested, you can view the yangtingkun blog. -- to_lob can only be used in CTAs, and in 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 type fixed long type. Fortunately, there is a clear prompt that long rawsql> L 1 declare 2 v_id int; 3 v_img long raw; 4 Begin 5 select "ID", "Im G "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 using insert values seems to be a failure to change insert select try 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 I Mgtest @ 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: perform the test in Oracle only. 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 wocould be the thing that wocould 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.

Then we can change our thinking. We can first Insert the data to a long raw local table through the transparent gateway, and then insert into to_lob to the Blob field (without considering the length) 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

then I open blob. Wow, only the first half of the image is saved in PNG format. The original file is 180 KB and now it is 32 KB. Why?

the reason is that the maximum size of long raw as columns datatype is Max size 2g, and the maximum value of long raw is 32767 bytes in PL/SQL, the lob type is not transmitted in dblink but can be long. lob cannot be displayed in 10g and sqlplus, and can be displayed in 11G. Therefore, if a single image is larger than 32 K, you can use Java or other OCI Program to solve the problem.