Looping Chain of Synonyms [message #680307] |
Wed, 06 May 2020 07:54  |
 |
mushy.khan1985
Messages: 6 Registered: May 2020
|
Junior Member |
|
|
Hi All,
I am facing an issue of looping chain of synonyms in a package body. Package body is too long to go one by one and while compiling it in toad it is showing me only the error not the exact line/procedure/function where the error is.
Is there any way to find it where the synonym or table is missing which is being used by this package.
Thanks
|
|
|
|
|
|
|
|
|
|
Re: Looping Chain of Synonyms [message #680322 is a reply to message #680319] |
Wed, 06 May 2020 10:47   |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
ahh ... I see:
I suppose the package has nothing to do with it. It just shows the problem....the looping chain of synonyms might be allready there.
Maybe you can do something like:
SQL> create synonym s1 for s2;
Synonym created.
SQL> create synonym s2 for s1;
Synonym created.
SQL> select s1.owner,
s1.synonym_name,
s1.table_owner,
s1.table_name,
s2.owner,
s2.synonym_name,
s2.table_owner,
s2.table_name
from dba_synonyms s1 join dba_synonyms s2
on s1.synonym_name=s2.table_name
and s1.owner=s2.table_owner
where s1.synonym_name=s2.table_name
and s1.table_name=s2.synonym_name;
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------ ---------------- ---------------- ---------------- ------ ---------------- ---------------- ----------------
TINUS S1 TINUS S2 TINUS S2 TINUS S1
TINUS S2 TINUS S1 TINUS S1 TINUS S2
SQL>
SQL>
But....Take care....this only works if the looping is "direct" : s1 pints to s2 and s2 point to s1.
If the looping is over more elements above sql doesn't show.
|
|
|
|
|
|
|
|
|