Oracle Database Discussions

I'm trying to use the TG4MSSQL to access tables in a SS2k database. All is well, except for 1 table that has an IMAGE column in it. I must say that I'm a little surprised and disappointed that the 10gR2 gateway for MSSQL apparently translates these things into LONG RAW columns, which are known to be antiquated and minimally supported. However, I still need access to the data, and ultimately, I need it in a BLOB. Doing simple queries (in TOAD) such as:

select * from image@tg4ss where image_id = 1;

returns the row, and I can use TOAD to determine that all the data is there. However, apparently, you cannot use PL/SQL to bind more than 32K of this data into a variable, so I haven't had much success there.

The only way I know of converting a long raw to a blob is to use the to_lob function, but that's only good in the select list of a subquery of an insert statement. Thing is, that doesn't seem to work with the gateway. I tried something like:

insert into my_blob_table( id, image )
select id, to_lob(image) as image from image@tg4ss where id = 1;

That gets me a ORA-00997: illegal use of LONG datatype.

In fact, I can't even do this:

insert into my_long_raw_table( id, image )
select id, image from image@tg4ss where id = 1;

I get the same error on that command.

Now, I can do this, but I only get the first 32k of the image:

begin
for I in (select id, image from IMAGE@tg4ss)
loop
insert into my_long_raw_table( id, image )
values (I.id, I.image);
end loop;
end;

So, does anyone know how you can get image data out of an SQL Server database into a blob value? If so, I'd appreciate a reply. Thanks.

-Rich.