Oracle Scripts - dbapool.com

REM: Package for generating a sql file for Table/Indexes
REM:
REM: Author: Ankur Shah
REM: Date Submitted: 06-Apr-2005
REM: Date Posted: 06-Apr-2005
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

Requirements :
Ų	Directory object  'EXPORT'   defined with read,write privileges to the
             user hosting REMOTE_METADATA package
Ų	EXECUTE DBMS_METADATA privilege to the user hosting REMOTE_METADATA pacakage.
Ų	Dblink created for remote database.

-- Package Spec

Create or Replace  PACKAGE remote_metadata AS

PROCEDURE get_remote_tables;
END;

-- Package Body

Create or Replace PACKAGE BODY  REMOTE_METADATA AS

-- Global Variables

fileHandle   UTL_FILE.FILE_TYPE;

-- Exception initialization

file_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(file_not_found, -1309);

-- Package-private routine to write a CLOB to an output file.

PROCEDURE write_lob(doc IN CLOB) IS

outString        varchar2(32760);
     cloblen             number;
     offset               number := 1;
     amount               number;

BEGIN
  cloblen := dbms_lob.getlength(doc);
  WHILE cloblen > 0
  LOOP
    IF cloblen > 32760 THEN
      amount := 32760;
    ELSE
      amount := cloblen;
    END IF;
    outString := dbms_lob.substr(doc, amount, offset);
    utl_file.put(fileHandle, outString);
    utl_file.fflush(fileHandle);
    offset  := offset  + amount;
    cloblen := cloblen - amount;
  END LOOP;
  RETURN;
END;

-- Public routines

-- GET_REMOTE_TABLES: Fetch DDL for <remote_schema> tables and their indexes.

PROCEDURE  get_remote_tables IS

tableOpenHandle              NUMBER;
indexOpenHandle              NUMBER;
tableTransHandle        NUMBER;
indexTransHandle        NUMBER;
schemaName                    VARCHAR2(30);
tableName                  VARCHAR2(30);
tableDDLs                  sys.ku$_ddls;
tableDDL                    sys.ku$_ddl;
parsedItems                  sys.ku$_parsed_items;
indexDDL                    CLOB;
vtablestr                  VARCHAR2(2000);
idx                        INTEGER:=0;

-- Create cursor for remote tables list using dblink

cursor curremotetabs is
 (select table_name  from user_tables
 MINUS
 select table_name  from user_tables@dblink) order by table_name;

BEGIN

--  open the output file... note that the 1st param. (DIRECTORY) must be
--  created as DIRECTORY schema Object with read,write access.

BEGIN
    fileHandle := utl_file.fopen('EXPORT', 'remotetablesddl.sql', 'w', 32760);
  EXCEPTION
    WHEN OTHERS THEN
        RAISE file_not_found;
  END;

-- Open a handle for tables in the current schema.

tableOpenHandle := dbms_metadata.open('TABLE');

-- Call 'set_count' to request retrieval of one table at a time. 
-- This call is not actually necessary because 1 is the default.

dbms_metadata.set_count(tableOpenHandle,1);

-- Retrieve  Remote Tables. When the filter is
-- 'NAME_EXPR', the filter value string must include the SQL operator. This
-- gives the caller flexibility to use LIKE, IN, NOT IN, subqueries, and so on.

for cur in curremotetabs LOOP
idx := idx + 1;
   if idx = 1 then
             vtablestr :=''''||cur.table_name||'''';
        else
            vtablestr := vtablestr||','||''''||cur.table_name||'''';
        end if;
    end loop;
dbms_metadata.set_filter(tableOpenHandle,'NAME_EXPR','IN('||vtablestr||')');

-- Tell Metadata API to parse out each table's schema and name separately
-- so we can use them to set up the calls to retrieve its indexes.

dbms_metadata.set_parse_item(tableOpenHandle, 'SCHEMA');
  dbms_metadata.set_parse_item(tableOpenHandle, 'NAME');

-- Add the DDL transform so we get SQL creation DDL

tableTransHandle := dbms_metadata.add_transform(tableOpenHandle, 'DDL');

-- Tell the XSL stylesheet we don't want physical storage information (storage,
-- tablespace, etc), and that we want a SQL terminator on each DDL. Notice that
-- these calls use the transform handle, not the open handle.
 
  dbms_metadata.set_transform_param(tableTransHandle, 'SQLTERMINATOR', TRUE);

-- Ready to start fetching tables. We use the FETCH_DDL interface (rather than
-- FETCH_XML or FETCH_CLOB)

LOOP
    tableDDLs := dbms_metadata.fetch_ddl(tableOpenHandle);

-- Get out when no more Remote Tables

EXIT WHEN tableDDLs IS NULL;

-- First, write the CREATE TABLE text to our output file, then retrieve the
-- parsed schema and table names.

tableDDL := tableDDLs(1);
    write_lob(tableDDL.ddltext);
    parsedItems := tableDDL.parsedItems;

-- Must check the name of the returned parse items as ordering isn't guaranteed

FOR i IN 1..2 LOOP
      IF parsedItems(i).item = 'SCHEMA'
      THEN
        schemaName := parsedItems(i).value;
      ELSE
        tableName  := parsedItems(i).value;
      END IF;
    END LOOP;

-- Then use the schema and table names to set up a 2nd stream for retrieval of
-- the current table's indexes.
-- (Note that we don't have to specify a SCHEMA filter for the indexes,
-- Because SCHEMA defaults to the value of BASE_OBJECT_SCHEMA.)

indexOpenHandle := dbms_metadata.open('INDEX');
   dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_SCHEMA',schemaName);
dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_NAME',tableName);

-- Add the DDL transform and set the same transform options we did for tables
-- indexTransHandle := dbms_metadata.add_transform(indexOpenHandle, 'DDL');

dbms_metadata.set_transform_param(indexTransHandle,'SQLTERMINATOR', TRUE);

-- Retrieve index DDLs as CLOBs and write them to the output file.

LOOP
      indexDDL := dbms_metadata.fetch_clob(indexOpenHandle);
      EXIT WHEN indexDDL IS NULL;
      write_lob(indexDDL);
    END LOOP;

-- Free resources allocated for index stream.

dbms_metadata.close(indexOpenHandle);

END LOOP;

-- Free resources allocated for table stream and close output file.
  dbms_metadata.close(tableOpenHandle);
  utl_file.fclose(fileHandle);
  RETURN;

-- end of procedure get_remote_tables

END;

END REMOTE_METADATA;

Home Discussion Forum FAQs Articles Jobs Newsletters Directory Downloads

Our Premium Sponsor