SQL Language (MOSC)

MOSC Banner

Query question comparing a SYS view and a local table

edited Nov 8, 2012 9:46PM in SQL Language (MOSC) 4 commentsAnswered
Hello:

I have a query that I've been struggling with.  It is a (maybe) simple hierarchical query into ALL_SYNONYMS:

SELECT  asy.*
              FROM ALL_SYNONYMS asy
              START WITH  ( asy.OWNER = 'PUBLIC') AND
                          ( asy.SYNONYM_NAME = 'MASK_XREF')
              CONNECT BY  ( asy.SYNONYM_NAME = PRIOR asy.TABLE_NAME) AND
                          ( asy.OWNER =        PRIOR asy.TABLE_OWNER);

no rows selected

I know that there is a record in there that should match because this does work:

select * from all_synonyms
where synonym_name like 'MASK%';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
--
PUBLIC                         MASK_XREF                      MANITDBA                       MASK_XREF

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center