Home » SQL & PL/SQL » SQL & PL/SQL » Looping Chain of Synonyms (Oracle Database 12)
Looping Chain of Synonyms [message #680307] Wed, 06 May 2020 07:54 Go to next message
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 #680309 is a reply to message #680307] Wed, 06 May 2020 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All synonyms you have access are in ALL_SYNONYMS view.

So:
SELECT so.line, sy.synonym_name
FROM all_source so, all_synonyms sy
WHERE so.owner = <the owner>
  AND so.name = <package name>
  AND so.type = 'PACKAGE BODY'
  AND instr(so.line, sy.synonym_name) != 0
ORDER BY 1, 2
/
Then just try each synonym to see which one(s) loops.

Re: Looping Chain of Synonyms [message #680310 is a reply to message #680307] Wed, 06 May 2020 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Consider to start by producing the list of SYNONYMS which exist in "this" PACKAGE using ALL_DEPENDENCIES
Hopefully it is a reasonably short list.
Then you can manually test each SYNONYM for correctness & expecting one to fail.

Re: Looping Chain of Synonyms [message #680314 is a reply to message #680310] Wed, 06 May 2020 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Right! This restricts for a better query:
SELECT so.line, sy.synonym_owner, sy.synonym_name
FROM all_source so, 
     ( SELECT referenced_owner synonym_owner, referenced_name synonym_name 
       FROM all_dependencies d
       WHERE d.owner = <the owner>
         AND d.name = <package name>
         AND d.type = 'PACKAGE BODY'
         AND d.referenced_type = 'SYNONYM'
     ) s
WHERE so.owner = <the owner>
  AND so.name = <package name>
  AND so.type = 'PACKAGE BODY'
  AND instr(so.line, sy.synonym_name) != 0
ORDER BY 1, 2
/
The inline view giving the list of the referenced synonyms and the query the lines where there are referenced (many with false positives).

Re: Looping Chain of Synonyms [message #680315 is a reply to message #680309] Wed, 06 May 2020 08:47 Go to previous messageGo to next message
mushy.khan1985
Messages: 6
Registered: May 2020
Junior Member
thanks for the query but not able to find anything any other way may be any table used in the package body is dropped or calling any other package or function which is invalid.

I just want to identify where is the error on which line code.

Re: Looping Chain of Synonyms [message #680316 is a reply to message #680315] Wed, 06 May 2020 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
If you want to BRUTE FORCE a solution, you can implement your own binary search.

Remove the bottom half of the source lines & try to compile the top half.
If it compiles without error, the you know the problem line is in the bottom half.
Just keep adding or subtracting source lines until you identify the line throwing the error.
Re: Looping Chain of Synonyms [message #680318 is a reply to message #680307] Wed, 06 May 2020 09:49 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Would a look in all_errors might help?
Re: Looping Chain of Synonyms [message #680319 is a reply to message #680318] Wed, 06 May 2020 09:55 Go to previous messageGo to next message
mushy.khan1985
Messages: 6
Registered: May 2020
Junior Member
all_errors is also showing only error looping chain of synonyms no position or line.
Re: Looping Chain of Synonyms [message #680322 is a reply to message #680319] Wed, 06 May 2020 10:47 Go to previous messageGo to next message
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.
Re: Looping Chain of Synonyms [message #680325 is a reply to message #680315] Wed, 06 May 2020 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mushy.khan1985 wrote on Wed, 06 May 2020 15:47
thanks for the query but not able to find anything any other way may be any table used in the package body is dropped or calling any other package or function which is invalid.

I just want to identify where is the error on which line code.
1/ You can't know the line from Oracle
2/ So my query to search for the query, note this has nothing to do with tables, I don't why you still mention that
3/ You say the query is not able to find, to find what? and which query? Copy and paste your SQL*Plus session.

The plan is
1/ find the synonym referenced in the package
2/ find the synonym that loops

Re: Looping Chain of Synonyms [message #680327 is a reply to message #680325] Wed, 06 May 2020 12:32 Go to previous messageGo to next message
mushy.khan1985
Messages: 6
Registered: May 2020
Junior Member
3/I was refereeing to this query i ran it my package and schema.

SELECT so.line, sy.synonym_name
FROM all_source so, all_synonyms sy
WHERE so.owner = <the owner>
AND so.name = <package name>
AND so.type = 'PACKAGE BODY'
AND instr(so.line, sy.synonym_name) != 0
ORDER BY 1, 2

Yes you are right and i am trying to find the same for plan1 and 2.
Re: Looping Chain of Synonyms [message #680328 is a reply to message #680327] Wed, 06 May 2020 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Is query:
Quote:
The inline view giving the list of the referenced synonyms
2/ is just a select on these synonyms.

Have you the error at compile time or at execution time?

[Updated on: Wed, 06 May 2020 12:52]

Report message to a moderator

Re: Looping Chain of Synonyms [message #680329 is a reply to message #680328] Wed, 06 May 2020 13:18 Go to previous messageGo to next message
mushy.khan1985
Messages: 6
Registered: May 2020
Junior Member
Package body is curretly in invalid state and it is giving me error at time of compilation.

thanks
Re: Looping Chain of Synonyms [message #680330 is a reply to message #680329] Wed, 06 May 2020 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
mushy.khan1985 wrote on Wed, 06 May 2020 11:18
Package body is curretly in invalid state and it is giving me error at time of compilation.

thanks

When was last time application worked OK?
What changed since then?

Some, most, many Professional IT staff maintain application source code within code repository.
What recent code changes were made & why can't they be over written by most recent error free code?
Re: Looping Chain of Synonyms [message #680331 is a reply to message #680330] Wed, 06 May 2020 14:21 Go to previous message
mushy.khan1985
Messages: 6
Registered: May 2020
Junior Member
thanks issue resolved actually package was referring to another package in a function which does not exists anymore in the database.
Previous Topic: Query needs to be tune
Next Topic: Configuring Scripts
Goto Forum:
  


Current Time: Wed Sep 23 20:32:13 CDT 2020