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.ctlonde, 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!
