Tabelas externas

O assunto de hoje será sobre um interessante recurso disponível no Oracle 10g: as tabelas externas. Como o nome diz, se trata de uma tabela ou arquivo que não reside no banco de dados. Com esse recurso é possível integrar diferentes bases e aplicações de forma simples e rápida. Usando recursos de paralelismo para geração dos dados, ou fazendo select's com ordenação envolvendo tabelas externas e tabelas nativas, insert's em tabelas da base através de select's em tabelas externas e por ae afora.

Vamos à pratica.

Criaremos um arquivo bancos.csv na pasta d:\oracle\externo, contendo o seguinte:

237,Banco Bradesco
1,Banco do Brasil S.A
356, Santander Brasil
2, Caixa Economica Federal
424, Banco Noroeste

Importante ressaltar que os arquivos devem estar no servidor onde roda o banco de dados, que nesse caso é windows.

O próximo passo é criar uma referência no BD para o diretório onde está o arquivo externo, atraves do comando create directory.

Create or replace directory dir_ext as 'd:\oracle\externo';
Create or replace directory dir_bad as 'd:\oracle\externo\bad';
Create or replace directory dir_log as 'd:\oracle\externo\log';

Supondo que temos um usuário financeiro, iremos conceder grant's de leitura e gravação nesse diretório para ele.

grant read, write on directory dir_ext to financeiro;
grant read, write on directory dir_bad to financeiro;
grant read, write on directory dir_log to financeiro;

Conn financeiro/financeiro;

Agora vamos criar duas tabelas: Bancos_ext e Bancos.

create table bancos
(
Banco_id number(4),
Banco_nome varchar2(30)
);

create table bancos_ext
(
Banco_id number(4),
Banco_nome varchar2(30)
)
organization external
(
type oracle_loader default directory dir_ext access parameters
(
records delimited by newline
badfile dir_bad:'bancos%a_%p.bad'
logfile dir_log:'bancos%a_%p.log'
fields terminated by ','
missing field values are null
(
banco_id,
banco_nome
)
)
Location ('bancos.csv')
)
Parallel
reject limit unlimited;

Vamos analisar o comando:
- Organization external: indica que se trata de uma tabela externa ao BD Oracle.
- Type oracle_loader: a interface usada pra fazer a carga será o oracle loader.
- Default directory dir_ext access parameters: indica o diretorio onde está o arquivo, nesse
caso definido pelo objeto directory dir_ext.
- Acces Parameters: Parametros de localização do arquivo no sistema operacional , badfiles,
logfiles, etc;
- Records delimitd by newline: o banco entenderá que ao encontrar um fim de linha irá
adicionar um novo registro na tabela.
- Badfile dir_Bad: caso não queira gerar badfile substitua por NOBADFILE;
- Logfile dir_log: caso não queira gerar log substitua por NOLOGFILE.

Apos criar a tabelas, habilitamos o paralelismo para dml (operações de insert, delete e update), e em seguida executamos um insert na tabela bancos fazendo leitura na tabela externa.

alter session enable parallel dml;

insert into bancos
(
Banco_id,
Banco_nome
)
Select * From bancos_ext;

Vamos para o próximo caso. Join envolvendo uma tabela externa e uma tabela do DB.

Select * from agencias a, bancos_ext b
where
a.banco_id = b.banco_id
and b.banco_id = 1;

Limitações de uso de tabela externa

Em uma tabela externa não é possível realizar uma operação de DML (insert, delete e update), tampouco criar um índice, embora seja possível fazer uma ordenação.

Não é permitido também criar tabelas externas com campos do tipo clob, blob e long.

Criação de tabela externa usando datapump e paralelismo

Nesse exemplo criaremos uma tabela externa a partir de uma tabela do BD.

Create agencias_ext (banco_id,agencia_id,endereco,numero,bairro,cidade,uf)
Organization external
(type oracle_datapump
default directory dir_ext
location ('agencias.csv'))
parallel 4
as
select banco_id,agencia_id,endereco,numero,bairro,cidade,uf
from agencias;

Podemos ainda gerar os dados em múltiplos diretórios simultaneamente:

create or replace directory dir_ext_1 as 'd:\oracle\externo\dir1';

create or replace directory dir_ext_2 as 'd:\oracle\externo\dir2';

Create agencias_ext (banco_id,agencia_id,endereco,numero,bairro,cidade,uf)
Organization external
(type oracle_datapump
default directory dir_ext
location ('dir_ext_1:agencias.csv','dir_ext_2:agencias.csv'))
parallel 2
as
select banco_id,agencia_id,endereco,numero,bairro,cidade,uf
from agencias;

É isso aí...

[]'s