Criando e usando tabelas externas

O recurso de tabelas externas é um complemento à funcionalidade existente do SQL * Loader. Permite acessar dados em fontes externas como se estivessem em uma tabela no banco de dados.

Uma tabela externa é uma tabela somente de leitura que é definida no banco de dados, mas existe fora do banco de dados. Em termos mais técnicos, os metadados da tabela externa são armazenados dentro do banco de dados e os dados que ela contém estão fora do banco de dados. As tabelas externas têm várias restrições sobre elas. Você pode consultá-los com a instrução SELECT, mas não pode usar nenhuma outra instrução DML neles. Você não pode criar um índice sobre eles e eles não aceitarão constraints.

Para criar uma tabela externa, você pode declarar suas colunas e seus tipos de dados. Você também pode preencher a tabela externa com uma subconsulta no momento em que a criou. Mas é tudo o que você pode fazer com tabelas externas. Eles são restritos de várias maneiras:

Você não pode criar uma coluna com um tipo de dados LOB – sem CLOB, BLOB, NCLOB, etc.
Você não pode adicionar uma restrição a uma tabela externa
Você não pode alterar a coluna de uma tabela externa para UNUSED. Se você tentar, o SQL processará a instrução, mas eliminará a coluna.

Basicamente, tudo o que você faz com uma tabela externa é declarar sua estrutura e definir os parâmetros pelos quais o banco de dados SQL se comunica com a tabela externa. Para estabelecer essa comunicação, você deve primeiro entender dois assuntos:

Objetos DIRECTORY
Os utilitários Oracle SQL * Loader e Oracle Data Pump

Veremos a seguir e depois criaremos uma tabela externa

Objeto DIRETORY

Para criar uma tabela externa, precisamos identificar o local no sistema operacional em que o arquivo externo que contém a tabela residirá. Para isso, precisamos examinar a instrução CREATE DIRECTORY. A instrução CREATE DIRECTORY cria um objeto no banco de dados que representa o nome de um diretório no sistema de arquivos do servidor. Aqui está um exemplo:

CREATE OR REPLACE DIRECTORY directory_name 
AS directory_reference;

Onde directory_name é um nome que você especificar, assim como faria com qualquer outro objeto de banco de dados, e directory_reference é uma cadeia de caracteres literal, cercada por aspas simples, que identifica um local no sistema de arquivos do servidor Oracle, no qual você deseja que as tabelas externas sejam armazenado. Por exemplo:

CREATE OR REPLACE DIRECTORY BANK_FILES 
AS 'F:\bnk_files\trnsfr';

O resultado desta declaração é que acabamos de criar um objeto no banco de dados denominado BANK_FILES que olha para o sistema operacional em que o servidor Oracle reside e assume que a referência de diretório na cadeia literal é consistente com a sintaxe necessária para essa operação específica sistema. Nesse caso, apontamos para uma unidade do Windows “F:” e seu diretório de nível raiz “bnk_files”, dentro do qual está o subdiretório “trnsfr” – esse subdiretório é o nosso destino.

O objeto DIRECTORY não analisa essa referência, mas apenas a armazena como está. Se estiver incorreto, você não descobrirá mais tarde quando tentar usar o objeto DIRECTORY. Além disso, o objeto DIRECTORY não criará o subdiretório; a suposição aqui é que o subdiretório já existe. Caso contrário, você não receberá uma mensagem de erro até usar o objeto DIRECTORY posteriormente. As palavras-chave OU SUBSTITUIR são opcionais. Em nosso exemplo, o nome BANK_FILES é um nome que especificamos. Este nome é o nome atribuído ao objeto e é como referenciaremos o objeto DIRECTORY no futuro.

Depois que um diretório é criado, o proprietário deve conceder acesso de LEITURA e / ou GRAVAÇÃO a qualquer usuário que possa usá-lo:

GRANT READ ON DIRECTORY directory_name TO username;

Isso inclui usuários que desejam usar tabelas externas criadas com os objetos de diretório.

Utilitários Oracle

O banco de dados Oracle fornece vários utilitários que acompanham seu produto de banco de dados. Os utilitários importantes para tabelas externas incluem:

SQL*Loader
Oracle Data Pump Export
Oracle Data Pump Import

Cada um deles está documentado no manual de referência da Oracle Corporation intitulado “Oracle Utilities”. Juntos, os utilitários fornecem recursos que permitem que fontes de dados externas se comuniquem com objetos SQL no banco de dados. Uma revisão completa de seus recursos está além do escopo do exame. Mas é importante que o exame reconheça que um grande componente das definições associadas à declaração de uma tabela externa vem desses utilitários.

Criando uma tabela externa

Vamos dar um exemplo. Digamos que tenhamos um arquivo de texto externo contendo os seguintes dados sobre faturas:

ID  INV_DATE ACCT_NO
701 03/15/09 CODDA009
702 03/17/09 CODDA010
703 03/18/09 CODDA011

Queremos criar uma tabela externa para esses dados; vamos chamá-lo de INVOICE_DATA.TXT. Primeiro, vamos ao sistema de arquivos no qual o banco de dados Oracle reside, localizamos a mesma unidade e criamos um subdiretório fora do nível raiz. Vamos chamá-lo de “LOAD_INVOICES”. Em seguida, criamos o objeto DIRECTORY associado:

CREATE DIRECTORY INVOICE_FILES AS '\LOAD_INVOICES';

A essa altura, não precisamos necessariamente criar o diretório LOAD_INVOICES nem colocar o arquivo INVOICE_DATA.TXT nesse diretório. Mas, pelo bem do nosso exemplo, agora o fazemos, antes de continuar. Em seguida, executamos uma instrução CREATE TABLE que referencia o diretório, juntamente com as cláusulas necessárias para instruir o Oracle SQL a carregar o arquivo externo e como carregá-lo:

CREATE TABLE INVOICES_EXTERNAL 
( INVOICE_ID CHAR(3), 
  INVOICE_DATE CHAR(9), 
  ACCOUNT_NUMBER CHAR(13) 
)
ORGANIZATION EXTERNAL 
  (TYPE ORACLE_LOADER 
   DEFAULT DIRECTORY INVOICE_FILES
   ACCESS PARAMETERS 
   (  RECORDS DELIMITED BY NEWLINE 
      SKIP 2 
      FIELDS( INVOICE_ID CHAR(3), 
              INVOICE_DATE CHAR(9), 
              ACCOUNT_NUMBER CHAR(13)) 
   )
LOCATION ('INVOICE_DATA.TXT' )
);

Depois que essa instrução é executada, terminamos com uma tabela externa no banco de dados chamada INVOICES_EXTERNAL.

Observe as linhas 2 a 4 em que declaramos nossa tabela usando os tipos de dados CHAR. Você deve se lembrar de que esses são tipos de dados de tamanho fixo. Fizemos isso para acomodar a transferência de linhas do arquivo de texto nas linhas 12 a 14. Cada tipo de dados da coluna é definido como CHAR, o tipo de dados alfanuméricos de comprimento fixo e as contagens para cada tipo de dados correspondem às contagens do colunas no arquivo de texto ‘INVOICE_DATA.TXT’, identificado na linha 16 e no diretório armazenado no objeto de diretório INVOICE_FILES, nomeado na linha 8.
As linhas 1 a 5 formam uma instrução CREATE TABLE completa por si mesmas, sem a cláusula de tabela externa. Mas, começando na linha 6, estão as palavras-chave e cláusulas usadas para declarar a tabela externa e, juntas, as linhas 1 a 17 formam a instrução CREATE TABLE completa para o nosso exemplo.
A linha 6 inclui as palavras-chave ORGANIZATION EXTERNAL, necessárias
A linha 7 é onde especificamos que estamos usando ORACLE_LOADER, também conhecido como os recursos do SQL * Loader. Um valor alternativo de TYPE aqui seria ORACLE_DATAPUMP.
A linha 9 inicia o conjunto de valores para PARÂMETROS DE ACESSO, que são colocados entre parênteses que abrem na linha 10 e fecham na linha 15.
Três PARÂMETROS DE ACESSO são usados aqui: RECORDS, SKIP e FIELDS.
Linha 10 – REGISTROS DELIMITADOS POR NOVA LINHA – significa que cada nova linha inicia uma nova linha de dados para a tabela INVOICES_EXTERNAL.
A linha 11 – SKIP 2 – informa ao ORACLE_LOADER que as duas primeiras linhas do arquivo INVOICE_DATA.TXT devem ser ignoradas – elas contêm apenas informações de cabeçalho.
A linha 12 – FIELDS – inicia as especificações de cada coluna, onde o comprimento de cada coluna é cuidadosamente especificado para corresponder ao comprimento no arquivo INVOICES_DATA.TXT.

Existem muitos outros PARÂMETROS DE ACESSO que não são chamados aqui mas você não precisa disso para o exame.

Usando tabela externa

Depois de criar uma tabela externa, podemos usar SELECT a partir dela, como qualquer outra tabela. Por exemplo:

select * from invoices_external

Muitas tabelas externas começarão com dados de origem aproximados e não formatados. No entanto, o primeiro passo é apenas obtê-lo no banco de dados. Feito isso, você pode usar as várias funções de conversão e outros recursos do SQL para limpar e reformatar os dados:

Obs: Tabelas externas podem ser consultadas como qualquer tabela ou view no banco de dados. Lembre-se de que você não pode usar instruções INSERT, UPDATE ou DELETE em tabelas externas.

Referência

OCA Oracle Database Exam Guide. Páginas 142 – 148

Referência

Compartilhe isso: