Главная > Oracle e-Business Suite > Oracle SQL*Loader


Oracle SQL*Loader

SQL*Loader — мощный инструмент для импорта различных данных из файла в базу данных.
Если надо загрузить что-либо из файла в БД, то этот инструмент, как раз то что вам поможет.

Возможности SQL*Loader

  • Загрузка данных из разных файлов в одной сессии
  • Загрузка данных в разные таблицы в одной сессии
  • Указание кодировки файла данных
  • Использование SQL функций перед загрузкой
  • Генерация уникальных ключей для колонок
  • Ограничение вставляемых данных по условию
  • Загрузка двоичных данных
  • Запись ошибочных данных в файл ошибок



На входе требуется два файла:

1) Файл данных

2) Управляющий файл (.CTL)

Файл описания структуры данных (loader control file), содержит описание типов данных столбцов, способ разделения столбцов, как обрабатывать данные и т.д. Именно в этом файле задаются настройки обработки файла данных. Часть настроек, можно переопределить через командную строку при запуске Oracle SQL*Loader.

Выходные данные:

1) Данные в таблице БД

2) Файл отчета (Log file)

3) Файл с некорректными данными (Bad files) — данные которые не вставились из-за каких-либо ограничений БД

4) Файл с отвергнутыми данными (Discard files) — данные которые не прошли дополнительные условия

Управляющий файл SQL*Loader (CTL)

Управляющий файл — это простой текстовый файл, который можно редактировать в обычном текстовом редакторе. Именно здесь описываются все правила импорта.

Пример файла «myexample.ctl»:

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INFILE 'myexample.csv'
BADFILE 'myexample.bad'
DISCARDFILE 'myexample.dsc'
TRUNCATE
INTO TABLE APPS.XX_MYEXAMPLE
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( 
  VAL1,
  VAL2 "rtrim(trim(:VAL2), chr(10)||chr(13))"
)

1 — OPTIONS позволяет задать ряд параметров не в командной строке, а непосредственно в файле.
2LOAD DATA требуется в начале управляющего файла
3 — CHARACTERSET если требуется указать кодировку.
4INFILE определяет файл с исходными данными.
5 — BADFILE определяет файл с некорректными данными.
6 — DISCARDFILE определяет файл с отброшенными данными.
7 — метод загрузки данных в таблицу

INSERT Используется по умолчанию. Таблица перед загрузкой не должна содержать данные. Если в таблице есть строки данных, то выполнение загрузки SQL*Loader-ом не будет выполнено
APPEND Позволяет добавлять строки в таблицу таким образом, чтобы они не оказывали воздействия на уже существующие строки данных.
REPLACE Удаляются все имеющиеся в таблице строки, а затем загружаются новые. При удалении строк срабатывают триггеры существующие на таблице.
TRUNCATE Удаляются все имеющиеся в таблице строки. Триггеры не срабатывают.

8INTO TABLE + название таблицы для вставки данных
9FIELDS TERMINATED BY определяет разделитель данных в файле данных
10 OPTIONALLY ENCLOSED BY ‘»‘ определяет, что данные могут содержать символ обрамления
11 TRAILING NULLCOLS если поле не имеет данных в файле данных, то записать NULL
12-15 — описание столбцов таблицы, с применяемыми SQL функциями

Запуск импорта данныхSQL*Loader

Для запуска загрузки данных через SQL*Loader

sqlldr APPS/APPS control=./myexample.ctl data=./myexample.csv

Помните, что перед запуском скрипта загрузки, таблица уже должна быть создана.

Опции командной строки SQL*Loader

Список параметров можно получить запустив SQL*Loader без указания параметров:

[*** ~]$ sqlldr

SQL*Loader: Release 10.1.0.5.0 - Production on Fri Aug 8 14:34:23 2014

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

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

    userid -- ORACLE username/password
   control -- control file name
       log -- log file name
       bad -- bad file name
      data -- data file name
   discard -- discard file name
discardmax -- number of discards to allow          (Default all)
      skip -- number of logical records to skip    (Default 0)
      load -- number of logical records to load    (Default all)
    errors -- number of errors to allow            (Default 50)
      rows -- number of rows in conventional path bind array or between direct path data saves
               (Default: Conventional path 64, Direct path all)
  bindsize -- size of conventional path bind array in bytes  (Default 256000)
    silent -- suppress messages during run (header,feedback,errors,discards,partitions)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- do parallel load                     (Default FALSE)
      file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
  readsize -- size of read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array  (Default 5000)
streamsize -- size of direct path stream buffer in bytes  (Default 256000)
multithreading -- use multithreading in direct path
 resumable -- enable or disable resumable for current session  (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache -- size (in entries) of date conversion cache  (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords.  An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'.  One may specify parameters by position before
but not after parameters specified by keywords.  For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
[*** ~]$

Данные для загрузки непосредственно в управляющем файле

Пример данных содержащихся в управляющем файле. Начало данных определяет оператор BEGINDATA

Файл XX_MYEXAMPLE.sql

create table APPS.XX_MYEXAMPLE
(
  val1 number
 ,val2 varchar2(100)
 ,val3 number
)
/
exit;

Файл XX_MYEXAMPLE.ctl

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE APPS.XX_MYEXAMPLE
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
  VAL1,
  VAL2,
  VAL3
)
BEGINDATA
100;table;12480
100;chair;3800
200;box;500

Файл test.sh

sqlplus apps/apps @XX_MYEXAMPLE.sql
sqlldr apps/apps control=XX_MYEXAMPLE.ctl

Выполняем скрипт test.sh и смотрим результат:

SQL> column VAL2 format a10;
SQL> select * from APPS.XX_MYEXAMPLE t
  2  /
      VAL1 VAL2             VAL3
---------- ---------- ----------
       100 table           12480
       100 chair            3800
       200 box               500

Плюс создался файл лога XX_MYEXAMPLE.log

SQL*Loader: Release 10.1.0.5.0 - Production on Fri Aug 8 15:19:28 2014

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

Control File:   XX_MYEXAMPLE.ctl
Data File:      XX_MYEXAMPLE.ctl
  Bad File:     XX_MYEXAMPLE.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 APPS.XX_MYEXAMPLE, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
VAL1                                FIRST     *   ;       CHARACTER            
VAL2                                 NEXT     *   ;       CHARACTER            
VAL3                                 NEXT     *   ;       CHARACTER            

Table APPS.XX_MYEXAMPLE:
  3 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:             3
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Aug 08 15:19:28 2014
Run ended on Fri Aug 08 15:19:28 2014

Elapsed time was:     00:00:00.15
CPU time was:         00:00:00.01

Данные с разными разделителями в данных + форматирование даты

Файл XX_MYEXAMPLE2.sql

create table APPS.XX_MYEXAMPLE2
(
  id   number
 ,item  varchar2(100)
 ,color varchar2(100)
 ,price number
 ,creation_date date
)
/
exit;

Файл XX_MYEXAMPLE2.ctl
Оператор FILLER — позволяет пропускать колонку в данных и не грузить в таблицу.

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE APPS.XX_MYEXAMPLE2
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  id,
  item  terminated by "$",
  color terminated by "^",
  price ,
  weight FILLER,
  creation_date DATE "dd-mm-yyyy"
)
BEGINDATA
1,table$black^12480,12,30-01-2014
2,table$green^12580,12,11-05-2013
3,chair$black^3800,3,11-08-2014

Запускаем импорт и смотрим результат:

SQL> column item format a10;
SQL> column color format a10;
SQL> select * from APPS.XX_MYEXAMPLE2 t
  2  /
        ID ITEM       COLOR           PRICE CREATION_DATE
---------- ---------- ---------- ---------- -------------
         1 table      black           12480 30.01.2014
         2 table      green           12580 11.05.2013
         3 chair      black            3800 11.08.2014

Доп. информацию можно посмотреть здесь:

Oracle® Database Utilities 11g Release 2: SQL*Loader Concepts

Похожие записи:

  • No Related Posts
  1. Пока что нет комментариев.
  1. Пока что нет уведомлений.