Thursday, June 18, 2009

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!

11 comments:

  1. 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

    ReplyDelete
  2. example of call:

    select demo_fn@db_link('TABLE','DEMO') FROM DUAL;

    ReplyDelete
  3. Hi Tunde, Thanks for the suggestions! I'll definitely check it out.

    Sincerely,

    Phil

    ReplyDelete
  4. 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

    ReplyDelete
  5. 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!

    ReplyDelete
  6. 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.

    ReplyDelete
  7. Works like a charm!

    Thanks for sharing.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. remote_dbms_metadata.get_ddl is not extracting complete code always any thought.

    ReplyDelete
  10. It works fine on 11.2.
    Thank you very much!

    ReplyDelete