DBMS_UTILITY - Secrets of the Oracle Database - page 214

Usage Notes
The name to resolve may include three identifiers as in schema.table_name.column_name or
schema.package_name.subroutine_name . These types of expressions are resolved to schema.
table_name and schema.package_name respectively. Neither column_name nor subroutine_
name are checked for correctness. When a database link is detected, PART1_TYPE=0 is returned
and no checking of object names occurs. To resolve such names, connect to the database
where the remote object resides and repeat name resolution there or perform a remote proce-
dure call by using the database link.
Exceptions
If a name cannot be resolved in the specified context, “ORA-06564: object object_name does
not exist” is raised, where object_name is the value of parameter NAME passed to the procedure
NAME_RESOLVE . If an existing object is resolved in the wrong context, the exception “ORA-04047:
object specified is incompatible with the flag specified” is thrown.
Examples
In Oracle10 g , the public synonym PRODUCT_USER_PROFILE points to a table with SQL*Plus
configuration data in schema SYSTEM. The following example resolves this public synonym
and displays the table it refers to (see file name_resolve_table.sql in the source code depot):
SQL> VARIABLE name VARCHAR2(100)
SQL> VARIABLE context NUMBER
SQL> VARIABLE schema VARCHAR2(30)
SQL> VARIABLE part1 VARCHAR2(30)
SQL> VARIABLE part2 VARCHAR2(30)
SQL> VARIABLE dblink VARCHAR2(30)
SQL> VARIABLE part1_type NUMBER
SQL> VARIABLE object_number NUMBER
SQL> BEGIN
:context:=2; -- 1: package, 2: table
:name:=' "SYSTEM" . Product_User_Profile '; -- name to resolve
DBMS_UTILITY.NAME_RESOLVE (
name => :name,
context => :context,
schema => :schema,
part1 => :part1,
part2 => :part2,
dblink => :dblink,
part1_type => :part1_type,
object_number => :object_number
);
end;
/
PL/SQL procedure successfully completed.