1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Database Link

Discussion in 'SQL PL/SQL' started by meu, Oct 22, 2009.

  1. meu

    meu Guest

    i have created a database link, i am able to fetch data by giving the following query..

    select a,b,c from Schema.Table@DB_LinkName

    (i have 4 columns namely a,b,c,d where a,b,c are of varchar2 type and column d is of CLOB type)

    But i am not able to fetch columns which is CLOB type ie column "d".

    following query results an error as b is of CLOB type.
    select a,b,c,d from Schema.Table@DB_LinkName

    Can anyone please help me to sort this out..!!!
     
  2. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    Create a view on the Remote Database and Select from the View using DB Link.

    Code (Text):
    SQL >CREATE TABLE TEST
      2  ( NAME VARCHAR2(10),
      3  D CLOB)
      4  /

    Table created.

    SQL >INSERT INTO TEST VALUES ('SIKKI','SIKKI IS JUST FOR TEST')
      2  /

    1 row created.

    SQL >SELECT NAME,D FROM TEST
      2  /

    NAME       D
    ---------- --------------------------------------------------------------------------------
    SIKKI      SIKKI IS JUST FOR TEST


    SQL >CREATE VIEW TEST_VW
      2  AS
      3  SELECT NAME,dbms_lob.substr(D,4000,1) D FROM TEST
      4 /

    View created.


    SQL >SELECT NAME,D FROM TEST@DBLINK_NAME  
      2  /
    SELECT NAME,D FROM TEST@DBLINK_NAME
                *
    ERROR at line 1:
    ORA-22992: cannot use LOB locators selected from remote tables

    SQL >SELECT * FROM TEST_VW@DBLINK_NAME      
      2  /

    NAME
    ----------
    D
    --------------------------------------------------------------------------
    SIKKI
    SIKKI IS JUST FOR TEST
    From Next time, Please Post the error message too in your question.