How to move images data from SQL Server to Oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to move images data from SQL Server to Oracle

  1. #1
    Join Date
    May 2001
    Location
    Fairfax, virginia
    Posts
    23

    Unhappy How to move images data from SQL Server to Oracle

    Hello, everyone,

    I am working on migrating data from SQL Server 2000 to Oracle 10g for millions of records of data in some tables in SQL Server.
    I am using “BULK COLLECT…FOR ALL” store procedure to do it (using either explicit/implicit cursor to fetch data record by record is too slow). Some of the data type in SQL Server tables are "images" type.
    The corresponding data type for "images" in Oracle is LOB/BLOB. First, I create a table with BLOB data types in Oracle, then, I execute the store procedure, But I got the error message: "ora-22992 cannot use LOB locators selected from remote tables"

    I checked the error message in Oracle documentation, and got the following information:
    Cause: A remote LOB column cannot be referenced.
    Action: Remove references to LOBs in remote tables.

    However, I am not allowed to do any changes in the remote database (in SQL Server), I can do anything in Oracle.
    What I did is: first, I changed the data type from BLOB to RAW(2000) in Oracle, it doesn’t work. Then, I change the data type to LONG RAW, Still doesn’t work. Can anybody tell that changing the data type in Oracle is the right track? If it is, what type of data type I need to change to?
    If not, what is the right direction I need to go? At the same time, I checked Migration workbench, this tool can't move BLOB data.

    Another approach probably is: move all of the data except images type, and then try to move the images column individual, but it is possible for cursor, not practical for “BULK COLLECT…FORALL”. And I was asked to write store procedure code to do the migration.

    I really appreciate any suggestion.

    Best Regards,

    Gary

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The corresponding data type for "images" in Oracle is LOB/BLOB. First, I create a table with BLOB data types in Oracle, then, I execute the store procedure, But I got the error message: "ora-22992 cannot use LOB locators selected from remote tables"
    You need to use either JAVA or some OCI to load LOB data from SQL Server to Oracle. PL/SQL proc will fail if the size of LOB > 32K.

    Tamil

  3. #3
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Try InterMedia.
    "What is past is PROLOGUE"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width