|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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...
... 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 16th 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.