Tablas externas en Oracle

Un requerimiento en mi pasado proyecto, consistía en que el usuario pudiera cargar en el momento que desee un archivo proveniente de Excel (que serían los usuarios sin Excel), se procesara la información con datos complementarios y le fuera devuelta en otro archivo que pudiera manejar también en Excel.

Para la carga inicial del archivo contaba con varias opciones, al sistema estar desarrollado en Oracle Developer 6i, igual se puede generar un nuevo modulo y mediante TEXT_IO manejar la información del archivo, al ser una carga de información se podría usar SQL*Loader, también podría haberse usado UTL_FILE para leer y cargar el archivo a la base o la que finalmente el cliente acabo aceptando por su facilidad de uso y mantenimiento, las tablas externas.

En la practica una tabla externa no es mas que un archivo plano con cierto layout predefinido en el cual los campos están separados por algún carácter como una coma, comillas, o tabuladores, muy parecido al que se usaría para hacer una carga desde SQL*Loader, Que es declarado en Oracle y que puede ser manejado directamente como cualquier otro objeto de la base de datos, pero a fin de cuentas es un archivo en el sistema operativo, con algunas limitantes, como no poder realizar inserts, deletes, updates sobre el objeto, no se pueden generar indices y su tamaño máximo es de 2GB, veamos ahora como crearlo:

• Previamente se crea un directorio donde vivirá el mencionado archivo plano, (la ruta depende de nuestro servidor):

  CREATE OR REPLACE DIRECTORY EXTERNAL_TABLES AS '/usr/files/external/';

• Le damos privilegios a determinado usuario para leer y escribir en este directorio:

  GRANT READ, WRITE ON DIRECTORY EXTERNAL_TABLES TO USUARIO;

• En el directorio físico del servidor copiamos el archivo que contendrá los datos llamado “tabla_externa.txt”, para este ejemplo usaremos datos como los que siguen (¿creo que es evidente que son de prueba verdad?):

  1,00001,551122334455,11111111111
  2,00002,551122334466,22222222222
  3,00003,551122334477,33333333333
  4,00004,551122334488,44444444444
  5,00005,551122334499,55555555555

• Ahora creamos la tabla externa:

  create table tabla_externa (
    CLI_ID         varchar2(50),
    CONTRATO       varchar2(50),
    TELEFONO       varchar2(50),
    NO_SERIE       varchar2(50)
  )
  organization EXTERNAL (
    type oracle_loader 
    default directory EXTERNAL_TABLES
    access parameters
  ( records delimited by newline
    skip 1
    badfile 'tabla_externa.bad'
    logfile 'tabla_externa.log'
    fields terminated by ','
    missing field values are null
  )
  location ('tabla_externa.txt')
  )
  reject limit unlimited;

Listo, a partir de este momento tenemos acceso a los datos dentro de cualquier consulta normal, solo unas observaciones adicionales:

Si el archivo que deseas mostrar contiene muchos, muchos datos, igual y te conviene mejor usar SQL*Loader para previamente cargar tus datos a Oracle ya que los tiempos para consultas a estas tablas con bastante mas grandes que los tiempos de una tabla normal, esto debido a que no se pueden crear indices sobre la tabla externa o, en su defecto, usar una segunda tabla, que sea llenada con la información de la externa con un simple “insert as select” la cual tenga sus debidos indices.

Para no perder la facilidad en el acceso a la información ganado con la tabla externa por algún error “de dedo”, esta fue creada especificando varchar2(50) para sus campos, aunque en realidad se espera en esas columnas valores numéricos y de mucha menos extensión, por ejemplo para el cli_id se espera un numérico de 10 caracteres, pero declararlo así no me regresaría por ejemplo un valor que tuviera un carácter, preferí dejarlo como varchar2 y manejar esto mediante la consulta que extrae los datos, en este caso, limpiando la cadena de caracteres no numéricos, esto claro depende de la calidad esperada de la información, si se espera una información de calidad igual y conviene declarar los tipos de acuerdo a su valor esperado, en mi caso esto no fue así.