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!

DBMS_METADATA Across Database Links!

As a Data Modeler and Developer, I've always wanted a very easy way to get Table/Function/Procedure/View DDL SQL from a remote database - via Database Link.



The DBMS_METADATA.GET_DDL function is quite handy for getting DDL in the SAME database - but it isn't quite so easy to use to get DDL from a remote database.



So - in the spirit of the USMC - I chose to adapt, improvise, and overcome - by writing my own interface to DBMS_METADATA - called "REMOTE_DBMS_METADATA".



If you want the source code - here goes:

create or replace PACKAGE boolean_pkg
IS
   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN;

   FUNCTION true_value
      RETURN VARCHAR2;

   FUNCTION false_value
      RETURN VARCHAR2;
END boolean_pkg;
/

SHOW ERRORS;


create or replace PACKAGE BODY boolean_pkg
IS
   c_true    CONSTANT VARCHAR2 (5) := 'TRUE';
   c_false   CONSTANT VARCHAR2 (5) := 'FALSE';

/***************************************************/
   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      IF boolean_in
      THEN
         RETURN c_true;
      ELSIF NOT boolean_in
      THEN
         RETURN c_false;
      ELSE
         RETURN NULL;
      END IF;
   END bool_to_str;

/***************************************************/
   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      IF string_in = c_true
      THEN
         RETURN TRUE;
      ELSIF string_in = c_false
      THEN
         RETURN FALSE;
      ELSE
         RETURN NULL;
      END IF;
   END str_to_bool;

/***************************************************/
   FUNCTION true_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_true;
   END true_value;

   FUNCTION false_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_false;
   END false_value;
/***************************************************/

END boolean_pkg;
/

SHOW ERRORS;


create or replace PACKAGE REMOTE_DBMS_METADATA AS
/*************************************************************************
 * Program  : remote_dbms_metadata
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This package provides access to DDL for objects in a remote
              Oracle database via Database Links.
 * Warnings : This package has only been tested in Oracle 9iR2 and 10gR2.
 *************************************************************************/

-- Types
   TYPE session_transform_param_rec IS RECORD (parameter_name VARCHAR2 (50)
                                             , value_datatype PLS_INTEGER
                                             , varchar2_value VARCHAR2 (50)
                                             , boolean_value  BOOLEAN
                                             , number_value   NUMBER);

   TYPE session_transform_params_type IS TABLE OF session_transform_param_rec INDEX BY VARCHAR2 (50);
   TYPE db_link_session_transform_type IS TABLE OF session_transform_params_type INDEX BY all_db_links.db_link%TYPE;

-- Global Constants
   c_varchar2_type CONSTANT PLS_INTEGER := 1;
   c_boolean_type CONSTANT PLS_INTEGER := 2;
   c_number_type CONSTANT PLS_INTEGER := 3;

-- Global Variables
   g_session_transform_params db_link_session_transform_type;

-- Custom Exceptions
   handle_not_open                    EXCEPTION;
   PRAGMA EXCEPTION_INIT (handle_not_open, -31600);

-- Procedures and Functions
   FUNCTION OPEN (
      db_link       IN   VARCHAR2 DEFAULT NULL
    , object_type   IN   VARCHAR2
    , version       IN   VARCHAR2 DEFAULT 'COMPATIBLE'
    , model         IN   VARCHAR2 DEFAULT 'ORACLE'
   )
      RETURN NUMBER;

   PROCEDURE dbms_metadata_close (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
   );

   PROCEDURE set_count (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
    , value     IN   NUMBER
   );

   PROCEDURE set_filter (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
    , name      IN   VARCHAR2
    , value     IN   VARCHAR2
   );

   PROCEDURE set_filter (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
    , name      IN   VARCHAR2
    , value     IN   BOOLEAN
   );

   FUNCTION add_transform (
      db_link    IN   VARCHAR2 DEFAULT NULL
    , handle     IN   NUMBER
    , name       IN   VARCHAR2
    , encoding   IN   VARCHAR2 DEFAULT NULL
   )
      RETURN NUMBER;

   PROCEDURE set_transform_param (
      db_link            IN   VARCHAR2 DEFAULT NULL
    , transform_handle   IN   NUMBER
    , name               IN   VARCHAR2
    , value              IN   VARCHAR2
   );

   PROCEDURE set_transform_param (
      db_link            IN   VARCHAR2 DEFAULT NULL
    , transform_handle   IN   NUMBER
    , name               IN   VARCHAR2
    , value              IN   BOOLEAN DEFAULT TRUE
   );

   FUNCTION fetch_ddl_text (
      db_link   IN       VARCHAR2 DEFAULT NULL
    , handle    IN       NUMBER
    , partial   OUT      NUMBER
   )
      RETURN VARCHAR2;

   FUNCTION fetch_clob (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
   )
      RETURN CLOB;
   
   FUNCTION fetch_ddl_clob (
         db_link   IN   VARCHAR2 DEFAULT NULL
       , handle    IN   NUMBER
      )
      RETURN CLOB;

   FUNCTION fetch_ddl (
         db_link   IN   VARCHAR2 DEFAULT NULL
       , handle    IN   NUMBER
      )
      RETURN ku$_ddls;

   PROCEDURE set_default_table_transforms (
      db_link                IN   VARCHAR2 DEFAULT NULL
    , table_transform_handle IN   NUMBER
   );
   
   PROCEDURE set_default_index_transforms (
      db_link                IN   VARCHAR2 DEFAULT NULL
    , index_transform_handle IN   NUMBER
   );

   FUNCTION get_ddl (db_link   IN       VARCHAR2 DEFAULT NULL
                   , object_type     IN  VARCHAR2
                   , name            IN  VARCHAR2
                   , schema          IN  VARCHAR2 DEFAULT NULL
                   , version         IN  VARCHAR2 DEFAULT 'COMPATIBLE'
                   , model           IN  VARCHAR2 DEFAULT 'ORACLE'
                   , transform       IN  VARCHAR2 DEFAULT 'DDL') 
     RETURN CLOB;

   FUNCTION get_dependent_ddl (
         db_link              IN   VARCHAR2 DEFAULT NULL
       , object_type          IN   VARCHAR2
       , base_object_name     IN   VARCHAR2
       , base_object_schema   IN   VARCHAR2 DEFAULT NULL
       , version              IN   VARCHAR2 DEFAULT 'COMPATIBLE'
       , model                IN   VARCHAR2 DEFAULT 'ORACLE'
       , transform            IN   VARCHAR2 DEFAULT 'DDL'
       , object_count         IN   NUMBER DEFAULT 10000)
      RETURN CLOB;
      
END REMOTE_DBMS_METADATA;
/

SHOW ERRORS;


create or replace
PACKAGE BODY remote_dbms_metadata AS
/*************************************************************************
 * Program  : remote_dbms_metadata
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This package provides access to DDL for objects in a remote
              Oracle database via Database Links.
 * Warnings : This package has only been tested in Oracle 9iR2 and 10gR2.
 *************************************************************************/

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

/* ------------------------------------- */
   FUNCTION open (
      db_link       IN   VARCHAR2 DEFAULT NULL
    , object_type   IN   VARCHAR2
    , version       IN   VARCHAR2 DEFAULT 'COMPATIBLE'
    , model         IN   VARCHAR2 DEFAULT 'ORACLE'
   )
      RETURN NUMBER IS
      -- Variables
      l_plsql_call                       VARCHAR2 (4000);
      l_return                           NUMBER;
      l_db_link                          VARCHAR2 (100) := build_db_link_string (p_db_link      => db_link);
   BEGIN
      l_plsql_call :=
            'BEGIN '
         || '     :l_return := DBMS_METADATA.OPEN'
         || l_db_link
         || ' (object_type => :object_type '
         || ', version     => :version '
         || ', model       => :model '
         || ' ); '
         || 'END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING OUT    l_return
                      , IN     object_type
                      , IN     version
                      , IN     model;

      RETURN l_return;
   END open;

/* ------------------------------------- */
   PROCEDURE dbms_metadata_close (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
   ) IS
      -- 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_METADATA.CLOSE' || l_db_link || ' (handle => :handle); END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING IN handle;
   END dbms_metadata_close;

/* ------------------------------------- */
   PROCEDURE set_count (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
    , value     IN   NUMBER
   ) IS
      -- 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_METADATA.SET_COUNT' || l_db_link || ' (handle => :handle, value  => :value); END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING IN handle, IN value;
   END set_count;

/* ------------------------------------- */
   PROCEDURE set_filter (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
    , name      IN   VARCHAR2
    , value     IN   VARCHAR2
   ) IS
      -- 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_METADATA.SET_FILTER'
         || l_db_link
         || ' (handle => :handle '
         || ', name   => :name '
         || ', value  => :value '
         || '); '
         || 'END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING IN handle
                      , IN name
                      , IN value;
   END set_filter;

/* ------------------------------------- */
   PROCEDURE set_filter (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
    , name      IN   VARCHAR2
    , value     IN   BOOLEAN
   ) IS
      -- 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_METADATA.SET_FILTER'
         || l_db_link
         || ' (handle => :handle '
         || ', name   => :name '
         || ', value  => '
         || boolean_pkg.bool_to_str (boolean_in      => value)
         || '); '
         || 'END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING IN handle, IN name;
   END set_filter;

/* ------------------------------------- */
   FUNCTION add_transform (
      db_link    IN   VARCHAR2 DEFAULT NULL
    , handle     IN   NUMBER
    , name       IN   VARCHAR2
    , encoding   IN   VARCHAR2 DEFAULT NULL
   )
      RETURN NUMBER IS
      -- Variables
      l_plsql_call                       VARCHAR2 (4000);
      l_return                           NUMBER;
      l_db_link                          VARCHAR2 (100) := build_db_link_string (p_db_link      => db_link);
   BEGIN
      l_plsql_call :=
            'BEGIN '
         || '   :l_return := DBMS_METADATA.ADD_TRANSFORM'
         || l_db_link
         || ' (handle => :handle '
         || ', name   => :name '
         || ', encoding => :encoding '
         || '); '
         || 'END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING OUT    l_return
                      , IN     handle
                      , IN     name
                      , IN     encoding;

      RETURN l_return;
   END add_transform;

/* ------------------------------------- */
   PROCEDURE set_transform_param (
      db_link            IN   VARCHAR2 DEFAULT NULL
    , transform_handle   IN   NUMBER
    , name               IN   VARCHAR2
    , value              IN   VARCHAR2
   ) IS
      -- Variables
      l_plsql_call                       VARCHAR2 (4000);
      l_db_link                          VARCHAR2 (100) := build_db_link_string (p_db_link      => db_link);
      l_session_transform_param_rec      session_transform_param_rec;
   BEGIN
      IF transform_handle = DBMS_METADATA.session_transform THEN
         /* Since we don't hold a session open for a remote database - we have to
            store the session transformation parameters in a local global structure */
         l_session_transform_param_rec.parameter_name := UPPER (TRIM (name));
         l_session_transform_param_rec.value_datatype := c_varchar2_type;
         l_session_transform_param_rec.varchar2_value := value;
         g_session_transform_params (db_link) (l_session_transform_param_rec.parameter_name) := l_session_transform_param_rec;
      ELSE
         l_plsql_call :=
               'BEGIN '
            || '   DBMS_METADATA.SET_TRANSFORM_PARAM'
            || l_db_link
            || ' (transform_handle => :transform_handle '
            || ', name             => :name '
            || ', value            => :value '
            || '); '
            || 'END;';

         EXECUTE IMMEDIATE l_plsql_call
                     USING IN transform_handle
                         , IN name
                         , IN value;
      END IF;
   END set_transform_param;

/* ------------------------------------- */
   PROCEDURE set_transform_param (
      db_link            IN   VARCHAR2 DEFAULT NULL
    , transform_handle   IN   NUMBER
    , name               IN   VARCHAR2
    , value              IN   BOOLEAN DEFAULT TRUE
   ) IS
      -- Variables
      l_plsql_call                       VARCHAR2 (4000);
      l_db_link                          VARCHAR2 (100) := build_db_link_string (p_db_link      => db_link);
      l_session_transform_param_rec      session_transform_param_rec;
   BEGIN
      IF transform_handle = DBMS_METADATA.session_transform THEN
         /* Since we don't hold a session open for a remote database - we have to
            store the session transformation parameters in a local global structure */
         l_session_transform_param_rec.parameter_name := UPPER (TRIM (name));
         l_session_transform_param_rec.value_datatype := c_boolean_type;
         l_session_transform_param_rec.boolean_value := value;
         g_session_transform_params (db_link) (l_session_transform_param_rec.parameter_name) := l_session_transform_param_rec;
      ELSE
         l_plsql_call :=
               'BEGIN '
            || '   DBMS_METADATA.SET_TRANSFORM_PARAM'
            || l_db_link
            || ' (transform_handle => :transform_handle '
            || ', name             => :name '
            || ', value            => '
            || boolean_pkg.bool_to_str (boolean_in      => value)
            || '); '
            || 'END;';

         EXECUTE IMMEDIATE l_plsql_call
                     USING IN transform_handle, IN name;
      END IF;
   END set_transform_param;

/* ------------------------------------- */
   PROCEDURE set_transform_param (
      db_link            IN   VARCHAR2 DEFAULT NULL
    , transform_handle   IN   NUMBER
    , name               IN   VARCHAR2
    , value              IN   NUMBER
   ) IS
      -- Variables
      l_plsql_call                       VARCHAR2 (4000);
      l_db_link                          VARCHAR2 (100) := build_db_link_string (p_db_link      => db_link);
      l_session_transform_param_rec      session_transform_param_rec;
   BEGIN
      IF transform_handle = DBMS_METADATA.session_transform THEN
         /* Since we don't hold a session open for a remote database - we have to
            store the session transformation parameters in a local global structure */
         l_session_transform_param_rec.parameter_name := UPPER (TRIM (name));
         l_session_transform_param_rec.value_datatype := c_number_type;
         l_session_transform_param_rec.number_value := value;
         g_session_transform_params (db_link) (l_session_transform_param_rec.parameter_name) := l_session_transform_param_rec;
      ELSE
         l_plsql_call :=
               'BEGIN '
            || '   DBMS_METADATA.SET_TRANSFORM_PARAM'
            || l_db_link
            || ' (transform_handle => :transform_handle '
            || ', name             => :name '
            || ', value            => :value '
            || '); '
            || 'END;';

         EXECUTE IMMEDIATE l_plsql_call
                     USING IN transform_handle
                         , IN name
                         , IN value;
      END IF;
   END set_transform_param;

/* ------------------------------------- */
   FUNCTION fetch_ddl_text (
      db_link   IN       VARCHAR2 DEFAULT NULL
    , handle    IN       NUMBER
    , partial   OUT      NUMBER
   )
      RETURN VARCHAR2 IS
      -- Variables
      l_plsql_call                       VARCHAR2 (4000);
      l_db_link                          VARCHAR2 (100) := build_db_link_string (p_db_link      => db_link);
      l_return                           VARCHAR2 (32767);
   BEGIN
      l_plsql_call := 'BEGIN :l_return := DBMS_METADATA.fetch_ddl_text' || l_db_link || ' (handle => :handle, partial => :partial); END;';

      EXECUTE IMMEDIATE l_plsql_call
                  USING OUT    l_return
                      , IN     handle
                      , OUT    partial;

      RETURN l_return;
   END fetch_ddl_text;

/* ------------------------------------- */
   FUNCTION fetch_clob (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
   )
      RETURN CLOB IS
      -- Variables
      l_partial                          PLS_INTEGER;
      l_return                           CLOB;
   BEGIN
      -- Loop until the partial flag is 0
      LOOP
         l_return := l_return || fetch_ddl_text (db_link      => db_link
                                               , handle       => handle
                                               , partial      => l_partial
                                                );
         EXIT WHEN l_partial = 0;
      END LOOP;

      RETURN l_return;
   END fetch_clob;

/* ------------------------------------- */
   FUNCTION fetch_ddl_clob (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
   )
      RETURN CLOB IS
      -- Variables
      l_return                           CLOB;
   BEGIN

      -- Keep fetching until we get an error...
      <<fetch_loop>>
      LOOP
         BEGIN
            l_return := l_return || fetch_clob (db_link      => db_link, handle => handle);
         EXCEPTION
            WHEN DBMS_METADATA.invalid_argval THEN
               EXIT fetch_loop;
         END;
      END LOOP fetch_loop;

      RETURN l_return;
   END fetch_ddl_clob;

/* ------------------------------------- */
   FUNCTION fetch_ddl (
      db_link   IN   VARCHAR2 DEFAULT NULL
    , handle    IN   NUMBER
   )
      RETURN ku$_ddls IS
      -- Variables
      l_temp_clob                        CLOB;
      l_return                           ku$_ddls := ku$_ddls ();
   BEGIN

      -- Keep fetching until we get an error...
      <<fetch_loop>>
      LOOP
         BEGIN
            l_temp_clob := fetch_clob (db_link      => db_link, handle => handle);
            l_return.EXTEND;
            l_return (l_return.COUNT).ddltext := l_temp_clob;
         EXCEPTION
            WHEN DBMS_METADATA.invalid_argval THEN
               EXIT fetch_loop;
         END;
      END LOOP fetch_loop;

      RETURN l_return;
   END fetch_ddl;

/* ------------------------------------- */
   PROCEDURE set_default_table_transforms (
      db_link                  IN   VARCHAR2 DEFAULT NULL
    , table_transform_handle   IN   NUMBER
   ) IS
   BEGIN
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'PRETTY'
                         , value                 => TRUE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'SQLTERMINATOR'
                         , value                 => TRUE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'SIZE_BYTE_KEYWORD'
                         , value                 => FALSE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'SEGMENT_ATTRIBUTES'
                         , value                 => TRUE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'STORAGE'
                         , value                 => FALSE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'TABLESPACE'
                         , value                 => TRUE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => table_transform_handle
                         , name                  => 'CONSTRAINTS_AS_ALTER'
                         , value                 => TRUE
                          );
   END set_default_table_transforms;

/* ------------------------------------- */
   PROCEDURE set_default_index_transforms (
      db_link                  IN   VARCHAR2 DEFAULT NULL
    , index_transform_handle   IN   NUMBER
   ) IS
   BEGIN
      set_transform_param (db_link               => db_link
                         , transform_handle      => index_transform_handle
                         , name                  => 'PRETTY'
                         , value                 => TRUE
                          );
      set_transform_param (db_link               => db_link
                         , transform_handle      => index_transform_handle
                         , name                  => 'SQLTERMINATOR'
                         , value                 => TRUE
                          );
   END set_default_index_transforms;

/* ------------------------------------- */
   PROCEDURE apply_session_transform_params (
      db_link            IN   VARCHAR2
    , transform_handle   IN   NUMBER
   ) IS
      -- Variables
      l_array_index                      VARCHAR2 (50);
   BEGIN
      IF db_link IS NOT NULL THEN
         -- Apply any global session trasformation parameters which have been set prior to this call...
         l_array_index := g_session_transform_params (db_link).FIRST;

         WHILE l_array_index IS NOT NULL LOOP
            CASE g_session_transform_params (db_link) (l_array_index).value_datatype
               WHEN c_varchar2_type THEN
                  set_transform_param (db_link               => db_link
                                     , transform_handle      => transform_handle
                                     , name                  => g_session_transform_params (db_link) (l_array_index).parameter_name
                                     , value                 => g_session_transform_params (db_link) (l_array_index).varchar2_value
                                      );
               WHEN c_boolean_type THEN
                  set_transform_param (db_link               => db_link
                                     , transform_handle      => transform_handle
                                     , name                  => g_session_transform_params (db_link) (l_array_index).parameter_name
                                     , value                 => g_session_transform_params (db_link) (l_array_index).boolean_value
                                      );
               WHEN c_number_type THEN
                  set_transform_param (db_link               => db_link
                                     , transform_handle      => transform_handle
                                     , name                  => g_session_transform_params (db_link) (l_array_index).parameter_name
                                     , value                 => g_session_transform_params (db_link) (l_array_index).number_value
                                      );
            END CASE;

            l_array_index := g_session_transform_params (db_link).NEXT (l_array_index);
         END LOOP;
      END IF;
   
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END apply_session_transform_params;

/* ------------------------------------- */
   FUNCTION get_ddl (
      db_link       IN   VARCHAR2 DEFAULT NULL
    , object_type   IN   VARCHAR2
    , name          IN   VARCHAR2
    , SCHEMA        IN   VARCHAR2 DEFAULT NULL
    , version       IN   VARCHAR2 DEFAULT 'COMPATIBLE'
    , model         IN   VARCHAR2 DEFAULT 'ORACLE'
    , transform     IN   VARCHAR2 DEFAULT 'DDL'
   )
      RETURN CLOB IS
      -- Variables
      l_return                           CLOB;
      l_handle                           NUMBER;
      l_transform                        NUMBER;

      -- Private Sub-Procedure
      PROCEDURE cleanup IS
      BEGIN
         dbms_metadata_close (db_link      => db_link, handle => l_handle);
      EXCEPTION
         WHEN handle_not_open THEN
            NULL;
      END cleanup;
   BEGIN
      l_handle := remote_dbms_metadata.open (db_link          => db_link, object_type => object_type);
      set_count (db_link      => db_link
               , handle       => l_handle
               , value        => 1
                );
      set_filter (db_link      => db_link
                , handle       => l_handle
                , name         => 'NAME'
                , value        => name
                 );
      set_filter (db_link      => db_link
                , handle       => l_handle
                , name         => 'SCHEMA'
                , value        => SCHEMA
                 );
      l_transform := add_transform (db_link      => db_link
                                  , handle       => l_handle
                                  , name         => transform
                                   );
      apply_session_transform_params (db_link               => db_link, transform_handle => l_transform);
      l_return := fetch_ddl_clob (db_link      => db_link, handle => l_handle);
      cleanup;
      RETURN l_return;
   EXCEPTION
      WHEN OTHERS THEN
         cleanup;
         RAISE;
   END get_ddl;

/* ------------------------------------- */
   FUNCTION get_dependent_ddl (
      db_link              IN   VARCHAR2 DEFAULT NULL
    , object_type          IN   VARCHAR2
    , base_object_name     IN   VARCHAR2
    , base_object_schema   IN   VARCHAR2 DEFAULT NULL
    , version              IN   VARCHAR2 DEFAULT 'COMPATIBLE'
    , model                IN   VARCHAR2 DEFAULT 'ORACLE'
    , transform            IN   VARCHAR2 DEFAULT 'DDL'
    , object_count         IN   NUMBER DEFAULT 10000
   )
      RETURN CLOB IS
      -- Variables
      l_return                           CLOB;
      l_handle                           NUMBER;
      l_transform                        NUMBER;

      -- Private Sub-Procedure
      PROCEDURE cleanup IS
      BEGIN
         dbms_metadata_close (db_link      => db_link, handle => l_handle);
      EXCEPTION
         WHEN handle_not_open THEN
            NULL;
      END cleanup;
   BEGIN
      l_handle := remote_dbms_metadata.open (db_link          => db_link, object_type => object_type);
      set_filter (db_link      => db_link
                , handle       => l_handle
                , name         => 'BASE_OBJECT_NAME'
                , value        => base_object_name
                 );
      set_filter (db_link      => db_link
                , handle       => l_handle
                , name         => 'BASE_OBJECT_SCHEMA'
                , value        => base_object_schema
                 );
      set_count (db_link      => db_link
               , handle       => l_handle
               , value        => object_count
                );
      l_transform := remote_dbms_metadata.add_transform (db_link      => db_link
                                                       , handle       => l_handle
                                                       , name         => transform
                                                        );
      apply_session_transform_params (db_link               => db_link, transform_handle => l_transform);
      l_return := fetch_ddl_clob (db_link      => db_link, handle => l_handle);
      cleanup;
      RETURN l_return;
   EXCEPTION
      WHEN OTHERS THEN
         cleanup;
         RAISE;
   END get_dependent_ddl;
END remote_dbms_metadata;
/

SHOW ERRORS

Watchouts: Remember - since PL/SQL Packages require DIRECT granted permissions (not through a role) - the schema you compile this package in requiresj EXECUTE privileges on the SYS.DBMS_METADATA package.



Also - using DBMS_METADATA.GET_DDL requires the "SELECT_CATALOG_ROLE" if you are using it to get DDL in a schema other than your own - so the user you have defined your database link to use MUST have that privilege in the remote database for this to work.



How to use: Just call it from SQL!

Example call:

select remote_dbms_metadata.get_ddl ('PROD_LINK'
                                   , 'TABLE'
                                   , 'EMP'
                                   , 'SCOTT') AS table_ddl
     , remote_dbms_metadata.get_dependent_ddl ('PROD_LINK'
                                             , 'INDEX'
                                             , 'EMP'
                                             , 'SCOTT') AS index_ddl
FROM dual; 

Notes:
This package has been tested in Oracle 9iR2 and 10GR2.


Good luck...

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