Criando Tabelas Externas

Olá pessoal,

    No artigo de hoje, vou mostrar, de um modo simples, como criar e apresentar dados de tabelas externas no Oracle Database.
   
    O recurso de tabelas externas permite pesquisar dados em arquivos "flat" (arquivos texto em um formato conhecido ou pré-definido) como se o arquivo fosse uma tabela do Oracle, com algumas limitações. Em tabelas externas não é possível executar comandos DML de UPDATE, DELETE e INSERT.
   
    Tabelas externas utilizam o driver de acesso a dados ORACLE_LOADER, o mesmo utilizado pelo SQL Loader, para ler os arquivos flat.

    Tabelas externas são muito úteis para copiar dados dos arquivos flat para o Banco de Dados (BD), com a possibilidade de transformá-lo em outras tabelas (internas). Também podem ser utilizadas como entrada de dados para ETL (Extract, Transform, Load) em Data Warehouse.

     Para mais informações, sugiro a leitura do FAQ oficial da Oracle.
 
     

    CRIANDO E TESTANDO UMA TABELA EXTERNA

   
    PASSO 1: Arquivo CSV
 
        Crie um arquivo CSV, chamado empregados.csv, com os dados das linhas abaixo:
       
        1,Fabio Prado,fabioprado@emp.com
        2,Homer Simpson,homer@emp.com
        3,Chuck Norris,braddock@emp.com
 
       
    PASSO 2: Criação de objeto DIRECTORY no Oracle
 
        Grave o arquivo em uma pasta do Sistema Operacional e crie um objeto DIRECTORY no BD Oracle, atribuindo privilégios de leitura e escrita nesta pasta para o usuário proprietário da tabela externa (que será criada no próximo passo).
  
        No exemplo abaixo, devemos considerar que o arquivo CSV foi gravado na pasta '/tmp/teste' de um sistema de arquivos no Linux, portanto, iremos criar um objeto DIRECTORY com o nome tmp_dir apontando para essa pasta e daremos privilégio de leitura e escrita no objeto tmp_dir para um usuário do Oracle chamado HR:
       
        CREATE OR REPLACE DIRECTORY tmp_dir AS '/tmp/teste';
       
        GRANT READ, WRITE ON DIRECTORY tmp_dir TO hr;       

        Obs.: O privilégio de escrita (WRITE) no diretório é necessário para permitir criação de logs e outros arquivos relacionados ao acesso do arquivo flat. O exemplo utilizado neste artigo irá criar logs.
  
       
    PASSO 3: Tabela externa EMPREGADO
  
        Crie uma tabela externa chamada EMPREGADO que irá ler os dados do arquivo empregados.csv:
   
        CREATE TABLE EMPREGADO
        (
            ID varchar2(3),
            NOME varchar2(50),   
            EMAIL varchar2(100)
        )
        ORGANIZATION EXTERNAL
        (
          TYPE ORACLE_LOADER
          DEFAULT DIRECTORY TMP_DIR
          ACCESS PARAMETERS
              (
                RECORDS DELIMITED BY NEWLINE
                FIELDS TERMINATED BY ','
              )
        LOCATION ('empregados.csv')
        );   

    
    PASSO 4: Teste de acesso
  
        Execute a instrução SQL abaixo para ler os dados da tabela externa:
            SELECT * FROM EMPREGADO;
       
  
    PASSO 5: Teste de negação de DML
 
        Execute o UPDATE abaixo na tabela para verificar um erro que indica que não é possível atualizar dados na tabela externa:
            UPDATE EMPREGADO SET id = 4 WHERE id = 3;



Referências:    - Material do curso oficial da Oracle: Oracle Database 10G Administration Workshop I.