Social Icons

28 de set. de 2010

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.

15 comments:

  1. Olá Fábio,

    Em que momento do dia a dia nos vemos na necessidade de criar e utilizar tabelas externas?

    ResponderExcluir
  2. Olá Erik, vou citar 2 exemplos.

    1- Tabela externa é um ótimo recurso para integrar sistemas. Se vc tem um sistema que gera um arquivo XML para alimentar dados de outro sistema, ao invés de construir um programa para ler este arquivo e carregar os dados dele nas tabelas do sistema destino, crie uma tabela externa para ler os dados do arquivo XML. Issó é mais rápido e mais simples.

    2- Até o Oracle 10G, muitos DBAs (assim como eu) criavam tabelas externas para ler o conteúdo do alert.log. Este caminho era mais fácil que se logar no servidor para executar um comando do sistema operacional e ler o conteúdo do arquivo. No 11G não é mais necessário, pois agora existe uma tabela do próprio BD (X$DBGALERTEXT) que permite ler o alert.log.

    ResponderExcluir
  3. Como posso fazer isso no SQL Server ou MySql?

    ResponderExcluir
    Respostas
    1. Não sei, conheço SQL Server e MySQL, mas sou especialista em Oracle.

      Excluir
  4. Se o arquivo "empregados.csv" não existir, a tabela é criada?

    ResponderExcluir
    Respostas
    1. Sim, mas vai dar erro qdo vc tentar fazer um SELECT nela.

      Excluir
    2. Olá Fábio blz,

      Eu executei os procedimentos mostrados acima criação do diretório, permissão do diretório, criação do arquivo na pasta, e por fim realizei a criação da tabela tudo isso com exito.
      Mas ocorre um problema na realização da consulta.
      ORA - 29913: erro ao executar chamada ODCIEXTTABLEOPEN
      ORA - 29400: erro no cartucho de dados
      KUP - 04040: file empregados.csv in DOCUMENTO no found
      ORA -61512: em "SYS.ORACLE_LOADER", line 19

      Quero saber se vc pode me dar alguma dica de como solucionar o problema?

      Excluir
    3. Brunno, a mensagem me leva a crer que o arquivo empregados.csv está na pasta errada. Ele tem que existir na pasta do DIRECTORY, o usuário dono da tabela tem que ter privilégios de leitura e escrita nesse directory, e o dono do software oracle (usuário do sistema operacional) também tem que ter privilégios de leitura,escrita na pasta física do arquivo, ok?

      []s

      Excluir
  5. Boa tarde, estou com o seguente problema, minha tabela esta declarada com o campo de varchar2(500) o arquivo tras para a mesma coluna mais de 255 caracteres ao tentar fazer o select ele reclama que so é permitido 255, tem como eu aumentar para 500 por exemplo

    ResponderExcluir
    Respostas
    1. Carolina, nunca passei por este problema, isso não deveria acontecer. Se quiser me passa em meu e-mail o arquivo texto e o ddl para criar a tabela para eu testar e tentar descobrir o problema.
      []s

      Excluir
    2. Qual o seu e-mail para eu te passar um exemplo, o meu é caperezc@gmail.com Obrigada

      Excluir
    3. Meu e-mail você encontra na página SOBRE

      Excluir
    4. Encontrei seu problema. Pesquise pela cláusula datatype_spec no arquivo de controle. Quando isso não é especificado ele assume que os dados no arquivo de dados tem um tamanho padrão de 255, ok?

      Excluir
    5. Fabio, valeu funcionou. Obrigada

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)