RBDMS ORACLE - Esempio di utilizzo di Dbms_Metadata in Oracle 11gR2:

Di seguito un semplice esempio di come utilizzare il package DBMS_METADATA per generare una ddl partendo da un indice esistente e rimappando l'indice su un nuovo nome e schema. Logicamente si può anche rimappare tutto ciò che riguarda la parte dei tablespace.

FUNCTION FNC_DDL_REMAP_SCHEMA_INDEX(
    V_INDEX_NAME    IN VARCHAR2,
    V_SCHEMA_IDX   IN VARCHAR2,
    V_TAB_NAME    IN VARCHAR2,
    V_SCHEMA_TAB   IN VARCHAR2,
    V_REMAP_INDEX_NAME    IN VARCHAR2,
    V_REMAP_SCHEMA_IDX   IN VARCHAR2,
    V_REMAP_TAB_NAME    IN VARCHAR2,
    V_REMAP_SCHEMA_TAB   IN VARCHAR2,
    V_SEGMENT_ATTRIBUTES IN BOOLEAN DEFAULT FALSE)

 RETURN CLOB IS
-- Define local variables.
doc CLOB;
v_cmd varchar2(10000):=null;
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM

BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('INDEX');

-- Use filters to specify the particular object desired.

DBMS_METADATA.SET_FILTER(h,'SCHEMA',V_SCHEMA_IDX);
DBMS_METADATA.SET_FILTER(h,'NAME',V_INDEX_NAME);

-- Request that the schema name be modified.
th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');


DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA',V_SCHEMA_IDX,V_REMAP_SCHEMA_IDX);
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_NAME',V_INDEX_NAME,V_REMAP_INDEX_NAME);
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA',V_SCHEMA_TAB,V_REMAP_SCHEMA_TAB);
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_NAME',V_TAB_NAME,V_REMAP_TAB_NAME);
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_TABLESPACE','TBSF1_IDX','TBSF2_IDX');

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Specify that segment attributes are not to be returned.
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',V_SEGMENT_ATTRIBUTES);

-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h);

-- Release resources.
DBMS_METADATA.CLOSE(h);
RETURN doc;

exception
  when others then
      raise_application_error (-20000,'ERRORE :'||SQLERRM);

END FNC_DDL_REMAP_SCHEMA_INDEX;

Lo stesso può essere fatto per tabelle, viste ecc. qualsiasi Object_Type_Name.

Di seguito una semplice funzione per estrarre una DDL senza alcun REMAP.

FUNCTION FNC_DDL_GENERIC(
    V_OBJECT_TYPE    IN VARCHAR2,
    V_SCHEMA   IN VARCHAR2,
    V_OBJECT_NAME    IN VARCHAR2)
 RETURN CLOB IS
-- Define local variables.
doc CLOB;
v_cmd varchar2(10000):=null;
---
BEGIN


   v_cmd:='SELECT DBMS_METADATA.GET_DDL(:v_object_type,:v_object_name, :v_schema) FROM DUAL';
   execute immediate v_cmd into doc using in v_object_type,v_object_name,v_schema;


RETURN doc;

exception
  when others then
      raise_application_error (-20000,'ERRORE :'||SQLERRM);

END FNC_DDL_GENERIC;