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;
SQL> select resolve_name('DBMS_STANDARD') from dual;
RESOLVE_NAME('DBMS_STANDARD')
------------------------------------------------------------------------------
PUBLIC.DBMS_STANDARD ->SYS.DBMS_STANDARD (PACKAGE)
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--)
SQL> select resolve_name('C') from dual;
RESOLVE_NAME('C')
--------------------------------------------------------------
PUBLIC.C ->SDS.CLIENTS@MY_HR_DB (--REMOTE UNKNOWN--)
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.
Comments (2)
Commented:
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...!!!
Author
Commented: