A definição de tabela externa assemelha-se a uma “view” que permite o banco de dados consultar os dados externos sem que os dados estejam carregados na base de dados. Pode-se, por exemplo realizar operações de “selects”, “joins”, “order by” usando dados das tabelas do banco de dados e dados externos. Pode-se ainda criar views e sinônimos para tabelas externas.
O banco de dados Oracle fornece os meios de definição dos metadados para tabelas externas através do comando “CREATE TABLE... ORGANIZATION EXTERNAL”.
Neste artigo serão descritos os passos para a criação e manutenção de uma tabela externa !
Para demonstrar a criação e manutenção de uma tabela externa criaremos um arquivo texto chamado “estados.dat”, cujo o conteúdo segue a abaixo.
SP,Sao Paulo
RJ,Rio de Janeiro
MG,Minas Gerais
TO,Tocantins
MA,Maranhao
AM,Amazonas
RS,Rio Grande do Sul
MS,Mato Grosso do Sul
SC,Santa Catarina
PR,Parana
DF,Distrito Federal
PE,Pernambuco
[oracle]$ pwd
/home/oracle/dir
[oracle]$ ls -ltr
-rw-r--r-- 1 oracle dba 190 Mar 14 17:21 estados.dat
Este arquivo foi criado no caminho [/home/oracle/dir] e deverá ser referenciado no banco de dados através da criação de um diretório.
Abaixo temos a criação de um diretório dentro do banco de dados, para indicar onde o arquivo texto “estados.dat” está localizado.
SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS '/home/oracle/dir';
Directory created.
Após a criação do diretório deve-se criar a “tabela externa” para acessar o arquivo texto.
SQL> CREATE TABLE ESTADOS_EXT
(
SIGLA VARCHAR2(2),
NOME_ESTADO VARCHAR2(40)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_TABLES
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('estados.dat')
)
REJECT LIMIT UNLIMITED;
Table created.
A cláusula REJECT LIMIT especifica que não existirão limites para o número de erros que poderão ocorrer durante uma consulta ao dado externo. (mais informações ver manual Oracle 11g SQL Reference).
Para consultar as informações, basta simplesmente executar o “select” abaixo.
SQL> SELECT * FROM ESTADOS_EXT;
SI NOME_ESTADO
-- ----------------------------------------
SP Sao Paulo
RJ Rio de Janeiro
MG Minas Gerais
TO Tocantins
MA Maranhao
AM Amazonas
RS Rio Grande do Sul
MS Mato Grosso do Sul
SC Santa Catarina
PR Parana
DF Distrito Federal
PE Pernambuco
13 rows selected.
Para criar uma view a partir de uma “tabela externa”, usar:
SQL> CREATE VIEW ESTADOS_TESTE
AS SELECT * FROM ESTADOS_EXT
WHERE SIGLA LIKE 'M%';
View created.
Acessando dados da view ESTADOS_TESTE.
SQL> SELECT * FROM ESTADOS_TESTE;
SI NOME_ESTADO
-- ----------------------------------------
MG Minas Gerais
MA Maranhao
MS Mato Grosso do Sul
As informações dos objetos podem ser acessadas através das “views” DBA_OBJECTS, ALL_OBJECTS ou USER_OBJECTS.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'ESTADOS_EXT';
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
ESTADOS_EXT TABLE
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EXT_TABLES';
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
EXT_TABLES DIRECTORY
Incluindo uma coluna numa tabela externa
Inicialmente no arquivo texto chamado “estados.dat”, foi incluída uma nova coluna de regiões.
SP,Sao Paulo,Sudeste
RJ,Rio de Janeiro,Sudeste
MG,Minas Gerais,Sudeste
TO,Tocantins,Centro-Oeste
MA,Maranhao,Centro-Oeste
AM,Amazonas,Norte
RS,Rio Grande do Sul,Sul
MS,Mato Grosso do Sul,Centro-Oeste
SC,Santa Catarina,Sul
PR,Parana,Sul
DF,Distrito Federal,Centro-Oeste
PE,Pernambuco,Nordeste
Para alterar a tabela externa no banco de dados, utilizar o comando “ALTER TABLE”.
SQL> ALTER TABLE ESTADOS_EXT ADD(REGIAO VARCHAR(40));
Table altered.
SQL> DESC ESTADOS_EXT
Name Null? Type
----------------------------------------- -------- ---------------------------
SIGLA VARCHAR2(2)
NOME_ESTADO VARCHAR2(40)
REGIAO VARCHAR2(40)
Consultando as informações.
SQL> SELECT * FROM ESTADOS_EXT;
SI NOME_ESTADO REGIAO
-- --------------------------------------- ------------------------------------
SP Sao Paulo Sudeste
RJ Rio de Janeiro Sudeste
MG Minas Gerais Sudeste
TO Tocantins Centro-Oeste
MA Maranhao Centro-Oeste
AM Amazonas Norte
RS Rio Grande do Sul Sul
MS Mato Grosso do Sul Centro-Oeste
SC SantaCatarina Sul
PR Parana Sul
DF Distrito Federal Centro-Oeste
PE Pernambuco Nordeste
13 rows selected.
Carregando informações de texto para tabelas do banco de dados
Para carregar informações de texto para as tabelas do banco de dados, a partir das “tabelas externas”, deve-se utilizar o comando "INSERT INTO… SELECT FROM" ao invés de utilizar o utilitário SQL*Loader. Este método é mais prático e eficiente.
Criando uma tabela vazia.
SQL> CREATE TABLE ESTADOS (
UF CHAR(2),
NOME VARCHAR2(40));
Table created.
SQL> DESC ESTADOS
Name Null? Type
----------------------------------------- -------- ---------------------------
UF CHAR(2)
NOME VARCHAR2(40)
Carregando informações para a tabela no banco de dados.
SQL> INSERT INTO ESTADOS (UF,NOME) SELECT * FROM ESTADOS_EXT;
13 rows created.
SQL> COMMIT;
Commit complete.
Acessando dados na tabela.
SQL> SELECT * FROM ESTADOS;
UF NOME
-- ----------------------------------------
SP Sao Paulo
RJ Rio de Janeiro
MG Minas Gerais
TO Tocantins
MA Maranhao
AM Amazonas
RS Rio Grande do Sul
MS Mato Grosso do Sul
SC Santa Catarina
PR Parana
DF Distrito Federal
PE Pernambuco
13 rows selected.
Criando uma “tabela externa” através de tabelas do banco de dados
Para criar uma “tabela externa” a partir de uma tabela do banco de dados, basta utilizar o comando “CREATE TABLE...ORGANIZATION EXTERNAL” (opção disponível a partir da versão 10g).
Consulta a tabela do banco de dados “estados”.
SQL> DESC ESTADOS
Name Null? Type
---------------------------------------- -------- ----------------------------
UF CHAR(2)
NOME VARCHAR2(40)
SQL> SELECT * FROM ESTADOS;
UF NOME
-- ----------------------------------------
SP Sao Paulo
RJ Rio de Janeiro
MG Minas Gerais
TO Tocantins
MA Maranhao
AM Amazonas
RS Rio Grande do Sul
MS Mato Grosso do Sul
SC Santa Catarina
PR Parana
DF Distrito Federal
PE Pernambuco
BA Bahia
ES Espirito Santo
SE Sergipe
RN Rio Grande do Norte
AL Alagoas
CE Ceara
PB Paraiba
RR Roraima
RO Rondonia
MT Mato Grosso
AC Acre
PA Para
PI Piaui
GO Goias
26 rows selected.
Criação da “tabela externa” export_estados.
SQL>CREATE TABLE EXPORT_ESTADOS
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_TABLES
LOCATION ('estados.txt')
) AS SELECT * FROM ESTADOS;
Table created.
Verificação da nova tabela externa criada.
SQL> SELECT * FROM EXPORT_ESTADOS;
UF NOME
-- ----------------------------------------
SP Sao Paulo
RJ Rio de Janeiro
MG Minas Gerais
TO Tocantins
MA Maranhao
AM Amazonas
RS Rio Grande do Sul
MS Mato Grosso do Sul
SC Santa Catarina
PR Parana
DF Distrito Federal
PE Pernambuco
BA Bahia
ES Espirito Santo
SE Sergipe
RN Rio Grande do Norte
AL Alagoas
CE Ceara
PB Paraiba
RR Roraima
RO Rondonia
MT Mato Grosso
AC Acre
PA Para
PI Piaui
GO Goias
26 rows selected.
Lendo informações de arquivos textos compactados
A partir da versão 11g, o Oracle permite que os arquivos externos possam estar compactados (nos formatos *.zip, *.gzip, entre outros), para utilizar essa funcionalidade deve-se seguir os passos abaixo:
Criar um diretório onde serão executadas as descompactações.
SQL> CREATE OR REPLACE DIRECTORY EXECDIR AS '/home/oracle/dir';
Directory created.
Dar permissão de execução no diretório criado.
SQL> GRANT EXECUTE ON DIRECTORY EXECDIR TO PUBLIC;
Grant succeeded.
Dar permissão de leitura e gravação no diretório onde ficará armazenado o arquivo compactado.
SQL> GRANT READ, WRITE ON DIRECTORY EXT_TABLES TO PUBLIC;
Grant succeeded.
Criar a nova tabela externa.
SQL> CREATE TABLE ESTADOS_EXT_ZIP
2 (
3 SIGLA VARCHAR2(2),
4 NOME_ESTADO VARCHAR2(40)
5 )
6 ORGANIZATION EXTERNAL
7 (
8 TYPE ORACLE_LOADER
9 DEFAULT DIRECTORY EXT_TABLES
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE
13 PREPROCESSOR EXECDIR:'zcat' -- > Parâmetro necessário para
14 executar a descompactação.
15 FIELDS TERMINATED BY ","
16 )
17 LOCATION ('estBR.zip') -- > Arquivo compactado.
18 )
19 REJECT LIMIT UNLIMITED;
Table created.
Para acessar os dados da tabela basta somente executar um “select”.
SQL> select * from ESTADOS_EXT_ZIP;
SI NOME_ESTADO
-- ----------------------------------------
SP Sao Paulo
RJ Rio de Janeiro
MG Minas Gerais
TO Tocantins
MA Maranhao
AM Amazonas
RS Rio Grande do Sul
MS Mato Grosso do Sul
SC Santa Catarina
PR Parana
DF Distrito Federal
PE Pernambuco
BA Bahia
ES Espirito Santo
SE Sergipe
RN Rio Grande do Norte
AL Alagoas
CE Ceara
PB Paraiba
RR Roraima
RO Rondonia
MT Mato Grosso
AC Acre
PA Para
PI Piaui
GO Goias
26 rows selected.
Arquivos de apoio utilizados para esta funcionalidade:
Conteúdo do arquivo “estBR.zip” este deverá ser compactado e gravado no mesmo diretório indicado comando “create directory” no banco de dados, para esse exemplo será no caminho [/home/oracle/dir].
SP,Sao Paulo
RJ,Rio de Janeiro
MG,Minas Gerais
TO,Tocantins
MA,Maranhao
AM,Amazonas
RS,Rio Grande do Sul
MS,Mato Grosso do Sul
SC,Santa Catarina
PR,Parana
DF,Distrito Federal
PE,Pernambuco
BA,Bahia
ES,Espirito Santo
SE,Sergipe
RN,Rio Grande do Norte
AL,Alagoas
CE,Ceara
PB,Paraiba
RR,Roraima
RO,Rondonia
MT,Mato Grosso
AC,Acre
PA,Para
PI,Piaui
GO,Goias
Conteúdo do script shell "zcat", utilizado para a descompactação dinâmica do arquivo.
/bin/zcat -f $1
Onde a opção “-f” irá forçar a descompactação mesmo que o arquivo já exista e a variável “$1” servirá de passagem para o comando “zcat”.
Removendo tabelas externas
Para remover uma tabela externa, basta executar o comando “DROP TABLE”, este comando irá remover o metadado do banco de dados, não afetando os dados que estão gravados externamente em disco.
Restrições de tabelas externas
- Tabelas externas não suportam colunas criptografadas, tanto para exportação quanto para importação.
- Não descrevem todos os dados que são armazenados no banco de dados.
- Não descrevem como os dados são armazenados na fonte externa. Esta é a função dos parâmetros de acesso.
- Para processamento de colunas: O recurso de tabelas externas recupera todas as colunas definidas para uma tabela externa. Isso garante um resultado consistente conjunto de todas as consultas. No entanto, por motivos de desempenho, pode-se optar em processar apenas as colunas referenciadas de uma tabela externa, minimizando a quantidade de conversão e manipulação de dados necessária para executar uma consulta.
- Não carrega dados de colunas do tipo LONG.
- Strings de SQL não podem ser especificados nos parâmetros de acesso ao driver ORACLE_LOADER. Como solução, pode-se usar a cláusula DECODE no comando SELECT que está lendo a tabela externa. Outra opção seria criar uma “view” da tabela externa que usa a cláusula DECODE e selecionar os dados a partir dessa “view”, dispensando o acesso a tabela externa.
- Os identificadores (por exemplo, nomes de colunas ou nome de tabelas) são especificados nos parâmetros de acesso externo da tabela, certos valores são considerados palavras reservadas pelo banco de dados. Se uma palavra reservada for utilizada como um identificador, ele deverá ser colocado entre aspas duplas “”.
Conclusão
Através deste artigo vimos algumas possibilidades de trabalho com “tabelas externas”, que permitem o acesso a informações ainda em modo texto. Tabelas externas fornecem uma maneira conveniente e prática para manipular dados dentro e fora do banco de dados. Na próxima vez que um processo de carga for definido em seu ambiente considere o uso tabelas externas.
Maiores informações podem ser obtidas através do manual (Oracle Database Utilities 11g).
Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com
