CLOBS, synonyms, and Oracle 8
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: CLOBS, synonyms, and Oracle 8

  1. #1
    Join Date
    Oct 2001
    Location
    London
    Posts
    13

    Unhappy

    Hi,

    I have a problem with the following scenario:

    I have an Oracle 8i database with a synonym to Oracle 8.0.5 tables. This Oracle 8 table contains a column of datatype CLOB.

    When I try to bring this column over to 8i (using select myclobcol into v_myclobvar from myOracle8Synonym), I get this error:

    ORA-22992: cannot use LOB locators selected from remote tables

    I have search everywhere for a solution, but cannot find one.
    I know that it is specifically an Oracle 8 issue.

    Looking forward to your solutions

    G

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    Distributed LOBs are not supported. Therefore, you cannot use a remote locator in SELECT or WHERE clauses of queries or in functions of the DBMS_LOB package.

    However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:

    UPDATE t SET lobcol = (SELECT lobcol FROM able1@remote_site);

    only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB APIs on LOBs are not supported. For example, the following statement is supported:

    CREATE TABLE AS SELECT clob_col FROM tab@dbs2;


    F.


  3. #3
    Join Date
    Oct 2001
    Location
    London
    Posts
    13
    The "create table" methid works, thanks very much - you can also bring in a primary key field to help with identification.

    Again, thanks

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