Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Resolving Oracle Object Names and Synonym Chains

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Published:
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy-version support.  I hope you enjoy it and find it helpful.

The data dictionary holds a wealth of information about your objects, but sometimes seemingly easy questions are still difficult to answer with a simple query to one of the views.  Somebody once asked me how to determine what object was pointed to by a synonym.  That's easy, just query DBA_SYNONYMS. The question becomes more complicated though if the target object is itself a synonym.  Then you need to use hierarchical queries (i.e. CONNECT BY or recursive WITH clauses.)  Answering this question is made even more difficult by changes in the way synonyms are resolved if the target doesn't exist.  

Prior to 10g this condition would produce an error.  Starting in 10g you can sometimes use a public synonym of the same name to fill in for the missing object.  To this was added a further requirement of displaying the object type of the final object in the synonym chain.

The function below is what I came up with to answer the question.  By default it resolves synonyms by 10g rules.  You can pass in any number less than 10 to change to the pre-10g method.  If a synonym chain resolves to some object in a remote database that's as far as I search and identify the final object as REMOTE.  I also keep track of the synonym chain as it's built, so if a synonym loop is created, it will be detected and identified.  

Since the output is of VARCHAR2 type, there is an implicit upper limit of 32K on the output in pl/sql  or 4K in sql; but I've never encountered a synonym chain that came anywhere close to those limits in a real system.

It was a seemingly easy question that produced an interesting answer that was fun to write.


The Code


CREATE OR REPLACE FUNCTION resolve_name(
                          p_object      IN VARCHAR2,
                          p_delimiter   IN VARCHAR2 DEFAULT ' ->',
                          p_owner       IN VARCHAR2 DEFAULT USER,
                          p_version     IN INTEGER DEFAULT 10
                      )
                          RETURN VARCHAR2
                      IS
                          -- Resolve_Name
                          --    by Sean D. Stuber
                          --
                          -- Given an object name, show how that name is resolved through
                          -- a chain of private and/or public synonyms to the final object
                          -- and show what type of object is resolved.
                          -- If no object can be found then return NULL.
                          -- If the resolution chain runs into a remote object the returned path
                          -- stops at the link and the remote type is undefined
                          -- If synonyms loop (causing ORA-01775 errors)
                          -- the path to the loop will be returned with the loop identified
                          TYPE object_table IS TABLE OF CHAR(1)
                                                   INDEX BY VARCHAR2(200);
                      
                          v_objects object_table;
                      
                          FUNCTION recursive_resolve(
                              p_object      IN VARCHAR2,
                              p_delimiter   IN VARCHAR2,
                              p_owner       IN VARCHAR2,
                              p_version     IN INTEGER,
                              p_first       IN BOOLEAN
                          )
                              RETURN VARCHAR2
                          IS
                              v_owner         dba_objects.owner%TYPE;
                              v_object        dba_objects.object_name%TYPE;
                              v_object_type   dba_objects.object_type%TYPE;
                              v_link          dba_synonyms.db_link%TYPE;
                              v_temp          VARCHAR2(32767);
                              v_dummy         INTEGER;
                              v_schema_exists BOOLEAN;
                          BEGIN
                              -- Check to see if we're recursing into a loop
                              IF v_objects.EXISTS('"' || p_owner || '"."' || p_object || '"')
                              THEN
                                  v_temp := p_owner || '.' || p_object || ' (--SYNONYM LOOP--)';
                              ELSE
                                  v_objects('"' || p_owner || '"."' || p_object || '"') := NULL;
                      
                                  BEGIN
                                      -- At what type of object are we looking?
                                      SELECT object_type
                                        INTO v_object_type
                                        FROM dba_objects
                                       WHERE object_name = p_object AND owner = p_owner
                                         AND object_type NOT IN
                                                 ('PACKAGE BODY', 'TYPE BODY', 
                                                  'TABLE PARTITION', 'TABLE SUBPARTITION');
                      
                                      v_temp := p_owner || '.' || p_object;
                      
                                      IF v_object_type = 'SYNONYM'
                                      THEN
                                          -- If it's a synonym then find what the synonym points to
                                          -- and call this procedure again to resolve that object
                                          -- if the synonym points to a remote object,
                                          -- note it and end the search.
                                          SELECT table_owner, table_name, db_link
                                            INTO v_owner, v_object, v_link
                                            FROM dba_synonyms
                                           WHERE synonym_name = p_object AND owner = p_owner;
                      
                                          v_temp :=
                                              v_temp || p_delimiter
                                              || CASE
                                                     WHEN v_link IS NULL
                                                     THEN
                                                         recursive_resolve(
                                                             v_object,
                                                             p_delimiter,
                                                             v_owner,
                                                             p_version,
                                                             FALSE
                                                         )
                                                     ELSE
                                                            v_owner
                                                         || '.'
                                                         || v_object
                                                         || '@'
                                                         || v_link
                                                         || ' (--REMOTE UNKNOWN--)'
                                                 END;
                                      ELSE
                                          -- If this object is not a synonym then we've resolved
                                          -- to the end, so return the object with its type
                                          v_temp := v_temp || ' (' || v_object_type || ')';
                                      END IF;
                                  EXCEPTION
                                      WHEN NO_DATA_FOUND
                                      THEN
                                          -- For first attempt or version is 10 or higher
                                          -- when no object is found in given schema
                                          -- look for a public synonym of the same name
                                          -- Note, 10g after the first level we only do this
                                          -- if the synonym points to an existing schema
                                          IF (NOT p_first) AND p_version >= 10
                                          THEN
                                              BEGIN
                                                  SELECT 1
                                                    INTO v_dummy
                                                    FROM dba_users
                                                   WHERE username = p_owner;
                      
                                                  v_schema_exists := TRUE;
                                              EXCEPTION
                                                  WHEN NO_DATA_FOUND
                                                  THEN
                                                      v_schema_exists := FALSE;
                                              END;
                                          END IF;
                      
                                          IF p_first OR v_schema_exists
                                          THEN
                                              v_temp :=
                                                  recursive_resolve(
                                                      p_object,
                                                      p_delimiter,
                                                      'PUBLIC',
                                                      p_version,
                                                      FALSE
                                                  );
                                          ELSE
                                              -- In versions 9i or lower,  if a synonym points to a
                                              -- nonexisting object then the search ends
                                              -- In 10g or higher if the synonym points to a schema
                                              -- that doesn't exist then the search ends
                                              v_temp := NULL;
                                          END IF;
                                  END;
                              END IF;
                      
                              RETURN v_temp;
                          END;
                      BEGIN
                          RETURN recursive_resolve(
                                     p_object,
                                     p_delimiter,
                                     p_owner,
                                     p_version,
                                     TRUE
                                 );
                      END resolve_name;

Open in new window



Example Usage


Here you can see DBMS_STANDARD is a PUBLIC synonym pointing to an object of the same name owned by SYS.  That object is a package.

SQL> select resolve_name('DBMS_STANDARD') from dual;
                      
                      RESOLVE_NAME('DBMS_STANDARD')
                      ------------------------------------------------------------------------------
                      
                      PUBLIC.DBMS_STANDARD ->SYS.DBMS_STANDARD (PACKAGE)

Open in new window


Here the function is used to identify the cause of ORA-01775 looping error

SQL> select * from yyy;
                      select * from yyy
                                    *
                      ERROR at line 1:
                      ORA-01775: looping chain of synonyms
                      
                      SQL> select resolve_name('YYY') from dual;
                      
                      RESOLVE_NAME('YYY')
                      -------------------------------------------------------------------------
                      
                      SDS.YYY ->PUBLIC.XXX ->SDS.XXX (--SYNONYM LOOP--)

Open in new window



And finally, here's an example of a tracing a synonym across a dblink.  In this case "C" points to a remote table "CLIENTS" but the function doesn't traverse the remote dictionary so the type of the remote object is unknown and labeled so.

SQL> select resolve_name('C') from dual;
                      
                      RESOLVE_NAME('C')
                      --------------------------------------------------------------
                      
                      PUBLIC.C ->SDS.CLIENTS@MY_HR_DB (--REMOTE UNKNOWN--)

Open in new window

13
14,835 Views
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT

Comments (2)

CERTIFIED EXPERT

Commented:
Excellent...!!!
you have got tremendous knowledge of oracle..

please continue writing articles.. this will help people like who can make out their oracle concepts clear..

thanks again buddy...!!!
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
glad you liked it and thank you for voting

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.