DBMS_UTILITY - Secrets of the Oracle Database - page 215

After a successful call to DBMS_UTILITY.NAME_RESOLVE , the bind variables contain the
constituent parts of the referenced database object.
SQL> SELECT -- resolved_name
'"' || :schema|| '"' || nvl2(:part1,'."'||:part1 || '"', null)||
nvl2(:part2,'."'||:part2 || '"',NULL) ||
nvl2(:dblink,'@"'||:dblink || '"' ,NULL) || ' is ' ||
-- translate part1_type to object type
decode(:part1_type, 0, 'an object at a remote database',
2, 'a table',
4, 'a view',
6, 'a sequence',
7, 'a procedure',
8, 'a function',
9, 'a package',
12, 'a trigger',
13, 'a type') ||
' (PART1_TYPE=' || :part1_type || ', OBJECT_NUMBER=' ||
:object_number || ')' AS detailed_info
FROM dual;
DETAILED_INFO
---------------------------------------------------------------------------------
"SYSTEM"."SQLPLUS_PRODUCT_PROFILE" is a table (PART1_TYPE=2, OBJECT_NUMBER=10209)
The OUT parameter OBJECT_NUMBER may be used to retrieve additional information on a
database object from the dictionary view DBA_OBJECTS .
SQL> SELECT owner, object_name, object_type, status, created
FROM all_objects
WHERE object_id=:object_number;
OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED
------ ----------------------- ----------- ------- ---------
SYSTEM SQLPLUS_PRODUCT_PROFILE TABLE VALID 30.Aug.05
The result of the query on ALL_OBJECTS at the very end of the script confirms that the name
was correctly resolved. Thus, the public synonym PRODUCT_USER_PROFILE resolves to the table
SQLPLUS_PRODUCT_PROFILE. With a name containing a database link
( "SYSTEM".Product_User_Profile@db_link ) as a final example, the result is as follows:
DETAILED_INFO
---------------------------------------------------------------------------
"SYSTEM"."PRODUCT_USER_PROFILE"@"DB_LINK" is an object at a remote database
(PART1_TYPE=0, OBJECT_NUMBER=0)
no rows selected
This time, the query on ALL_OBJECTS does not return a result, since the value of the OUT
parameter OBJECT_NUMBER is zero.