September 19, 2005: On using dbms_utility.name_resolve

Say, you encounter the following SQL statement:

select * from some_table;

Without knowing further details, you cannot be totally sure that there is actually a table named some_table. This is the case if there is a synonym named some_table that actually points to a table with a different name.

If you want to find out which table and schema some_table stands for, dbms_utility.name_resolve helps do that.

As always, a copy/pastable example is worth 1000 words.

We need a schema (which is created along with a user):

create user usr_01 identified by usr_01 
default tablespace   users
temporary tablespace temp
quota unlimited on   users;

This user needs some privileges:

grant create session, create table, create synonym, create public synonym to usr_01;

usr_01 then creates a table:

connect usr_01/usr_01

create table item (
  article_no  number  (10 ),
  price       number  (8,2),
  description varchar2(200)
);

usr_01 also gives select rights on the table to me:

grant select on item to rene;

Then, usr_01 creates a synonym for the table:

create synonym syn_item for item;

In order to make things a bit more interesting, a public synonym is also created for syn_item:

create public synonym pub_syn_item for syn_item;

I connect myself...

connect rene/rene

... and, for fun's sake, create a synonym on the prevously created public synonym:

create synonym my_syn for pub_syn_item;

And finally, I am ready to use dbms_utility.name_resolve to find out what my_syn points to:

declare
  context number := 2;
  schema  varchar2(30);
  part1   varchar2(30);
  part2   varchar2(30);
  dblink  varchar2(128);
  part1_type number;
  object_number number;
begin

  dbms_utility.name_resolve('my_syn', context, schema,part1, part2, dblink, part1_type, object_number);

  dbms_output.put_line('Schema:     ' || schema);
  dbms_output.put_line('table_name: ' || part1 );
exception
  when others then 
    case when sqlcode = -6564 then dbms_output.put_line('object does not exist');
    else 
      dbms_output.put_line('exception: ' || sqlerrm || '(' || sqlcode || ')' ); 
    end case;
end;
/

This anonymous block correctly reports:

Schema:     USR_01
table_name: ITEM

Update September 26, 2005

Robert Chin pointed out to me that name_resolve is only supported for procedures, functions and packages, but not for tables (http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9452). Although the documentation tells so, it still looks like it is perfectly capable of doing table name translations as well. However, metalink note 1008700.6 (dated March 16^th 1995 [!]) states that enhancement request 263534 had been filed which requests that tables be added as valid objects. I have no idea, if this request is already implemented or not.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger