Phil's Oracle SQL and PL/SQL tips

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

2010-06-22T12:12:00.001-07:00

Hi all,

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

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

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

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

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

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

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

#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

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

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

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

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



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


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

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


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

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

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

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

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



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

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


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

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

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

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

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

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

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

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


    EXEC SQL OPEN C;
    return select_dp;
}


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

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

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

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

    sqlclu(select_dp);

    EXEC SQL CLOSE C;

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



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


    process_parms( argc, argv );

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

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

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

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

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

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

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

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


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

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

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

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

Now for some execution examples:

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


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

Connected to ORACLE as user: scott/tiger@db

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

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


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

Here is the output from stdout.csv:

[phil@unixserver:/home/phil/unloader]
$ cat stdout.csv
"1"|"ABC"

Here is the output from stderr.txt:

[phil@unixserver:/home/phil/unloader]
$ cat stderr.txt

Connected to ORACLE as user: scott/tiger@db

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

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

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

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

Good luck, and God bless!.

Phil

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

2009-07-22T07:39:00.000-07:00

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

Handy Function for parsing strings in PL/SQL!

2009-07-10T19:50:00.000-07:00

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!

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i

2009-06-23T13:10:00.000-07:00

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!

2009-06-18T09:50:00.001-07:00

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!