Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    6

    Question Unanswered: ANSI Join syntax behavior different on remote link

    I prefer the ansi sytax for outer joins for a variety of reasons

    Can someone explain to me based on the three queries below why Oracle (9.2.0.4) attempts to resolve the LOB when using ansi syntax and does not when using traditional SQL.


    --Query a run on dev server with remote link to prod server
    -- works
    SELECT
    COUNT(*)
    FROM
    PROD_SERVER.TABLE_WITH_LOB@LINK_FROM_DEV_TO_PROD TWL
    ,PROD_SERVER.OUTER_TABLE@LINK_FROM_DEV_TO_PROD OT
    WHERE
    TWL.MASTER_COLUMN = OT.CHILD_COLUMN (+)
    AND OT.CHILD_COLUMN IS NULL


    --Same query a run on dev server with remote link to prod server
    -- only using ansi join syntax
    -- RESULT: ORA-22992: cannot use LOB locators selected from remote tables

    SELECT
    COUNT(*)
    FROM
    PROD_SERVER.TABLE_WITH_LOB@LINK_FROM_DEV_TO_PROD TWL
    LEFT OUTER JOIN
    PROD_SERVER.OUTER_TABLE@LINK_FROM_DEV_TO_PROD OT ON TWL.MASTER_COLUMN = OT.CHILD_COLUMN
    WHERE
    OT.CHILD_COLUMN IS NULL


    --Same ansi syntax query a run on dev server to resource on same Oracle instance
    -- Works
    SELECT
    COUNT(*)
    FROM
    DEV_SERVER.TABLE_WITH_LOB TWL
    LEFT OUTER JOIN
    DEV_SERVER.OUTER_TABLE OT ON TWL.MASTER_COLUMN = OT.CHILD_COLUMN
    WHERE OT.CHILD_COLUMN IS NULL

  2. #2
    Join Date
    Dec 2009
    Posts
    1
    Quote Originally Posted by Stocker1216 View Post
    I prefer the ansi sytax for outer joins for a variety of reasons

    Can someone explain to me based on the three queries below why Oracle (9.2.0.4) attempts to resolve the LOB when using ansi syntax and does not when using traditional SQL.


    --Query a run on dev server with remote link to prod server
    -- works
    SELECT
    COUNT(*)
    FROM
    PROD_SERVER.TABLE_WITH_LOB@LINK_FROM_DEV_TO_PROD TWL
    ,PROD_SERVER.OUTER_TABLE@LINK_FROM_DEV_TO_PROD OT
    WHERE
    TWL.MASTER_COLUMN = OT.CHILD_COLUMN (+)
    AND OT.CHILD_COLUMN IS NULL


    --Same query a run on dev server with remote link to prod server
    -- only using ansi join syntax
    -- RESULT: ORA-22992: cannot use LOB locators selected from remote tables

    SELECT
    COUNT(*)
    FROM
    PROD_SERVER.TABLE_WITH_LOB@LINK_FROM_DEV_TO_PROD TWL
    LEFT OUTER JOIN
    PROD_SERVER.OUTER_TABLE@LINK_FROM_DEV_TO_PROD OT ON TWL.MASTER_COLUMN = OT.CHILD_COLUMN
    WHERE
    OT.CHILD_COLUMN IS NULL


    --Same ansi syntax query a run on dev server to resource on same Oracle instance
    -- Works
    SELECT
    COUNT(*)
    FROM
    DEV_SERVER.TABLE_WITH_LOB TWL
    LEFT OUTER JOIN
    DEV_SERVER.OUTER_TABLE OT ON TWL.MASTER_COLUMN = OT.CHILD_COLUMN
    WHERE OT.CHILD_COLUMN IS NULL
    Did you find any explanation and/or workaround for this problem?

    I get a similar strange error when trying to access a LOCAL CLOB column in combination with a left join to a remote database!

    best regards

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Did you find any explanation and/or workaround for this problem?
    If you are still running with V9 Oracle, it is past time to upgrade to a supported version.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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