Home » SQL & PL/SQL » SQL & PL/SQL » LONG value VS DB Links
LONG value VS DB Links [message #362522] Tue, 02 December 2008 11:41 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
So I have a query that pulls some constraint information from the user_constraints and user_cons_columns tables over a database link. One of the columns (SEARCH_CONDITION) is of the LONG type, which is causing me all sorts of headaches. I finally thought I got the problem licked (the query worked fine from the user), however when run from another user via the database link it fails. Any work-around?

Test (any user you have with constraints, or even without them)

QUERY from the user (WORKS)
SELECT *
  FROM (SELECT   ucc.table_name, ucc.constraint_name, uc.constraint_type,
                 ucc.column_name, ucc.POSITION, uc.search_condition,
                 ROW_NUMBER () OVER (PARTITION BY ucc.constraint_name ORDER BY ucc.POSITION)
                                                                           ID,
                 COUNT (*) OVER (PARTITION BY ucc.constraint_name) cnt
            FROM user_cons_columns ucc,
                 user_constraints uc
           WHERE uc.owner = ucc.owner
             AND uc.constraint_name = ucc.constraint_name
             AND uc.table_name = ucc.table_name
             AND uc.GENERATED = 'USER NAME'
             AND uc.constraint_type IN ('P', 'C'))


QUERY from another user via a DB Link (BROKEN)
SELECT *
  FROM (SELECT   ucc.table_name, ucc.constraint_name, uc.constraint_type,
                 ucc.column_name, ucc.POSITION, uc.search_condition,
                 ROW_NUMBER () OVER (PARTITION BY ucc.constraint_name ORDER BY ucc.POSITION)
                                                                           ID,
                 COUNT (*) OVER (PARTITION BY ucc.constraint_name) cnt
            FROM user_cons_columns@data_copy_link ucc,
                 user_constraints@data_copy_link uc
           WHERE uc.owner = ucc.owner
             AND uc.constraint_name = ucc.constraint_name
             AND uc.table_name = ucc.table_name
             AND uc.GENERATED = 'USER NAME'
             AND uc.constraint_type IN ('P', 'C'))


Interestingly enough the following DOES work across the DB Link
SELECT   ucc.table_name, ucc.constraint_name, uc.constraint_type,
                 ucc.column_name, ucc.POSITION, uc.search_condition,
                 ROW_NUMBER () OVER (PARTITION BY ucc.constraint_name ORDER BY ucc.POSITION)
                                                                           ID,
                 COUNT (*) OVER (PARTITION BY ucc.constraint_name) cnt
            FROM user_cons_columns@data_copy_link ucc,
                 user_constraints@data_copy_link uc
           WHERE uc.owner = ucc.owner
             AND uc.constraint_name = ucc.constraint_name
             AND uc.table_name = ucc.table_name
             AND uc.GENERATED = 'USER NAME'
             AND uc.constraint_type IN ('P', 'C')

[Updated on: Tue, 02 December 2008 11:42]

Report message to a moderator

Re: LONG value VS DB Links [message #362625 is a reply to message #362522] Wed, 03 December 2008 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interestingly, it seems to be the analytics throwing this out.
this works fro me:
SELECT *
  FROM (SELECT   ucc.table_name, ucc.constraint_name, uc.constraint_type,
                 ucc.column_name, ucc.POSITION, uc.search_condition
            FROM user_cons_columns@data_copy_link ucc,
                 user_constraints@data_copy_link uc
           WHERE uc.owner = ucc.owner
             AND uc.constraint_name = ucc.constraint_name
             AND uc.table_name = ucc.table_name
             AND uc.GENERATED = 'USER NAME'
             AND uc.constraint_type IN ('P', 'C'))
Re: LONG value VS DB Links [message #362680 is a reply to message #362522] Wed, 03 December 2008 14:03 Go to previous message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
It seems to be a combination of the extra layer (select * from (...)) or the analytics (row_number/count) and database links that are causing the error.

Pick any 2 and it works fine, add the third and kaboom.

Note: I've found a workaround to this issue, but am still interested in the source/solution of this problem just because ... I like to know stuff.

[Updated on: Wed, 03 December 2008 14:16]

Report message to a moderator

Previous Topic: how to generate number without exceed 6 digigts
Next Topic: top SQL
Goto Forum:
  


Current Time: Sat Dec 10 18:19:43 CST 2016

Total time taken to generate the page: 0.04204 seconds