artigo SQL Magazine 03 - IMPORTAÇÃO COM SQL*LOADER

O SQL*Loader é um aplicativo de importação que acompanha o Oracle, tendo como diferencial a flexibilidade na configuração, pois utiliza uma linguagem de scripts para importar os dados.

Entre os recursos disponibilizados pela linguagem de script, é possível validar cada registro antes da importação, definir valores default, aplicar alterações sobre os registros lidos, separar os dados importados em duas ou mais tabelas, entre outros. O SQL Loader também permite a importação de tipos BLOB, objetos complexos e coleções de dados.

O utilitário foi projetado para importação de arquivos texto que tenham formato de tabela, onde cada registro é representado por uma linha, os campos podem ter tamanho fixo ou variável e qualquer caracter pode ser utilizado para separá-los.

O script de importação deve ser salvo em um arquivo texto, conhecido como arquivo de controle (Control File). O SQL*Loader é um aplicativo de linha de comando, que interpreta o arquivo de controle passado como parâmetro. A linguagem utilizada no script é específica, denominada SQL*Loader´s DDL (Data Definition Language). A documentação completa desta linguagem está disponível na instalação do Oracle.

Sintaxe do Control File

Um arquivo de controle é dividido em três seções. A primeira contém informações globais, como localização do arquivo de dados. A segunda contém um ou mais blocos "INTO TABLE", indicando a tabela que receberá os dados. A terceira, iniciada por BEGINDATA, é opcional, contendo os valores que serão importados, caso um arquivo externo não seja especificado na primeira seção. A formatação deste arquivo é livre, podendo conter múltiplas linhas e comentários, que devem ser precedidos por dois hífens (--).

Na Listagem 1, vemos um arquivo de controle que contém os próprios dados a serem importados, dispensando um arquivo externo. Vamos analisar cada linha:

LOAD DATA – Todo arquivo de controle deve iniciar com esta linha, obrigatoriamente.
INFILE * - Determina a localização do arquivo que contém os dados a serem importados. Neste caso, “*” indica que os dados estão contidos no próprio arquivo de script, na seção BEGINDATA.
INTO TABLE - Especifica a tabela onde os dados serão inseridos.
FIELDS TERMINATED BY ‘,’ - Indica o caracter utilizado para separar os campos. O exemplo utiliza vírgula. A opção OPTIONALLY ENCLOSED BY indica que os campos podem estar envolvidos por aspas duplas.
(IDAGENDA, NOME, TEL) - Campos da tabela agenda que receberão os valores importados.
BEGINDATA – Indica os valores de origem, no próprio script. Se os dados estiverem em um arquivo externo, esta seção não deve ser utilizada.

LOAD DATA 
 INFILE * 
 INTO TABLE AGENDA
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
 (IDAGENDA, NOME, TEL) 
 BEGINDATA 
 1, "GUSTAVO","9123-4567" 
 2,"JOSÉ DAS COUVES",”7777-7777”
 3,"MARIA ANTÔNIA",”6545-87987”

Listagem 1 - Arquivo de controle

Para executar o script, salve o arquivo como “agenda.ctl” (a extensão “ctl” é opcional). Em seguida, no prompt do sistema, execute o SQL*LOADER através do comando:

C:\>sqlldr userid=usuario/senha@nome_do_alias control=agenda.ctl

onde, userid=usuario/senha são as informações de usuário e senha, @nome_do_alias é o nome do alias de conexão definido no arquivo tnsnames.ora e control é o arquivo de script.

Após a execução, um SELECT na tabela AGENDA exibe os registros importados (figura 2).

Figura 1 – Execução loader a partir do arquivo da Listagem 2

Figura 2 – Verificação dos registros importados a partir do arquivo da listagem 2.

NOTA: O SQL*Loader é instalado com o servidor e com o client do Oracle, podendo ser executado de qualquer estação que esteja devidamente conectada.

Após a importação, alguns arquivos de log são automaticamente criados no diretório onde o SQL*Loader foi executado. Esses arquivos são gerados no formato texto e podem ser visualizados em qualquer editor, como o Notepad. Veja a descrição de cada um:

“.log” – Possui todas as mensagens geradas pelo SQL*LOADER durante a execução do script.
“.bad” - Contém os dados que não foram carregados devido a ocorrência de erros.
“.dsc” - Abreviação de discarded. Contém os registros que, em função de algum teste efetuado no script, não foram importados.

Indicando um Arquivo Externo

A listagem 2 apresenta um script que busca os dados em um arquivo denominado agenda.dat. Os campos neste arquivo estão separados por tamanho fixo. O conteúdo do arquivo de dados está na listagem 3.

Repare que o comando INFILE indica a localização do arquivo .dat e que a seção BEGINDATA não foi utilizada. A cláusula POSITION indica a posição de início e fim de cada coluna, dispensando o comando FIELDS TERMINATED BY. Ao final de cada campo podemos definir o tipo que será atribuído ao valor importado.

LOAD DATA
 INFILE 'c:\agenda.dat'
 INTO TABLE AGENDA
  (IDAGENDA POSITION(01:05) INTEGER EXTERNAL,
  NOME POSITION(06:36) CHAR,
  TEL POSITION(37:45) CHAR)

listagem 2 – Arquivo de controle com dados em um arquivo externo

    4 ANTONIO CARLOS                 9876542
    5 VINÍCIUS                       1546545
    6 SEPÚLVEDA                      9876545
    7 MARCOS PEIXOTO                 1264555
    8 JOAQUIM PADILHA                2165466
    9 MARTA PEREIRA                  8732433
   10 MARIA JOSEFA                   1365456

listagem 3 – Arquivo Agenda.Dat a ser importado pelo arquivo de controle da Listagem 2

Uso de Funções

Na listagem 4 vemos o uso da cláusula APPEND, necessária se a tabela não estiver vazia. Se tabela tiver algum registro e APPEND não for utilizado, o SQL*Loader aborta a importação.

Neste exemplo vemos também o uso de funções DDL. O campo IDAGENDA é preenchido automaticamente pela função SEQUENCE, que recupera o maior valor da coluna e incrementa em 1.

Além de SEQUENCE, podemos utilizar três outras funções DDL para atribuir valores durante a importação:

Nome_da_coluna RECNUM – Atribui o valor físico do registro que está sendo importado, incrementado automaticamente a cada linha de dados processada .
Nome_da_coluna SYSDATE – Atribui a dada do servidor de banco de dados. A coluna deve ser do tipo DATE ou CHAR.
CONSTANT valor – Utilizado para atribuição de valores fixos.

É possível ainda utilizar funções SQL do Oracle, como TO_DATE, TO_CHAR, TO_NUMBER, LOWER, UPPER, SUBSTR e outras. Por exemplo, para formatar a coluna NOME em caixa baixa, podemos usar a função LOWER:

NOME CHAR TERMINATED BY ':' "LOWER(:NOME)"

Repare que o nome do campo deve ser iniciado pelo caracter “:” e passado dentro de parênteses. Veja outro exemplo:

NOME CHAR TERMINATED BY "," "SUBSTR(:NOME,1,5)"
 <h3 align="left">-- Carregando Formatos Variáveis e Criação de Seqüências
 LOAD DATA 
 INFILE *
 APPEND 
 INTO TABLE AGENDA
 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
 (IDAGENDA SEQUENCE(MAX,1), 
 NOME CHAR TERMINATED BY ':',
 TEL)
 BEGINDATA 
    "JOSÉ SILVIO":555-7422
    "JOSÉ SILVINO":555-1455
    "JOSÉ SILVA":555-8844

listagem 4 – Arquivo de controle com formatação variada e utilização de SEQUENCE

Alterando Dados

Além do APPEND, podemos utilizar o comando REPLACE, que faz o SQL*Loader deletar todos os registros da tabela de destino antes de iniciar a importação. Não existe uma estrutura do tipo “FIND and UPDATE” ou seja, verificar se o registro já existe e alterá-lo, a partir da indicação de um ou mais campos chave.

Importação de múltiplas tabelas

O Loader permite carregar várias tabelas a partir de um único arquivo de dados. Veja um exemplo na listagem 5.

Repare que o campo IDAGENDA recebe o mesmo valor nas duas tabelas, pois a cláusula POSITION possui as mesmas posições de início e fim. Os campos nome e telefone são específicos da tabela agenda e o campo email é específico da tabela emails.

Uma particularidade neste exemplo é o uso do comando WHEN, que serve para filtrar a inserção do registro baseado em uma condição. No exemplo, a tabela emails só receberá um registro caso a coluna EMAIL seja diferente de vazio, ou seja, apenas o registro 99 será importado para esta tabela.

LOAD DATA
 INFILE *
 APPEND 
  
 INTO TABLE AGENDA
 (IDAGENDA POSITION(01:4),
  NOME POSITION(05:17) CHAR, 
  TEL POSITION(23:29) CHAR)  
  
 INTO TABLE EMAILS WHEN EMAIL != '   '
 (IDEMAILS SEQUENCE(MAX,1),
  IDAGENDA POSITION(01:4),
  EMAIL POSITION(35:70) CHAR) 
  
 BEGINDATA 
   99GUSTAVO          9123-4567    gustavo@sqlmagazine.com.br
   88JOSÉ DAS COUVES  7777-7777
   77MARIA ANTÔNIA    6545-87987

listagem 5

Utilizando Filtros

O comando WHEN é utilizado para filtrar linhas que não satisfaçam uma condição. Na listagem 6 vemos um exemplo que filtra os registro com código igual a ‘88’ e telefone iniciado por ‘6’.

LOAD DATA
   INFILE  *
   APPEND
   INTO TABLE AGENDA
   WHEN (01:04) <> '88' and (23:24) <> '6'
   (
 IDAGENDA POSITION(01:4),
  NOME POSITION(05:17) CHAR, 
  TEL POSITION(23:29) CHAR)  
   )
 BEGINDATA 
   99GUSTAVO          5123-4567    gustavo@sqlmagazine.com.br
   88JOSÉ DAS COUVES  5777-7777
   77MARIA ANTÔNIA    6545-8798

Importando dados para um campo BLOB

A listagem 6 demonstra um script para importação em um campo BLOB. Vamos analisar as linhas principais:

  VAR_SINOPSE FILLER CHAR,
   "SINOPSE" LOBFILE (VAR_SINOPSE) TERMINATED BY EOF

A primeira linha declara a variável VAR_SINOPSE, do tipo FILLER. O papel desta variável é receber o texto e repassar para o campo BLOB. A segunda linha carrega o campo e indica que o delimitador será o final do arquivo.

O texto que será inserido na variável está contido em arquivos externos, passados como parâmetro na seção de dados. No exemplo, o tipo de dado indicado é CHAR, logo os arquivos auxiliares terão texto em seu conteúdo.

LOAD DATA 
 INFILE * 
 APPEND
 INTO TABLE LIVROS
 FIELDS TERMINATED BY ','
 ( IDLIVROS SEQUENCE(MAX,1), 
   NOME CHAR,   
   VAR_SINOPSE FILLER CHAR, 
   "SINOPSE" LOBFILE (VAR_SINOPSE) TERMINATED BY EOF) 
  
 BEGINDATA
 DELPHI 7  A BÍBLIA,sinopse1.dat
 APRENDA SQL 3 EM 24 Horas,sinopse2.dat
 PL*SQL EM 24 HORAS, sinopse3.dat
 SQL MAGAZINE,sinopse4.dat

listagem 6 – Arquivo de controle para importação de valores em campos BLOB

IMPORTAÇÃO CSV

CSV é um padrão de formatação de arquivos texto, muito utilizado em intercâmbio de dados e adotado por uma grande variedade de aplicativos. É caracterizado por campos de tamanho variável separados por vírgula ou ponto-e-vírgula. Para exemplificar, vamos importar valores de uma tabela do Excel.

Para gerar um arquivo CSV a partir de uma planilha (figura 3), selecione a opção “Salvar Como” e especifique “CSV” na lista “Salvar Como Tipo” (figura 4). O Script da listagem 7 importa os dados deste arquivo.

LOAD DATA
 INFILE 'agenda.csv'
 APPEND
 INTO TABLE AGENDA
 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' 
 (IDAGENDA SEQUENCE(MAX,1), NOME, TEL)

listagem 7

CONCLUSÃO

Importação é uma tarefa comum na vida do administrador de um banco de dados. O SQL*Loader,com seu mecanismo de scripts, é uma ótima opção para quem precisa de um pouco mais de automação e flexibilidade na construção desse tipo de rotina. Para informações detalhadas sobre a linguagem utilizada pelo aplicativo, consulte a referência do Oracle. Até a próxima!