SQL*Loader

Muitas vezes, pode existir a necessidade de inserir dados no nosso banco Oracle com arquivos de outras fontes, como um arquivo texto por exemplo, e é aí que o SQL*Loader pode ajudar.

O dados precisam apenas ter um layout que podemos especificar, para que os dados possam ser lidos e inseridos em uma tabela, ou até mesmo mais tabelas. Essa layout fica em um arquivo conhecido como o loader control file. O arquivo a ser lido e inserido no banco é o datafile. No processo será criado outros arquivos, como o arquivo de log, o bad files em que mostra os dados rejeitados que não obedecem o layout, com formato inválido ou o próprio database pode rejeitar o insert daquela linha, devido a constraints por exemplo. E também tem o discard file, que é opcional, em que se encontra os dados que foram filtrados por que não corresponde a nenhum critério especificado no loader control file.

Temos duas opções de executar o SQL*Loader. Com e sem o control file. Com o control file, podemos fazer cargas mais complexas de dados, nos dando maior controle do que carregar, como carregar e aonde. Caso seja uma carga bem simples, podemos fazer sem o uso do control file, esse é o modo express do SQL*Loader.

Vamos ver na prática, um exemplo bem simples, feito pela própria Oracle.

Salve o seguinte trecho de código em um arquivo chamado ulcase1.ctl. Será o nosso loader control file.

LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"

LOAD DATA é necessário no control file. INFILE * esta dizendo que os dados esta dentro do próprio control file e não em um arquivo separado. INTO TABLE especifica em que tabela os dados serão inseridos. FILDS TERMINATED BY indica o separador, no caso a vírgula. Nosso dados são de tamanho variável, um stream, e que também podem talvez estar entre “. Os nomes das colunas da tabela a ser inserida esta entre parênteses. BEGINDATA informa que vai iniciar os dados a serem inseridos. Vamos executar o SQL*Loader e ver o resultado:

$ sqlldr USERID=scott/tiger CONTROL=ulcase1.ctl LOG=ulcase1.log

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Nov 20 13:31:46 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 7

Table DEPT:
  7 Rows successfully loaded.

Check the log file:
  ulcase1.log
for more information about the load.
$ sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 20 13:31:55 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Nov 20 2019 13:31:46 -03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        12 RESEARCH       SARATOGA
        10 ACCOUNTING     CLEVELAND
        11 ART            SALEM
        13 FINANCE        BOSTON
        21 SALES          PHILA.
        22 SALES          ROCHESTER
        42 INT'L          SAN FRAN

7 rows selected.

Para executar o SQL*Loader, temos o utilitário sqlldr. Foi informado com que usuário seria executado o processo, no caso scott. Qual é o loader control file e o log que seria gerado. A tabela estava vazia, que é a necessidade padrão do SQL*Loader, como também não foi informado o data type e o tamanho, ficou o padrão, que é CHAR com tamanho 255. Logo depois foi executado um SELECT mostrando o conteúdo agora na tabela. O conteúdo do log:

$ cat ulcase1.log

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Nov 20 13:31:46 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Control File:   ulcase1.ctl
Data File:      ulcase1.ctl
  Bad File:     ulcase1.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,  O(") CHARACTER
DNAME                                NEXT     *   ,  O(") CHARACTER
LOC                                  NEXT     *   ,  O(") CHARACTER


Table DEPT:
  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Nov 20 13:31:46 2019
Run ended on Wed Nov 20 13:31:48 2019

Elapsed time was:     00:00:01.71
CPU time was:         00:00:00.02

Vejamos um outro exemplo, agora com um data file e com daods de tamanhos fixos.

$ cat ulcase2.dat
7782 CLARK      MANAGER   7839  2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10
7566 JONES      MANAGER   7839  3123.75          20
7499 ALLEN      SALESMAN  7698  1600.00   300.00 30
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30
7658 CHAN       ANALYST   7566  3450.00          20
$ cat ulcase2.ctl
LOAD DATA
INFILE 'ulcase2.dat'
INTO TABLE EMP

( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
  ENAME    POSITION(06:15) CHAR,
  JOB      POSITION(17:25) CHAR,
  MGR      POSITION(27:30) INTEGER EXTERNAL,
  SAL      POSITION(32:39) DECIMAL EXTERNAL,
  COMM     POSITION(41:48) DECIMAL EXTERNAL,
  DEPTNO   POSITION(50:51) INTEGER EXTERNAL)

$ sqlldr USERID=scott/tiger CONTROL=ulcase2.ctl LOG=ulcase2.log

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Nov 20 13:47:47 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 7

Table EMP:
  7 Rows successfully loaded.

Check the log file:
  ulcase2.log
for more information about the load.
$ sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 20 13:48:16 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Nov 20 2019 13:47:47 -03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839               2572.5
        10

      7839 KING       PRESIDENT                            5500
        10

      7934 MILLER     CLERK           7782                  920
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839              3123.75
        20

      7499 ALLEN      SALESMAN        7698                 1600        300
        30

      7654 MARTIN     SALESMAN        7698               1312.5       1400
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7658 CHAN       ANALYST         7566                 3450
        20


7 rows selected.

O que temos de diferente aqui? Os dados estão em um arquivo diferente do control file. Os dados também são de tamanhos fixos. No control file especificamos todas as colunas com o seu data type e a posição em que se encontra os dados. No caso da coluna EMNO, é os 4 primeiros caracteres, a coluna ENAME, começa do sexto caractere e vai até o décimo quinto, e assim por diante…

Outro exemplo:

LOAD DATA
INFILE *
APPEND

INTO TABLE EMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
 hiredate DATE(20) "DD-Month-YYYY",
 sal, comm,
 deptno   CHAR TERMINATED BY ':',
 projno,
 loadseq  SEQUENCE(MAX,1) )

BEGINDATA
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
7839, "King", "President", , 17-November-1981, 5500.00,, 10:102
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101

O que temos de diferente aqui? APPEND. Isso nos informa que a tabela não precisa estar vazia, os dados serão inseridos mesmo com linhas já existentes. Temos agora uma mistura na lista de colunas, com e sem data types. Já sabemos que se não informar o SQL*Loader utilizará o default CHAR(255). Nos outros casos foram informados. A função SEQUENCE gera um valor único na coluna loadseq. Nesse caso ele vai encontrar o valor máximo já existente e irá incrementar com 1.

Existe muito mais opções, como inserir em mais de uma tabela, de filtrar os dados e etc. Sugiro ler a documentação oficial para saber toda a capacidade do SQL*Loader.

Vejamos como seria o modo express agora. Nesse modo não é necessário um loader controlfile, apenas informamos direto na linha de comando qual é a tabela, e o arquivo que será lido será o arquivo com o mesmo nome da tabela .dat. Temos algumas restrições nesse modo. As colunas só podem ser escalar, os dados devem estar delimitados por vírgula, as colunas será as colunas na ordem descritas na view ALL_TAB_COLUMNS e etc. Vejamos um exemplo.

$ cat DEPT.dat
 12,RESEARCH,SARATOGA
 10,ACCOUNTING,CLEVELAND
 11,ART,SALEM
 13,FINANCE,BOSTON
 21,SALES,PHILA.
 22,SALES,ROCHESTER
 42,INT'L,SAN FRAN
]$ sqlldr scott/tiger TABLE=DEPT

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Nov 20 14:18:38 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: DEPT
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table DEPT:
  7 Rows successfully loaded.

Check the log files:
  DEPT.log
  DEPT_%p.log_xt
for more information about the load.

Bem simples não é verdade?

Agora pra finalizar um pouco o assunto SQL*Loader, vamos ver os diferentes métodos que ele utiliza para carregar os dados, que são dois: Conventional Path e Direct Load.

No Conventional Path, o SQL*Loader usa as instruções INSERT que tanto conhecemos utilizando os dados existentes no control file ou no data file. Nesse método é utilizado o buffer cache, gera tanto REDO como UNDO.

Já o Direct Load, o SQL*Loader formata os dados em Oracle data Blocks e insere esses blocks diretamente no datafile. Ou seja, bem mais rápido! Mas, temos algumas restrições aqui. Foreign Keys na tabela tem que estar desabilitadas ou devem ser removidas. Triggers de INSERT não será disparadas. A tabela estará com LOCK de DML durante o load e etc.

O padrão do SQL*Loader é o convention path, se quiser o Direct Load, devemos adicionar a seguinte instrução no loader control file:

DIRECT=TRUE

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Marcado com: ,
Publicado em Administração, sql*loader

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Esse Blog é reconhecido pela
Sou um
Certificações
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 155.990 Visualizações
%d blogueiros gostam disto: