External Tables en Oracle 9i

El objetivo de del uso de las tablas externas es otorgar una alternativa al uso del SQL Loader o de la UTL FILE para la lectura de archivos planos. En muchos lugares la carga de información, sobre todo cuando se trata de grandes volúmenes, se efectúa por medio de un archivo plano que por lo general es generado por algún sistema satélite. Con las tablas externas se pueden seleccionar datos de archivos con los mismos comandos con los cuales seleccionamos datos de tablas de la base de datos. Sobre estas no se pueden crear índices o ejecutar cualquier sentencia del tipo DML (UPDATE, INSERT, o DELETE). Esto se puede hacer casi tan simple como poner un archivo en un directorio y hacer un “SELECT” de dicho archivo.

Pasos para la creación de una tabla externa.

• En primer lugar debemos crear el directorio para los archivos.
• El directorio debe existir y con el usuario SYS se debe correr el siguiente comando.
• CREATE OR REPLACE DIRECTORY nombre_directorio AS 'C:\path\';
• Haciendo un query a la DBA_OBJECTS podemos encontrar un objeto del tipo DIRECTORY con el nombre que le hayamos puesto.
• Una vez creado el directorio debemos poner el archivo que contiene la información en dicho directorio.
• Ahora es hora de crear la tabla que vinculará el contenido del archivo con el motor de base de datos.

CREATE TABLE nombre_tabla
(nombre_campo1 NUMBER(4),
nombre_campo1 VARCHAR2(25),
nombre_campo1 date)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY nomre_directorio
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
skip 1
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS)
LOCATION ('archivo_plano.dat'))
REJECT LIMIT UNLIMITED;

El REJECT LIMIT UNLIMITED especifica que no hay límite en el número de errores durante la carga del archivo a la tabla.

Nota: En este ejemplo usamos un archivo que se llama “archivo_plano.dat” con tres columnas con un terminador de línea que esta implícito en el NEWLINE y un separador de campos que es el caracter “,”.

Una vez generada la tabla se puede hacer un insert a otra tabla para poder contener los datos que necesitemos dentro de la base de datos.

• INSERT INTO nombre_tabla_int... SELECT * FROM nombre_tabla_ext;

Depurar una tabla con un campo LOB

Objetivo:

Pasar toda la información que contiene
una tabla con un campo LOB del on-line
al esquema histórico, dejando libre el
espacio que ocupó el LOB.
Esto se podría realizar, en una tabla
normal con solo correr el TRUNCATE,
pero para poder llevar a la posición
inicial la high water mark del campo
LOB es necesario correr un comando
específico.

* Export de la tabla.
* Import en una tabla de información histórica.
* Truncate de la trabla del on-line.
* Corrida de comando para bajar la high water mark del campo LOB.

ALTER TABLE owner.table_name MOVE LOB(nombre_de_campo) STORE AS nombre_del_LOB
(TABLESPACE nombre_del_tablespace STORAGE (INITIAL 64K NEXT 64K))

Cursor ORACLE

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE parent_rec IS RECORD (part_num dbms_sql.number_table,
part_name dbms_sql.varchar2_table);
p_rec parent_rec;

CURSOR c IS
SELECT part_num, part_name
FROM parent;

l_done BOOLEAN;

BEGIN OPEN c;
LOOP
FETCH c BULK COLLECT INTO p_rec.part_num, p_rec.part_name LIMIT 500;

l_done := c%NOTFOUND;
FOR i IN 1 .. p_rec.part_num.COUNT
LOOP
p_rec.part_num(i) := p_rec.part_num(i) * 10;
END LOOP;

FORALL i IN 1 .. p_rec.part_num.COUNT
INSERT INTO child (part_num, part_name)
VALUES (p_rec.part_num(i), p_rec.part_name(i));

EXIT WHEN (l_done);
END LOOP;

COMMIT;

CLOSE c;

END fast_way;
/