This content has been marked as final. Show 3 replies
LOBs are not supported in the gateways. From 11g R2 documentation : http://docs.oracle.com/cd/E11882_01/server.112/e11050/majfeat.htm, the following applies to your situation in my opinion:
3.1 SQL and PL/SQL Support
SQL statements are translated and data types are mapped according to capabilities. PL/SQL calls are mapped to non-Oracle system stored procedures. With SQL statements, if functionality is missing at the remote system, then either a simpler query is issued or the statement is broken up into multiple queries. Then, the desired results are obtained by postprocessing in the Oracle database.
Even though Heterogeneous Services can, for the most part, incorporate non-Oracle systems into Oracle distributed sessions, there are several limitations to this. Some of the generic limitations are:
•There is no support for CONNECT BY clauses in SQL statements.
•ROWID support is limited; consult individual gateway documentation for more details. The Oracle Universal ROWID data type is not supported in any gateway that uses Heterogeneous Services.
•Large objects (LOBs), abstract data types (ADTs), and reference data types (REFs) are not supported.
•Remote packages are not supported.
•Remote stored procedures can have out arguments of type REF CURSOR but not in or in-out objects.
•Oracle Heterogeneous Services agents do not support shared database links.
So I think what is happening is that the HS gateway agent is doing some default mapping of the IMAGE data type into LONG in Oracle. You can create an SR with Oracle Support to get a confirmation.
there's a gateway/heterogeneous services forum which can assist you with your issue: Heterogeneous Connectivity
Root cause is the mapping done by the gateway - SQL Server image data types are mapped to Oracle LONGs. So you need to write a conversation routine that is doing the conversation of LONG data type to BLOB.