3 Replies Latest reply: Apr 25, 2012 1:38 AM by Kgronau-Oracle RSS

    Data type conversion from SQL Server to Oracle

    663858
      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
          Anyone have a idea?
          • 2. Re: Data type conversion from SQL Server to Oracle
            Pnauduri-Oracle
            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-Oracle
              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.