Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Проблема с внешней таблицей  [new]
Андрей Р.
Member

Откуда:
Сообщений: 7
Изучаю разные методы загрузки данных, так как мне необходимо сделать приложение, позволяющее пользователям периодически обновлять справочную информацию из файлов dBase. (С Oracle работаю очень недавно. Версия 10g)
Прочитав об SQL Loader, попробовал отрезать от dBase-файла заголовок и последний байт и загрузить как текстовый файл с постоянной длиной записи:
LOAD DATA CHARACTERSET RU8PC866

INFILE 'SBJ.txt' "fix 349" 

INTO TABLE NSI.SBJ

(SBJ_ID  position (2:16), 
 ITN     position (17:29),
 INN     position (30:41),
 KPP     position (42:50),
 OGRN    position (51:65),
 SNAME   position (66:319),
 INEGRUL position (320:334),
 ONEDAY  position (335:349)
)

(1-я позиция - признак удаления - игнорируется)
SQL Loader загрузил данные быстро и качественно:
...
Таблица NSI.SBJ:
  365398 Строки успешно загружено.
  0 Строки не загружены из-за ошибки в данных.
  0 Строки не загружены из-за сбоев во всех фразах WHEN.
  0 Строки не загружены из-за того, что все поля были пусты.


Для массива привязки отведено:                  23680 байт(64 строк)
Байтов буфера чтения: 1048576

Всего пропущено логических записей:          0
Всего прочитано логических записей:       365398
Всего забраковано логических записей:        0
Всего удалено логических записей:        0

Прогон начался в Пн Июн 15 10:10:41 2009
Прогон кончился в Пн Июн 15 10:11:32 2009
...
Окрыленный успехом, я попросил SQL Loader сделать из этого внешнюю таблицу. Вот слегка "причесанный" скрипт:
CREATE TABLE sbj_ext
    (sbj_id                         FLOAT(126),
    itn                            VARCHAR2(13),
    inn                            VARCHAR2(12),
    kpp                            VARCHAR2(9),
    ogrn                           VARCHAR2(15),
    sname                          VARCHAR2(255),
    inegrul                        NUMBER(38,0),
    oneday                         NUMBER(38,0))
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY  MY_FILES
    ACCESS PARAMETERS(RECORDS FIXED 349 CHARACTERSET RU8PC866
    READSIZE 1048576
    FIELDS LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "SBJ_ID" (2:16) CHAR(15),
      "ITN" (17:29) CHAR(13),
      "INN" (30:41) CHAR(12),
      "KPP" (42:50) CHAR(9),
      "OGRN" (51:65) CHAR(15),
      "SNAME" (66:319) CHAR(254),
      "INEGRUL" (320:334) CHAR(15),
      "ONEDAY" (335:349) CHAR(15)
    )
  )
   LOCATION (
    MY_FILES:'SBJ.txt'
   )
  )
   REJECT LIMIT UNLIMITED
Таблица благополучно открылась в SQL-навигаторе, который показывает только первые 250 записей. А дальше начались трудности. В 45068-й записи первые 5 полей оказались пустыми, а в поле SNAME вместо названия появилось:
<12 пробелов>1              0       152643758RU000002963856140019059  6140010011026101793178  ПАРУС
То есть "хвост" предыдущей записи и неразобранные поля текущей. В 90125 - еще один сдвиг:
<105 пробелов>1              0       153430080RU000010603074220024244  4220010011034220001969  СИБУГЛЕИНВЕСТ
Дальше я не смотрел, но любой запрос, просматривающий всю таблицу, неизбежно ругается на неполную запись в конце текстового файла.
Анализ SBJ.txt показал, что в файле пропусков нет. (Кстати, SQL Loader его загрузил!) Но после адреса 0x0EFFEFF внешняя таблица пропускает 256 байт и новую запись ищет с 0xEFFFFF. Так получается 1-й сдвиг. Аналогично, после 0x01DFFEFF пропуск до 0x01DFFFFD. (Может быть, я ошибаюсь на 1-2 байта, их пришлось считать вручную)
Можно ли как-то настроить параметры внешней таблицы, чтобы избежать этих неприятностей? Или этот "глюк" связан с чем-то другим?
Заранее спасибо за помощь и за терпение, тем, кто дочитал пост до конца...
15 июн 09, 14:39    [7300418]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
hoarfrost
Member

Откуда: Волгоград
Сообщений: 438
FAQ -> Создание гетерогенного сервиса с использованием ODBC в Oracle для Windows на примере Excel.
Для DBF-ок тоже самое работает на ура, только службе Listener-а надо дать доступ к папке с DBF-ками.
15 июн 09, 17:04    [7301397]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
С ODBC большая вероятность, если в dbf будут ошибки формата - получите произвольные данные. Зачем "службам листенера" доступ к dbf-кам? Если доступ через odbc, то доступ нужен пользователю от которого сделан источник данных.
15 июн 09, 17:28    [7301575]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
hoarfrost
Member

Откуда: Волгоград
Сообщений: 438
Bfink
С ODBC большая вероятность, если в dbf будут ошибки формата - получите произвольные данные.

Тогда это уже не DBF-ки, а произвольные файлы.

Зачем "службам листенера" доступ к dbf-кам? Если доступ через odbc, то доступ нужен пользователю от которого сделан источник данных.

Потому что к DBF-кам обращается СУБД, а не процессы на локальном компьютере пользователя.
15 июн 09, 17:34    [7301616]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
Андрей Р.

Какая у Вас операционная система и установки разделителя дробной и целой части числа?
15 июн 09, 17:36    [7301627]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Aliona
Member

Откуда: Питер, Москва
Сообщений: 4074
вот мой примерчик,который работает хорошо:

автор
CREATE TABLE test
(
CHANNEL_ID VARCHAR2(1024 BYTE),
CHANNEL_STAGE VARCHAR2(1024 BYTE),
CHANNEL_STAGE_DATEON VARCHAR2(1024 BYTE),
IMPOSED_ID VARCHAR2(1024 BYTE),
IMPOSED_SCHEME VARCHAR2(1024 BYTE),
IMPOSED_STAGE VARCHAR2(1024 BYTE),
IMPOSED_STAGE_DATEON VARCHAR2(1024 BYTE),
FIO VARCHAR2(1024 BYTE),
ADDRESS VARCHAR2(1024 BYTE),
ADDRESS_OBJECT_ID2 VARCHAR2(1024 BYTE),
TSTAMP VARCHAR2(1024 BYTE),
TP_ID VARCHAR2(1024 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY ASRZ_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY newline CHARACTERSET UTF8
FIELDS TERMINATED BY "|"
(
Channel_ID CHAR(1024),
Channel_stage CHAR(1024),
Channel_stage_DateOn CHAR(1024),
Imposed_ID CHAR(1024),
Imposed_scheme CHAR(1024),
Imposed_stage CHAR(1024),
Imposed_stage_DateOn CHAR(1024),
FIO CHAR(1024),
Address CHAR(1024),
Address_object_ID2 CHAR(1024),
TSTAMP CHAR(1024),
TP_ID CHAR(1024)
)
)
LOCATION (ASRZ_DIR:'test.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


Глазами ни сам файл, всю внешнюю таблицу не просматривала, но зато просматривала файл протокола, который образуется при кажом селекте к внешней таблице
15 июн 09, 17:40    [7301652]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
hoarfrost

СУБД обращается не к DBF файлам, а к источнику данных ODBC!
15 июн 09, 17:40    [7301656]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
hoarfrost
Member

Откуда: Волгоград
Сообщений: 438
Bfink
hoarfrost

СУБД обращается не к DBF файлам, а к источнику данных ODBC!

Естественно. Только для того, чтобы этот самый источник смог вытянуть из DBF-ок данные, нужно чтобы у процесса листенера был доступ к этой папке. По крайней мере, если вы используете Microsoft FoxPro VFP driver.
15 июн 09, 17:45    [7301694]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
Андрей Р.,

Попробуйте убрать параметр readsize из описания внешней таблицы.
15 июн 09, 17:57    [7301803]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Андрей Р.
Member

Откуда:
Сообщений: 7
hoarfrost, спасибо. Я прекрасно понимаю, что "есть много способов ободрать (причесать) кошку". Можно и в клиентском приложении читать dBase-файл и запись за записью Insert-ировать на сервер (медленно, но верно). А еще я пробовал dbase_pkg.load_table от Тома Кайта (кстати, на удивление шустро работает). Можно запускать сам SQL Loader из клиентского приложения. Хотелось бы сравнить разные методы по производительности и удобству (установки, настройки и т.д.). Но для этого необходимо, как минимум, наладить работу и с временными таблицами тоже.

В предложенном Вами способе больше работы для админа. А это надо будет объяснить админу у клиента. К тому же настройка драйвера ODBC, кодовые страницы... А если у него, не дай бог, нет этого самого ODBC...
15 июн 09, 18:00    [7301828]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
hoarfrost
Member

Откуда: Волгоград
Сообщений: 438
Андрей Р.

В предложенном Вами способе больше работы для админа. А это надо будет объяснить админу у клиента. К тому же настройка драйвера ODBC, кодовые страницы... А если у него, не дай бог, нет этого самого ODBC...

Пожалуйста!
Это верно. И тут уже "рулят организационные вопросы".
15 июн 09, 18:04    [7301864]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
Просто я уже проходил этот путь, причем несколько раз, поэтому про каждый способ знаю его недостатки. Самый удобный на данный момент именно с внешними таблицами. А у odbc два недостатка - скорость и ненадежность - сообщений об ошибках нет, а в базе мусор!
15 июн 09, 18:13    [7301941]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Андрей Р.
Member

Откуда:
Сообщений: 7
Bfink,

Попробовал убрать READSIZE.
Теперь сбой происходит на 4507-й записи. Уже неплохо, значит это как-то связано :-) Да и бегать за ошибкой не так далеко. Это соответствует адресу 0x0017FF00 исходного файла, потерялось, по моим подсчетам 307 байт. Причем, на этот раз прямо из середины записи.

Сервер развернут на Windows Server 2003. Какой установлен десятичный разделитель, не выяснил, наверное "по умолчанию". Но в исходном dBase-файле нет дробных чисел. Float и длинные Number поля как-то случайно получились, на самом деле SBJ_ID - это длинные целые, а INEGRUL и ONEDAY - вообще 0 или 1.
15 июн 09, 18:28    [7302023]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Андрей Р.
Member

Откуда:
Сообщений: 7
Aliona,

У меня другой файл (542 записи) тоже во внешней таблице открылся без ошибок.
В принципе, если не получится с FIXED полями, можно и переписывать dBase в текст, добавляя разделители и концы строк. Но вопрос в другом - почему ЭТО не работает!?
15 июн 09, 18:37    [7302062]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
Странная история. У меня каждый день десятки таблиц так обрабатываются. Попробуйте readsize сделать кратным длине записи - 349 * 10000
А потом, если не поможет степенью двойки 1024*1024
15 июн 09, 20:46    [7302446]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Aliona
Member

Откуда: Питер, Москва
Сообщений: 4074
Андрей Р.
Aliona,

У меня другой файл (542 записи) тоже во внешней таблице открылся без ошибок.

А у меня во внешнем файле миллионы строк, и файл каждый день новый.
И работает, пользователи не жалуются.
16 июн 09, 10:37    [7303568]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Андрей Р.
Member

Откуда:
Сообщений: 7
Bfink,

1024*1024=1048576 у меня уже было.
А с READSIZE=349000 и 3490000 получилось!!! Странно, в документации о READSIZE говорится только, что размер влияет на скорость работы. Или, может быть, есть какие-то еще настройки, которые делают этот параметр столь критичным? Или это какой-то "глюк"?
При этом параметр NOPARALLEL, который я заметил в примере Aliona, не действует. Я было подумал, что дело в попытке параллельной обработки.
А в Ваших таблицах размер записи кратен READSIZE? По умолчанию он, вроде бы 512К.
Спасибо всем за обсуждение и добрые советы. Но хотелось бы все-таки разобраться поглубже, а то чувствую себя, как на минном поле :-)
16 июн 09, 11:28    [7303958]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Bfink
Member

Откуда: Москва
Сообщений: 2798
С такой проблемой не сталкивался, хотя используем внешние таблицы ОЧЕНЬ активно. Попробую разобраться. Я обычно использую форматированные типы данных - числа, даты и т.п. Тогда ошибки сразу попадают в логи. Но такие пропадания части записи если были, то ошибка была в файле.
16 июн 09, 11:59    [7304163]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
Быков Игорь Викторович
Member

Откуда:
Сообщений: 61
Я подымал на форуме тему а можно ли как нибуть извратится и так построить внешнию таблицу на DBF чтоб т откусывать ничего не нужно, но не получилось. из за

------
в лоадере к сожелению нельзя поставить 2 критерия строки
(одновремено FIX и STEAM или вначале одно потом другое всетаки это не программа а продукт) былоб так, проблему можно было решить.

также к сожелению нет формы skip смещение (байтовое)

в DBF файле сначала N строк по 32 байта потом символ 0d потом строки данных с длиной записи
DBF необязательно да и скорее всего не 32 байта
FIX ом нельзя строки разные а STR X'.....' заголовок да но даные же нет.

по смыслу FIX 71 это какбы конец строки виртуально вставленый в файл через 71 символ то есть часный случай STR
поэтому впринципе могли и сделать одновремену работу разных критериев конца записи
( для поля также былоб неплохо ) но нет и ладно

--------------
а так предлагаю программу которя делает скрипт создания екст таблицы на дбв файл.
он похож на ваш. но у меня нет MISSING FIELD VALUES ARE NULL

procedure addpol(namepol varchar2,dlpol number) is
dlp varchar2:=ltrim(to_char(dlpol));
begin
  spol1:=spol1||vk||' XX'||namepol||' VARCHAR2('||dlp||'),';
  spol2:=spol2||vk||'   XX'||namepol||' CHAR('||dlp||'),';
end;

function np(str varchar2) return varchar2 is
n integer;
namepol varchar2(11);
begin
    namepol:=rtrim(str);
    n:=instr(namepol,chr(0));
    if n>0 then namepol:=substr(namepol,1,n-1); end if;
    return namepol;
end;

procedure f_crtab(namedbf varchar2,dir varchar2,dirora varchar2) is
crtab varchar2(2000);
file integer;
buf  raw(8192);
str  varchar2(64);
namepol varchar2(11);
dlpol integer;
dlall integer:=1;
sk integer:=33;
tt integer:=32;
tt2 integer;
begin

spol1:='';
spol2:='';

return ; -- требует дальнейшего развития
-- чтение структуры DBF
  file :=stdio.FOPEN  (dir||'\'||namedbf,0);
  if file<0 then return; end if;

  tt  :=stdio.FREAD (file, 32, buf);
  addpol('FL_DEL',1); -- признак в dbf удалена ли запись или нет
  loop
    tt  :=stdio.FREAD (file, 32, buf);
    str:=utl_raw.CAST_TO_varchar2(buf);
    if substr(str,1,1)=chr(13) then exit; end if;
    sk:=sk+32;
    namepol:=np(substr(str,1,11));
    dlpol:=ascii(substr(str,17,1));
    dlall:=dlall+dlpol;
    addpol(namepol,dlpol);
  end loop;

  tt:=stdio.f_close(file);

  spol1:=rtrim(spol1,','); -- удаляем лишнию запятую
  spol2:=rtrim(spol2,','); -- удаляем лишнию запятую
-- прочли и подготовили  spol1,spol2
-- теперь создаем create table
crtab:=crtab||'CREATE TABLE extdbf_'||replace(namedbf,'.dbf')||vk;
crtab:=crtab||'('||spol1||')'||vk;
crtab:=crtab||'ORGANIZATION EXTERNAL('||vk;
crtab:=crtab||' DEFAULT DIRECTORY '||dirora||vk;
crtab:=crtab||' ACCESS PARAMETERS('||vk;
crtab:=crtab||'  RECORDS FIXED '||dlall||' CHARACTERSET RU8PC866'||vk;
crtab:=crtab||'  NOBADFILE NODISCARDFILE NOLOGFILE'||vk;
crtab:=crtab||'  READSIZE 1048576'||vk;
crtab:=crtab||'  FIELDS LDRTRIM'||vk;
crtab:=crtab||'  REJECT ROWS WITH ALL NULL'||vk;
crtab:=crtab||'  FIELDS('||vk;
crtab:=crtab||spol2||'))'||vk;
crtab:=crtab||' LOCATION (SYS_SQLLDR_XT_TMPDIR_00000:''a'||namedbf||'''))'||vk;
crtab:=crtab||'REJECT LIMIT UNLIMITED;';
biv_lib.debug(crtab);

-- сдесь выполнит этот скрипт дабы появилась внешння таблица 
--и решить проблему куда девать заголовок дбв файла 
--(типа откусить в с++ тамже построить скрипт кинут все на сервер .... ) 

end;

пример не до конца доделанный
5 авг 09, 02:34    [7498196]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с внешней таблицей  [new]
-2-
Member

Откуда:
Сообщений: 15330
А dbf-txt случаем не ntfs-compressed?
5 авг 09, 07:31    [7498298]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить