-- 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