Phil's Oracle Exadata, SQL, and PL/SQL tips: DBMS

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!

11 comments:

TundeOctober 29, 2009 at 6:16 AM
Wow, that's a nice one you've got there.
DBMS_METADATA.GET_DDL is sure a powerful tool but I don't like the format he retrieves the ddl so I tweaked it a bit to remove the owner of the database object and then return string instead of clob to allow transfer across a database link (I think urs is a better option though). You could also incorporate removing the schema names and double quotes if u want here's my own version of the get_ddl function:

CREATE OR REPLACE function DEMO_FN
(object_type varchar2, table_name varchar2) return varchar2
is

v_longstrings varchar2(32223);
c_doublequote constant char(1) := '"';
begin
v_longstrings := dbms_metadata.get_ddl(object_type,table_name);

-- Remove double quotes from DDL string:
v_longstrings := replace(v_longstrings, c_doublequote || user || c_doublequote || '.','');

-- Remove the following from DDL string:
-- 1) "new line" characters (chr(10))
-- 2) leading and trailing spaces
v_longstrings := ltrim(rtrim(replace(v_longstrings, chr(10), '')));
return v_longstrings;
end;
/

Cheers,
Tunde
TundeOctober 29, 2009 at 6:18 AM
example of call:

select demo_fn@db_link('TABLE','DEMO') FROM DUAL;
PhilOctober 30, 2009 at 12:18 AM
Hi Tunde, Thanks for the suggestions! I'll definitely check it out.

Sincerely,

Phil
TundeOctober 30, 2009 at 6:24 AM
You are welcome anytime Phil, I am still having some trouble with recreating tables with dependencies on tables not yet imported.

Could you by any chance have found a way around tweaking the table metadata not to include foreign key constraints.

Many thanks.

Regards,
Tunde
PhilNovember 2, 2009 at 11:04 AM
Hi Tunde,

DBMS_METADATA uses the "SYS.METASTYLESHEET" table - specifically the "XSL" stylesheets with CLOB contents in column: "STYLESHEET" to transform XML into usable DDL when you use the "GET_DDL" function.

I've hacked those stylesheets by storing them in my own table, and by using them as transforms when programmatically using DBMS_METADATA - so that I can do things like rename the table being created, put it in another schema, turn on or off the partitions, the constraints, etc.

I would recommend that you use the Oxygen XML editor if you plan on modifying the stylesheets - and do NOT change the original ones contained in the sys.METASTYLESHEET table (create your own).

Another approach would be to use regular expressions to find and change patterns in the SQL generated by DBMS_METADATA.

Good luck!
TundeNovember 3, 2009 at 7:25 AM
Hello Phil,

Thanks, I have been able to work around the constraints and all.

There is still one problem left, is it possible to change the datatype of the sys.METASYLESHEET; i.e. my own copy from CLOB to VARCHAR2?

If not, which way do you think transfer of metadata can be done across a database link using DBMS_METADATA?

Oracle doesnt support transfer of CLOB datatype across the database link yet; so I tweaked my function to return varchar2; meaning the max it can return is 32767.

Many thanks.
konfushusFebruary 11, 2010 at 5:39 PM
Works like a charm!

Thanks for sharing.
Suresh PanavalliSeptember 16, 2011 at 4:53 PM
This comment has been removed by the author.
Suresh PanavalliSeptember 16, 2011 at 5:24 PM
Thanks man it works really good...
AnisJanuary 17, 2012 at 1:41 AM
remote_dbms_metadata.get_ddl is not extracting complete code always any thought.
UnknownJanuary 18, 2018 at 5:32 AM
It works fine on 11.2.
Thank you very much!

Phil's Oracle Exadata, SQL, and PL/SQL tips

Thursday, June 18, 2009

DBMS_METADATA Across Database Links!

11 comments:

About Me

Blog Archive