While working in Datawarehousing - and with PL/SQL in particular, it is very handy to be able to resolve object owners for the purposes of clearly identifying which object you are actually performing SELECT, or DML operations on. It is also very handy for logging purposes - as it eliminates any doubt (such as a messed up synonym, etc.)
It is also nice to be able to resolve an object owner for a caller in an invoker's rights routine - so that the procedure operates on THEIR copy of an object - much like a SELECT statement would if they didn't qualify the table name.
So - for this reason - I developed RESOLVE_UTIL - a PL/SQL package which simplifies the DBMS_UTILITY.NAME_RESOLVE procedure and wraps it with very handy functions.
It also works across database links!
For example - if I want to see who owns the "EMP" table - I just run this query:
SELECT resolve_util.resolve_table_schema (p_table_identifier => 'EMP') AS emp_owner
FROM dual;
If I really want to see if "SCOTT.EMP" is a table in SCOTT's schema, and not a synonym pointing somewhere else - I can run:
SELECT resolve_util.resolve_table_qualified_name (p_table_identifier => 'SCOTT.EMP') AS x
FROM dual;
There are many more uses of this - which I will elaborate on later...
It has been tested in 11gR2 (11.2.0.3).
Anyway - here is the source code:
create or replace
FUNCTION "BUILD_STRING" (p_str IN VARCHAR2
, p1 IN VARCHAR2
, p2 IN VARCHAR2 DEFAULT NULL
, p3 IN VARCHAR2 DEFAULT NULL
, p4 IN VARCHAR2 DEFAULT NULL
, p5 IN VARCHAR2 DEFAULT NULL
, p6 IN VARCHAR2 DEFAULT NULL
, p7 IN VARCHAR2 DEFAULT NULL
, p8 IN VARCHAR2 DEFAULT NULL
, p9 IN VARCHAR2 DEFAULT NULL
, p10 IN VARCHAR2 DEFAULT NULL
, p_null_string IN VARCHAR2 DEFAULT '(null)'
, p_cleanup_extra_newlines IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2 AS
-- Constants
oracr CONSTANT VARCHAR2 (1) := CHR (10);
-- Variables
l_temp_str VARCHAR2 (4000);
l_replace_str_array dbms_sql.varchar2a;
BEGIN
l_temp_str := p_str;
-- Load the "P#" arguments into an array...
l_replace_str_array (1) := p1;
l_replace_str_array (2) := p2;
l_replace_str_array (3) := p3;
l_replace_str_array (4) := p4;
l_replace_str_array (5) := p5;
l_replace_str_array (6) := p6;
l_replace_str_array (7) := p7;
l_replace_str_array (8) := p8;
l_replace_str_array (9) := p9;
l_replace_str_array (10) := p10;
-- Loop through the input string - relacing any "%s" strings with its corresponding "P{number}" argument value...
FOR i IN 1 .. 10 LOOP
l_temp_str := REGEXP_REPLACE (l_temp_str, '%s', NVL (l_replace_str_array (i), p_null_string), 1, 1);
END LOOP;
-- Now replace any "\n" characters with the real new-line character...
l_temp_str := REPLACE (l_temp_str, '\n', oracr);
IF p_cleanup_extra_newlines THEN
l_temp_str := REGEXP_REPLACE (l_temp_str, '(' || CHR(10) || ')\1+', '\1');
END IF;
RETURN l_temp_str;
END build_string;
/
/* =========================================================================================== */
create or replace
PACKAGE "RESOLVE_UTIL"
AUTHID CURRENT_USER
AS
/*************************************************************************
* Program : resolve_util
* Version : 1.0
* Author : Philip Moore - Dunnhumby USA
* Date : 29-JAN-2013
* Purpose : This package contains utilies to help resolve
* Oracle object details, such as the schema,
* the database link, real object name, etc.
* It allows programmatic emulation of the SQL resolving engine.
* This package makes it easy for example to see who owns a Table
* or View - so you can constrain the "OWNER" column in the
* Data Dictionary views just by knowing the object name.
*
* Change History
* Date Programmer Description
* -----------------------------------------------------------------------
* 29-JAN-2013 Philip Moore Initial Version
*
*************************************************************************/
-- Constants
c_package_name CONSTANT VARCHAR2 (100) := 'RESOLVE_UTIL';
c_pkg_proc_or_function_ctxt CONSTANT PLS_INTEGER := 1;
c_table_view_mview_or_seq_ctxt CONSTANT PLS_INTEGER := 2;
c_type_ctxt CONSTANT PLS_INTEGER := 3;
c_synonym_ctxt CONSTANT PLS_INTEGER := 5;
c_procedure_top_level_ctxt CONSTANT PLS_INTEGER := 7;
c_function_top_level_ctxt CONSTANT PLS_INTEGER := 8;
c_package_ctxt CONSTANT PLS_INTEGER := 9;
c_index_ctxt CONSTANT PLS_INTEGER := 9;
-- Custom Types
TYPE object_record_type IS RECORD (
schema all_objects.owner%TYPE
, name all_objects.object_name%TYPE
, db_link all_db_links.db_link%TYPE
, object_number all_objects.object_id%TYPE
, object_type all_objects.object_type%TYPE
, qualified_object_name VARCHAR2 (100)
);
-- Global Variables
g_debug_mode BOOLEAN := FALSE;
-- Procedures and Functions
FUNCTION build_db_link_string (
p_db_link IN VARCHAR2
)
RETURN VARCHAR2;
PROCEDURE name_resolve (
name IN VARCHAR2
, CONTEXT IN NUMBER
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
, SCHEMA OUT VARCHAR2
, part1 OUT VARCHAR2
, part2 OUT VARCHAR2
, dblink OUT VARCHAR2
, part1_type OUT NUMBER
, object_number OUT NUMBER
);
/* ------------------------------------------------------------ */
FUNCTION resolve_object (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name, type, and schema...
FUNCTION resolve_object (
p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_schema (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.owner%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_type (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_type%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_name%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_db_link (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_db_links.db_link%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_number (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_id%TYPE;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_qualified_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
FUNCTION get_identifier_string (p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION resolve_table_schema (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_tables.owner%TYPE;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION resolve_table_qualified_name (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION get_table_identifier_string (p_table_name IN all_tables.table_name%TYPE
, p_table_schema IN all_tables.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2;
/* ------------------------------------------------------------ */
FUNCTION resolve_table_object (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name and schema...
FUNCTION resolve_table_object (
p_table_name IN all_objects.object_name%TYPE
, p_table_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type;
END RESOLVE_UTIL;
/
/* =========================================================================================== */
create or replace
PACKAGE BODY "RESOLVE_UTIL" AS
/*************************************************************************
* Program : resolve_util
* Version : 1.0
* Author : Philip Moore - Dunnhumby USA
* Date : 29-JAN-2013
* Purpose : This package contains utilies to help resolve
* Oracle object details, such as the schema,
* the database link, real object name, etc.
* It allows programmatic emulation of the SQL resolving engine.
* This package makes it easy for example to see who owns a Table
* or View - so you can constraint the "OWNER" column in the
* Data Dictionary views just by knowing the object name.
*
* Change History
* Date Programmer Description
* -----------------------------------------------------------------------
* 29-JAN-2013 Philip Moore Initial Version
*
*************************************************************************/
/* ------------------------------------------------------------ */
FUNCTION build_db_link_string (
p_db_link IN VARCHAR2
)
RETURN VARCHAR2 IS
-- Variables
l_return VARCHAR2 (100);
BEGIN
IF p_db_link IS NOT NULL THEN
l_return := '@' || LTRIM (p_db_link, '@');
END IF;
RETURN l_return;
END build_db_link_string;
/* ------------------------------------------------------------ */
PROCEDURE name_resolve (
name IN VARCHAR2
, CONTEXT IN NUMBER
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
, SCHEMA OUT VARCHAR2
, part1 OUT VARCHAR2
, part2 OUT VARCHAR2
, dblink OUT VARCHAR2
, part1_type OUT NUMBER
, object_number OUT NUMBER
) AS
-- Constants
c_program_name CONSTANT VARCHAR2 (100) := c_package_name || '.NAME_RESOLVE';
-- Variables
l_plsql_call VARCHAR2 (4000);
l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);
BEGIN
l_plsql_call :=
'BEGIN '
|| ' DBMS_UTILITY.NAME_RESOLVE'
|| l_db_link
|| ' ( name => :name'
|| ' , context => :context'
|| ' , schema => :schema'
|| ' , part1 => :part1'
|| ' , part2 => :part2'
|| ' , dblink => :dblink'
|| ' , part1_type => :part1_type'
|| ' , object_number => :object_number'
|| ' ); '
|| 'END;';
EXECUTE IMMEDIATE l_plsql_call
USING IN name
, IN CONTEXT
, OUT SCHEMA
, OUT part1
, OUT part2
, OUT dblink
, OUT part1_type
, OUT object_number;
IF g_debug_mode THEN
dbms_output.put_line (build_string (p_str => '%s -> DBMS_UTILITY.NAME_RESOLVE%s call result: '
|| '\n ( name /* IN */ => ''%s'''
|| '\n , context /* IN */ => %s'
|| '\n , schema /* OUT */ => ''%s'''
|| '\n , part1 /* OUT */ => ''%s'''
|| '\n , part2 /* OUT */ => ''%s'''
|| '\n , dblink /* OUT */ => ''%s'''
|| '\n , part1_type /* OUT */ => %s'
|| '\n , object_number /* OUT */ => %s'
|| '\n ); '
, p1 => c_program_name
, p2 => l_db_link
, p3 => name
, p4 => CONTEXT
, p5 => SCHEMA
, p6 => part1
, p7 => part2
, p8 => dblink
, p9 => part1_type
, p10 => object_number
, p_null_string => NULL
));
END IF;
END name_resolve;
/* ------------------------------------------------------------ */
FUNCTION get_object_type_context (
p_object_type IN all_objects.object_type%TYPE
)
RETURN PLS_INTEGER IS
-- Variables
l_return PLS_INTEGER;
BEGIN
l_return :=
CASE UPPER (TRIM (p_object_type))
WHEN 'PACKAGE' THEN c_pkg_proc_or_function_ctxt
WHEN 'PROCEDURE' THEN c_pkg_proc_or_function_ctxt
WHEN 'FUNCTION' THEN c_pkg_proc_or_function_ctxt
WHEN 'TABLE' THEN c_table_view_mview_or_seq_ctxt
WHEN 'VIEW' THEN c_table_view_mview_or_seq_ctxt
WHEN 'SEQUENCE' THEN c_table_view_mview_or_seq_ctxt
WHEN 'MATERIALIZED VIEW' THEN c_table_view_mview_or_seq_ctxt
WHEN 'TYPE' THEN c_type_ctxt
WHEN 'SYNONYM' THEN c_synonym_ctxt
WHEN 'INDEX' THEN c_index_ctxt
END;
RETURN l_return;
END get_object_type_context;
/* ------------------------------------------------------------ */
FUNCTION resolve_object (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type AS
-- Variables
l_context NUMBER;
l_schema_out all_objects.owner%TYPE;
l_part1_out VARCHAR2 (255);
l_part2_out VARCHAR2 (255);
l_db_link_out all_db_links.db_link%TYPE;
l_part1_type_out NUMBER;
l_object_number_out NUMBER;
l_return object_record_type;
l_db_link VARCHAR2 (100) := build_db_link_string (p_db_link => db_link);
l_sql_stmt VARCHAR2 (255);
BEGIN
name_resolve (name => p_object_identifier
, CONTEXT => get_object_type_context (p_object_type => p_object_type)
, db_link => db_link
, SCHEMA => l_schema_out
, part1 => l_part1_out
, part2 => l_part2_out
, dblink => l_db_link_out
, part1_type => l_part1_type_out
, object_number => l_object_number_out
);
l_return.SCHEMA := l_schema_out;
l_return.name := l_part1_out;
l_return.db_link := l_db_link_out;
l_return.object_number := l_object_number_out;
l_sql_stmt := 'SELECT object_type
FROM all_objects' || l_db_link || '
WHERE object_id = :l_object_number_out';
-- Get the Object Type if possible... (doesn't work if the object is in a remote database)
BEGIN
EXECUTE IMMEDIATE l_sql_stmt
INTO l_return.object_type
USING l_object_number_out;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_return.object_type := NULL;
END;
l_return.qualified_object_name := DBMS_ASSERT.enquote_name (str => l_return.SCHEMA
, capitalize => FALSE
)
|| '.'
|| DBMS_ASSERT.enquote_name (str => l_return.name
, capitalize => FALSE
)
|| l_db_link;
RETURN l_return;
END resolve_object;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name, type, and schema...
FUNCTION resolve_object (
p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type AS
BEGIN
RETURN resolve_object (p_object_identifier => DBMS_ASSERT.enquote_name (str => p_object_schema)
|| '.'
|| DBMS_ASSERT.enquote_name (str => p_object_name)
, p_object_type => p_object_type
, db_link => db_link
);
END resolve_object;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_schema (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.owner%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.owner%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.SCHEMA;
RETURN l_return;
END resolve_object_schema;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_type (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_type%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.object_type%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.object_type;
RETURN l_return;
END resolve_object_type;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_name%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.object_name%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.name;
RETURN l_return;
END resolve_object_name;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_db_link (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_db_links.db_link%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_db_links.db_link%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.db_link;
RETURN l_return;
END resolve_object_db_link;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_number (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_objects.object_id%TYPE AS
-- Variables
l_object_record object_record_type;
l_return all_objects.object_id%TYPE;
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.object_number;
RETURN l_return;
END resolve_object_number;
/* ------------------------------------------------------------ */
FUNCTION resolve_object_qualified_name (
p_object_identifier IN VARCHAR2
, p_object_type IN all_objects.object_type%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2 AS
-- Variables
l_object_record object_record_type;
l_return VARCHAR2 (100);
BEGIN
l_object_record := resolve_object (p_object_identifier => p_object_identifier
, p_object_type => p_object_type
, db_link => db_link
);
l_return := l_object_record.qualified_object_name;
RETURN l_return;
END resolve_object_qualified_name;
/* ------------------------------------------------------------ */
FUNCTION get_identifier_string (p_object_name IN all_objects.object_name%TYPE
, p_object_type IN all_objects.object_type%TYPE
, p_object_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2
IS
-- Variables
l_return VARCHAR2 (255);
l_object_record object_record_type;
BEGIN
l_object_record := resolve_object (p_object_name => p_object_name
, p_object_type => p_object_type
, p_object_schema => p_object_schema
, db_link => db_link
);
l_return := l_object_record.qualified_object_name;
RETURN l_return;
END get_identifier_string;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION resolve_table_schema (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN all_tables.owner%TYPE AS
BEGIN
RETURN resolve_object_schema (p_object_identifier => p_table_identifier
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_schema;
/* ------------------------------------------------------------ */
-- Also works for views
FUNCTION resolve_table_qualified_name (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2 AS
BEGIN
RETURN resolve_object_qualified_name (p_object_identifier => p_table_identifier
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_qualified_name;
/* ------------------------------------------------------------ */
-- Also works for views...
FUNCTION get_table_identifier_string (p_table_name IN all_tables.table_name%TYPE
, p_table_schema IN all_tables.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN VARCHAR2
IS
BEGIN
RETURN get_identifier_string (p_object_name => p_table_name
, p_object_type => 'TABLE'
, p_object_schema => p_table_schema
, db_link => db_link
);
END get_table_identifier_string;
/* ------------------------------------------------------------ */
FUNCTION resolve_table_object (
p_table_identifier IN VARCHAR2
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type
IS
BEGIN
RETURN resolve_object (p_object_identifier => p_table_identifier
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_object;
/* ------------------------------------------------------------ */
-- Overloaded to allow resolution by supplying the name and schema...
FUNCTION resolve_table_object (
p_table_name IN all_objects.object_name%TYPE
, p_table_schema IN all_objects.owner%TYPE
, db_link IN all_db_links.db_link%TYPE DEFAULT NULL
)
RETURN object_record_type
IS
BEGIN
RETURN resolve_object (p_object_name => p_table_name
, p_object_schema => p_table_schema
, p_object_type => 'TABLE'
, db_link => db_link
);
END resolve_table_object;
/* ------------------------------------------------------------ */
END resolve_util;
/
