Tablas Externas

Objetivos:

•Crear y utilizar tablas externas.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________

Tablas Externas.

Una tabla externa es una tabla de sólo lectura cuyos metadatos se almacenan en la base de datos pero cuyos datos se almacenan fuera de la base de datos. Esta definición de tabla externa se puede considerar una vista que se utiliza para ejecutar cualquier consulta SQL en datos externos sin necesidad de que se carguen primero los datos externos en la base de datos. Los datos de tabla externa se pueden consultar y unir directamente y en paralelo sin necesidad de que se carguen primero los datos externos en la base de datos. Puede utilizar SQL, PL/SQL y Java para consultar los datos en una tabla externa.

La diferencia principal entre las tablas externas y las normales es que las tablas organizadas externamente son de sólo lectura. No son posibles las operaciones DML y no se pueden crear índices en ellas. Sin embargo, se puede crear una tabla externa (y, por tanto, descargar datos) mediante el comando CREATE TABLE AS SELECT.

Oracle Server proporciona dos controladores de acceso principales para las tablas externas. Uno de ellos es el controlador de acceso de cargador (u ORACLE_LOADER), que se utiliza para leer datos de archivos externos cuyo formato se puede interpretar mediante la utilidad SQL*Loader. Observe que no se soporta toda la funcionalidad de SQL*Loader con tablas externas.

El controlador de acceso ORACLE_DATAPUMP se puede utilizar para importar y exportar datos mediante un formato independiente de la plataforma. El controlador de acceso ORACLE_DATAPUMP escribe filas de una sentencia SELECT que se cargarán en una tabla externa como parte de la sentencia CREATE TABLE...ORGANIZATION EXTERNAL...AS SELECT. Puede utilizar SELECT para leer datos de ese archivo de datos. También puede crear una definición de tabla externa en otro sistema y utilizar ese archivo de datos. Esto permite que se muevan datos entre bases de datos Oracle.

Creación de un Directorio para la Tabla Externa.

Utilice el comando CREATE DIRECTORY para crear un objeto de directorio. Un objeto de directorio especifica un alias para un directorio en el sistema de archivos del servidor en el que reside un origen de datos externo. Puede utilizar nombres de directorio al hacer referencia a un origen de datos externo, en lugar de predefinir el nombre de ruta de acceso de sistema operativo, para obtener una mayor flexibilidad en la gestión de archivos. 

Debe tener privilegios del sistema CREATE ANY DIRECTORY para crear directorios. Al crear un directorio, se le otorgan automáticamente los privilegios de objeto READ y WRITE, y puede otorgar privilegios READ y WRITE a otros usuarios y roles. El DBA también puede otorgar estos privilegios a otros usuarios y roles. 

Un usuario necesita privilegios READ para todos los directorios que se utilizan en las tablas externas a las que haya que acceder y privilegios WRITE para las ubicaciones de los archivos log, de errores y de desechos que se estén utilizando. 

Además, es necesario un privilegio WRITE cuando el marco de tablas externas se utilice para descargar datos. 

Oracle proporciona además el tipo ORACLE_DATAPUMP, con el que se pueden descargar datos (es decir, leer datos de una tabla de la Base de Datos e insertarlos en una tabla externa) y recargarlos después en una Base de Datos Oracle. Se trata de una operación que sólo se realiza una vez y que se puede hacer al crear la tabla. Después de la creación y del relleno inicial, no se puede actualizar, insertar ni suprimir ninguna fila.

Sintaxis:
CREATE [OR REPLACE] DIRECTORY AS 'path_name';

En la sintaxis:

•OR REPLACE Especifique OR REPLACE para volver a crear el objeto de base de datos de directorio si ya existe. Puede utilizar esta cláusula para cambiar la definición de un directorio existente sin borrar, volver a crear y volver a otorgar privilegios de objeto de base de datos anteriormente otorgados en el directorio. Los usuarios a los que se otorgaron privilegios anteriormente en un directorio redefinido pueden seguir accediendo al directorio sin necesidad de que se vuelvan a otorgar los privilegios.

•directory Especifique el nombre del objeto de directorio que se va a crear. La longitud máxima del nombre de directorio es de 30 bytes. No se puede cualificar un objeto de directorio con un nombre de esquema.

•'path_name' Especifique el nombre de ruta de acceso completa del directorio de sistema operativo en el resultado. Observe que el nombre de ruta de acceso es sensible a mayúsculas/minúsculas. 

La sintaxis para utilizar el controlador de acceso ORACLE_DATAPUMP es la siguiente: 

CREATE TABLE extract_emps  

ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP

                       DEFAULT DIRECTORY …

                       ACCESS PARAMETERS (… )

                       LOCATION (…) 

                       PARALLEL 4  

                       REJECT LIMIT UNLIMITED AS  

SELECT * FROM …; 

Creación de una Tabla Externa.

Sintaxis:

CREATE TABLE <table_name>

( <col_name> <datatype>, … )

ORGANIZATION EXTERNAL

    (TYPE <access_driver_type>

     DEFAULT DIRECTORY <directory_name>

     ACCESS PARAMETERS

      (… ) )

      LOCATION ('<location_specifier>') )

REJECT LIMIT [0 | <number> | UNLIMITED];

•Cree tablas externas mediante la cláusula ORGANIZATION EXTERNAL de la sentencia CREATE TABLE. De hecho, no está creando una tabla. En realidad, está creando metadatos en el diccionario de datos que se pueden utilizar para acceder a datos externos. Utilice la cláusula ORGANIZATION para especificar el orden en que se almacenarán las filas de datos de la tabla. Al especificar EXTERNAL en la cláusula ORGANIZATION, indica que la tabla es de sólo lectura y que está ubicada fuera de la Base de Datos. Observe que los archivos externos deben existir ya fuera de la Base de Datos.

•TYPE <access_driver_type> indica el controlador de acceso de la tabla externa. El controlador de acceso es la API (interfaz de programación de aplicaciones) que interpreta los datos externos para la Base de Datos. Si no especifica TYPE, Oracle utiliza el controlador de acceso por defecto, ORACLE_LOADER. La otra opción es ORACLE_DATAPUMP.

•Utilice la cláusula DEFAULT DIRECTORY para especificar uno o más objetos de directorio de Base de Datos Oracle que se correspondan con directorios del sistema de archivos en los que puedan residir orígenes de datos externos.

•La cláusula opcional ACCESS PARAMETERS le permite asignar valores a los parámetros del controlador de acceso específico para esta tabla externa.

•Utilice la cláusula LOCATION para especificar un localizador externo para cada origen de datos externo. Normalmente, <location_specifier> es un archivo, pero no necesariamente.

•La cláusula REJECT LIMIT le permite especificar cuántos errores de conversión se pueden producir durante una consulta de los datos externos antes de que se devuelva un error Oracle y se aborte la consulta. El valor por defecto es 0.

Ejemplos:

--1. Primero Creé una carpeta llamada: Directorio_Externo; ruta: 'E:\Directorio_Externo'

--2. Dentro de mi carpeta, Creé un archivo .dat: Registros_Externos.dat, con un Formato:

--3. Creo mi objeto directorio:

CREATE OR REPLACE DIRECTORY dir_ext

  AS 'E:\Directorio_Externo';

---

4. Creo mi Tabla Externa(ORACLE_LOADER)

CREATE TABLE tabla_ext

(

  id_emp NUMBER(4),

  nombre VARCHAR2(35),

  fecha DATE

)

  ORGANIZATION EXTERNAL

(

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY dir_ext

  ACCESS PARAMETERS

  (

     RECORDS DELIMITED BY NEWLINE

     BADFILE dir_ext:'bad.bad'

     LOGFILE dir_ext:'log.log'

     FIELDS TERMINATED BY ','

  )

    LOCATION ('Registros_Externos.dat')

)

    PARALLEL 5

    REJECT LIMIT 200;

-- 5. Consulto mi tabla:

SELECT *

FROM tabla_ext;

-- 6. A continuación vemos un ejemplo con: ORACLE_DATAPUMP. La sentencia crea la tabla externa: extract_depts, la cual lee de las tablas employees y departments de la Base de Datos y crea en el directorio: dir_ext un archivo .dmp: extract_depts_file.dmp (dicho archivo puede ser usado para mover la data a otra Base de Datos):

CREATE TABLE extract_depts

ORGANIZATION EXTERNAL

(

  TYPE ORACLE_DATAPUMP

  DEFAULT DIRECTORY dir_ext

  LOCATION ('extract_depts_file.dmp')

)

AS

  SELECT 
        d.department_id AS id_dept,
        d.department_name AS nombre_dept,
        count(e.employee_id) AS cant_emps
  FROM departments d, employees e
  WHERE d.department_id = e.department_id
  GROUP BY d.department_id, d.department_name;
-- 7. Consultamos la nueva tabla:
SELECT *
FROM extract_depts;

-- 8. Podemos suponer que ahora estamos conectados a otra Base de Datos y queremos mover los datos anteriormente Extraídos en el archivo .dm. Esta seria la instrucción para hacerlo:

CREATE TABLE dept_2
(
  id_dept       NUMBER(4),  
  nombre_dept   VARCHAR2(30),
  cant_emps     NUMBER(3)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY dir_ext
  LOCATION ('extract_depts_file.dmp')
);

-- 9. Creamos otra tabla externa: dept_2 la cual lee del archivo: extract_depts_file.dmp localizado en el directorio: dir_ext.

SELECT *
FROM dept_2;

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1