Resolving Oracle Object Names and Synonym Chains: Oracle, Synonym, Object, Link

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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:

Select all 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)

1:
2:
3:
4:
5:
6:

Select all 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--)

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Select all 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--)

1:
2:
3:
4:
5:
6:

Select all Open in new window

Resolving Oracle Object Names and Synonym Chains

The Code

Example Usage

Oracle

Synonym

Object

Link

Oracle Database

About The Author

More Articles From sdstuber

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Oracle Database Experts