Un extractor de DDL en archivos separados

¿Cómo podemos exportar todos los objetos de un esquema de Oracle en archivos separados para poder versionar el código?

Algunos de los IDEs comerciales más populares de PL/SQL traen esta funcionalidad (lo pueden investigar), pero me parece una buena oportunidad para poder hacerlo sin ayuda de ninguna otra herramienta que... PL/SQL!

Para poder cristalizar esto, ejecutaremos desde sql*plus bloques de PL/SQL que realicen lo siguiente:
1) Extraer el DDL de los objetos - usaremos el paquete dbms_metadata para obtener el DDL de tablas, indices y constraints. También leeremos la tabla de diccionario user_source para capturar el código de procedimientos, funciones, paquetes y triggers.
2) Escribir en archivos el código generado - invocaremos al paquete utl_file para crear archivos en el sistema operativo.

Este procedimiento funciona en 9i, XE o 10g, tanto para Unix como para Windows. Los archivos quedarán alojados localmente en el servidor (el usuario necesitará los debidos permisos de acceso a estas carpetas).

Nota para usuarios Oracle XE:
El paquete utl_file no vine compilado por defecto luego de la instalación. Es necesario ejecutar el siguiente script con el usuario sys:
$ORACLE_HOME/RDBMS/ADMIN/utlfile.sql
Luego deberán revisar con el usuario sys si quedaron paquetes, vistas o procedimientos mal compilados y en tal caso, recompilarlos.

Bueno, ahora sí vamos con los pasos necesarios:

Paso 1) Crear directorios en el servidor - El usuario de la base de datos necesita privilegios para crear directories de Oracle, de lo contrario deberá solicitar al DBA que lo haga por él o le indique alguno que pueda usar. De lo contrario no vamos a poder escribir con el paquete utl_file.

Las carpetas deben existir en el sistema operativo y luego crearse los objetos directorios en Oracle, asociados a las mismas.
Por ejemplo, luego de crear las carpetas en Unix, ejecutar lo siguiente en SQL*Plus:

create directory tables as '/oracle/source/tables';
create directory indexes as '/oracle/source/indexes';
create directory procedures as '/oracle/source/procedures';
create directory functions as '/oracle/source/functions';
create directory packages as '/oracle/source/packages';
create directory packagebodies as '/oracle/source/packagebodies';
create directory triggers as '/oracle/source/triggers';
... etc

Paso 2) Especificar la información a ser generada con dbms_metadata - La función set_transform_param nos permite especificar que detalle de código debe generarse.

Ejecutar lo siguiente en SQL*Plus:
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER', TRUE ) ;
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR', TRUE ) ;
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', FALSE) ;

Paso 3) Finalmente, la parte interesante... codificar el PL/SQL! :)

Voy a mostrar dos ejemplos: el primero para capturar tablas y el segundo para capturar los procedimientos. El caso para capturar DDL de índices es completamente análogo al de tablas, y el caso de funciones, paquetes, body de paquetes, triggers es similar al de procedimientos.

Ejecutar en SQL*Plus:

1  DECLARE
2    f UTL_FILE.FILE_TYPE;
3  BEGIN
4    FOR cr IN (SELECT table_name FROM USER_tables)
5    LOOP
6      f := UTL_FILE.FOPEN ('TABLES', cr.table_name || '.sql', 'w');
7      UTL_FILE.PUT_LINE (f, dbms_metadata.get_ddl('TABLE', cr.table_name));
8      UTL_FILE.FCLOSE (f);
9    END LOOP;
10 END;
11 /

Las líneas 2, 6, 7 y 8 muestran el uso del paquete utl_file en cuatro pasos básicos: se declara el manejador, se abre el archivo para escritura, se escribe y finalmente se cierra.

La línea 4 selecciona todas las tablas de mi esquema.

En cada iteración del FOR, abrimos un nuevo archivo (línea 6) con el nombre de la tabla, y escribimos en el directorio TABLES (creado en el paso 1) la salida del procedimiento dbms_metadata.get_ddl.

Para capturar los índices es análogo, cambiando en la línea 7 el parámetro 'TABLE' por 'INDEX' y cambiando también si se desea el directorio destino en la línea 6.

Ahora veremos el caso de escribir código de usuario leyendo la tabla user_source. Tenemos que tener algunos detalles en cuenta, ya que la tabla user_source contiene todo el código separado una línea por fila. Vamos a ir escribiendo el archivo de a una línea y tendremos que saber cuándo termina un procedimiento y comienza otro.

1  DECLARE
2    f UTL_FILE.FILE_TYPE;
3    nombre VARCHAR2(4000) := NULL;
4  BEGIN
5    FOR cr IN (SELECT name, text FROM USER_SOURCE WHERE type='PROCEDURE' order by name, line)
6    LOOP
7      IF (nombre IS NULL OR cr.name <> nombre) THEN
8        IF (nombre IS NOT NULL) THEN
9          UTL_FILE.FCLOSE (f);
10       END IF;
11       f := UTL_FILE.FOPEN ('TABLES', cr.name || '.sql', 'w');
12       UTL_FILE.PUT (f, 'CREATE OR REPLACE ');
13     END IF;
14     nombre := cr.name;
15     UTL_FILE.PUT (f, cr.text);
16   END LOOP;
17   IF UTL_FILE.IS_OPEN(f) THEN
18     UTL_FILE.FCLOSE (f);
19   END IF;
20 END;
21 /

En la línea 3 declaramos una variable para leer cada línea.

En la línea 5 seleccionamos el tipo de objeto que queremos capturar. De la misma forma lo haremos para triggers, funciones, paquetes, etc.

Las líneas 7 y 8 tratan la apertura y cierre del archivo cuando se termina un procedimiento; al ir leyendo línea a línea de todos los objetos, necesito saber cuando comienza y cuando termina un procedimiento para podeer abrir un nuevo archivo de escritura.

En la línea 12 le agrego 'CREATE OR REPLACE' ya que la tabla user_source no almacena esta directiva.

Observar en las líneas 12 y 15 que utilizamos la función PUT en lugar de PUT_LINE. Esto es porque las líneas de user_source ya almacenan un retorno de carro por lo cual no es necesario que use PUT_LINE, de lo contrario voy a escribir un renglón por medio.

Con estos ejemplos es posible capturar todo el código de nuestro esquema y almacenar cada objeto en un archivo diferente. Traté de simplificar el código a costa de sacrificar algunas buenas prácticas, pero en este caso no quise complicar la visibilidad de la idea por lo cual cada uno podrá mejorar su propia implementación.