-- 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, '.');

				-- DBMS_OUTPUT.PUT_LINE('No user "' || :owner || '" found in &DICT_LEVEL._USERS');
				-- DBMS_OUTPUT.PUT_LINE('Object name is "' || :owner || '".');
		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
     , DECODE(aa.object_name,
              NULL, 'No', 'Yes' ) granted
     , s.synonym_name
FROM   &DICT_LEVEL._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

-- vi: set ts=8: