Wednesday, July 22, 2009

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

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

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

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

Here goes:

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

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

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




Just run this SQL to test it:

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

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

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



Semper Fidelis

Friday, July 10, 2009

Handy Function for parsing strings in PL/SQL!

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

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

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

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

Version: 2.0

Author: Philip Moore - (version 2.0)

Original Author: Todd Ward - (version 1.0)

Date: 23-JAN-2006

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

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

SHOW ERRORS;



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

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

Good luck!

Tuesday, June 23, 2009

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i

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

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

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

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


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

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

l_flags := l_flags || match_parameter;

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

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

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

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

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

RETURN l_source_string;
END regexp_replace;
/


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

RETURN l_return;
END regexp_like;
/




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

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

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




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

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

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

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!