This discussion is archived
3 Replies Latest reply: Apr 24, 2012 11:38 PM by kgronau RSS

Data type conversion from SQL Server to Oracle

663858 Newbie
Currently Being Moderated
I have to migrate data from SQL server tables to oracle tables.

Iam facing problem for tables in oracle which have BLOB datatype.
There is no BLOB datatype in SQL server,In place of BLOB there is image datatype in SQL Server.
Iam using Oracle Heterogenous Services to migrate the data,Using Merge statement and database link.

When Iam running the statement
MERGE INTO METRICS_DETAIL ora
USING METRICS_DETAIL@testlink sql
ON(ora.METRICS_DETAIL_ID = sql.METRICS_DETAIL_ID)
WHEN MATCHED THEN
UPDATE SET ora.COLLECTION_DATE = sql.COLLECTION_DATE
, ora.SEND_DATE = sql.SEND_DATE
, ora.METRICS_FILE = sql.METRICS_FILE
, ora.CREATED_BY = sql.CREATED_BY
, ora.CREATED_DATE = sql.CREATED_DATE
, ora.UPDATED_BY = sql.UPDATED_BY
, ora.UPDATED_DATE = sql.UPDATED_DATE
WHEN NOT MATCHED THEN
INSERT( ora.METRICS_DETAIL_ID
, ora.COLLECTION_DATE
, ora.SEND_DATE
, ora.METRICS_FILE
, ora.CREATED_BY
, ora.CREATED_DATE
, ora.UPDATED_BY
, ora.UPDATED_DATE)
VALUES( sql.METRICS_DETAIL_ID
, sql.COLLECTION_DATE
, sql.SEND_DATE
, sql.METRICS_FILE
, sql.CREATED_BY
, sql.CREATED_DATE
, sql.UPDATED_BY
, sql.UPDATED_DATE);

I am getting the following error-


ERROR at line 7:
ORA-00932: inconsistent datatypes: expected BLOB got LONG BINARY

here metrics_file is the BLOB field in oracle

Can any one suggest me how to migrate Image datatype to BLOB???
  • 1. Re: Data type conversion from SQL Server to Oracle
    932692 Newbie
    Currently Being Moderated
    Anyone have a idea?
  • 2. Re: Data type conversion from SQL Server to Oracle
    84959 Explorer
    Currently Being Moderated
    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.

    Regards

    Prakash
  • 3. Re: Data type conversion from SQL Server to Oracle
    kgronau Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points