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!

Query Taking very long time

Discussion in 'SQL PL/SQL' started by arivazhagan, Dec 1, 2011.

  1. arivazhagan

    arivazhagan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    75
    CURSOR C1 IS
    SELECT 'SELECT ''1'' FROM '||UCC.TABLE_NAME||' WHERE '|| UCC.COLUMN_NAME ||' = '''||P_COL_VAL||''' UNION ' COL, UCC.TABLE_NAME TAB
    FROM USER_CONS_COLUMNS UCC,
    USER_CONSTRAINTS UC
    WHERE NVL(UC.CONSTRAINT_TYPE, 'X') = 'R'
    and UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
    AND EXISTS (SELECT CONSTRAINT_NAME
    FROM USER_CONS_COLUMNS UCC1
    WHERE TABLE_NAME = P_TABLE_NAME
    AND COLUMN_NAME = P_COLUMN_NAME
    AND CONSTRAINT_NAME = R_CONSTRAINT_NAME )
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Try with

    Code (SQL):

    SELECT 'SELECT ''1'' FROM '||UCC.TABLE_NAME||' WHERE '|| UCC.COLUMN_NAME ||' = '''||P_COL_VAL||''' UNION ' COL, UCC.TABLE_NAME TAB
    FROM USER_CONS_COLUMNS UCC,
    USER_CONSTRAINTS UC
    WHERE NVL(UC.CONSTRAINT_TYPE, 'X') = 'R'
    AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
    AND EXISTS (SELECT CONSTRAINT_NAME
    FROM USER_CONS_COLUMNS UCC1
    WHERE   TABLE_NAME = P_TABLE_NAME
    AND COLUMN_NAME = P_COLUMN_NAME
    AND ucc1.POSITION = ucc.POSITION
    AND CONSTRAINT_NAME = R_CONSTRAINT_NAME )