Obtener ddl de objetos

Jul 23

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.

This work, unless otherwise expressly stated, is licensed under a Creative Commons Attribution 3.0 License.

9 Responses to “Obtener ddl de objetos”

Siro Says:
September 25th, 2007 at 4:15 pm
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 ‘
Sir Ragnar Says:
September 26th, 2007 at 8:16 pm
Hola Siro, ¿Puedes postear el script de creación de la tabla para ver si puedo reproducir el error?.
Siro Says:
October 3rd, 2007 at 5:12 pm
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.
ciri Says:
May 27th, 2008 at 5:40 pm
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
Sir Ragnar Says:
May 29th, 2008 at 7:42 pm
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;
Jim Says:
June 2nd, 2008 at 3:28 pm
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????
David Ballester Says:
July 11th, 2008 at 12:37 pm
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.
David Ballester Says:
July 11th, 2008 at 12:39 pm
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.
Sir Ragnar Says:
July 14th, 2008 at 7:03 pm
Gracias por el comentario David. La verdad es que no lo sabía y hace más útil el paquete.

Templarios de San Oracle