How to export Oracle Virtual Private Database (a.k.a.: VPD, or "Row Level Security") policy creation statements

Hey crew,

Long time no post.

Have you ever had to drop an Oracle VPD policy from a table, and then realized that it isn't very easy to put it back?

Hopefully this little snippet will help with backing up the VPD policy definition so that you can easily add it back later.

I hope this is helpful:

-- Build the ADD policy statement from the dictionary
SELECT 'BEGIN
   DBMS_RLS.add_policy (object_schema         => ' || DBMS_ASSERT.enquote_literal (str => object_owner) || '
                      , object_name           => ' || DBMS_ASSERT.enquote_literal (str => object_name) || '
                      , policy_name           => ' || DBMS_ASSERT.enquote_literal (str => policy_name) || '
                      , function_schema       => ' || DBMS_ASSERT.enquote_literal (str => pf_owner) || '
                      , policy_function       => ' || DBMS_ASSERT.enquote_literal (str => CASE WHEN package IS NOT NULL
                                                                                               THEN package || '.'
                                                                                       END
                                                                                    || FUNCTION
                                                                               ) || '
                      , statement_types       => ' || DBMS_ASSERT.enquote_literal (str => LTRIM (RTRIM (CASE sel
                                                                                                            WHEN 'YES' THEN 'SELECT'
                                                                                                         END
                                                                                                      || ','
                                                                                                      || CASE ins
                                                                                                            WHEN 'YES' THEN 'INSERT'
                                                                                                         END
                                                                                                      || ','
                                                                                                      || CASE upd
                                                                                                            WHEN 'YES' THEN 'UPDATE'
                                                                                                         END
                                                                                                      || ','
                                                                                                      || CASE del
                                                                                                            WHEN 'YES' THEN 'DELETE'
                                                                                                         END
                                                                                                      , ','
                                                                                                       )
                                                                                                  , ','
                                                                                                   )
                                                                                   ) || '
                      , update_check          => ' || CASE WHEN chk_option = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , enable                => ' || CASE WHEN enable = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , static_policy         => ' || CASE WHEN static_policy = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , policy_type           => DBMS_RLS.' || policy_type || '
                      , long_predicate        => ' || CASE WHEN long_predicate = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , sec_relevant_cols     => ' || DBMS_ASSERT.enquote_literal (str => (SELECT listagg (dsrc.sec_rel_column, ',') WITHIN GROUP (ORDER BY dsrc.sec_rel_column ASC) AS col_list
                                                                                             FROM dba_sec_relevant_cols dsrc
                                                                                            WHERE dsrc.object_owner = dp.object_owner
                                                                                              AND dsrc.object_name = dp.object_name
                                                                                              AND dsrc.policy_name = dp.policy_name
                                                                                          )
                                                                                  ) || '
                      , sec_relevant_cols_opt => ' || CASE WHEN EXISTS (SELECT 1
                                                                          FROM dba_sec_relevant_cols dsrc
                                                                         WHERE dsrc.object_owner = dp.object_owner
                                                                           AND dsrc.object_name = dp.object_name
                                                                           AND dsrc.policy_name = dp.policy_name
                                                                           AND dsrc.column_option = 'ALL_ROWS'
                                                                       ) THEN 'DBMS_RLS.ALL_ROWS'
                                                                         ELSE 'NULL'
                                                      END || '
                       );
END;
/' AS stmt
  FROM dba_policies dp
 WHERE object_owner = '<>'
   AND object_name = '<>';

Take care!

Pivot/Unpivot Examples (with multi-column UNPIVOT)

Hey fellow database enthusiasts,


Long time no post...


I figured I would post some examples of "PIVOT" and "UNPIVOT" because I feel like the Oracle documentation doesn't give enough.


I've constructed some of my own - which includes a "multi-column" UNPIVOT operation as well.


Take these and try them on your own in your own database - see if you can apply the techniques used in the examples with real data as well...


Here goes:


/* ------------------------------------------------------------------- */
-- Pivot Example (rows-to-columns)
WITH row_based_table AS (
SELECT 1 AS ID, 'jack' AS code, 123.33 AS sales, 999 AS units FROM dual
UNION ALL
SELECT 1 AS ID, 'tom' AS code, 456.44 AS sales, 199 AS units FROM dual
UNION ALL
SELECT 1 AS ID, 'bob' AS code, 789.55 AS sales, 100 AS units FROM dual
)
SELECT *
  FROM row_based_table
PIVOT
(SUM (sales) AS sales
,SUM (units) AS units
FOR code IN ('jack' AS jack, 'tom' AS tom, 'bob' AS bob)
);
/* ------------------------------------------------------------------- */
-- Multi-Column Un-Pivot Example (columns-to-rows)
WITH column_based_table AS (
SELECT 1       AS ID
    , 'jack'   AS guy_a
    , 'jill'   AS gal_a
    --
    , 'tom'    AS guy_b
    , 'tanya'  AS gal_b
    --
    , 'bob'    AS guy_c
    , 'belinda'AS gal_c
  FROM dual
)
SELECT *
  FROM column_based_table
   UNPIVOT EXCLUDE NULLS ((guy, gal)
     FOR couple IN (
       (guy_a, gal_a) AS 'a couple'
     , (guy_b, gal_b) AS 'b couple'
     , (guy_c, gal_c) AS 'c couple'
    )
);
/* ------------------------------------------------------------------- */
-- Un-Pivot Example (columns-to-rows)
WITH column_based_table AS (
SELECT 1 AS ID, 'jack' AS A, 'tom' AS b, 'bob' AS c FROM dual
)
SELECT *
  FROM column_based_table
   UNPIVOT EXCLUDE NULLS (val
     FOR code IN (
       a AS 'a column'
     , b AS 'b column'
     , c AS 'c column'
    )
);


Take care!

Beware of empty partitions!

Hey crew,

Oracle partitioning is a great feature to use for your data warehouse - particularly for your fact table.  A common, best practice approach is to RANGE partition your fact table by a DATE column - choosing a range with a Year, Quarter, Month, Week, or Day interval.

One thing that we learned the "hard way" was that having a large number of empty partitions in our fact table(s) caused very poor performance in our Exadata queries that ran MUCH better in a case where there were no empty partitions.

As mentioned in my previous post - we typically copy only a few weeks of transaction data down to our development environments because we do not have nearly as much space or compute resources there to house/process it.

One mistake we used when exporting our fact table (while filtering down to subsets of dates) - using Oracle's Data Pump Export tool (expdp) - is that we exported the entire table DDL - meaning all partitions got created when we imported into development.

While all partitions were there - only a small number were actually populated.  We partition by DATE range - typically with partitions that hold one week of point-of-sale transactional data.

So - in our case - we had like 8 populated partitions, with over 100 empty ones in development (all 108 would be populated in Production).

We did all of the stuff you are supposed to do - we ensured that stats were up to date - but developers still complained that "Dev was too slow...  We must work in Production!".

I was immediately suspicious - Dev had way LESS data than Production - so even with reduced resources - it shouldn't be that bad...

While investigating SQL Monitor reports from Dev and comparing them to similar reports from Production - I noticed that in Development - the Cost Based Optimizer estimated VERY LOW row counts from the fact table - while Production got it much closer.

Our queries spanned multiple partitions - so they used global stats - which were accurate in both places.

What I deduced from this was that Oracle was actually taking the total row count of the table (Global stats) divided by the total number of partitions to "guess" the cardinality that would be returned from the each partition of the fact table.

In development - that formula had a much smaller numerator (due to smaller overall row count) - but a denominator that was just as large (108) as production's.

When I dropped the empty partitions - something magical happened - Oracle got the cardinality estimates much closer to reality - and performance picked up because some bad nested loop joins turned back into hash joins (as they should have been based upon the actual cardinality) - and queries performed in a manner consistent with Production.

I wanted something in my toolbox to help prevent this in the future - so I developed this script to search the partitions of the fact table for data, as well as to generate "ALTER TABLE x DROP PARTITION y;" commands to drop those empty partitions.  Please be VERY CAREFUL with this - test it in development to ensure it works properly for you...

-- Generate statements to drop empty partitions from the fact table(s)
WITH t AS (
SELECT   table_owner
       , table_name
       , partition_name
       , TO_NUMBER (EXTRACTVALUE (XMLTYPE (DBMS_XMLGEN.getxml ('SELECT COUNT(*) AS rows_exist FROM '
                                                             || DBMS_ASSERT.enquote_name (str => table_owner)
                                                             || '.'
                                                             || DBMS_ASSERT.enquote_name (str => table_name)
                                                             || ' PARTITION ('
                                                             || DBMS_ASSERT.enquote_name (str => partition_name)
                                                             || ') WHERE ROWNUM <= 1'
                                                              )
                                          )
                                , '/ROWSET/ROW/ROWS_EXIST'
                                 )
                   ) AS rows_exist
    FROM all_tab_partitions
   WHERE table_owner = 'WH'
     AND table_name IN ('POINT_OF_SALE_FACTS')
ORDER BY table_owner
       , table_name
       , partition_position
)
SELECT 'ALTER TABLE '
     || DBMS_ASSERT.enquote_name (str => table_owner)
     || '.'
     || DBMS_ASSERT.enquote_name (str => table_name)
     || ' DROP PARTITION '
     || DBMS_ASSERT.enquote_name (str => partition_name)
     || ';' AS stmt
    , t.*
FROM t
WHERE rows_exist = 0
;

 Basically - this script performs dynamic SQL from SQL (pretty cool) - using DBMS_XMLGEN.  It lets you "probe" each partition for data - it does NOT rely on stats which can get stale...

I hope this is helpful for your efforts in finding/dropping empty partitions...

God bless... 

Finding Contiguous Date Ranges in your Data - using Recursive SQL

Hey team,

I found myself needing to write a query to detect the contiguous date ranges available in my warehouse's Point of Sale fact data table (in Development).

While we have data for every day in Production, in Development we use a technique that only copies a few weeks from the same month of two consecutive years - this lets us calculate "Year-over-Year" growth - while limiting the data segment size.

In such cases - finding the MIN/MAX is not acceptible, because it will look like I have data for about 13 months - while in actuality I only have data for two months (April 2014 and April 2015 for example).

I decided not to "google" my problem because I wanted a brain-teaser SQL problem (I love those) - but I think I came up with something kind of cool - a recursive SQL solution using Oracle's CONNECT BY syntax.  I haven't yet tackled it with ANSI recursive SQL - but I plan to convert this approach soon.

Anyway - here goes:
WITH dates AS (
 SELECT DISTINCT
 TRUNC (date_id, 'DD') AS date_id
 FROM (SELECT TO_DATE ('01-APR-2014', 'DD-MON-YYYY') + LEVEL - 1 AS date_id
         FROM dual
        CONNECT BY LEVEL <= 60
       UNION ALL
       SELECT TO_DATE ('01-APR-2015', 'DD-MON-YYYY') + LEVEL - 1 AS date_id
         FROM dual
        CONNECT BY LEVEL <= 90
      ) fact_table /* Here we are simulating a fact table having unbalanced date ranges available */
 )
 , recursive_dates AS (
 SELECT dates.date_id
 /* CONNECT_BY_ISLEAF tells me that I am at the bottom of the hierarchy in the recursive join for a branch */
 , CONNECT_BY_ISLEAF AS date_id_is_leaf_flag
 /* CONNECT_BY_ROOT always returns the top node in the recursive join - where "LEVEL" = 1 */
 , CONNECT_BY_ROOT date_id AS root_date_id
 /* The LEVEL expression tells me how many levels deep I am in the recursive join */
 , LEVEL AS date_id_level
 FROM dates
 /* Here we are finding any dates which do NOT have a preceding date (one day prior) in our "DATES" data set */
 START WITH date_id NOT IN (SELECT date_id + 1
 FROM dates
 )
 /* Here we are recursively joining the data set to itself to find the next date that is one day in the future from the current row's date */
 CONNECT BY PRIOR date_id = /* NEXT */ (date_id - 1)
 )
 SELECT root_date_id AS min_date_id
 , date_id AS max_date_id
 FROM recursive_dates rd
 WHERE date_id_is_leaf_flag = 1
 ORDER BY min_date_id 
/

I hope this is helpful - can you think of other ways to tackle it?

Phil 

Oracle table (or query) CHECKSUM function

For a long time now, I've wanted table (or query) checksum functionality like the SQL Server "CHECKSUM_AGG" function (detailed here: http://technet.microsoft.com/en-us/library/ms188920.aspx).

I couldn't find anything "built-in" (if you do know of such a function, please comment below) - so I wrote my own.

Please note - I don't use MD5 - I use ORA_HASH - but I think this is "close enough for government work".  I multiply the output of ORA_HASH by the base-10 log of the column number in the result set (+1) so that:

SELECT 'X' AS x, 'Y' AS y FROM dual

and

SELECT 'Y' AS x, 'X' AS y FROM dual;

Do not have the same checksums...

Anyway - here goes - hopefully this works well for you:

 CREATE OR REPLACE FUNCTION marketplace."GET_QUERY_CHECKSUM" (
   p_query                   IN CLOB,
   p_output_checksum_query   IN INTEGER DEFAULT 0,
   p_use_result_cache_hint   IN INTEGER DEFAULT 0,
   p_primary_key_column_name IN all_tab_columns.column_name%TYPE DEFAULT NULL
)
   RETURN NUMBER
   AUTHID CURRENT_USER
IS
   /***********************************************************************************************
   Package :           GET_QUERY_CHECKSUM
   Author  :           Philip Moore
   Creation Date :     05-FEB-2014 A.D.
   Version :           1.0
   Purpose: This function will return a checksum for a query which is passed in.  It can be used
            to quickly determine if the result set of the query had changed.  There is a low
            risk for collisions (false positive match) - however the risk is quite low.
   ------------------------------------------------------------------------------------------------
   Modification History
   Date:            Version:     Modified by:        Modifications:
   ----------       --------     ------------        --------------------------------------------------
    ***********************************************************************************************/
   -- Variables
   l_theCursor                INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_descTbl                  DBMS_SQL.desc_tab;
   l_colCnt                   NUMBER;
   l_return                   NUMBER;
   l_hash_column_sql          CLOB;
   l_this_column_sql          VARCHAR2 (32767);
   l_primary_key_column_sql   VARCHAR2 (32767);
   l_sql_stmt                 CLOB;
   -- Private-scope procedure
   PROCEDURE cleanup
   IS
   BEGIN
      IF DBMS_SQL.is_open (c => l_theCursor)
      THEN
         DBMS_SQL.close_cursor (c => l_theCursor);
      END IF;
   END cleanup;
BEGIN
   -- Parse the Query
   DBMS_SQL.parse (c               => l_theCursor,
                   STATEMENT       => p_query,
                   language_flag   => DBMS_SQL.native);
   -- Get the column names, data types, etc..
   DBMS_SQL.describe_columns (c         => l_theCursor,
                              col_cnt   => l_colCnt,
                              desc_t    => l_descTbl);
   -- Close our cursor
   DBMS_SQL.close_cursor (c => l_theCursor);
  -- Build our HASH SQL...
  <>
   FOR i IN 1 .. l_colCnt
   LOOP
      l_this_column_sql :=
      ' + ('
         || TO_CHAR (LOG (10, i + 1))
         || ' * '
         || CASE WHEN l_descTbl (i).col_null_ok THEN 'COALESCE (' END
         || 'ORA_HASH ('
         || DBMS_ASSERT.enquote_name (str => l_descTbl (i).col_name)
         || ')'
         || CASE
               WHEN l_descTbl (i).col_null_ok
               THEN
                     ', ORA_HASH ('
                  || DBMS_ASSERT.enquote_literal (
                        str   => l_descTbl (i).col_name)
                  || '))'
            END
         || ')';
      l_hash_column_sql :=
            l_hash_column_sql
         || l_this_column_sql;
      IF (    p_primary_key_column_name IS NOT NULL
          AND l_descTbl (i).col_name = p_primary_key_column_name
         )
         OR
         (    p_primary_key_column_name IS NULL
          AND i = 1
         ) THEN
         l_primary_key_column_sql := LTRIM (l_this_column_sql, ' +');
      END IF;
   END LOOP column_loop;
   l_sql_stmt :=
         'SELECT '
      || CASE
            WHEN p_use_result_cache_hint = 1 THEN '/*+ result_cache */ '
         END
      || 'COUNT (*) + ROUND (COALESCE (SUM ('
      || l_primary_key_column_sql
      || ' * ('
      || LTRIM (l_hash_column_sql, ' +')
      || ')), 0), 0) AS hash_total FROM ('
      || p_query
      || ')';
   IF (p_output_checksum_query = 1)
   THEN
      DBMS_OUTPUT.PUT_LINE (SUBSTR (l_sql_stmt, 1, 32767));
   END IF;
   EXECUTE IMMEDIATE l_sql_stmt INTO l_return;
   RETURN l_return;
EXCEPTION
   WHEN OTHERS
   THEN
      cleanup ();
      RAISE;
END get_query_checksum;
/

Demo of usage:

SELECT get_query_checksum (p_query  => 'SELECT * FROM all_objects') AS checksum
FROM dual;

RESOLVE_UTIL - A way to programmatically resolve object details - such as schema, qualified identifier, etc.

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

I'm speaking at Oracle OpenWorld 2013!

I'll be speaking at Oracle OpenWorld 2013! My session: CON3412 - Extreme Performance with Very Big Data, Using Oracle Exadata X3-8 - will be on September Thursday, Sep 26, 11:00 AM - 12:00 PM PDT at Moscone South - 300. I hope to see you there!