Obtener ddl de objetos

Para aquellos que utilizan el Toad(el lado oscuro de la fuerza) o similares nunca es un problema recuperar de la base de datos el ddl de una tabla o un paquete. Para los fanáticos del plus como yo puede ser algo tedioso. El código de los paquetes, funciones y procedimientos puedes obtenerlo de la vista ALL_SOURCE, el de las vistas de ALL_VIEWS y el de los triggers de ALL_TRIGGERS. A partir de la Oracle 9i hay una función que nos puede sacar el código ddl de cualquier objeto de tu esquema: DBMS_METADATA.get_DDL. Tiene varios parámetros, pero sólo los tres primeros son obligatorios. Los otros toman valores por defecto. El primer parámetro se refiere al tipo de objeto, el segundo al nombre del objeto y el tercero al esquema al que pertenece. El paquete pertenece a SYS pero, como es lógico, sólo te deja consultar objetos para los que tengas permiso.
Por ejemplo, si creamos esta tabla:

CREATE TABLE PRUEBA(
  A VARCHAR2(1) PRIMARY KEY,
  B DATE        NOT NULL,
  C VARCHAR2(3) )
/

Asumiendo que el esquema propietario fuese TSORACLE, el resultado del get_ddl sería este:

SQL> SET LONG 5000
SQL> SELECT DBMS_METADATA.get_DDL('TABLE','PRUEBA','TSORACLE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','PRUEBA','TSORACLE')
--------------------------------------------------------------------------------

  CREATE TABLE "TSORACLE"."PRUEBA"
   (    "A" VARCHAR2(1),
        "B" DATE NOT NULL ENABLE,
        "C" VARCHAR2(3),
         PRIMARY KEY ("A")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TSORACLE"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TSORACLE"

El SET LONG es importante porque el valor por defecto del plus es bajo. Habría que ajustarlo según la salida esperada.
Otro ejemplo podría ser cómo obtener el ddl de creación de todos los sinónimos del esquema:

SET LONG 5000
SELECT DBMS_METADATA.get_DDL( 'SYNONYM', SYNONYM_NAME, USER ) 
FROM   ALL_SYNONYMS 
ORDER  BY SYNONYM_NAME;

No sé, me ha parecido una utilidad molona.

Anuncios

9 comentarios en “Obtener ddl de objetos

  1. Hola, estoy ejecutando estos ejemplos y obtengo el siguiente error:

    SQL> SELECT DBMS_METADATA.GET_DDL(‘TABLE’,’PRUEBA_XX’,’APPS’) FROM dual
    2 /
    SELECT DBMS_METADATA.GET_DDL(‘TABLE’,’PRUEBA_XX’,’APPS’) FROM dual
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    LPX-00210: expected ‘

  2. Gracias, pero por lo que estuve investigando falta agregarle unos parches a la base.
    Me fije en metalink y el numero de bug es 3361288.

  3. llevo unos dias trabajando con el dbms_metadata.get_ddl(). Me parece muy interesante. Le veo una pega, que a mi en particular me esta volviendo loco.

    Devuelve la definicion DDL del objeto preguntado, pero no finaliza la definicion con el separador adecuado. Por ejemplo, en un procedure no finaliza con ‘/’. Con lo cual, no se puede coger tal cual esta definicion y ejecutarla en otro esquema.

    ¿Sabeis de alguna solución (automatica) a este problema?

    Gracias por adelantado

  4. Hola Ciri.
    La solución es bien sencilla, sólo tienes que concatenar un salto de carro y la barra. Por ejemplo:
    SELECT DBMS_METADATA.get_DDL( ‘SYNONYM’, SYNONYM_NAME, USER ) || CHR(10) || ‘/’
    FROM ALL_SYNONYMS
    ORDER BY SYNONYM_NAME;

  5. Yo tengo Oracle 7.3.4 y 8.0.5, no puedo ejecutar este sql por que es para 8i y 9i, como puedo visualiar los DDL de mis tablas, alguna ayuda????

  6. 4 y 5

    Podéis modificar el comportamiento de dbms_metadata para que formatee la salida de varias maneras ( no mostrar alters, no mostrar la parte de storage….) En vuestro caso en concreto, podéis utilizar estas 2 opciones ( ejecutadlas antes de lanzar el get_ddl )

    execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’PRETTY’,true);

    execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,true);

    HTH

    D.

  7. No se visualiza bien en 7.

    execute
    DBMS_METADATA.SET_TRANSFORM_PARAM
    ( DBMS_METADATA.SESSION_TRANSFORM,
    ‘PRETTY’,true);

    execute DBMS_METADATA.SET_TRANSFORM_PARAM
    (DBMS_METADATA.SESSION_TRANSFORM,
    ‘SQLTERMINATOR’,true);

    D.

Los comentarios están cerrados.