Oracle External Table - Flat File CSV

Bom dia a todos,

Hoje vim apenas adicionar um passo a passo que utilizo esporadicamente. Por diversas vezes utilizamos flat files em ETL, ou mesmo como origem em ferramentas OLAP ou Data Discovery. Mas e se quisermos manipular os dados, ou apenas visualiza-los, ou interagir? Existe a possibilidade de utilizarmos a linguagem SQL para TAL! A mágica do mago Larry Ellison(Dono da Oracle) sempre nos ajuda, ele nunca está atrasado ou adiantado, e sim sempre na hora.

Segundo a definição da Oracle-BASE (https://oracle-base.com), "External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader." ou em um bom português, "External tables permitem que o Oracle (Banco de Dados) faça consultas a arquivos externos ao banco, em arquivos flat. O driver do ORACLE_LOADER pode ser utilizado para acessar qualquer data guardada em qualquer formato que seja lido pelo driver".

Sem mais delongas, deixo abaixo um exemplo para criar uma tabela tendo como origem um flat file do tipo CSV:

1. Ensinar para o Oracle onde que o seu flat file está. Para isso devemos criar um diretório dentro do sql plus.
• Logue no sql plus com um usuário que tenha o privilégio de CREATE ANY DIRECTORY.
• Crie o diretório: CREATE OR REPLACE DIRECTORY DIRETORIO AS '/u01/app/oracle/oradata/';
• Dê as permissões necessárias para o usuário/schema de banco que irá criar/ler a external table: GRANT READ, WRITE ON DIRECTORY DIRETORIO TO scott;
2. Execute o comando para criar a tabela

CREATE TABLE NOME_DA_TABELA

   (

        NOME_COLUNA_NUMBER NUMBER(10,0), 

NOME_COLUNA_CHAR CHAR(15 BYTE), 

NOME_COLUNA_DATE DATE

   )

ORGANIZATION EXTERNAL

 ( TYPE ORACLE_LOADER

   DEFAULT DIRECTORY DIRETORIO

   ACCESS PARAMETERS

    (   

RECORDS DELIMITED BY newline   

SKIP 1    

FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'

MISSING FIELD VALUES ARE NULL

    )

   LOCATION ('NOME_DO_SEU_FLAT_FILE')

 )

REJECT LIMIT UNLIMITED

;

Atualização:

1- Caso dê algum erro parecido com:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file "nome do seu arquivo"

Altere "RECORDS DELIMITED BY newline" por "RECORDS DELIMITED BY \n"

2- Caso precise inserir registros com acentuação:

Basta após o "SKIP 1" adicionar "CHARACTERSET WE8ISO8859P1".

Chegamos ao fim. basta agora realizar select nessa external table! Lembrando que em external tables não é possível realizar DML! Para isso deve se criar uma outra tabela utilizando um CREATE TABLE as SELECT NOME_DA_TABELA.

Obrigado mais uma vez pela atenção