Neste artigo, abordarei o
recurso External Tables do Oracle Database, que permite ao DBA
consultar e importar dados externos em um banco de dados Oracle, exportar dados
para um outro banco de dados Oracle e também realizar operações de ETL com o
Oracle Database.

O External
Tables é um dos tópicos cobrados nos exames de certificação Oracle 1Z0-052
(OCA Oracle Database 11g: Administration I), 1Z0-042
(OCA Oracle Database 10g: Administration I) e 1Z0-047
(OCE SQL Expert).

1.
Tabelas externas

Tabela externa (External Table)
é um recurso bastante útil do software Oracle Database, presente a partir da
versão 9i e melhorado na seguinte, a 10g. Com tabelas externas, é possível:

• Consultar dados externos ao banco de dados
  Oracle sem ter que importá-los;
• Importar dados externos para dentro do banco de
  dados Oracle;
• Realizar operações de ETL em bancos de dados
  Oracle;
• Mover (ou compartilhar) dados entre bancos de
  dados Oracle.

Uma tabela externa é uma
tabela somente para leitura, criada internamente no banco de dados Oracle, tal
qual ocorre com uma tabela comum. Uma tabela externa é, sob
alguns aspectos, semelhante a uma View somente para leitura. Seus metadados residem dentro
do banco de dados Oracle. Porém, os seus dados ficam armazenados externamente
em um arquivo texto, localizado no sistema de arquivos do servidor. Tabelas
externas apresentam algumas limitações:

• Não é possível criar constraints em tabelas
  externas;
• Não é possível criar índices em tabelas
  externas;
• Não é possível criar campos do tipo LOB em
  tabelas externas;
• Não é possível definir um campo de uma tabela
  externa como UNUSED;
• Não é possível executar instruções DML (Insert, Update, Delete) em tabelas
  externas.

Só é possível consultar dados armazenados em tabelas
externas, e elas também podem ser criadas com instruções do tipo CTAS,
com a finalidade de exportação de dados.

2.
Cenários típicos para utilização de tabelas externas

Um cenário típico para
utilização de tabelas externas é quando há a necessidade de ler dados
provenientes de outras fontes de dados, como os dados provenientes de
um mainframe, exportados como um arquivo texto em formato SDF ou CSV, residente
no sistema de arquivos do servidor.

Outro cenário típico é quando
há a necessidade de exportar (ou compartilhar) dados entre bancos de dados
Oracle. Neste cenário, cria-se uma tabela externa em um banco de dados Oracle
com uma instrução do tipo CTAS. A tabela externa criada poderá ser acessada por
outro banco de dados Oracle para consulta. 
Esta é uma das formas de exportação/importação (ou compartilhamento) de
dados entre bancos de dados Oracle, porém, não é a única.

Tabelas externas são também
uma opção para operações de ETL. Os dados “brutos” podem ser salvos em arquivos
de texto (com formatos SDF ou CSV) no sistema de arquivos do servidor e, a partir
destes, é possível criar tabelas externas no banco de dados Oracle e acessá-los
como tabelas “normais”. Basta, então, realizar as transformações necessárias
nos dados “brutos”, beneficiando-se de toda a funcionalidade das linguagens SQL
e PL/SQL do Oracle, e carregar os dados processados nas tabelas apropriadas.

3.
O objeto Directory

Para poder ler dados a partir
de tabelas externas, ou para poder exportar dados a partir do banco de dados
Oracle para tabelas externas, é necessário, antes, criar um objeto Directory no
banco de dados Oracle.

O objeto Directory é um alias criado no banco de dados Oracle para referenciar um diretório
físico do sistema de arquivos do servidor. Um objeto Directory é um objeto em
nível de banco de dados e não um objeto de esquema.

Para criar objetos Directory é
necessário ter o privilégio de sistema Create
Any Directory que, por padrão, somente os usuários SYS e SYSTEM possuem.
Este privilégio de sistema pode ser concedido a outros usuários do banco de
dados Oracle, mas, recomendo que conceda este privilégio de sistema com bastante
cautela, pois ele pode fragilizar a segurança dos dados, especialmente no que
tange à confidencialidade dos mesmos. Para criar objetos Directory, utilize a
seguinte instrução:

Create [Or Replace] Directory <Alias_Diretorio> As <Diretorio_Fisico_Sistema_Arquivos>;

 Onde:

• [Or Replace]: (cláusula opcional) quando
  utilizada, altera a definição de um objeto Directory (caso este já exista no
  banco de dados Oracle);
• <Alias_Diretorio>: (cláusula
  obrigatória) é o nome (apelido) dado ao objeto Directory seguindo as convenções
  de nomenclatura para objetos de banco de dados Oracle, podendo ter até 30
  caracteres de comprimento;

• <Diretorio_Fisico_Sistema_Arquivos>:
   (cláusula obrigatória) é o caminho
  completo do diretório físico no sistema de arquivos do servidor, é case-sensitive.

 Exemplos:

Em sistemas GNU/Linux:

 Create Directory Dados_Logistica As ‘/media/Logistica/ArquivosTexto’;

 Create Or Replace Directory Dados_Logistica  As ‘/media/Logistica/ArquivosTexto’;

Em sistemas MS Windows:

 Create Directory Dados_Logistica As ‘D:LogisticaArquivosTexto’;

Create Or Replace Directory Dados_Logistica As ‘D:LogisticaArquivosTexto’;

O usuário que cria um objeto Directory já tem, por
padrão, os privilégios do READ e WRITE neste objeto. Porém, para
permitir que outro(s) usuário(s) do banco de dados possa(m) consultar, ou criar
tabelas externas utilizando o objeto Directory criado, é necessário conceder tais
privilégios no objeto Directory a estes usuários. Para conceder tais
privilégios, utilize uma das seguintes instruções DCL, conforme a necessidade:

GRANT READ,WRITE ON DIRECTORY <Objeto_Directory> TO <Usuario_BD>;

ou

GRANT READ ON DIRECTORY <Objeto_Directory> TO <Usuario_BD>;

ou

GRANT WRITE ON DIRECTORY <Objeto_Directory> TO <Usuario_BD>; 

Onde:

• <Objeto_Directory>: (cláusula
  obrigatória) é o nome do objeto Directory existente no banco de dados;
• <Usuario_BD>: (cláusula
  obrigatória) é o nome do usuário do banco de dados ao qual se está concedendo o
  privilégio; é possível especificar vários nomes de usuários ao mesmo tempo,
  bastando separar seus nomes por vírgulas.

Exemplos:

 GRANT READ,WRITE ON DIRECTORY Dados_RH TO HR;

GRANT READ ON DIRECTORY Dados_Logistica TO OE;

 GRANT WRITE ON DIRECTORY Dados_Vendas TO BI;

Ao criar um objeto Directory, o software Oracle Database
não gera um erro, caso o diretório físico referenciado pelo objeto não existe no
sistema de arquivos do servidor. Portanto, é possível criar um objeto Directory
antes de criar o diretório físico referenciado por este objeto no sistema de
arquivos do servidor. Porém, quando um objeto Directory for utilizado para
acessar, ou criar uma tabela externa, o software Oracle Database gerará um erro,
caso o diretório físico referenciando por este objeto não exista no sistema de
arquivos do servidor.

Para excluir (dropar) um
objeto Directory do banco de dados Oracle, será necessário ter o privilégio de
sistema DROP ANY DIRECTORY. Os usuários SYS e SYSTEM, por padrão, já possuem
estes privilégios. Este é um privilégio de sistema que eu, particularmente,
como DBA, dificilmente concedo a outros usuários do banco de dados. A instrução
utilizada para excluir um objeto Directory é a seguinte:

 DROP DIRECTORY <Objeto_Directory>;

 Onde:

•  <Objeto_Directory>: (cláusula
  obrigatória) é o nome do objeto Directory existente no banco de dados Oracle;

Quando um objeto Directory é excluído (dropado) do banco
de dados Oracle, o diretório físico referenciado por ele no sistema de arquivos
do servidor (bem como seu conteúdo) não é excluído. Caso queira excluir o
diretório físico do sistema de arquivos do servidor, deverá fazê-lo
manualmente.

 4.
Criação de tabelas externas para acesso a dados externos

Para criar uma tabela externa
para acessar dados armazenados fisicamente em um arquivo localizado no sistema
de arquivos do servidor, é necessário, antes, ter um objeto Directory criado no
banco de dados Oracle. Caso o objeto Directory tenha sido criado por outro
usuário, será necessário ter os privilégios READ/WRITE
neste objeto Directory.

É necessário conhecer
previamente o layout (formato) dos dados contidos no arquivo externo para
poder definir corretamente os parâmetros de acesso (Access Parameters) da
instrução Create Table que criará
a tabela externa no banco de dados Oracle. Os layouts (formatos) mais comuns
são:

• SDF:
  registros com tamanho fixo e campos determinados para sua posição;
• CSV: registros
  contendo campos de tamanhos variáveis separados por vírgulas;
• TXT:
  registros contendo campos de tamanhos variáveis, ou fixos que podem ser
  separados por vírgulas, ou por algum caracter específico, ou por tabulações.

Quando se cria uma tabela externa no banco de dados
Oracle, ao especificar seus atributos (campos), somente é permitido declarar o
nome do campo, seu tipo de dados e seu tamanho. Não é possível criar campos do
tipo LOB, ou criar constraints e índices para tabelas externas.

Para criar um tabela externa,
utiliza-se um dos seguintes drivers do banco de dados Oracle:

• ORACLE_LOADER:
  para acessar dados contidos em arquivo externo com lay-out (formato) SDF, CSV
  ou TXT;
• ORACLE_DATAPUMP:
  para acessar dados contidos em arquivo externo criado pelo banco de dados
  Oracle para exportação/ compartilhamento de dados.

4.1.
Criação de tabela externa para acessar dados organizados em um arquivo com
layout SDF

CREATE TABLE Funcionarios_Externa (
Codigo VARCHAR2(5 BYTE)
,Nome VARCHAR2(30 BYTE)
,DtNasc VARCHAR2(8 BYTE)
,DtContrat VARCHAR2(8 BYTE)
,SalInicial NUMBER(8,2)
,SalAtual NUMBER(8,2)
,DtReajSal VARCHAR2(8 BYTE)
,DtSaida VARCHAR2(8 BYTE)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY Dir_DadosExternos_RH
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
FIELDS 
MISSING FIELD VALUES ARE NULL (
Codigo POSITION(1:5) CHAR
,Nome POSITION(6:35) CHAR
,DtNasc POSITION(36:43) CHAR
,DtContrat POSITION(44:51) CHAR
,SalInicial POSITION(52:59) CHAR
,SalAtual POSITION(60:67) CHAR
,DtReajSal POSITION(68:75) CHAR
,DtSaida POSITION(76:83) CHAR
)  
)
LOCATION ('Funcionarios.sdf')
) REJECT LIMIT UNLIMITED;

4.2.
Criação de tabela externa para acessar dados organizados em um arquivo com
layout CSV

CREATE TABLE Funcionarios_Externa (
Codigo VARCHAR2(5 BYTE)
,Nome VARCHAR2(30 BYTE)
,DtNasc VARCHAR2(8 BYTE)
,DtContrat VARCHAR2(8 BYTE)
,SalInicial NUMBER(8,2)
,SalAtual NUMBER(8,2)
,DtReajSal VARCHAR2(8 BYTE)
,DtSaida VARCHAR2(8 BYTE)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY Dir_DadosExternos_RH
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline   
SKIP 1    
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('Funcionarios.csv')
) REJECT LIMIT UNLIMITED;

4.3.
Criação de tabela externa para acessar dados organizados em um arquivo com campos
separados por tabulações

CREATE TABLE Funcionarios_Externa (
Codigo VARCHAR2(5 BYTE)
,Nome VARCHAR2(30 BYTE)
,DtNasc VARCHAR2(8 BYTE)
,DtContrat VARCHAR2(8 BYTE)
,SalInicial NUMBER(8,2)
,SalAtual NUMBER(8,2)
,DtReajSal VARCHAR2(8 BYTE)
,DtSaida VARCHAR2(8 BYTE)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY Dir_DadosExternos_RH
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
SKIP 1
FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('Funcionarios.txt')
) REJECT LIMIT UNLIMITED;

4.4.
Criação de tabela externa para acessar dados exportados por um banco de dados
Oracle como uma tabela externa utilizando o driver ORACLE_DATAPUMP

CREATE TABLE Funcionarios_Externa (
Codigo VARCHAR2(5 BYTE)
,Nome VARCHAR2(30 BYTE)
,DtNasc VARCHAR2(8 BYTE)
,DtContrat VARCHAR2(8 BYTE)
,SalInicial NUMBER(8,2)
,SalAtual NUMBER(8,2)
,DtReajSal VARCHAR2(8 BYTE)
,DtSaida VARCHAR2(8 BYTE)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY Dir_DadosExternos_RH
LOCATION ('Funcionarios.dat')
) REJECT LIMIT UNLIMITED;

Após uma tabela externa ser
criada com sucesso no banco de dados Oracle, é possível consultar seus dados
normalmente, como qualquer tabela “normal”, ou como uma View somente para
leitura. Porém, não é
possível executar instruções DML (INSERT, UPDATE, DELETE) em uma tabela externa.

Consultando os dados da tabela
externa Funcionarios_Externa criada:

SELECT * FROM Funcionarios_Externa;

5.
Criação de tabelas externas para exportação de dados para outro banco de dados
Oracle

Para criar uma tabela externa
para exportação de dados, é necessário, antes, ter um objeto Directory criado
no banco de dados Oracle. Caso esse objeto tenha sido criado por outro
usuário do banco de dados Oracle, será necessário ter os privilégios READ/WRITE
neste objeto Directory.

A criação de uma tabela
externa, a partir de um banco de dados Oracle, para exportação de dados para
outro banco de dados Oracle gera um arquivo no sistema de arquivos do
servidor, na localização referenciada pelo objeto Directory utilizado. A criação da tabela externa é
feita com uma instrução CTAS, utilizando o driver ORACLE_DATAPUMP. Veja:

CREATE TABLE Funcionarios_Exportacao
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY Dir_DadosExternos_RH
LOCATION ('Funcionarios.dat')
) AS
SELECT *
FROM Funcionarios;

Após a tabela externa para
exportação de dados ter sido criada com sucesso, os dados exportados, contidos
no arquivo externo (gerados no sistema de arquivos do servidor), poderão ser
acessados por outro banco de dados Oracle como uma tabela externa criada
naquele outro banco de dados.

É possível dropar a tabela externa criada após exportação
de dados. Ao fazer isso, o arquivo externo gerado não será excluído
automaticamente. Caso precise excluir esse arquivo, deverá fazê-lo manualmente.
Portanto, ao tentar criar novamente a mesma tabela externa para exportação de
dados, utilizando o mesmo nome para o arquivo externo a ser gerado no mesmo
objeto Directory, o Oracle Database emitirá uma mensagem de erro, pois já
existe um arquivo externo com o mesmo nome na localização referenciada pelo
objeto Directory.

6.
Importação de dados utilizando tabelas externas

Para importar dados em banco
de dados Oracle de destino, basta criar neste uma tabela externa, a partir do
arquivo externo que já foi exportado. Feito isso, é só utilizar, por exemplo, a
instrução MERGE para realizar as transformações necessárias nos dados
importados e carregá-los na tabela de destino. Essa combinação de recursos
também permite realizar operações de ETL. Veja:

MERGE INTO Funcionarios F
USING Funcionarios_Externa FE ON (F.Codigo = FE.Codigo)
    WHEN MATCHED THEN
        UPDATE SET
            F.SalAtual = TO_NUMBER(FE.SalAtual)
            ,F.DtReajSal = TO_DATE(FE.DtReajSal,'YYYYMMDD')
            ,F.DtSaida = TO_DATE(FE.DtSaida,'YYYYMMDD')
    WHEN NOT MATCHED THEN
        INSERT (
            Codigo
            ,Nome
            ,DtNasc
            ,DtContrat
            ,SalInicial
            ,SalAtual
            ,DtReajSal
            ,DtSaida
        ) VALUES (
            FE.Codigo
            ,FE.Nome
            ,TO_DATE(FE.DtNasc,'YYYYMMDD')
            ,TO_DATE(FE.DtContrat,'YYYYMMDD')
            ,FE.SalInicial
            ,FE.SalAtual
            ,TO_DATE(FE.DtReajSal,'YYYYMMDD')
            ,TO_DATE(FE.DtSaida,'YYYYMMDD')
        );

COMMIT;

Consultando os dados
importados para a tabela Funcionarios:

 

SELECT * FROM Funcionarios ORDER BY DtContrat ASC;