Which
Resolves an object name following the same resolution path as SQL or PL/SQL:
e.g. given the name "EMP", figure out that it's a public synonym for SCOTT.EMPLOYEES.
Usage - use any of the following:
@which emp @which scott.emp @which em%
The report is in two parts. First it queries DBA_OBJECTS (or if that is not accessible it will automaticallly switch to ALL_OBJECTS) for any matching objects. The results include whether there is a synonym and whether you have been granted access. Then it calls DBMS_UTILITY.NAME_RESOLVE, which prior to 9i resolved the names of stored code (packages, procedures etc) but in recent versions has been extended to other database objects such as tables.
If you use a wildcard, the report will stop after querying DBA_OBJECTS.
It is possible for an object to appear in the first part of the results because it was found in DBA_OBJECTS, but not actually be resolvable, for example because it resides in another schema and you are missing a synonym.
-- Attempt to resolve an object name following the same resolution path as SQL or PL/SQL:
-- e.g. given the name "EMP", figure out that it's a public synonym for SCOTT.EMPLOYEES.
--
-- Note that SQL and PL/SQL may resolve objects differently due to definer/invoker rights, roles etc.
--
-- Three parts below:
-- 1. Parse multipart string e.g. "scott.somepkg.someproc" into owner, name and sub-element.
-- 2. Query the data dictionary to list out all objects matching the above.
-- 3. Call DBMS_UTILITY.NAME_RESOLVE and some increasingly contrived tests to figure out which it is.
--
-- William Robertson - www.williamrobertson.net
set term off
store set sqlplus_settings.sql replace
-- Use DBA_ views if possible:
col dict_level new_value dict_level
select 'ALL' as dict_level from dual;
select 'DBA' as dict_level from dba_users where rownum = 1;
-- Now &DICT_LEVEL contains either 'ALL' or 'DBA'
col dict_level clear
set term on
set autoprint off autotrace off
set timing off
set feed off
def object = &&1
col object_name hea "Name" format a30
col object_type hea "Type"
col granted format a8 hea "Granted?"
col synonym_name hea "Synonym"
col owner format a25
break on object_name skip1 on object_type skip1
var object_name varchar2(100)
var object_name varchar2(30)
var owner varchar2(30)
var sub varchar2(30)
var dblink varchar2(30)
exec :object_name := upper('&object')
-- define object
-- print :object_name
declare
nextpos binary_integer; -- discard
v_name varchar2(30);
v_test_user varchar2(30);
k_original_object constant varchar2(30) := :object_name;
begin
if :object_name like '%#%%' escape '#'
then
-- Wildcard was passed: skip this part:
return;
end if;
dbms_utility.name_tokenize
( :object_name
, :owner
, :object_name
, :sub
, :dblink
, nextpos );
-- Above call is not foolproof, e.g. X.Y may be parsed as owner X, object Y
-- when a more likely explanation is object X, sub-element Y (e.g. package.procedure):
if :owner = k_original_object and :object_name is null then
-- NAME_RESOLVE parsed "X" as "owner = X, object = NULL" -
-- More likely that we want "owner unspecified, object = X":
:object_name := k_original_object;
:owner := null;
elsif :owner is not null
and :dblink is null
then
begin
select username into v_test_user
from &dict_level._users
where upper(username) like :owner
and rownum = 1;
-- DBMS_OUTPUT.PUT_LINE('Found user "' || :owner || '" in &DICT_LEVEL._USERS.');
if :object_name is null
and :owner is not null
then
dbms_output.put_line('"' || :owner || '" is a schema name.');
else
dbms_output.put_line('object name is "' || :object_name || '".');
end if;
exception
when no_data_found then
-- There is no user matching this name: try shifting elements up:
-- (e.g. "X.Y" was parsed into "owner X, object Y", when
-- "object X, sub-element Y" e.g. "package.proc" more likely)
:dblink := :sub;
:sub := :object_name;
:object_name := :owner;
:owner := NULL;
:object_name := LTRIM(:owner || '.' || :object_name, '.');
end;
end if;
end;
/
-- Report matches from data dictionary using info parsed into owner/object/subobject etc above:
ttitle on left "Objects matching '&1':"
select --+ all_rows
distinct
da.object_name
, da.object_type
, da.owner
, case
when da.object_type not in ('TABLE','VIEW','SEQUENCE','OPERATOR','PROCEDURE','FUNCTION','PACKAGE','MATERIALIZED VIEW','TYPE') then null
when aa.object_name is not null then 'Yes'
else 'No'
end as granted
, case
-- when da.object_type not like '% PARTITION' and da.object_type != 'DIMENSION' then s.synonym_name
when da.object_type in ('TABLE','VIEW','SEQUENCE','OPERATOR','PROCEDURE','FUNCTION','PACKAGE','MATERIALIZED VIEW','TYPE') then s.synonym_name
end as synonym_name
from all_objects aa
, &dict_level._objects da
, &dict_level._synonyms s
where da.object_name like :object_name
and ( da.owner like :owner
or :owner is null )
and aa.object_name (+)= da.object_name
and aa.object_type (+)= da.object_type
and aa.owner (+)= da.owner
and s.table_name (+)= da.object_name
and s.table_owner (+)= da.owner
order by da.object_name, da.object_type, da.owner
/
ttitle off
set serverout on size 1000000
def table = &1
declare
v_objectname varchar2(70) default upper(:object_name); -- Might be a synonym to a differently-named table
v_objecttype varchar2(70);
v_tablename varchar2(70) := v_objectname;
v_db_link varchar2(30);
v_owner varchar2(30) := upper(:owner);
v_remote_owner varchar2(30);
v_found_yn varchar2(1) := 'n';
v_name_resolve_part1 varchar2(35);
v_name_resolve_part2 varchar2(35);
v_name_resolve_part1_type varchar2(35);
v_object_id number;
name_resolved boolean := false;
v_oracle_version integer;
invalid_type_for_subobjects exception;
pragma exception_init(invalid_type_for_subobjects, -06563);
no_such_object exception;
pragma exception_init(no_such_object, -06564);
incompatible_flag exception;
pragma exception_init(incompatible_flag, -04047);
cursor c_resolved_owner(cp_objectname varchar2) is
select /*+ all_rows */
nvl(s.table_owner, o.owner) owner
, nvl(s.table_name, o.object_name) name
, o.object_type
, s.db_link
, s.table_owner
from ( select object_name
, object_type
, owner
, decode
( owner || ' ' || object_type
, user || ' TABLE', 1
, user || ' VIEW', 1
, user || ' SYNONYM', 1
, 'PUBLIC SYNONYM', 2) ORDER_KEY
from &dict_lEVEL._objects
where object_name = cp_objectname
and object_type in ('TABLE','VIEW','SYNONYM')
and owner in (user, 'PUBLIC') ) o
, &dict_level._synonyms s
where s.synonym_name (+)= o.object_name
and s.owner (+)= o.owner
order by order_key;
cursor c_tables
( cp_owner varchar2
, cp_objectname varchar2 )
is
select 'Y'
from &dict_level._all_tables
where table_name = cp_objectname
and owner = cp_owner
union
select 'Y'
from &dict_level._views
where view_name = cp_objectname
and owner = cp_owner;
begin
select substr(version,1,instr(version,'.')) into v_oracle_version from v$instance;
-- DBMS_OUTPUT.PUT_LINE('1: owner = "' || v_owner || '", object_name = "' || v_objectname || '"');
if v_objectname like '%#%%' escape '#'
or v_objectname is null
then
-- Wildcard was passed, or no object exists (e.g. the object was actually a schema name): skip whole NAME_RESOLVE process.
return;
end if;
-- NB as of 9.2(?) DBMS_UTILITY.NAME_RESOLVE *DOES* find types which are not stored PL/SQL. In particular this is useful for
-- resolving synonyms, e.g. "CREATE SYNONYM BANANA FOR SYS.DUAL;" - BANANA is not resolved in 8i, OK in 9i.
-- Documentation for earlier versions recommended context = 1, while claiming it was unused (see below).
-- First try with DBMS_UTILITY.NAME_RESOLVE.
-- If unsuccessful, continue query of data dictionary:
begin
-- Exact meaning of 'context' param appears to be undocumented, but:
-- ORU-10034: context argument must be 1 or 2 or 3 or 4 or 5 or 6 or 7
-- (even though the only comment in dbmsutil.sql is "Must be an integer between 0 and 8".)
-- From experience it seems:
-- 1 Package, procedure or function
-- 2 Table (any type), view, materialized view or sequence
-- 7 Type (object or collection)
-- Not catered for:
-- Indexes
-- Users
-- Roles
-- Directories
-- Contexts
-- Policies
-- Queues
for i_context in 1..7 loop
exit when name_resolved = true;
begin
dbms_utility.name_resolve
( v_objectname
, i_context -- required but undocumented (see note above): loop through values 1-7.
, v_owner
, v_name_resolve_part1
, v_name_resolve_part2
, v_db_link
, v_name_resolve_part1_type
, v_object_id );
name_resolved := TRUE;
-- DBMS_OUTPUT.PUT_LINE ( 'Success using context ' || i_context );
exception
when incompatible_flag or no_such_object then null;
when invalid_type_for_subobjects then
-- e.g. my_object_type.method, when my_object_type has no type body
-- DBMS_OUTPUT.PUT_LINE('Discarding subobject');
:sub := null;
end;
end loop;
-- Examine what DBMS_UTILITY.NAME_RESOLVE captured, if anything:
-- (may need to take context into account - needs testing)
if not name_resolved then
raise no_such_object;
elsif v_name_resolve_part1_type = 2 then
v_objecttype := 'TABLE';
elsif v_name_resolve_part1_type = 4 then
v_objecttype := 'VIEW';
elsif v_name_resolve_part1_type = 5 then
v_objecttype := 'SYNONYM';
elsif v_name_resolve_part1_type = 6 then
v_objecttype := 'SEQUENCE';
elsif v_name_resolve_part1_type = 7 then
v_objecttype := 'PROCEDURE';
elsif v_name_resolve_part1_type = 8 then
v_objecttype := 'FUNCTION';
elsif v_name_resolve_part1_type = 9 then
if v_name_resolve_part2 is null then
v_objecttype := 'PACKAGE';
else
v_objecttype := 'PACKAGE ELEMENT';
-- DBMS_OUTPUT.PUT_LINE('v_name_resolve_part2 = ' || v_name_resolve_part2);
end if;
elsif v_name_resolve_part1_type = 12 then
v_objecttype := 'TRIGGER'; -- context = 3
elsif v_name_resolve_part1_type = 13 then
v_objecttype := 'TYPE';
else
-- DBMS_OUTPUT.PUT_LINE('v_name_resolve_part1_type = ' || v_name_resolve_part1_type);
-- DBMS_OUTPUT.PUT_LINE('v_name_resolve_part2 = ' || v_name_resolve_part2);
v_objecttype := 'UNKNOWN';
end if;
dbms_output.put_line
( rtrim
( v_owner || '.'
|| ltrim(v_name_resolve_part1 || '.', '.')
|| v_name_resolve_part2
, '.' )
|| ' (' || v_objecttype || ')'
);
-- Successful result found - end processing:
return;
exception
when no_such_object then
-- NAME_RESOLVE failed to resolve, e.g. because object is a table, view etc,
-- or does not exist:
name_resolved := false;
dbms_output.put_line('Could not resolve "' || ltrim(v_owner || '.' || v_objectname,'.') || '".');
end;
if v_oracle_version <= 8 then
-- Prior to 9i, NAME_RESOLVE only works for stored PL/SQL, e.g. not for synonyms.
-- NAME_RESOLVE does not resolve other users' private synonyms as of 9.2.0.5, e.g:
-- SCOTT creates synonym E for EMP,
-- then WILLIAM calls DBMS_UTILITY.NAME_RESOLVE for SCOTT.E.
-- Possibly code below should check for this speciic case only.
if v_owner is null then
if v_objectname like '%.%' then
v_owner := substr(v_objectname,1,instr(v_objectname,'.') -1);
v_objectname := substr(v_objectname,instr(v_objectname,'.') +1);
v_tablename := v_objectname;
if v_tablename like '%@%' then
v_db_link := substr(v_tablename,instr(v_tablename,'@') +1);
v_tablename := substr(v_tablename,1,instr(v_tablename,'@') -1);
v_remote_owner := v_owner;
end if;
else
open c_resolved_owner(v_objectname);
fetch c_resolved_owner into v_owner, v_tablename, v_objecttype, v_db_link, v_remote_owner;
close c_resolved_owner;
end if;
end if;
if v_owner is null then
dbms_output.put_line
( 'Table or view ''' || v_objectname ||
''' does not exist, or must be qualified with a schema name.' );
elsif v_db_link is not null then
dbms_output.put_line(v_owner || '.' || v_tablename || '@' || v_db_link);
else
-- Double-check in case just found invalid synonym above:
-- c_tables looks for a table named v_tablename owned by v_owner
open c_tables(v_owner, v_tablename);
fetch c_tables into v_found_yn;
dbms_output.put_line('Found tables matching ' || v_owner || ',' || v_tablename || '? ' || v_found_yn);
close c_tables;
if v_found_yn = 'Y' then
dbms_output.put(v_owner || '.' || v_tablename);
if v_objecttype != 'TABLE' then
dbms_output.put(' (' || v_objecttype || ')');
end if;
dbms_output.new_line;
else
if v_objecttype = 'SYNONYM' then
dbms_output.put_line
( 'Synonym ''' || v_objectname
|| ''' (' || v_owner || '.' || v_tablename || ') is no longer valid' );
else
dbms_output.put_line
( nvl(initcap(v_objecttype),'Object')
|| ' ' || v_owner || '.' || v_tablename
|| ' is not a table, view or synonym.' );
end if;
end if;
end if;
end if;
end;
/
set feed 1
prompt
@sqlplus_settings.sql
set term on
-- vim: ft=plsql