Wednesday, May 7, 2014

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            get_query_checksum (p_query                  IN CLOB
                           , p_output_checksum_query  IN INTEGER DEFAULT 0
                           , p_use_result_cache_hint  IN INTEGER DEFAULT 0
                            )
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_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...
   << column_loop >>
   FOR i IN 1 .. l_colCnt LOOP
      l_hash_column_sql := l_hash_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
                        || ')';
   END LOOP column_loop;

   l_sql_stmt := 'SELECT '
              || CASE WHEN p_use_result_cache_hint = 1
                         THEN '/*+ result_cache */ '
                 END
              || 'COUNT (*) + ROUND (COALESCE (SUM ('
              || 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;

Thursday, October 3, 2013

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

Friday, September 6, 2013

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!

Tuesday, June 22, 2010

Tom Kyte's Pro*C "array_flat" CSV file Unloader - with enhancements

Hi all,

It's been a long time since I've posted (I've been a lazy blogger).

Recently I had the need to unload a CSV file from a rather complex query in Oracle 10g Release 2. I just could not find a SQL*PLus solution that suited me, so I stumbled upon Tom Kyte's "array_flat" solution - found here in its orginal glory:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:459020243348

Tom's solution uses Pro*C code to connect to Oracle and then dump the output of a query to the stdout, while dumping the query text and column headings to the stderr.

Tom's solution of course is the bomb, but I wanted to improve upon it by adding the ability to change the delimiter character, and optionally specify an enclosure character (such as double-quote for example), and also have the ability to designate what should be output in the case of NULL values.

I'm VERY new to Pro*C programming, and I suspect others are too - so I'm posting in hopes that this helps some other poor souls that are Pro*C newbs also.

Ok - so here's the modified code - shamelessly taken from Tom's code found in the URL above - and modified to add the enhancements mentioned earlier:

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <stdlib.h>

#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

static char * USERID = NULL;
static char * SQLSTMT = NULL;
static char * ARRAY_SIZE = "10";
static char * DELIMITER = ",";
static char * ENCLOSURE = "\"";
static char * NULL_STRING = NULL;

#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void sqlclu();



static void die( char * msg )
{
fprintf( stderr, "%s\n", msg );
exit(1);
}


/*
this array contains a default mapping
I am using to constrain the
lengths of returned columns. It is mapping,
for example, the Oracle
NUMBER type (type code = 2) to be 45 characters
long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };


static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;

for( i = 1; i < argc; i++ )
{
if ( !strncmp( argv[i], "userid=", 7 ) )
USERID = argv[i]+7;
else
if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
SQLSTMT = argv[i]+8;
else
if ( !strncmp( argv[i], "arraysize=", 10 ) )
ARRAY_SIZE = argv[i]+10;
else
if ( !strncmp( argv[i], "delimiter=", 10 ) )
DELIMITER = argv[i]+10;
else
if ( !strncmp( argv[i], "enclosure=", 10 ) )
ENCLOSURE = argv[i]+10;
else
if ( !strncmp( argv[i], "null_string=", 12 ) )
NULL_STRING = argv[i]+12;
else
{
fprintf( stderr,
"usage: %s %s %s %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN> ",
"delimiter=x ",
"enclosure=x ",
"null_string=x ");
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
fprintf( stderr,
"usage: %s %s %s %s %s %s\n",
argv[0],
"userid=xxx/xxx sqlstmt=query ",
"arraysize=<NN> ",
"delimiter=x ",
"enclosure=x ",
"null_string=x ");
exit(1);
}
}

static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

fprintf(stderr,"\nORACLE error detected:");
fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}



static SQLDA * process_1(char * sqlstmt, int array_size, char * delimiter, char * enclosure )
{
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;

fprintf( stderr, "Unloading '%s'\n", sqlstmt );
fprintf( stderr, "Array size = %d\n", array_size );


EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;

if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for select descriptor." );

select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return NULL;

if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp =
sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
== NULL )
die( "Cannot allocate memory for descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;

for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) *
array_size );

for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]),
&(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] <
sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;

select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] *
array_size );

for( j = MAX_VNAME_LEN-1;
j > 0 && select_dp->S[i][j] == ' ';
j--);
fprintf (stderr,
"%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
fprintf( stderr, "\n" );


EXEC SQL OPEN C;
return select_dp;
}


static void process_2( SQLDA * select_dp, int array_size, char * delimiter, char * enclosure, char * null_string )
{
int last_fetch_count;
int row_count = 0;
short ind_value;
char * char_ptr;
int i,
j;

for ( last_fetch_count = 0;
;
last_fetch_count = sqlca.sqlerrd[2] )
{
EXEC SQL FOR :array_size FETCH C
USING DESCRIPTOR select_dp;

for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
for (i = 0; i < select_dp->F; i++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] +
(j*select_dp->L[i]);

printf( "%s%s%s%s", i?delimiter:"",
enclosure,
ind_value?null_string:char_ptr,
enclosure );
}
row_count++;
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}

sqlclu(select_dp);

EXEC SQL CLOSE C;

EXEC SQL COMMIT WORK;
fprintf( stderr, "%d rows extracted\n", row_count );
}



main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;


process_parms( argc, argv );

/* Connect to ORACLE. */
vstrcpy( oracleid, USERID );

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

EXEC SQL CONNECT :oracleid;
fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",
oracleid.arr);

EXEC SQL ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE), DELIMITER, ENCLOSURE );
process_2( select_dp , atoi(ARRAY_SIZE), DELIMITER, ENCLOSURE, NULL_STRING );

/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}



Ok - now you may ask - how the heck do I compile the thing? This is where I had my trouble as well.

Well - Here are the steps I took to get it to compile and working:
1) Save the code to a file - in my case I chose "unloader.pc"
2) Create a folder in your home directory on the UNIX server where you are going to compile this code. For example: "mkdir /home/phil/unloader".
3) FTP the file in ascii mode to the UNIX server directory created in step 2 above. Make sure that the server has the Oracle client installed. In my case I used Oracle 10g Release 2 client (10.2.0.3). Make sure you've run ". oraenv" to have all of your Oracle environment variables set up!
4) Copy the contents of directory $ORACLE_HOME/precomp/demo/proc/ to my directory created in step 2 - example unix command:
"cp $ORACLE_HOME/precomp/demo/proc/*.* /home/phil/unloader"
5) Modify your personal copy of the "demo_proc.mk" file - by adding our new Pro*C file to the list defined by the keyword "SAMPLES" - like so:


# SAMPLES is a list of the c proc sample programs.
# CPPSAMPLES is a list of the c++ proc sample programs.
# OBJECT_SAMPLES is a list of the proc sample programs using the new
# type features. You must install ott in addition to proc to run some
# of those samples.
SAMPLES=unloader sample1 sample2 sample3 sample4 sample6 sample7 sample8 \
sample9 sample10 sample11 sample12 oraca sqlvcp cv_demo \
ansidyn1 ansidyn2 cpdemo1 cpdemo2 scdemo1 scdemo2



6) Set your "LD_LIBRARY_PATH" environment variable to include: "$ORACLE_HOME/lib" (if it doesn't already have it) - you can do that like this:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

7) Now you're ready to compile - just issue this command:
make -f ./demo_proc.mk unloader

After it completes (you may get warnings - but this is ok) - you should have an executable file called "unloader" with no extension. If you have this file - your compile worked - and you should be ready for execution.
-------------------------------

Now for some execution examples:

Example 1 - Unload a simple query against the dual table:

[phil@unixserver:/home/phil/unloader]
$ ./unloader userid="scott/tiger@db" sqlstmt="select * from dual" arraysize=100 \
> delimiter="|" enclosure='"' null_string="(null)"

Connected to ORACLE as user: scott/tiger@db

Unloading 'select * from dual'
Array size = 100
DUMMY
"X"
1 rows extracted



Example 2 - Unload a bigger SQL Query from a file - send the data to one file - and the query and column headings to another file:
Step 1) Store our query in a file - like so:
echo "SELECT 1 AS num_field, 'ABC' AS text_field FROM dual" > ./query.sql
Step 2) Run this command:

[phil@unixserver:/home/phil/unloader]
$ ./unloader userid="scott/tiger@db" sqlstmt="`cat ./query.sql`" \
> arraysize=100 delimiter="|" enclosure='"' null_string="(null)" 1>./stdout.csv 2>./stderr.txt


Here is the output from stdout.csv:
[phil@unixserver:/home/phil/unloader]
$ cat stdout.csv
"1"|"ABC"


Here is the output from stderr.txt:
[phil@unixserver:/home/phil/unloader]
$ cat stderr.txt

Connected to ORACLE as user: scott/tiger@db

Unloading 'SELECT 1 AS num_field, 'ABC' AS text_field FROM dual'
Array size = 100
NUM_FIELD,TEXT_FIELD
1 rows extracted



-------------------

I hope this has been useful. I know that Pro*C can be intimidating - hopefully this example can make it a little easier for you. I am still very new, and I know there is probably a WAY better way to compile the Pro*C code - but hey - this worked!

This solution was tested on an HP-UX 64-bit Itanium server - and worked with both Oracle 9.2 and 10.2 Databases.

Good luck, and God bless!.

Phil

Wednesday, July 22, 2009

How to get the name of the Partition within a table that a ROWID comes from...

Oracle's partitioning feature is extremely handy for allowing you to break up a huge table into smaller, more manageable pieces.

I use it extensively for Datawarehousing - where it allows one to add and remove fact data partitioned by time/date very efficiently.

What is hard, however - is to find out which partition a particular row comes from within a table. Well - not any more. Just create this handy little function to get the name of the partition that any row comes from (using the "ROWID" pseudocolumn).

Here goes:

CREATE OR REPLACE FUNCTION get_partition_name_for_rowid (
p_rowid IN ROWID)
RETURN VARCHAR2 AS
-- Variables
l_all_objects_rec all_objects%ROWTYPE;
l_partition_name all_tab_partitions.partition_name%TYPE;
-- Custom Exceptions
rowid_not_from_a_partition EXCEPTION;
BEGIN
SELECT /*+ FIRST_ROWS */
*
INTO l_all_objects_rec
FROM all_objects
WHERE data_object_id = dbms_mview.pmarker (p_rowid);

IF l_all_objects_rec.object_type = 'TABLE PARTITION' THEN
l_partition_name := l_all_objects_rec.subobject_name;
ELSE
RAISE rowid_not_from_a_partition;
END IF;

RETURN l_partition_name;
EXCEPTION
WHEN rowid_not_from_a_partition THEN
raise_application_error (-20101
, build_string (p_input_string => 'ROWID: %s is in table: %s - which is not partitioned.'
, str1 => p_rowid
, str2 => l_all_objects_rec.object_name));
WHEN OTHERS THEN
RAISE;
END get_partition_name_for_rowid;
/




Just run this SQL to test it:

CREATE TABLE part_table
(partition_key INTEGER
, some_other_column VARCHAR2 (30)
)
PARTITION BY RANGE (partition_key)
( PARTITION p1 VALUES LESS THAN (50)
, PARTITION p2 VALUES LESS THAN (MAXVALUE)
)
;

INSERT INTO part_table
(partition_key, some_other_column)
SELECT ROWNUM
, TO_CHAR (ROWNUM)
FROM all_objects
WHERE ROWNUM <= 100;

SELECT get_partition_name_for_rowid (ROWID) AS partition_name
, partition_key
, some_other_column
FROM part_table
WHERE partition_key BETWEEN 47 AND 52;



Semper Fidelis

Friday, July 10, 2009

Handy Function for parsing strings in PL/SQL!

One common function needed for PL/SQL is some sort of string parsing solution. For instance, if you want to parse out the "3rd" position of a string delimited by an "/" character.

My buddy Todd had written a handy function in Visual Basic for Applications, and even converted it for use in Oracle. After I saw Tom Kyte use a combination of INSTR and SUBSTR to parse strings, I rewrote Todd's approach to perform a little better.

Here's the code in case you are interested in this handy little string function:

CREATE OR REPLACE FUNCTION parsestring (
p_string IN VARCHAR2
, p_delimiter IN VARCHAR2
, p_position IN INTEGER
)
/**************************************************************
Function: parsestring

Version: 2.0

Author: Philip Moore - (version 2.0)

Original Author: Todd Ward - (version 1.0)

Date: 23-JAN-2006

Description: This function's purpose is to return
the "p_position"th portion of the string
"p_string", delimited by "p_delimiter".

Example call: parsestring('a/b/c', '/', 2) Returns: 'b'
**************************************************************/
RETURN VARCHAR2
AUTHID CURRENT_USER
PARALLEL_ENABLE
DETERMINISTIC
AS
-- Variables
v_txt VARCHAR2 (32767);
BEGIN
v_txt := p_delimiter || p_string || p_delimiter;
RETURN SUBSTR (v_txt
, INSTR (v_txt
, p_delimiter
, 1
, p_position
) + LENGTH (p_delimiter)
, INSTR (v_txt
, p_delimiter
, 1
, p_position + 1
) - INSTR (v_txt
, p_delimiter
, 1
, p_position
) - LENGTH (p_delimiter)
);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END parsestring;
/

SHOW ERRORS;



Now - note that this will not perform as well as using the SUBSTR and INSTR combination of functions shown in the source code itself - because of the PL/SQL context switches when run in the SQL engine. But I personally think that it is MUCH easier to remember the syntax for calling "PARSESTRING" than for calling the complex SUBSTR/INSTR combination.

I've used this function many thousands of times over the years - and I believe that you will find it handy as well.

Good luck!

Tuesday, June 23, 2009

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i

I really love using the REGEXP_REPLACE function and REGEXP_LIKE operator in Oracle 10G - and have been spoiled by their ease of use as a result.

One of my clients, however - is sort of "stuck" on Oracle 9iR2 due to regression testing cost requirements of an upgrade. The problem is that we still have to do development on the 9iR2 database - and I REALLY want my regular expressions!

So - I figured that I would take a stab at writing my own REGEXP_REPLACE and REGEXP_LIKE functions.

Here's the source code - in case you want the same functionality in Oracle 9iR2:


create or replace
FUNCTION regexp_replace (
source_char IN VARCHAR2
, pattern IN VARCHAR2
, replace_string IN VARCHAR2
, POSITION IN PLS_INTEGER DEFAULT 1
, occurrence IN PLS_INTEGER DEFAULT 0
, match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 AS
/*************************************************************************
* Program : regexp_replace
* Version : 1.0
* Author : Philip Moore
* Date : 20-JUN-2009 Anno Domini
* Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2
* Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
*************************************************************************/
-- Variables
l_source_string VARCHAR2 (32767);
l_temp_string VARCHAR2 (32767);
l_flags VARCHAR2 (10);
l_occurrence PLS_INTEGER;
l_end_of_pattern_pos PLS_INTEGER;
l_string_pos PLS_INTEGER;
BEGIN
-- Substr the source_char to start at the position specified
l_source_string := SUBSTR (source_char, POSITION);

-- Set up the flags argument
IF occurrence = 0 THEN
l_flags := 'g';
ELSE
l_flags := NULL;
END IF;

l_flags := l_flags || match_parameter;

-- Now replace the regular expression pattern globally if "g"
IF INSTR (l_flags, 'g') > 0 THEN
owa_pattern.CHANGE (line => l_source_string
, from_str => pattern
, to_str => replace_string
, flags => l_flags);
-- Not a global replace - loop until the "occurrence"th occurrence is replaced...
ELSE
l_string_pos := 0;
l_occurrence := 0;
WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP
l_string_pos := l_string_pos + 1;

l_end_of_pattern_pos := owa_pattern.amatch (line => l_source_string
, from_loc => l_string_pos
, pat => pattern
, flags => match_parameter);

IF l_end_of_pattern_pos != 0 THEN
l_occurrence := l_occurrence + 1;
END IF;
END LOOP;

IF l_occurrence = occurrence THEN
l_temp_string := SUBSTR (l_source_string
, l_string_pos
, (l_end_of_pattern_pos - l_string_pos));
owa_pattern.CHANGE (line => l_temp_string
, from_str => pattern
, to_str => replace_string
, flags => l_flags);
l_source_string := SUBSTR (l_source_string
, 1
, l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);
END IF;
END IF;

-- Piece the string back together if needed...
IF POSITION > 1 THEN
l_source_string := SUBSTR (source_char
, 1
, (POSITION - 1)) || l_source_string;
END IF;

RETURN l_source_string;
END regexp_replace;
/


CREATE OR REPLACE FUNCTION regexp_like (source_char IN VARCHAR2
, pattern IN VARCHAR2
, match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
/*************************************************************************
* Program : regexp_like
* Version : 1.0
* Author : Philip Moore
* Date : 20-JUN-2009 Anno Domini
* Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2
* Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
*************************************************************************/
-- Variables
l_return INTEGER;
BEGIN
IF owa_pattern.match (line => source_char
, pat => pattern
, flags => match_parameter) THEN
l_return := 1;
ELSE
l_return := 0;
END IF;

RETURN l_return;
END regexp_like;
/




The Functions in Action:
Here's a demo of my new Oracle 9iR2 REGEXP_REPLACE function:

SELECT regexp_replace ('5138675309', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS phone 
FROM v$version
WHERE banner = 'Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production';

Result:
PHONE
----------------
(513) 867-5309




Some Watchouts:
The "OWA_PATTERN" package isn't as powerful as the built-in REGEXP% functions that Oracle 10g Provides - for example - it doesn't support the "" OR operator. It is also much slower than those natively built-in 10g functions - so don't use these to find the last digit in pi :)

I hope these are helpful in your "stuck in Oracle 9i" endeavors!

Special thanks to Greg Houston's http://formatmysourcecode.blogspot.com/ for helping me format the code for blogger!