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...
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 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 OracleThis is an on Oracle article. The most current articles of this series can be found here.
|