SELECT resolve_util.resolve_table_schema (p_table_identifier => 'EMP') AS emp_owner
FROM dual;
SELECT resolve_util.resolve_table_qualified_name (p_table_identifier => 'SCOTT.EMP') AS x
FROM dual;
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;
/
