Oracle clob over dblink with different charset

Here is the scenario:

Oracle A: charset WE8ISO8859P1

Oracle B: charset WE8MSWIN1252

Oracle A <- dblink -> Oracle B

I can't access Oracle B directly, firewall issues :(开发者_Go百科

I have to get some binary files from OracleB and these files are in a column of type CLOB (don't ask me why and I can't change to BLOB).

I'm using a "select insert" to get the files from B to A and them converting them to binary using clob_to_blob function found here.

I'm getting some corrupted files and I believe it's because Oracle is converting WE8MSWIN1252 to WE8ISO8859P1 automatically over the dblink (well, the column is CLOB so it's text, right?).

I can't change the databases charsets in any way.

Is there any workaround for this?

Thanks in advance

Have you tried to use DBMS_LOB.CONVERTTOBLOB@remote(....)

But you probably want to get some sort of checksum of the remote CLOB to see whether they are getting a characterset conversion when they are inserted/updated from whatever original external source. That is, if the client characterset, when the insert is done, is different from the database characterset, the problem may have already occurred before you do your select.

Edited to add.

The closest I can come up with requires some objects on the other end of the link. Firstly a function that does the conversion at the remote end. Secondly a view that presents a 'BLOB' view of the data. This uses a dummy table (based of v$sql as it was the first CLOB I could find). No reason I can see that you can't simply pass the CLOB as a parameter to the function.

create or replace function ret_blob return blob is
  cursor c_1 is 
  select sql_fulltext, sql_id, length(sql_fulltext) 
  from v_sql
  where sql_id = 'bzmb01whp36wt';
  rec_c1 c_1%rowtype;
  --
  v_blob  blob;
  v_dest  number := 1;
  v_src   number := 1;
  v_lang  number := 0;
  v_warn  number;
  --
begin
  open c_1;
  fetch c_1 into rec_c1;
  close c_1;
  dbms_lob.createtemporary(v_blob, TRUE);
  --
  dbms_lob.CONVERTTOBLOB (v_blob, rec_c1.sql_fulltext, DBMS_LOB.LOBMAXSIZE, 
        v_dest, v_src, DBMS_LOB.DEFAULT_CSID, v_lang, v_warn);
  --
  dbms_output.put_line(':'||v_warn||'>'||length(v_blob));
  --
  return v_blob;
end;
/

create view rblob as select ret_blob from dual;

Then, from the local database, do a

create table t as select ret_blob from rblob@remote

My best suggestion would be not to use the DB link but instead do this:

1. Get a client program, either standalone or your own, to extract the CLOB from Oracle B and write the data out as a "text" file which contains the correct binary data.
2. Import that file into Oracle A as a binary file into the BLOB.

A totally different alternative. Create database C with the same characterset as B. Pull the data from B to C (without any conversion), then you can do you manipulation in C before moving the data to A.