|
|
ГЛАВА 5 ПРИМЕРЫ РАБОТЫ SQL*LOADER
---------------------------------Д
Примеры, содержащиеся в настоящей главе, иллюстрируют некото-
рые особенности работы утилиты SQL*Loader. Изложение начинается с
простых примеров и заканчивается более сложными. Демонстрируется
загрузка следующих объектов:
* данных переменной длины
* записей фиксированного формата
* файла в свободном формате с использованием разделителей
между данными
* нескольких физических записей как одной логической
* нескольких таблиц
Вы можете потренироваться сами, используя учебные файлы, ука-
занные в данной главе. Все они имеются на дистрибутиве, содержа-
щем утилиту SQL*Loader.
Описание примеров
-----------------
Краткое описание каждого примера:
Пример 1: Загрузка записей переменной длины, в которых поля
разделяются запятыми и могут быть заключены в кавычки. Данные
располагаются в конце управляющего файла.
Пример 2: Загрузка данных из файла, состоящего из записей
фиксированной длины и фиксированного формата.
Пример 3: Загрузка записей переменной длины, в которых ис-
пользуются ограничители и порядковые номера. Данные располагаются
в конце управляющего файла.
Пример 4: Объединение нескольких физических записей в одну
логическую, которой соответствует одна строка БД.
Пример 5: Загрузка данных в несколько таблиц сразу (в одном
сеансе).
Дистрибутив, на котором находится утилита SQL*Loader, содер-
жит для каждого из указанных примеров по одному или по два файла:
управляющий файл и файл данных. Эти файлы называются CASE1.CTL и
CASE1.DAT (для первого примера) и т.д.
Все примеры основаны на использовании стандартных демонстра-
ционных таблиц БД ORACLE EMP и DEPT, принадлежащих пользователю
SCOTT/TIGER; таблицы описаны следующим образом:
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME CHAR(14),
LOC CHAR(13))
В некоторых из примеров для повышения наглядности демонстри-
руемых положений в таблицы будут добавлены дополнительные столбцы.
-- 47 --
Пример 1: Загрузка данных переменной длины
------------------------------------------
В этом примере демонстрируется следующее:
* формат управляющего файла, описывающего одну загружаемую
таблицу с тремя столбцами
* включение данных в состав управляющего файла (отсутствие
отдельного файла данных)
* загрузка полей и записей переменной длины
Номера слева в приводимом ниже листинге содержимого управляю-
щего файла (CASE1.CTL) в самом файле отсутствуют; они используют-
ся в комментариях к файлу, следующих дальше. При создании управ-
ляющего файла эти номера не вводятся.
1 LOAD DATA
2 INFILE *
3 INTO TABLE DEPT
4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
5 (DEPTNO, DNAME, LOC)
6 BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
Комментарии:
1 Оператор LOAD DATA открывает управляющий файл.
2 Данные содержатся в управляющем файле.
3 Оператор INTO TABLE идентифицирует загружаемую таблицу
(DEPT). По умолчанию таблица, в которую добавляются за-
писи, должна быть вначале пустой.
4 Данные разделяются запятыми, но могут быть также заклю-
чены в кавычки. Тип данных для всех полей по умолчанию
- CHAR.
5 Имена загружаемых столбцов заключаются в круглые скобки.
6 Указание начала данных.
Запуск утилиты SQL*Loader в данном примере может быть произ-
веден следующим образом:
SQLLOAD SCOTT/TIGER CASE1.CTL
Результатом работы утилиты SQL*Loader является загрузка таб-
лицы DEPT и создание двух файлов: регистрационного и файла плохих
записей (файл плохих записей создается, невзирая на то, отверга-
ются ли в процессе загрузки какие-либо записи или нет).
Ниже представлен регистрационный файл. Номера слева имеют от-
ношение к комментариям, помещенным после файла; в самом регистра-
ционном файле они отсутствуют.
------------------------------------------------------------Д
Control File: case1.ctl
Data File: *
Read Mode: System Record
Bad File: case1.bad
Discard File: none specified
-- 48 --
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65336 bytes
Record Length: 726 (Buffer size allocated per logical
record)
Continuation: none specified
Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
1 DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
2 LOC NEXT * WHT 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: 46848 bytes (64 rows)
Space otherwise allocated: 48084 bytes
Total logical records skipped: 0
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0
------------------------------------------------------------Д
Комментарии:
1 Расположение и длина каждого поля в записи определяются
по положению ограничителей.
2 Выражение "WHT" означает, что поле LOC завершается пос-
ледовательностью пробелов (whitespace). Запись O(")
указывает на то, что значение поля может быть заключено
в кавычки.
Пример 2: Загрузка записей фиксированного формата
-------------------------------------------------------------
Пример 2 демонстрирует следующее:
* формат управляющего файла, описывающего файл данных и одну
загружаемую таблицу
* выделение данных в отдельный файл
* фиксированный формат файла данных
В управляющем файле расположение столбцов и типы данных ука-
заны явно.
Номера слева в приводимом ниже листинге содержимого управляю-
щего файла (CASE2.CTL) в самом файле отсутствуют; они используют-
ся в комментариях к файлу, следующих дальше. При создании управ-
ляющего файла эти номера не вводятся.
-- 49 --
1 LOAD DATA
2 INFILE CASE2.DAT
3 INTO TABLE EMP
4 (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,
5 DEPTNO POSITION (50:51) INTEGER EXTERNAL)
Комментарии:
1 Оператор LOAD DATA открывает управляющий файл.
2 Имя файла, содержащего данные, указывается после ключе-
вого слова INFILE.
3 Оператор INTO TABLE идентифицирует загружаемую таблицу.
4 Каждая следующая строка (по строку с номером 5 включи-
тельно) содержит имя столбца и описание расположения
загружаемых в столбец значений в файле данных. EMPNO,
ENAME, JOB и т.д. - имена столбцов в таблице EMP. Типы
данных (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) соот-
ветствуют типам тех данных, которые содержатся в инфор-
мационных полях файла, а не тех, которые содержатся в
соответствующих столбцах таблицы EMP.
5 Обратите внимание на то, что перечень спецификаций
столбцов заключается в скобки.
Ниже приводятся несколько строк из файла CASE2.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
Поля, заполненные пробелами, автоматически получают значение
"пусто" (null).
Запуск утилиты SQL*Loader в данном примере может быть произ-
веден следующим образом:
SQLLOAD SCOTT/TIGER CASE2.CTL CASE2.LOG
Результатом работы утилиты SQL*Loader является загрузка таб-
лицы EMP и создание двух файлов: регистрационного и файла плохих
записей (файл плохих записей создается, невзирая на то, отверга-
ются ли в процессе загрузки какие-либо записи или нет).
Регистрационный файл имеет следующий вид.
Control File: case2.ctl
Data File: CASE2.DAT
Read Mode: System Record
Bad File: case2.bad
Discard File: none specified
Number to load: ALL
Number to skip: 0
Errors allowed: 50
-- 50 --
Bind array: 64 rows, maximum of 65336 bytes
Record Length: 80 (Buffer size allocated per logical record)
Continuation: none specified
Table EMP, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
JOB 17:25 9 CHARACTER
MGR 27:30 4 CHARACTER
SAL 32:39 8 CHARACTER
COMM 41:48 8 CHARACTER
DEPTNO 50:51 2 CHARACTER
Table EMP:
20 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: 3776 bytes (64 rows)
Space otherwise allocated: 6740 bytes
Total logical records skipped: 0
Total logical records read: 20
Total logical records rejected: 0
Total logical records discarded: 0
Пример 3: Загрузка файла в свободном формате с использованием
разделителей между данными
-------------------------------------------------------------
Пример 3 демонстрирует следующее:
* загрузку данных в потоковом формате с использованием разде-
лителей двух типов - завершающих и окаймляющих
* загрузку дат
* включение данных, подлежащих загрузке, в состав управляюще-
го файла (начало данных отмечается ключевым словом
BEGINDATA)
* использование функции SEQUENCE для генерации уникальных
значений ключей, добавляемых в загружаемые данные
* использование ключевого слова APPEND, указывающего на то,
что таблица, в которую добавляются данные, может уже содер-
жать к началу загрузки какую-либо информацию
* использование в управляющем файле комментариев, начинающих-
ся с двойного тире
Рассматриваемый здесь управляющий файл выполняет загрузку той
же таблицы, что и в примере 2, но с тремя новыми столбцами
(HIREDATE, PROJNO, LOADSEQ). В демонстрационной таблице EMP
столбцы PROJNO и LOADSEQ отсутствуют, поэтому прежде чем запус-
кать этот файл, необходимо указанные столбцы добавить в таблицу
командой:
ALTER TABLE EMP ADD (PROJNO NUMBER, LOADSEQ NUMBER)
-- 51 --
По сравнению с примером 2 данные имеют другой формат. Одни
значения заключены в кавычки, другие разделяются запятыми, а зна-
чения полей DEPTNO и PROJNO - двоеточием.
Номера слева в приводимом ниже листинге содержимого управляю-
щего файла в самом файле отсутствуют; они используются в коммен-
тариях к файлу, следующих дальше. При создании управляющего файла
эти номера не вводятся.
1 -- Variable-length, delimited and enclosed data format
LOAD DATA
2 INFILE *
3 APPEND
INTO TABLE EMP
4 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
5 hiredate DATE "DD-Month-YYYY",
sal, comm,
deptno CHAR TERMINATED BY ':',
projno,
6 loadseq SEQUENCE (MAX,1))
7 BEGINDATA
8 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
Комментарии:
1 Комментарии могут появиться в любой из командных строк
файла, но не в строках данных. Им предшествует двойное
тире, которое может располагаться в любом месте строки.
2 Указание на то, что данные находятся в конце управляю-
щего файла.
3 Указание на то, что данные могут загружаться даже в том
случае, если таблица уже содержит какие-то строки.
4 По умолчанию разделителем значений информационных полей
является запятая, значения некоторых полей могут быть
заключены в двойные кавычки.
5 Данные, загружаемые в столбец HIREDATE, имеют формат
DD-Month-YYYY (DD - число, Month - месяц, YYYY - год).
6 Функция SEQUENCE используется для генерации уникальных
значений, добавляемых в столбец LOADSEQ. Функция нахо-
дит максимальное из имеющихся в столбце значений и до-
бавляет к нему единицу, получая в результате значение
столбца LOADSEQ для вновь добавляемой строки.
7 Указание конца управляющей информации и начала данных.
8 Обратите внимание на то, что хотя каждая физическая за-
пись и эквивалентна одной логической, длина полей варь-
ируется так, что некоторые записи выглядят длиннее ос-
тальных. Заметьте также, что несколько строк содержат
пустые значения для столбца COMM.
-- 52 --
Запуск утилиты SQL*Loader в данном примере может быть произ-
веден следующим образом:
SQLLOAD SCOTT/TIGER CASE3.CTL CASE3.LOG CASE3.BAD
Регистрационный файл имеет следующий вид.
Control File: case3.ctl
Data File: *
Read Mode: System Record
Bad File: case3.bad
Discard File: none specified
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65336 bytes
Record Length: 2182 (Buffer size allocated per logical
record)
Continuation: none specified
Table EMP, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
empno FIRST * , O(") CHARACTER
ename NEXT * , O(") CHARACTER
job NEXT * , O(") CHARACTER
mgr NEXT * , O(") CHARACTER
hiredate NEXT * , O(") CHARACTER
sal NEXT * , O(") CHARACTER
comm NEXT * , O(") CHARACTER
deptno NEXT * : O(") CHARACTER
projno NEXT * , O(") CHARACTER
loadseq SEQUENCE (MAX, 1)
Table EMP:
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: 63800 bytes (29 rows)
Space otherwise allocated: 64163 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Пример 4: Загрузка нескольких физических записей как одной
логической
-------------------------------------------------------------
Пример 4 демонстрирует следующее:
* объединение нескольких физических записей в одну логическую
* использование ключевого слова CONTINUEIF
* загрузку отрицательных значений
-- 53 --
* использование ключевого слова REPLACE, указывающего на то,
что новые записи, загружаемые в таблицу, должны заменять
собой старые
* указание имени файла отсеянных записей с помощью ключевого
слова DISCARDFILE
* указание верхнего ограничения на количество отсеиваемых за-
писей с помощью ключевого слова DISCARDMAX
* фильтрацию записей, имеющих повторяющиеся значения в поле
уникального индекса
* фильтрацию записей, содержащих недопустимые значения
Номера слева в приводимом ниже листинге содержимого управляю-
щего файла (CASE4.CTL) в самом файле отсутствуют; они используют-
ся в комментариях к файлу, следующих дальше. При создании управ-
ляющего файла эти номера не вводятся.
LOAD DATA
INFILE case4.dat
1 DISCARDFILE case4.dis
2 DISCARDMAX 999
3 REPLACE
4 CONTINUEIF THIS (1) = '*'
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,
HIREDATE POSITION (52:60) INTEGER EXTERNAL)
Комментарии:
1 Указание имени файла отсеянных записей (CASE4.DIS).
2 Указание верхнего ограничения на количество отсеиваемых
записей (999).
3 Указание утилите SQL*Loader на то, что если в таблице
уже есть данные, перед загрузкой новых записей они
должны быть удалены.
4 Если в первой позиции текущей записи стоит звездочка
(*), это означает, что следующая физическая запись
должна быть добавлена к текущей при формировании одной
логической записи. Обратите внимание на то, что первая
позиция каждой физической записи должна содержать либо
звездочку, либо незначащий символ.
Ниже приводится содержимое файла данных CASE4.DAT. Обратите
внимание на звездочку в первой позиции каждой записи и на внешне
незаметный признак перехода на новую строку, присутствующий в 20-
й позиции записи (после символов "MA", "PR" и т.д.). Начисления
на служащего CLARK имеют значение "-10" и утилита SQL*Loader заг-
ружает это значение, преобразуя его в отрицательное число.
Последние две записи будут отвергнуты в случае принятия сле-
дующих допущений. Если со столбцом EMPNO связан уникальный ин-
декс, запись о служащем CHIN будет отвергнута, ибо значение
столбца EMPNO в этой записи совпадает со значением указанного
-- 54 --
столбца в записи о служащем CHAN. Если столбец EMPNO определен
как NOT NULL, запись о служащем CHEN будет отвергнута из-за от-
сутствия значения для данного столбца.
*7782 CLARK MA
NAGER 7839 2572.50 -10 2512-NOV-85
*7839 KING PR
ESIDENT 5500.00 2505-APR-83
*7934 MILLER CL
ERK 7782 920.00 2508-MAY-80
*7566 JONES MA
NAGER 7839 3123.75 2517-JUL-85
*7499 ALLEN SA
LESMAN 7698 1600.00 300.00 25 3-JUN-84
*7654 MARTIN SA
LESMAN 7698 1312.50 1400.00 2521-DEC-85
*7658 CHAN AN
ALYST 7566 3450.00 2516-FEB-84
* CHEN AN
ALYST 7566 3450.00 2516-FEB-84
*7658 CHIN AN
ALYST 7566 3450.00 2516-FEB-84
Регистрационный файл имеет следующий вид.
Control File: case4.ctl
Data File: case4.dat
Read Mode: System Record
Bad File: case4.bad
Discard File: case4.dsc (Allow 999 discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65336 bytes
Record Length: 80 (Buffer size allocated per logical
record)
Continuation: 1:1 = '*', in current physical record.
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
JOB 17:25 9 CHARACTER
MGR 27:30 4 CHARACTER
SAL 32:39 8 CHARACTER
COMM 41:48 8 CHARACTER
DEPTNO 50:51 2 CHARACTER
HIREDATE 52:60 9 CHARACTER
Rejected logical records and errors:
Data File case4.dat -
-- 55 --
Record 8: Rejected - Error on table EMP, column EMPNO.
ORA-01400: mandatory (NOT NULL) column is missing or NULL
during insert
Record 9: Rejected - Error on table EMP.
ORA-00001: duplicate key in index
продолжение следует ...
Table EMP:
7 Rows successfully loaded.
2 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: 4480 bytes (64 rows)
Space otherwise allocated: 6740 bytes
Total logical records skipped: 0
Total logical records read: 9
Total logical records rejected: 2
Total logical records discarded: 0
Ниже приводится файл плохих записей, состоящий из записей 8 и
9. (Файл отсеянных записей пуст.)
* CHEN AN
ALYST 7566 3450.00 2516-FEB-84
*7658 CHIN AN
ALYST 7566 3450.00 2516-FEB-84
Пример 5: Загрузка данных в несколько таблиц
--------------------------------------------
Пример 5 демонстрирует следующее:
* загрузку двух таблиц (EMP и PROJ)
* использование утилиты SQL*Loader для разбиения повторяющих-
ся групп и загрузки данных в нормализованные таблицы. (Из
одной записи файла может получиться несколько строк БД.)
* получение нескольких логических записей из каждой физической
* загрузку одного и того же поля в несколько таблиц
* использование предложения WHEN
Номера слева в приводимом ниже листинге содержимого управляю-
щего файла в самом файле отсутствуют; они используются в коммен-
тариях к файлу, следующих дальше. При создании управляющего файла
эти номера не вводятся.
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO
-- listed for each employee
LOAD DATA
INFILE CASE5.DAT
BADFILE CASE5.BAD
DISCARDFILE CASE5.DSC
1 REPLACE
-- 56 --
2 INTO TABLE EMP
(EMPNO POSITION (1:4) INTEGER EXTERNAL,
ENAME POSITION (6:15) CHAR,
DEPTNO POSITION (17:18) CHAR,
MGR POSITION (20:23) INTEGER EXTERNAL)
2 INTO TABLE PROJ
-- PROJ has two columns, both not null: EMPNO and
-- PROJNO
3 WHEN PROJNO != ' '
(EMPNO POSITION (1:4) INTEGER EXTERNAL,
3 PROJNO POSITION (25:27) INTEGER EXTERNAL) -- 1st proj
продолжение следует ...
2 INTO TABLE PROJ
4 WHEN PROJNO != ' '
(EMPNO POSITION (1:4) INTEGER EXTERNAL,
4 PROJNO POSITION (29:31) INTEGER EXTERNAL) -- 2nd proj
2 INTO TABLE PROJ
5 WHEN PROJNO != ' '
(EMPNO POSITION (1:4) INTEGER EXTERNAL,
5 PROJNO POSITION (33:35) INTEGER EXTERNAL) -- 3rd proj
Комментарии:
1 Указание на то, что если в таблицах (EMP и PROJ), в ко-
торые предполагается загружать данные, уже есть ка-
кая-то информация, ее следует удалить.
2 При загрузке двух таблиц, EMP и PROJ, используются не-
сколько предложений INTO. При загрузке данных в таблицу
PROJ один и тот же набор записей фактически обрабатыва-
ется трижды с использованием различных комбинаций обра-
батываемых столбцов.
3 Использование предложения WHEN для загрузки только тех
строк, которые содержат непустое значение номера проек-
та (projno). Если номер проекта находится в позициях с
25-й по 27-ю, строки включаются в таблицу PROJ только в
том случае, если в этих позициях имеются значащие символы.
4 Если номер проекта находится в позициях с 29-й по 31-ю,
строки включаются в таблицу PROJ только в том случае,
если в этих позициях имеются значащие символы.
5 Если номер проекта находится в позициях с 33-й по 35-ю,
строки включаются в таблицу PROJ только в том случае,
если в этих позициях имеются значащие символы.
Файл данных в примере 5 имеет следующий вид:
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200
-- 57 --
Регистрационный файл в данном примере имеет следующий вид:
Control File: case5.ctl
Data File: CASE5.DAT
Read Mode: System Record
Bad File: CASE5.BAD
Discard File: CASE5.DSC (Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65336 bytes
Record Length: 80 (Buffer size allocated per logical record)
Continuation: none specified
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
DEPTNO 17:18 2 CHARACTER
MGR 20:23 4 CHARACTER
Table PROJ, loaded when PROJNO != ' '
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
EMPNO 1:4 4 CHARACTER
PROJNO 25:27 3 CHARACTER
Table PROJ, loaded when PROJNO != ' '
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
EMPNO 1:4 4 CHARACTER
PROJNO 29:31 3 CHARACTER
продолжение следует ...
Table PROJ, loaded when PROJNO != ' '
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- -------- --- ---- ---- ---------
EMPNO 1:4 4 CHARACTER
PROJNO 33:35 3 CHARACTER
Rejected records and errors:
Data File CASE5.DAT -
1 Record 2: Rejected - Error on table EMP.
1 ORA-00001: duplicate key in index
1 Record 8: Rejected - Error on table EMP, column DEPTNO.
-- 58 --
1 ORA-01722: invalid number
1 Record 3: Rejected - Error on table PROJ, column PROJNO.
1 ORA-01722: invalid number
Table EMP:
2 9 Rows successfully loaded.
2 3 Rows not loaded due to data errors.
2 0 Rows not loaded because all WHEN clauses were
failed.
2 0 Rows not loaded because all fields were null.
Table PROJ:
3 7 Rows successfully loaded.
3 2 Rows not loaded due to data errors.
3 3 Rows not loaded because all WHEN clauses were
failed.
3 0 Rows not loaded because all fields were null.
Table PROJ:
4 7 Rows successfully loaded.
4 3 Rows not loaded due to data errors.
4 2 Rows not loaded because all WHEN clauses were
failed.
4 0 Rows not loaded because all fields were null.
Table PROJ:
5 6 Rows successfully loaded.
5 3 Rows not loaded due to data errors.
5 3 Rows not loaded because all WHEN clauses were
failed.
5 0 Rows not loaded because all fields were null.
продолжение следует ...
Space allocated for bind array: 3904 bytes (64 rows)
Space otherwise allocated: 7568 bytes
Total logical records skipped: 0
Total logical records read: 12
Total logical records rejected: 3
Total logical records discarded: 0
Комментарии:
1 Как результат буферной обработки записей, ошибки возни-
кают не в той последовательности, в которой физические
записи располагаются в файле данных. Соответственно и в
файлы плохих и отсеянных записей эти ошибочные строки
попадают в том же порядке, в котором они появляются в
регистрационном файле.
2 3 из 12 записей оказались отвергнутыми (для служащих
JOKER, YOUNG и EDDS). Данные, содержащиеся в этих запи-
сях, в базу не попали.
3 Критерию, указанному в предложении WHEN, удовлетворяют
девять записей, две записи (о служащих JOKER и YOUNG)
отвергнуты из-за ошибок в данных.
4 Критерию, указанному в предложении WHEN, удовлетворяют
десять записей, три записи (о служащих JOKER, YOUNG и
EDDS) отвергнуты из-за ошибок в данных.
-- 59 --
5 Критерию, указанному в предложении WHEN, удовлетворяют
девять записей, три записи (о служащих JOKER, YOUNG и
EDDS) отвергнуты из-за ошибок в данных.
Результаты работы утилиты:
SQL> select empno, ename, mgr, deptno from emp;
EMPNO ENAME MGR DEPTNO
1234 BAKER 9999 10
5321 OTOOLE 9999 10
2134 FARMER 4555 20
2414 LITTLE 5634 20
6542 LEE 4532 10
4532 PERKINS 9999 10
1244 HUNT 3452 11
123 DOOLITTLE 9940 12
1453 MACDONALD 5532 25
SQL> select * from proj order by empno;
EMPNO PROJNO
123 132
1234 101
1234 103
1234 102
1244 665
1244 456
1244 133
1453 200
2134 236
2134 456
2414 236
2414 456
2414 40
4532 40
5321 321
5321 40
5321 55
6542 102
6542 14
6542 321
-- 60 --
ГЛАВА 6 СИНТАКСИС КОМАНДНОЙ СТРОКИ
---------------------------------ДД
В данной главе рассматривается запуск утилиты SQL*Loader с
помощью команды SQLLOAD и различные аргументы этой команды. В
числе прочих обсуждаются следующие вопросы:
* команда SQLLOAD
* синтаксис командной строки
* аргументы командной строки
* параметр OPTIONS
* сообщения утилиты SQL*Loader
Команда SQLLOAD
---------------
Утилита SQL*Loader запускается на выполнение командой
SQLLOAD, имеющей различные аргументы. Если Вы используете команду
SQLLOAD без аргументов, на экран будет выдан список аргументов с
их кратким описанием и значениями по умолчанию. (Некоторые из
умолчаний зависят от типа операционной системы, поэтому Вам лучше
всего обратиться к умолчаниям, принятым в Вашей операционной сис-
теме.) В приведенном ниже примере показаны умолчания, не завися-
щие от типа операционной системы.
SQLLOAD
Usage: SQLLOAD 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 bind array (Default 64)
bindsize -- Size of bind array in bytes (Default nnnnn)
silent -- Suppress messages during run (header, feedback, errors, discards)
После самой команды идет перечисление ее аргументов, которые
могут разделяться между собой запятыми. Аргументы можно указывать
двумя способами:
* в произвольном порядке, с использованием ключевых слов
* в заранее установленном порядке, без ключевых слов
Так, например, две следующие командные строки являются экви-
валентными:
SQLLOAD CONTROL=foo.ctl,LOG=bar.log,BAD=baz.bad,DATA=etc.dat,
USERID=scott/tiger,ERRORS=999,LOAD=2000,DISCARD=toss.dis,
DISCARDMAX=5
SQLLOAD scott/tiger, foo.ctl, bar.log, baz.bad, etc.dat,
toss.dis, 5, 0, 2000, 999
-- 61 --
При задании позиционных аргументов Вы можете как пользовать-
ся, так и не пользоваться ключевыми словами. Таким образом, сле-
дующая командная строка является допустимой:
SQLLOAD jane/pride, jobctl.ctl, DISCARD=jobctl.dis, SKIP=1000
а такая нет:
SQLLOAD SKIP=1000, jane/pride, jobctl.ctl, DISCARD=jobctl.dis
Аргументы командной строки
--------------------------
Описание аргументов командной строки SQL*Loader:
Ключевое слово Значение Положение в строке
---------------------------------------------------------------ДД
USERID Идентификация пользователя 1
В этом аргументе указывается имя пользователя БД и его па-
роль. Если аргумент опущен, утилита запросит его значение. Если
указан только символ наклонной черты, по умолчанию будут приняты
имя и пароль, с которыми пользователь зарегистрировался в систе-
ме. Для загрузки данных в удаленную базу можно использовать спе-
цификацию БД, принятую в SQL*Net.
CONTROL Управляющий файл 2
В этом аргументе указывается имя управляющего файла, описыва-
ющего условия загрузки данных. Если расширение имени файла (тип
файла) не указано, по умолчанию принимается расширение CTL. Если
аргумент опущен, утилита запросит его значение.
LOG Регистрационный файл 3
В этом аргументе указывается имя регистрационного файла, ко-
торый будет создан утилитой для сохранения информации о ходе заг-
рузки. Если имя файла не указано, по умолчанию будет использо-
ваться имя управляющего файла. Если расширение имени файла (тип
файла) не указано, по умолчанию принимается расширение LOG.
BAD Файл плохих записей 4
В этом аргументе указывается имя файла плохих записей, созда-
ваемого утилитой для запоминания тех записей, при загрузке кото-
рых обнаруживаются ошибки, или тех, которые имеют неверный
формат. Если имя файла не указано, по умолчанию будет использо-
ваться имя управляющего файла. Если расширение имени файла (тип
файла) не указано, по умолчанию принимается расширение BAD. Фор-
мат этого файла совпадает с форматом файла данных, поэтому после
редактирования и внесения всех необходимых изменений отвергнутые
ранее данные могут быть загружены вновь с использованием того же
самого управляющего файла.
DATA Файл данных 5
В этом аргументе указывается имя файла, который содержит заг-
ружаемые данные. Если имя файла не указано, по умолчанию будет
использоваться имя управляющего файла. Если расширение имени фай-
ла (тип файла) не указано, по умолчанию принимается расширение
DAT. Данные могут располагаться и в управляющем файле.
-- 62 --
Ключевое слово Значение Положение в строке
---------------------------------------------------------------ДД
DISCARD Файл отсеянных записей 6
В этом аргументе указывается имя необязательного файла отсе-
янных записей, создаваемого утилитой для запоминания тех записей,
которые не попадают ни в базу, ни в файл плохих записей. Если имя
файла не указано, по умолчанию будет использоваться имя управляю-
щего файла. Если расширение имени файла (тип файла) не указано,
по умолчанию принимается расширение DSC. Формат этого файла сов-
падает с форматом файла данных, поэтому после редактирования и
внесения всех необходимых изменений отсеянные ранее данные могут
быть загружены вновь с использованием того же самого управляющего
файла.
DISCARDMAX Максимальное число отсеиваемых 7
записей
В этом аргументе указывается максимальное число записей, от-
сев которых не приводит к завершению работы утилиты. По умолчанию
количество отсеиваемых записей не ограничивается. Для того, чтобы
остановиться при первом обнаружении подобной записи, следует ука-
зать нулевое значение.
SKIP Количество пропускаемых записей 8
В этом аргументе указывается количество логических записей,
пропускаемых при загрузке с начала файла данных. По умолчанию ни
одной записи не пропускается.
LOAD Количество загружаемых записей 9
В этом аргументе указывается максимальное количество логичес-
ких записей, которое должно быть загружено в данном сеансе (после
пропуска записей, количество которых указано в предыдущем аргу-
менте). По умолчанию загрузке подлежат все записи. Если будет
загружено меньшее количество записей, чем указано в аргументе,
ошибки не произойдет.
ERRORS Допустимое количество ошибок 10
В этом аргументе указывается максимально-допустимое количест-
во ошибок, возникновение которых не приводит к завершению работы
утилиты. По умолчанию допускается не более 50 ошибок. Для того,
чтобы прекратить работу при возникновении первой же ошибки, в ка-
честве значения аргумента следует указать 0. Чтобы обработать
весь файл данных, невзирая на содержащееся в нем количество оши-
бок, в качестве значения аргумента следует использовать какое-ни-
будь очень большое число.
Ключевое слово Значение Положение в строке
---------------------------------------------------------------ДД
ROWS Число строк в связанном массиве 11
В этом аргументе указывается количество строк, одновременно
помещаемых в буфер перед выполнением вставки данных и регистраци-
ей изменений в БД. Значение по умолчанию равно 64.
BINDSIZE Максимальный размер связанного 12
массива в байтах
В этом аргументе указывается максимальный размер связанного
массива в байтах. Значение аргумента отменяет умолчание на размер
массива (зависящее от типа системы) и то значение, которое может
быть вычислено на основе предыдущего аргумента.
-- 63 --
SILENT Режим обратной связи 13
Значениями этого аргумента могут быть следующие ключевые сло-
ва, которые используются для подавления:
HEADER Заголовка утилиты SQL*Loader
FEEDBACK Каждого сообщения о достижении точки регистра-
ции изменений в базе
ERRORS Включения в регистрационный файл каждой записи,
при загрузке которой имела место ошибка ORACLE
(при этом сообщение о количестве отвергнутых
записей остается)
DISCARDS Включения в регистрационный файл каждой записи,
помещенной в файл отсеянных записей
ALL Всего вышеперечисленного
Использование ключевого слова OPTIONS
-------------------------------------
Использование ключевого слова OPTIONS имеет смысл в том слу-
чае, если Вы многократно загружаете данные с одним и тем же набо-
ром опций, чрезвычайно удлиняющим командную строку, и имеет целью
экономию времени при вызове утилиты SQL*Loader.
Ключевое слово OPTIONS позволяет указывать некоторые аргумен-
ты не в командной строке, а в управляющем файле. К таким аргумен-
там относятся:
SKIP = n
LOAD = n
ERRORS = n
ROWS = n
BINDSIZE = n
SILENT = {HEADER і FEEDBACK і ERROR і DISCARDS і ALL}
Пример:
OPTIONS (BINDSIZE=100000, SILENT=FEEDBACK)
Значения аргументов, указанные в управляющем файле с помощью
ключевого слова OPTIONS, отменяют соответствующие значения, ука-
занные в командной строке.
Сообщения, возвращаемые утилитой SQL*Loader
-------------------------------------------
Во время работы утилита SQL*Loader выдает различные сообще-
ния, например:
SQL*Loader: Version 1.0 - Production on Wed Feb 24 15:07:23 1988
Copyright (c) Oracle Corporation 1979, 1988. All rights reserved
Commit point reached - logical record count 20
Могут иметь место и сообщения об ошибках, например:
SQL*Loader: Version 1.0 - Production on Wed Feb 24 15:07:23 1988
Copyright (c) Oracle Corporation 1979, 1988. All rights reserved
SQL*Loader-641: Non-recoverable ORACLE error occurred
ORA-00904: invalid column name
Error occurred on SQL insert statement for table EMP
Your use of column LOADSEQ is possibly in error
-- 64 --
Сообщения об ошибках также включаются в регистрационный файл.
Более подробно об этих сообщениях см. в Приложении B.
Вывод заголовка утилиты и обратную связь можно подавить, ис-
пользуя в командной строке опцию SILENT:
SILENT=(HEADER, FEEDBACK)
ГЛАВА 7 СИНТАКСИС УПРАВЛЯЮЩЕГО ФАЙЛА
------------------------------------Д
В данной главе рассматривается язык загрузки данных
SQL*Loader, используемый при написании управляющих файлов. Приме-
ры управляющих файлов см. в главе 5. Среди прочих обсуждаются
следующие вопросы:
* создание управляющего файла
* синтаксис оператора LOAD DATA
* описание файлов данных
* описание таблиц и столбцов
* указание начала загружаемых данных
Создание управляющего файла
---------------------------
Управляющий файл содержит в себе информацию:
* о местонахождении загружаемых данных, идентифицируя файлы
данных с помощью ключевого слова INFILE или отмечая начало
загружаемых данных в самом управляющем файле с помощью клю-
чевого слова BEGINDATA. Если ни одно из указанных ключевых
слов не используется, предполагается, что данные расположе-
ны в файле, имя которого содержится в командной строке.
* о базовых таблицах, в которые будет производиться загрузка
данных (начало этой информации отмечается ключевым словом INTO TABLE)
* о соответствии полей файла данных столбцам базовых таблиц
(эта информация описывается с помощью оператора LOAD DATA)
Основные правила написания управляющего файла:
* Файл пишется в "свободном формате". Другими словами, пред-
ложения, составляющие его, могут переходить с одной строки
на другую, разрываясь в любом месте.
* При написании файла могут использоваться как заглавные, так
и строчные буквы. Размер букв не имеет значения, если толь-
ко строка не заключена в кавычки.
* Начало комментариев в тексте отмечается двойным тире. Эта
последовательность символов может появляться в любом месте
строки; всю оставшуюся после нее часть строки утилита
SQL*Loader будет игнорировать. Комментарии не должны появ-
ляться внутри самих данных (в файле данных или в управляю-
щем файле); если двойное тире появится внутри данных, оно
будет истолковано как элемент данных.
* Если в качестве имен таблиц или столбцов Вы намереваетесь
использовать слова, зарезервированные утилитой SQL*Loader,
Вам при этом следует заключать их в кавычки. (Полный пере-
чень слов, зарезервированных для использования утилитой SQL
*Loader, приводится в Приложении D.)
Синтаксис оператора LOAD DATA
-----------------------------
Управляющий файл обычно открывается фразой LOAD DATA, за ко-
торой следует несколько фраз, характеризующих загружаемые данные.
(Раньше фразы LOAD DATA может располагаться только фраза OPTIONS
или комментарии.)
-- 65 --
Полный синтаксис оператора LOAD DATA:
OPTIONS (параметры)
LOAD [DATA]
[{INFILEіINDDN} {имя_файлаі * }
{STREAMіRECORDіFIXED длина [BLOCKSIZE размер]іVARIABLE [длина] ]
[{BADFILEіBADDN} имя_файла ]
[{DISCARDFILEіDISCARDDN} имя_файла ]
[{DISCARDSіDISCARDMAX} n ]
[{INDDNіINFILE} ...]
[APPENDіREPLACEіINSERT]
[RECLEN n ]
[{CONCATENATE n іCONTINUEIF {[THISіNEXT] (начало[:конец]) і
LAST} операция {'символьная_строка'іX'шестнадцатиричная_строка'}}]
INTO TABLE имя_таблицы
[APPENDіREPLACEіINSERT]
[WHEN состояние_поля [AND состояние_поля ...]]
[FIELDS [спецификация_ограничителя]]
(
имя_столбца
{
RECNUM і
CONSTANT значение і
SEQUENCE ({ n і MAX і COUNT} [, приращение]) і
[POSITION ({начало[:конец]і * [+n] })] описание_типа_данных
[NULLIF состояние_поля]
[DEFAULTIF состояние_поля]
}
[ ,... ]
)
[INTO TABLE ... ]
[BEGINDATA]
Элементы синтаксиса
Операции
Разрешены операции, связанные с равенством или неравенством:
= != ї= <>
Параметры
Разрешается задание следующих параметров:
LOAD SKIP ERRORS ROWS BINDSIZE SILENT
Эти параметры могут использоваться в любой комбинации. Формат
задания параметров следующий:
OPTIONS ( ROWS=200 SILENT= (HEADER,FEEDBACK) )
Обратите внимание на то, что указание в фразе OPTIONS пара-
метра SILENT=HEADER не будет иметь желаемого результата; к этому
моменту заголовок утилиты уже будет выведен на экран. Указание
подавления заголовка следует производить в командной строке.
Состояние_поля
Состояние поля указывается следующим образом:
{ ( начало [:конец ] ) і имя_столбца } операция
-- 66 --
{'символьная_строка' і X'шестнадцатиричная_строка' }
Примечание: 'символьная_строка' и 'шестнадцатиричная_строка'
могут быть заключены либо в одинарные, либо в двойные кавычки.
Описание_типа_данных
Тип данных описывается с помощью одного из следующих ключевых слов:
CHAR [ ( длина ) ] [ спецификация_ограничителя ]
DATE [ ( длина ) ] [ маска ] [ спецификация_ограничителя ]
VARCHAR [ ( длина ) ]
SMALLINT
DOUBLE
GRAPHIC [EXTERNAL] [ (длина) ]
{INTEGER і FLOAT і DECIMAL} EXTERNAL [ ( длина ) ]
INTEGER [ ( длина ) ]
FLOAT [ ( длина ) ]
DECIMAL ( разрядность [,точность] )
VARGRAPHIC [ ( длина ) ]
Спецификация_ограничителя
Ограничитель данных описывается следующим образом:
[ TERMINATED [ BY ] { WHITESPACE і [X] 'символ' } ]
[ [ OPTIONALLY ] ENCLOSED [ BY ] [X] 'символ' ]
Примечание: Значение переменной 'символ' может заключаться в
одинарные или двойные кавычки. Если используется формат X'сим-
вол', значение этой переменной интерпретируется как код симво-
ла-ограничителя в стандарте ASCII, указанный в шестнадцатиричном
формате. Ключевое слово TERMINATED должно идти прежде ключевого
слова ENCLOSED.
Описание файлов данных
----------------------
Чтобы полностью описать файл данных, укажите имя файла (в со-
ответствующем операторе) и в случае необходимости режим чтения
файла. С помощью нескольких предложений INFILE Вы можете описать
несколько файлов.
Имя файла
Файл, содержащий загружаемые данные, описывается с помощью
ключевых слов INFILE или INDDN, после которых указывается имя
файла и режим чтения. Имя файла, указанное в этих предложениях,
заменяет собой то имя, которое было указано в командной строке.
Если имя файла не указано, по умолчанию в качестве имени файла
данных используется имя управляющего файла с расширением DAT. Ес-
ли же данные содержатся в самом управляющем файле, вместо имени
файла следует указать "*".
{ INFILE і INDDN } { имя_файла і * }
где:
INFILE или Может использоваться любое из этих ключевых слов.
INDDN
имя_файла Имя файла, содержащего загружаемые данные. Может
указываться любое имя, допустимое в Вашей опера-
ционной системе. Оно может быть заключено в двой-
ные кавычки. Если имя файла содержит пробелы или
знаки пунктуации (двоеточие, например), его обя-
-- 67 --
зательно нужно заключить в двойные кавычки. Если
оно уже содержит двойные кавычки, можно использо-
вать одинарные.
* Используется вместо имени файла для указания то-
го, что данные содержатся в управляющем файле.
Режим чтения файла
Дополнительно может указываться режим чтения файла. Этот ре-
жим определяет порядок открытия файла данных и выборки физических
записей. Некоторые из режимов повышают гибкость взаимодействия
СУРБД ORACLE с различными операционными системами.
STREAM і RECORD і FIXED длина [BLOCKSIZE размер] VARIABLE [длина]
где:
STREAM Файл данных трактуется как последовательный поток
байтов. Символы считываются по одному. Физические
записи разделяются признаками перехода на новую строку.
RECORD Значение по умолчанию. Означает, что утилита SQL*
Loader должна использовать режим, принятый под-
системой управления файлами, входящей в состав
Вашей операционной системы. Режим RECORD является
единственным режимом, допускающим использование
"*" вместо имени файла. Таким образом, если дан-
ные находятся в управляющем файле, режим чтения
файла можно не указывать.
FIXED длина Логические записи имеют фиксированную длину. Но-
вая запись образуется в результате чтения из фай-
ла очередной последовательности байтов указанной
длины. Обратите внимание на то, что FIXED имеет
отношение к формату физической записи; поля запи-
си могут иметь свободный формат. Если данные заг-
ружаются непосредственно с ленты, можно указывать
размер блока (BLOCKSIZE).
VARIABLE Длина записи содержится в первых двух байтах. За-
[длина] пись читается следующим образом: сначала считыва-
ется длина, затем указанное в длине число байт.
Первые два байта, содержащие длину, не рассматри-
ваются как часть самой записи. Значение, указан-
ное в параметре VARIABLE, определяет максимально-
допустимую длину, используемую в целях оптимиза-
ции буферной обработки. Если никакого значения не
указано, для каждой записи выделяется 8 Кбайт
(этот размер может превышать ресурсы памяти, име-
ющиеся в Вашей системе).
Примечание: Не все из перечисленных выше режимов поддерживаются
в любых системах. К тому же если в системе отсутствуют средства
управления записями, режим RECORD совпадает с режимом STREAM.
Описание нескольких файлов данных
-- 68 --
В случае загрузки данных из нескольких файлов сразу каждый
файл описывается отдельным оператором INFILE. Режим чтения для
каждого файла может быть свой: для одного - STREAM, для другого -
FIXED, а третий файл, содержащий данные, может быть вообще управляющим.
С каждым файлом данных Вы также можете связать еще два файла:
файл отсеянных записей и файл плохих записей. Имена этих файлов
приводятся после имени файла данных. В следующем фрагменте управ-
ляющего файла содержится описание четырех файлов данных:
INFILE mydat1.dat BADFILE mydat1.bad DISCARDFILE mydat1.dis
INFILE mydat2.dat
INFILE mydat3.dat DISCARDFILE mydat3.dis
INFILE mydat4.dat DISCARDMAX 10
Поскольку для первого файла данных (MYDAT1.DAT) файлы плохих
и отсеянных записей указаны явно, оба файла будут созданы. Для
второго файла данных (MYDAT2.DAT) ни тот, ни другой файлы явно не
указаны, поэтому будет создан только файл плохих записей с именем
и расширением, выбранными по умолчанию. В третьем случае оба фай-
ла будут созданы: файл отсеянных записей, как указанный явно, и
файл плохих записей - по умолчанию.
В четвертом случае (файл данных MYDAT4.DAT) будет создан файл
плохих записей со спецификацией по умолчанию и, поскольку указан
параметр DISCARDMAX, утилита SQL*Loader предполагает, что файл
отсеянных записей тоже нужен и создает его с именем по умолчанию
(MYDAT4.DSC).
Примеры описания файла данных
В первом примере укажем, что данные находятся в самом управляющем файле:
INFILE *
В следующем примере укажем, что данные находятся в файле с
именем WHIRL, имеющем расширение по умолчанию (DAT):
INFILE WHIRL
Теперь укажем, что данные располагаются в файле с именем
TEST3.DAT в потоковом формате:
INFILE TEST3.DAT STREAM
В следующем примере укажем, что данные находятся в файле с
именем JULYREV.MJB, состоящем из записей фиксированной длины:
INFILE "JULYREV.MJB"
И наконец, попробуем указать, что данные находятся в ленточ-
ном файле с именем DAT0787.DAT, состоящем из записей фиксирован-
ной длины, объединенных в блоки размером 1 Кбайт:
INDDN "DAT0787.DAT" FIXED 80 BLOCKSIZE 1024
Описание файла плохих записей
-----------------------------
Во время своей работы утилита SQL*Loader создает файл, в ко-
-- 69 --
торый помещает записи, имеющие ошибки форматирования или вызываю-
щие ошибки в работе СУРБД ORACLE. Для задания имени этого файла
может использоваться ключевое слово BADFILE или BADDN. Если имя
не указано, файлу присваивается имя файла данных и расширение
BAD. Указание имени файла плохих записей в управляющем файле от-
меняет аналогичное указание, сделанное ранее в командной строке.
Режим чтения, формат записи и самого файла плохих записей
совпадает с режимом чтения и аналогичными форматами файла данных.
Если файл плохих записей с указанным именем уже существует, в за-
висимости от типа операционной системы он либо переписывается за-
ново, либо создается его новая версия.
[BADFILE і BADDN] спецификация_файла
где:
BADFILE или Может использоваться любое из этих ключевых слов.
BADDN
спецификация_ Любое допустимое имя файла, предназначенного для
файла сохранения отвергнутых записей.
Файл плохих записей создается всегда, даже если ни одна из
записей не будет отвергнута.
Примеры описания файла плохих записей
В первом примере укажем, что файл плохих записей имеет имя
UGH и расширение по умолчанию (BAD):
BADFILE UGH
В следующем примере укажем, что файл плохих записей имеет имя
BAD0001 и расширение REJ:
BADDN BAD0001.REJ
Описание файла отсеянных записей
--------------------------------
Во время своей работы утилита SQL*Loader может создать файл,
в который будет помещать записи, не удовлетворяющие ни одному из
условий фильтрации. Это т.н. отсеянные записи, не совпадающие с
отвергнутыми.
Наличие какой-либо неверной информации в такого рода записях
совсем не обязательно. Просто эти записи не удовлетворяют набору
критериев, налагаемых предложениями WHEN на загружаемые данные.
Если запись все-таки попадает в одну из таблиц (или же включается
в число отвергнутых из-за ошибки в данных), она не считается отсеянной.
Файл отсеянных записей создается по Вашему желанию.
Для того, чтобы создать файл отсеянных записей, используйте
один из следующих параметров:
В управляющем файле В командной строке
------------------------------------------------------Д
DISCARDFILE имя_файла DISCARD
DISCARDDN имя_файла DISCARDMAX
DISCARDS
DISCARDMAX
-- 70 --
Обратите внимание на то, что Вы можете потребовать создания
файла отсеянных записей явно, указав его имя, или неявно, указав
максимально-допустимое число отсеиваемых записей.
Чтобы указать имя файла, используйте параметры DISCARDFILE
или DISCARDDN:
[DISCARDFILE і DISCARDDN] спецификация_файла
где:
DISCARDFILE Может использоваться любое из этих ключевых слов.
или DISCARDDN
спецификация_ Любое допустимое имя файла, предназначенного для
файла сохранения отсеянных записей.
Имя файла отсеянных записей по умолчанию совпадает с именем
файла данных, расширение имени файла по умолчанию - DSC. Указание
имени файла отсеянных записей в управляющем файле отменяет анало-
гичное указание, сделанное ранее в командной строке. Если файл
отсеянных записей с указанным именем уже существует, в зависимос-
ти от типа операционной системы он либо переписывается заново,
либо создается его новая версия.
Режим чтения, формат записи и самого файла отсеянных записей
совпадает с режимом чтения и аналогичными форматами файла данных,
поэтому в дальнейшем его можно использовать для повторной загруз-
ки с тем же самым управляющим файлом (после изменения условий
фильтрации, то есть предложений WHEN).
Примеры описания файла отсеянных записей
В первом примере укажем, что файл отсеянных записей имеет имя
CIRCULAR и расширение по умолчанию (DSC):
DISCARDFILE CIRCULAR
В следующем примере укажем, что файл отсеянных записей имеет
имя FORGET и расширение ME:
DISCARDFILE FORGET.ME
И, наконец, укажем, что файл отсеянных записей имеет имя
NOTAPPL и расширение MAY:
DISCARDDN NOTAPPL.MAY
Ограничение количества отсеиваемых записей
Вы можете ограничить количество отсеиваемых при загрузке фай-
да данных записей, используя следующие параметры:
[DISCARDMAX і DISCARDS] n n - целое число (лимит).
Если лимит исчерпан, обработка этого файла данных прекращает-
ся и утилита переходит к следующему файлу данных, если таковой
имеется.
Однажды заданный лимит распространяет свое действие на все
файлы. Если же он указан вместе с конкретным именем файла, дейс-
твие его ограничивается только этим файлом данных.
-- 71 --
В том случае, когда лимит указан, а имя файла отсеянных запи-
сей отсутствует, утилита SQL*Loader создает файл с именем и рас-
ширением по умолчанию.
Загрузка табличных данных в режимах замены и добавления в
конец
-------------------------------------------------------------
Примечание: Материал этого раздела имеет много общего с рабо-
той утилиты DB2 в режиме RESUME; пользователям утилиты DB2 следу-
ет также обратиться к приложению C.
Если те таблицы, в которые Вы производите загрузку, уже со-
держат какие-то данные, у Вас есть три возможности продолжить работу:
* Загрузить Ваши данные, невзирая на ранее существовавшую информацию.
* Заменить ранее существовавшие данные новыми.
* Произвести загрузку данных только в пустые таблицы. Если
таблица непуста, возвратить код ошибки и прекратить загруз-
ку. (Действие по умолчанию.)
Таким образом, для каждой таблицы Вы можете указать один из
следующих режимов загрузки:
[APPEND і REPLACE і INSERT]
где:
APPEND Если таблица непуста, утилита SQL*Loader добавля-
ет новые строки в конец нее; если пуста - новые
строки загружаются обычным образом.
INSERT Требует, чтобы данные загружались в пустые табли-
цы. (Действие по умолчанию.) Если таблица непус-
та, утилита возвращает код ошибки и завершает работу.
REPLACE Если таблица непуста, утилита заменяет ранее су-
ществовавшие строки новыми; если пуста - новые
строки загружаются обычным способом. Таким обра-
зом, старые строки теряются. Работа в этом режиме
требует наличия у пользователя полномочий удале-
ния данных из таблиц.
Данные, содержавшиеся в таблицах до загрузки и потерянные в
результате загрузки, могут быть восстановлены только утилитой Эк-
спорт или аналогичной ей.
Один и тот же режим Вы можете избрать для всех таблиц сразу,
если укажете его перед самым первым предложением INTO TABLE. Вы
можете выбрать режим загрузки и для каждой таблицы в отдельности,
указав его после соответствующего предложения INTO TABLE. Первое
указание режима относится ко всем таблицам, для которых индивиду-
альный режим загрузки не установлен.
Установка максимальной длины логических записей
-----------------------------------------------
С помощью параметра RECLEN Вы можете задавать размер буферно-
го пространства, достаточный для размещения всей логической запи-
си. Это особенно важно в том случае, когда утилита SQL*Loader не
может самостоятельно установить длину записи на основании инфор-
мации о режиме чтения файла и расположении полей и данных в запи-
-- 72 --
си. Если же длина логической записи велика и утилита SQL*Loader
обнаруживает при ее обработке ошибку, в файл плохих записей попа-
дет только фактически считанная часть записи. Для того, чтобы в
файл плохих записей в этом случае попадала вся логическая запись,
Вам следует указать параметр:
RECLEN длина
где 'длина' - целое число, означающее максимальную длину логичес-
кой записи. Значение по умолчанию для файлов, состоящих из запи-
сей фиксированной длины, равно 80; если файл состоит из записей
переменной длины, значение параметра по умолчанию вычисляется
утилитой. Размер записи, показанный в регистрационном файле, от-
ражает только размер буфера, вычисляемый утилитой SQL*Loader, но
не число байт, считываемых по каждой записи.
Формирование логических записей из физических
---------------------------------------------
Формирование логической записи из нескольких физических может
производиться разными способами, с помощью одного из двух следую-
щих ключевых слов:
CONCATENATE
CONTINUEIF
Фраза CONCATENATE используется в простейшем случае, когда
утилита формирует каждую логическую запись из одного и того же
количества физических записей. Синтаксис этой фразы следующий:
CONCATENATE n
где n - целое число, означающее количество физических записей,
объединяемых в одну логическую.
Если количество объединяемых физических записей варьируется,
используется фраза CONTINUEIF. В этой фразе за ключевым словом
CONTINUEIF следует условие, проверяемое при считывании каждой фи-
зической записи. Примером такого условия может быть наличие сим-
вола "фунт стерлингов" в 80-й позиции записи, означающее, что
следующая запись будет объединяться с текущей. Синтаксис фразы:
CONTINUEIF { [THIS і NEXT] (начало [:конец]) і LAST }
операция { 'строка_символов' і X'шестнадцатиричная_строка' }
где:
THIS Если в данной записи условие истинно, считывается
следующая физическая запись, которая объединяется
с текущей, и так до тех пор, пока условие не ста-
нет ложным. Если же в текущей записи условие не
выполняется, текущая физическая запись становится
последней, участвующей в формировании текущей ло-
гической записи. THIS подразумевается по умолча-
нию.
NEXT Если в следующей записи условие истинно, каждая
следующая физическая запись будет объединяться с
текущей до тех пор, пока условие не станет лож-
ным. Как только это произойдет, текущая физичес-
кая запись станет последней, участвующей в форми-
ровании текущей логической записи.
начало:конец Указание начального и конечного номеров позиций в
физической записи. Позиции нумеруются, начиная с
1. В качестве разделителя может использоваться
дефис (начало-конец) или двоеточие (начало:ко-
-- 73 --
нец).
Если "конец" не указан, длина поля продолжения
принимается равной длине строки байтов или строки
символов. Если "конец" указан, но длина поля про-
должения в результате отличается от длины строки
байтов или строки символов, меньшая длина расши-
ряется за счет дополнения: символьных строк -
пробелами, шестнадцатиричных - нулями.
LAST Это условие похоже на условие THIS, но проверяет-
ся при этом всегда последний отличный от пробела
символ. Если последний отличный от пробела символ
удовлетворяет условию, каждая следующая физичес-
кая запись будет объединяться с текущей до тех
пор, пока условие не станет ложным. Как только
это произойдет, текущая физическая запись станет
последней, участвующей в формировании текущей ло-
гической записи.
операция Разрешены операции, связанные с равенством или
неравенством:
= != ї= <>
Если используется операция типа равенства, то для
того, чтобы условие было истинно, участвующие в
сравнении значение поля и подстрока должны в точ-
ности совпадать. Если используется операция типа
неравенства, сравниваемые значения должны отли-
чаться по любому из символов.
строка_ Строка символов, сравниваемая с полем продолжения
символов в указанных позициях на предмет соблюдения усло-
вия, заданного с помощью "операции". Строка может
быть заключена в кавычки - двойные или одинарные.
Сравнение производится посимвольно, в случае не-
обходимости справа добавляются пробелы.
X'шестнад- Строка байтов в шестнадцатиричном формате, обра-
цатиричная_ батывается так же, как и строка символов. Строка
строка' X'1FB033' описывает три байта, содержащие значе-
ния 1F, B0 и 33 (шестнадцатиричные).
Примечание: Фраза CONTINUEIF адресуется к позициям каждой фи-
зической записи. Это единственный случай подобного рода; в ос-
тальных случаях ссылки имеют отношение к логическим записям.
Если фраза имеет формат CONTINUEIF THIS или CONTINUEIF NEXT,
непосредственно перед сборкой логической записи поле продолжения
убирается из всех физических записей. Это дает возможность форми-
ровать записи, в которых отсутствуют дополнительные символы (сим-
волы продолжения). Рассмотрим примеры использования CONTINUEIF
THIS и CONTINUEIF NEXT:
CONTINUEIF THIS CONTINUEIF NEXT
(1:2) = '%%' (1:2) = '%%'
Пусть имеются четыре физические записи, в которых точка обоз-
начает пробел:
%%aaaaaaaa ..aaaaaaaa
-- 74 --
%%bbbbbbbb %%bbbbbbbb
..cccccccc %%cccccccc
%%dddddddd ..dddddddd
В обоих случаях логическая запись будет иметь один и тот же вид:
aaaaaaaabbbbbbbbcccccccc
Обратите внимание на то, что фраза CONTINUEIF LAST отличается
от фраз CONTINUEIF THIS и CONTINUEIF NEXT тем, что символы про-
должения из физических записей не убираются.
Примеры использования фразы CONTINUEIF
В первом примере укажем, что если текущая физическая запись
(назовем ее записью 1) в первой позиции содержит звездочку, сле-
дующая физическая запись (запись 2) должна добавляться к ней. Ес-
ли и во второй записи содержится звездочка, запись 3 объединяется
с предыдущими двумя.
Если во второй записи отсутствует звездочка (в первой позиции),
эта запись добавится к первой, а запись 3 откроет новую логическую запись.
CONTINUEIF THIS (1) = "*"
В следующем примере укажем, что если текущая физическая за-
пись (запись 1) содержит в последней отличной от пробела позиции
запятую, следующая физическая запись (запись 2) должна добавлять-
ся к ней. В противном случае текущая физическая запись является
последней, участвующей в формировании текущей логической записи.
CONTINUEIF LAST = ","
И наконец, укажем, что если следующая физическая запись (за-
пись 2) содержит в 7-й и 8-й позициях символы "10", она должна
добавляться к предыдущей физической записи (записи 1). В против-
ном случае этой записью откроется новая логическая запись.
CONTINUEIF NEXT (7:8) = '10'
Описание таблиц и столбцов
--------------------------
Та часть оператора LOAD DATA, которая содержит описание таб-
лиц, столбцов и типов данных, называется блоком INTO TABLE. В
случае загрузки нескольких таблиц на каждую из них отводится от-
дельный блок INTO TABLE.
Описание имен таблиц
Блок INTO TABLE открывается ключевым словом INTO TABLE, за
которым следует имя таблицы БД ORACLE.
INTO TABLE имя_таблицы [INSERT і APPEND і REPLACE]
Таблица к этому моменту уже должна существовать. Если имя
таблицы совпадает с одним из ключевых слов, зарезервированных для
использования в языке SQL или утилитой SQL*Loader, содержит спе-
циальные символы или чувствительно к размеру букв, его следует
заключить в двойные кавычки.
INTO TABLE SCOTT."COMMENT"
INTO TABLE SCOTT."comment"
INTO TABLE SCOTT."-COMMENT"
-- 75 --
Пользователь, запустивший утилиту на выполнение, должен иметь
право вставлять (INSERT) данные в таблицу, иначе имя таблицы
должно содержать имя ее владельца:
INTO TABLE SOPHIA.EMP
Блок INTO TABLE может включать в себя описание некоторых па-
раметров загрузки таблицы. В частности, по каждой таблице можно
указать, какое действие (INSERT, APPEND, REPLACE) следует предп-
ринять в том случае, если таблица уже содержит какие-то данные.
Выбор строк для загрузки
Задание условия, определяющего выбор строк для загрузки, про-
изводится с помощью предложения WHEN.
Предложение WHEN следует за именем таблицы и содержит одно
или несколько подлежащих проверке условий (состояний).
WHEN (состояние_поля) [AND (состояние_поля) ]...
Предложение WHEN похоже на предложение CONTINUEIF, но имеет
два важных отличия. Первое отличие связано с тем, что ссылка про-
изводится на логическую запись, а не на физическую. Второе отли-
чие заключается в том, что в условии указывается либо позиция в
логической записи, либо имя столбца в загружаемой таблице. Усло-
вия, содержащиеся в предложении WHEN, оцениваются после определе-
ния значений столбцов. Строка вставляется в таблицу только в том
случае, если все предложение принимает значение "Истина".
Следующий пример говорит о том, что загрузке подлежат записи,
содержащие в 5-й позиции значение "q":
WHEN (5) = 'q'
Предложение WHEN может содержать несколько условий сравнения,
связанных между собой союзом AND. Использование скобок в предло-
жении не обязательно, но желательно, как повышающее наглядность
представления. Например:
WHEN (DEPTNO = '10') AND (JOB = 'SALES')
Описание состояния поля
Состояние поля представляет собой условие, оцениваемое в кон-
тексте логической записи.
Оно используется в предложениях WHEN, NULLIF и DEFAULTIF.
{( начало [:конец] ) і имя_столбца} операция {'строка_символов' і
X'шестнадцатиричная_строка'}
где:
начало Указание начальной позиции сравниваемого поля в
логической записи.
конец Указание конечной позиции сравниваемого поля в
логической записи. Допускаются формы: начало-ко-
нец или начало:конец. Если "конец" не указан,
длина поля определяется длиной подстроки сравне-
ния. Если длины поля и подстроки различаются,
меньшая длина расширяется за счет дополнения:
символьных строк - пробелами, шестнадцатиричных - нулями.
-- 76 --
имя_столбца Имя столбца в таблице БД. Если вместо указания
начала и конца поля используется имя столбца, по-
ле описывается спецификацией столбца. Это имя
должно полностью совпадать с наименованием столб-
ца в описании таблицы. Если имя столбца совпадает
с одним из ключевых слов, зарезервированных для
использования в языке SQL или утилитой
SQL*Loader, содержит специальные символы или
чувствительно к размеру букв, его следует заклю-
чить в кавычки.
операция Разрешены операции, связанные с равенством или
неравенством:
= != ї= <>
'строка_ Строка символов, заключенная в одинарные или
символов' двойные кавычки и сравниваемая со значением поля.
Если сравнение проходит, строка вставляется в таблицу.
X'шестнад- Строка байтов в шестнадцатиричном формате, обра-
цатиричная_ батывается так же, как и строка символов.
строка'
Описание разделителей данных
Если все поля в файле данных разделяются одинаково, для опи-
сания разделителя можно использовать ключевое слово FIELDS. Кроме
того, может использоваться окаймляющий ограничитель. Синтаксис
фразы FIELDS:
FIELDS [TERMINATED [BY] {WHITESPACE і [X] 'символ'} ]
[ [OPTIONALLY] ENCLOSED [BY] [X] 'символ' ]
Ограничитель поля в любом месте может быть переопределен ука-
занием нового ограничителя после имени поля. Более подробно об
описании ограничителя см. в разделе настоящей главы "Описание
символьных данных с ограничителями".
Описание столбцов
Вы можете производить загрузку любого количества столбцов
таблицы. Все столбцы, описанные в базе, но не указанные в управ-
ляющем файле, заполняются пустыми значениями (это один из спосо-
бов ввода пустых значений).
Описания столбцов должны заключаться в скобки, каждое описа-
ние отделяется от следующего запятой и содержит имя столбца и
спецификацию его значения. Если значение считывается из файла
данных, его спецификация включает в себя указание расположения
значения в записи, тип данных, разрешение пустого значения, умол-
чания на значение. Если значение генерируется утилитой
SQL*Loader, спецификация включает ключевое слово RECNUM, функцию
SEQUENCE или ключевое слово CONSTANT.
Список столбцов заключается в скобки, столбцы разделяются
между собой запятыми:
(описание_столбца, описание_столбца, ... )
Имя каждого столбца должно соответствовать имени одного из
столбцов таблицы, указанной в предложении INTO. Если имя столбца
-- 77 --
совпадает с одним из ключевых слов, зарезервированных для исполь-
зования в языке SQL или утилитой SQL*Loader, содержит специальные
символы или чувствительно к размеру букв, его следует заключить в
кавычки.
Занесение константы в столбец
Для занесения в столбец значения константы используйте фразу,
состоящую из ключевого слова CONSTANT и заносимого значения:
CONSTANT значение
Значение может быть заключено в кавычки, а в том случае, ког-
да значение содержит пробелы или зарезервированные слова, наличие
кавычек является обязательным. Будьте внимательны при выполнении
назначения; если назначение константы сделано неправильно, все
строки будут отвергнуты.
Не используйте ключевое слово CONSTANT для назначения пустых
значений. Чтобы назначить столбцу пустое значение, этот столбец
не следует определять совсем; все неописанные обычным порядком
столбцы утилита SQL*Loader автоматически делает пустыми. Ключевое
слово CONSTANT в сочетании со значением дает полное описание
столбца.
Занесение в столбец номера записи из файла данных
Для занесения в столбец номера логической записи, послужившей
основой для формирования строки таблицы, используйте фразу, сос-
тоящую из ключевого слова RECNUM и имени столбца. Записи нумеру-
ются последовательно, начиная с 1; отсчет ведется с самого перво-
го из файлов данных. Значение RECNUM увеличивается с каждой новой
логической записью; таким образом, оно увеличивается для всех за-
писей - отсеянных, пропущенных, отвергнутых и загруженных. Если
задать параметр SKIP=10, первой загруженной записи будет соот-
ветствовать значение RECNUM, равное 11.
Ключевое слово RECNUM в сочетании с именем столбца дает пол-
ное описание столбца.
имя_столбца RECNUM
Генерация уникальных порядковых номеров
С помощью функции SEQUENCE можно гарантировать уникальность
содержащихся в любом указанном столбце значений.
Значение SEQUENCE увеличивается с каждой новой загруженной
или отвергнутой записью; оно не изменяется, если запись отсеяна
или пропущена. Функция имеет два аргумента. Первым аргументом яв-
ляется значение, соответствующее началу отсчета, вторым - прира-
щение. Если в качестве начальной точки указано положительное це-
лое число n, первая вставляемая строка будет иметь в указанном
столбце значение n; в последующих строках соответствующее значе-
ние будет увеличиваться на величину приращения.
Функция SEQUENCE в сочетании с именем столбца дает полное
описание столбца.
имя_столбца SEQUENCE ( { n і MAX і COUNT } [,приращение] )
где:
SEQUENCE Функция SEQUENCE, описывающая значение столбца.
-- 78 --
n Целое число, соответствующее началу отсчета зна-
чения столбца.
COUNT Начало отсчета определяется исходя из числа
строк, уже имеющихся в таблице, и приращения.
MAX Начало отсчета определяется исходя из максималь-
ного из имеющихся значения данного столбца и при-
ращения.
приращение Величина увеличения значения столбца для каждой
последующей строки. По умолчанию равна 1.
Если строка отвергнута (то есть имеется форматная ошибка или
ошибка СУБД ORACLE), этот факт не скрывается и сгенерированные
порядковые номера не пересчитываются. Пусть, например, четыре
строки содержат в означенном столбце порядковые номера 10, 12, 14
и 16, тогда если строка с номером 12 отвергается, три загруженные
строки будут содержать значения 10, 14 и 16, а не 10, 12, 14.
Таким образом, функция позволяет сохранить порядок номеров, нес-
мотря на ошибки в данных. Когда Вы откорректируете отвергнутые
данные и произведете их повторную загрузку, Вы вручную сможете
привести значения столбцов в соответствие со сложившейся нумерацией.
Указание расположения информационного поля
Чтобы загрузить значение, содержащееся в информационном поле,
утилита SQL*Loader должна иметь представление о том, где располо-
жено само поле, и его длину. Указание расположения поля в логи-
ческой записи производится с помощью предложения POSITION, вклю-
ченного в спецификацию столбца. Расположение поля может быть
описано как явно, так и по отношению к предшествующему полю. Ар-
гументы фразы POSITION заключаются в круглые скобки.
POSITION ( { начало [:конец] і * і [+n] } )
где:
начало Указание начальной позиции информационного поля в
логической записи. Первая позиция записи имеет номер 1.
конец Указание конечной позиции информационного поля в
логической записи. Допускаются формы: начало-ко-
нец или начало:конец. Если "конец" не указан,
длина поля выводится исходя из типа данных. (См.
разделы по каждому типу данных; учтите, что тип
CHAR без указания начальной и конечной позиций
означает длину, равную 1.) Если из типа данных
вывести длину не представляется возможным, выда-
ется сообщение об ошибке.
* Означает, что текущее поле начинается непосредст-
венно после предыдущего поля. Если текущее поле -
первое, символ "*" означает, что поле располага-
ется с самого начала логической записи. Если на-
чальная и конечная позиции не указаны, длина поля
выводится из типа данных.
-- 79 --
+n Смещение текущего поля относительно предыдущего,
может использоваться в сочетании со звездочкой.
Перед считыванием значения поля будет пропущено n символов.
Вы можете опустить фразу POSITION полностью. В этом случае
описание расположения поля будет таким же, как и при использова-
нии фразы в формате POSITION (*).
При загрузке нескольких таблиц фраза POSITION (*) для первого
столбца первой таблицы означает, что столбец располагается с са-
мого начала логической записи. Та же фраза по отношению к первому
столбцу всех последующих таблиц означает, что их расположение оп-
ределяется относительно последнего столбца последней из загружен-
ных таблиц.
Таким образом, во втором и последующих блоках INTO TABLE по-
ложение столбцов устанавливается не по отношению к началу
логической записи, если только об этом не сказано явно. Выполнить
последнее просто. Вместо того, чтобы опускать описание расположе-
ния поля или использовать фразу POSITION (*+n) для первого поля в
каждом из последующих блоков INTO TABLE, включите фразу POSITION
(1) или POSITION (n). Это позволит производить загрузку несколь-
ких таблиц в том случае, когда данные для одной таблицы распола-
гаются в той же самой логической записи; в этом случае Вам не
придется переустанавливать значение POSITION.
Если логическая запись содержит данные для одной или для дру-
гой таблицы, но не для обеих сразу, Вы можете переустановить зна-
чение POSITION.
Несколько примеров:
SITEID POSITION (*) SMALLINT
SITELOC POSITION (*) INTEGER
Если таким образом описать первые два столбца, столбец SITEID
будет располагаться, начиная с первой позиции, а столбец SITELOC
сразу после столбца SITEID.
ENAME POSITION (1:20) CHAR
EMPNO POSITION (22-26) INTEGER EXTERNAL
ALLOW POSITION (*+2) INTEGER EXTERNAL TERMINATED BY "/"
Столбец ENAME содержит значение в символьном формате, считы-
ваемое из позиций с 1-й по 20-ю, за ним следует столбец EMPNO,
содержащий числовое данное, считываемое из позиций с 22-й по
26-ю. Столбец ALLOW содержит значение, считываемое после значения
EMPNO со смещением +2, то есть с позиции 28, и до появления сим-
вола "наклонная черта".
Указание длины информационного поля
Размеры полей могут указываться несколькими способами. Если
случайно сделать два указания, одно из них будет иметь старшинс-
тво над другим. Описание размеров полей подчиняется следующим
правилам:
* Длина поля, вытекающая из типа содержащегося в нем значе-
ния, перекрывает длину, вытекающую из фразы POSITION (нача-
ло:конец) или из длины значения между двумя ограничителями
в записи свободного формата.
* Длина поля, вытекающая из фразы POSITION (начало:конец),
перекрывает длину, вытекающую из размерности значения между
двумя ограничителями в записи свободного формата.
-- 80 --
* Длина поля, вытекающая из размерности значения между двумя
ограничителями в записи свободного формата, перекрывает
длину, вытекающую из фразы POSITION, если эта фраза описы-
вает только начальную позицию.
* Символьные данные (CHARACTER), для которых известна только
начальная позиция их расположения (фраза ENCLOSED BY и дли-
на отсутствуют), по умолчанию имеют длину, равную 1.
Указание типа содержащегося в информационном поле значения
После указания расположения значения в логической записи Вам
следует описать его тип. При этом имеется в виду тип значения в
записи файла, который может не совпадать с типом значения, содер-
жащегося в столбце таблицы БД ORACLE. Типы значений могут не сов-
падать в силу того, что СУБД автоматически выполняет преобразова-
ние типов. Тем не менее, Вам следует проследить за тем, чтобы это
преобразование имело надлежащий смысл и не приводило к ошибкам.
Если, к примеру, значение типа CHAR преобразуется в значение
типа NUMBER, следует предусмотреть, чтобы содержимое символьного
поля представляло собой допустимое с точки зрения числового
представления значение. Для каждого поля может быть определен
только один тип данных. Если тип данных не указан, подразумевает-
ся тип CHAR.
Тип CHAR
Поле этого типа содержит символьные значения. Длина поля яв-
ляется необязательным параметром и если здесь она не указана, она
выбирается из спецификации POSITION. Если же длина указана явно,
она отменяет значение, заданное в спецификации POSITION. Если
длина не указана нигде, значение типа CHAR будет иметь длину,
равную 1.
CHAR [ ( длина ) ] [ спецификация_ограничителя ]
Поле типа CHAR может быть и переменной длины, в этом случае
используются ограничители - окаймляющие или завершающие. Более
подробно об этом см. в разделе настоящей главы "Описание символь-
ных данных с ограничителями".
Если столбец в таблице БД определен как LONG, Вам следует яв-
но указать максимальную длину поля (с помощью ключевого слова
LENGTH или фразы POSITION). Такая мера позволит гарантировать,
что для значения поля будет выделен буфер достаточно большого
размера, необходимый даже в том случае, когда поле снабжено огра-
ничителями.
Тип DATE
Поле этого типа содержит символьные значения, преобразуемые
СУБД ORACLE в дату в соответствии с заданной маской. Например:
LOAD DATA
INTO TABLE DATES (COL_A POSITION (1:15) DATE 'DD-Mon-YYYY')
BEGINDATA
01-Jan-1066
01-Apr-1534
28-Feb-2004
Длина поля является необязательным параметром и если здесь
она не указана, она выбирается из спецификации POSITION. Если же
длина указана явно, она отменяет значение, заданное в специфика-
-- 81 --
ции POSITION. Маской может быть любая известная СУБД ORACLE маска
даты. Если маска опущена, по умолчанию используется комбинация
"DD-MON-YY". Длина заключается в круглые скобки, а маска в кавычки.
DATE [ ( длина ) ] [ маска ] [ спецификация_ограничителя ]
Поле типа DATE может быть и переменной длины, в этом случае
используются ограничители - окаймляющие или завершающие. Более
подробно об этом см. в разделе настоящей главы "Описание символь-
ных данных с ограничителями".
Тип VARCHAR
Поле этого типа содержит символьную строку переменной длины,
открывающуюся двухбайтным префиксом, описывающим длину поля. Поле
располагается, начиная с позиции, указанной в предложении
POSITION. Первые два байта, составляющие префикс, в общую длину
поля не включаются. Если предложение POSITION задано в формате
POSITION (начало:конец), "конец" поля игнорируется.
VARCHAR [ ( длина ) ]
Если после ключевого слова VARCHAR Вы указали значение необя-
зательного параметра "длина", это значение будет использоваться в
целях буферизации в качестве максимального размера поля. Если
"длина" не указана, под буфер будет выделено 2 Кбайта.
Тип SMALLINT
Поле этого типа содержит двоичное целое значение размером в
полуслово. Если предложение POSITION задано в формате POSITION
(начало:конец), "конец" поля игнорируется. Длина поля совпадает с
размером целого числа, помещающегося в полуслове, принятом в Ва-
шей системе. (В языке Си ему соответствует тип SHORT INT.)
SMALLINT
Тип INTEGER
Поле этого типа содержит двоичное целое значение размером в
полное слово. Если предложение POSITION задано в формате POSITION
(начало:конец), "конец" поля игнорируется. Длина поля совпадает с
размером целого числа, помещающегося в полном слове, принятом в
Вашей системе. (В языке Си ему соответствует тип LONG INT.)
INTEGER
Тип FLOAT
Поле этого типа содержит двоичное значение с плавающей запя-
той одинарной точности. Если в предложении POSITION указана ко-
нечная позиция поля, она игнорируется. Длина поля совпадает с
размером двоичного числа с плавающей запятой, помещающегося в од-
ном слове. (В языке Си ему соответствует тип того же наименова-
ния.)
FLOAT
Тип DOUBLE
Поле этого типа содержит двоичное значение с плавающей запя-
той двойной точности. Если в предложении POSITION указана конеч-
ная позиция поля, она игнорируется. Длина поля совпадает с разме-
ром двоичного числа с плавающей запятой, помещающегося в двойном
слове. (В языке Си ему соответствует тип DOUBLE или LONG FLOAT.)
DOUBLE
-- 82 --
Тип DECIMAL
Поле этого типа содержит число в упакованном десятичном фор-
мате. Число в этом формате представляет собой последовательность
десятичных цифр, каждая из которых занимает четыре бита, со зна-
ком, на который также отводится четыре бита.
Формат DECIMAL требует указания разрядности (максимального
количества цифр) в значении поля. На этой основе вычисляется дли-
на упакованного десятичного поля в байтах. Вы можете также указы-
вать точность задания значения или коэффициент масштабирования.
Таким способом задается количество цифр справа от десятичной точки.
DECIMAL ( разрядность [,точность] )
где:
разрядность Количество цифр в значении, определяющее его дли-
ну. Необходимо заметить, что действительная длина
вычисляется как (разрядность+2)/2 с округлением в
меньшую сторону.
точность Коэффициент масштабирования или количество цифр
справа от десятичной точки. По умолчанию - 0 (и
значение при этом будет целым). Может превышать
разрядность, но не может быть отрицательным числом.
Тип EXTERNAL для числового поля
Поле этого типа содержит число во внешнем (неупакованном)
формате (то есть в символьной, а не в двоичной форме). Раз так,
то этот тип идентичен типу CHAR (см. выше) и трактуется идентич-
ным образом, с одним исключением, касающимся использования фразы
DEFAULTIF. Если Вы хотите, чтобы значение по умолчанию было пус-
тым, используйте тип CHAR; если - нулевым, используйте EXTERNAL.
Числовое поле типа EXTERNAL, подобно данным типа CHAR, может
иметь необязательный параметр "длина" и ограничители. Если длина
указана, она отменяет значение, заданное в спецификации POSITION.
INTEGER EXTERNAL [ ( длина ) ] [ спецификация_ограничителя ]
DECIMAL EXTERNAL [ ( длина ) ] [ спецификация_ограничителя ]
FLOAT EXTERNAL [ ( длина ) ] [ спецификация_ограничителя ]
Тип GRAPHIC
Поле этого типа содержит строку символов, каждый из которых
занимает два байта (английское сокращение DBCS - от "double-byte
characters"). СУБД ORACLE не поддерживает символы DBCS; тем не
менее, утилита SQL*Loader считывает эти символы как однобайтовые
и запоминает их без модифицирования в указанном Вами столбце.
Единственное исключение касается длины. Если Вы используете фразу
POSITION (начало:конец), она будет определять точное местоположе-
ние поля в логической записи. Однако, Вы можете задать длину поля
после ключевого слова GRAPHIC, в этом случае размер поля измеря-
ется в символах DBCS, поэтому для нахождения действительной длины
поля в байтах его необходимо умножить на 2.
Если поле, содержащее символы DBCS, заключено между двумя
символами - переключения на стандартный (SI) и дополнительный
(SO) регистры, для его описания следует использовать тип GRAPHIC
EXTERNAL. Этот тип идентичен типу GRAPHIC, если не считать того,
что символы переключения регистров не загружаются.
GRAPHIC EXTERNAL [ ( длина ) ]
где:
-- 83 --
GRAPHIC Означает, что поле состоит из символов DBCS.
EXTERNAL Первый и последний символы игнорируются.
длина Длина поля в символах DBCS.
Все вышеизложенное можно пояснить на примере:
Обозначим символы переключения регистров через [], а любой
символ DBCS через #.
Для того, чтобы описать поле ####, используйте фразу
"POSITION (1:4) GRAPHIC" или фразу "POSITION (1) GRAPHIC (2)".
Для того, чтобы описать поле [####], используйте фразу
"POSITION (1:6) GRAPHIC EXTERNAL" или фразу "POSITION (1) GRAPHIC
EXTERNAL (2)".
Тип VARGRAPHIC
Поле этого типа содержит строку символов DBCS, имеющую пере-
менную длину. В первых двух байтах указывается размер поля. Раз-
мер дается в символах DBCS, поэтому для нахождения действительной
длины поля в байтах его необходимо умножить на 2. Длина поля мо-
жет быть указана явно, после ключевого слова VARGRAPHIC, таким
образом Вы можете обеспечить оптимальный размер буфера. Если
"длина" не указана, под буфер отводится 4 Кбайта.
VARGRAPHIC [ ( длина ) ]
Описание символьных данных с ограничителями
Поля типов CHAR, DATE и EXTERNAL (числовые) могут иметь огра-
ничители - завершающие и окаймляющие. Соответственно, поля, ис-
пользующие ограничители, могут быть двух типов: TERMINATED и
ENCLOSED. Спецификация ограничителя указывается после типа данных.
Значения с завершающими ограничителями считываются с первой
позиции поля (которая может устанавливаться относительно послед-
ней позиции предыдущего поля) до первого появления символа-огра-
ничителя. Символ-ограничитель не включается в значение поля. Если
в качестве ограничителя используется пробел (WHITESPACE), данные
считываются до первого появления пробела (пустой позиции, символа
табуляции, символа перехода на новую строку). После этого текущая
позиция продвигается до первого символа, отличного от пробела.
Таким образом, значения полей могут разделяться любым количеством
пробелов и будут при этом допустимыми.
Значения с окаймляющими ограничителями считываются между дву-
мя парными символами-ограничителями. Если два символа-ограничите-
ля следуют один за другим, один из них включается в значение по-
ля. Например, последовательность 'DON''T' воспринимается как
DON'T. Однако, если поле состоит только из двух символов и оба
они являются ограничителями, значение поля будет пустым. При опи-
сании ограничителей Вы можете пользоваться фразами TERMINATED BY
или ENCLOSED BY или обеими сразу. В последнем случае порядок рас-
положения фраз следующий:
[ TERMINATED [ BY ] { WHITESPACE і [X]'символ' } ]
[ [ OPTIONALLY ] ENCLOSED [ BY ] [X]'символ' ]
где:
TERMINATED Означает, что данные считываются до первого появ-
ления символа-ограничителя.
-- 84 --
OPTIONALLY Данные могут быть заключены между двумя символа-
ми, указанными во фразе ENCLOSED BY. Утилита SQL*
Loader обнаруживает первое появление окаймляющего
ограничителя и считывает значение до появления
парного ограничителя. При этом фраза TERMINATED
BY в предложении FIELDS должна обязательно при-
сутствовать, будь то локально или глобально.
ENCLOSED Значение поля помещается между двумя окаймляющими
ограничителями.
BY Необязательное ключевое слово, облегчающее чтение фразы.
WHITESPACE Означает, что ограничителем является любой пустой
символ, в том числе символы перевода строки, пе-
ревода страницы или возврата каретки (только TERMINATED).
символ Одинарный символ-ограничитель.
x'шестнадца- Означает, что в качестве ограничителя выступает
тиричное- символ с указанным в шестнадцатиричной системе
байтовое' кодом ASCII.
Указание типа данных с ограничителями является единственным
способом загрузки конечных пробелов.
Если информационное поле имеет длину 8 символов и содержит
значение DANIELbbb, где bbb - три пробела, оно будет загружено в
БД ORACLE как "DANIEL" при условии, что оно объявлено как
CHAR(8). Однако, если Вам нужно загрузить и конечные пробелы, по-
ле следует объявить как CHAR(8) TERMINATED BY ':' и добавить в
конце его двоеточие, в результате чего значение поля будет выгля-
деть как DANIELbbb:. Тогда поле будет загружено вместе с конечны-
ми пробелами ("DANIEL ").
Занесение в столбец пустого значения
Если Вам нужно, чтобы все заносимые в данный столбец значения
были пустыми, описание столбца следует опустить полностью. Значе-
ние столбца можно сделать нулевым и по некоторому условию, прове-
ряемому в каждой логической записи. Это делается с помощью ключе-
вого слова NULLIF, которое появляется после описания типа поля и
спецификации ограничителя; в фразу NULLIF помимо ключевого слова
включается условие, имеющее ту же форму, что и условие в предло-
жении WHEN. Значение столбца становится пустым, если условие "ис-
тинно", в противном случае значение не изменяется.
NULLIF состояние_поля
Обратите внимание на то, что условие может быть "возвратным",
как в следующем примере:
COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = "unknown")
Значение поля в файле данных сначала анализируется, а затем
делается пустым непосредственно перед загрузкой в базу. Это может
пригодиться в том случае, если Вам понадобится определенные зна-
чения заменить пустыми.
Занесение нуля в столбец числового типа
-- 85 --
Если вместо пустого значения Вы хотите занести в столбец чис-
лового типа нуль, вместо предложения NULLIF используйте предложе-
ние DEFAULTIF. Если столбец не числовой, а символьный, его лучше
сделать пустым. Описание столбца может включать как предложение
NULLIF, так и предложение DEFAULTIF, хотя в некоторых случаях это
излишне.
DEFAULTIF состояние_поля
Обратите внимание на то, что использование фразы DEFAULTIF в
отношении данных типа NUMERIC EXTERNAL приводит к обнулению зна-
чения, в то время как в отношении данных типа CHAR эта фраза поз-
воляет получить пустое значение. Подробности см. в разделе "Опи-
сание состояния поля".
Указание начала загружаемых данных в управляющем файле
-------------------------------------------------------------
Если Ваши данные располагаются в том же файле, что и предло-
жение LOAD DATA, перед началом самих данных следует поместить
ключевое слово BEGINDATA, означающее завершение управляющей ин-
формации и открывающее собой порцию загружаемых данных.
BEGINDATA
Если указанное слово пропущено, утилита SQL*Loader предпримет
попытку интерпретации Ваших данных как управляющей информации и
Вы получите сообщение об ошибке. Если данные располагаются в от-
дельном файле, об окончании управляющей информации будет сигнали-
зировать обнаружение конца управляющего файла, в этом случае клю-
чевое слово BEGINDATA не требуется.
|