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

Возникла такая проблема:

В текстовом файле разделителем для числа является запятая,в системе разделитель точка.
Данному полю в файле соответствует столбцец типа number в внешней табличке. Естественно строки отбрасываются.
Если я загружаю в таблицу c помощью sqlldr,то там можно применить функцию преобразования to_number и строка на ходу во время загрузки преобразуется в число. Но тот же самый прием не могу применить ,используя внешнюю таблицу, не получается

Может у меня руки кривые?
25 апр 06, 10:38    [2598989]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Ora-dev
Если я загружаю в таблицу c помощью sqlldr,то там можно применить функцию преобразования to_number
Лучше устанавливать переменную nls_numeric_characters
Ora-dev
Но тот же самый прием не могу применить ,используя внешнюю таблицу, не получается
Может у меня руки кривые?

Может...
Опишите атрибут как varchar2 и делайте to_number в select.
25 апр 06, 10:45    [2599044]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
M_IV
Member

Откуда:
Сообщений: 1303
andrey_anonymous
Ora-dev
Если я загружаю в таблицу c помощью sqlldr,то там можно применить функцию преобразования to_number
Лучше устанавливать переменную nls_numeric_characters
Ora-dev
Но тот же самый прием не могу применить ,используя внешнюю таблицу, не получается
Может у меня руки кривые?

Может...
Опишите атрибут как varchar2 и делайте to_number в select.

1. wi ne pravi
2. eto workaround, a ne otvet na vopros

Ora-dev
Добрый день всем знатокам!

Возникла такая проблема:

В текстовом файле разделителем для числа является запятая,в системе разделитель точка.
Данному полю в файле соответствует столбцец типа number в внешней табличке. Естественно строки отбрасываются.
Если я загружаю в таблицу c помощью sqlldr,то там можно применить функцию преобразования to_number и строка на ходу во время загрузки преобразуется в число. Но тот же самый прием не могу применить ,используя внешнюю таблицу, не получается

Может у меня руки кривые?

posmotrite
25 апр 06, 11:59    [2599627]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
M_IV
1. wi ne pravi
2. eto workaround, a ne otvet na vopros

???????
Поясните плиз свою мысль...
Что касается sql*loader и nls_numeric_characters, можно посмотреть здесь
Что касается external tables, то Ваше решение мало чем отличается от предложенного ранее.
А индусам надо просто дать в глаз, чтобы для внешнего файла nls_numeric_characters можно было по-человечески указывать (ну или хотя бы брать из session...) :)
25 апр 06, 12:50    [2599997]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
M_IV
Member

Откуда:
Сообщений: 1303
andrey_anonymous
M_IV
1. wi ne pravi
2. eto workaround, a ne otvet na vopros

???????
Поясните плиз свою мысль...
Что касается sql*loader и nls_numeric_characters, можно посмотреть здесь
Что касается external tables, то Ваше решение мало чем отличается от предложенного ранее.
А индусам надо просто дать в глаз, чтобы для внешнего файла nls_numeric_characters можно было по-человечески указывать (ну или хотя бы брать из session...) :)

1. otnosilos' k "Может у меня руки кривые? -> Может..."

2. Что касается external tables, to reshenija, krome izmenenija nls_numeric_characters DB - net, ispol'sovanie view -> workaround. Kak nam otvetili razrabotchiki Oracle, privjaska ora_loadera v ext table k nls DB bila sdelana "Po mnogochislennim pros'bam trudjashichsja", esli ne ustraivaet:
a) view
b) ismenenie nls
c) sql*loader a ne ext tables

P.S.: Obidet' ili postavit' pod somnenie kompetenciü, ne hotel, k sozhaleniu vse sloshnee korrektno virashat' svoju misl' po russki.
25 апр 06, 13:51    [2600358]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
Garryх
Guest
Может быть я чайник и что-то путаю, но только что тестировал внешние таблицы - в одной колонке использовал и точки и запятые в качестве разделителя целой/дробной части.... кушает и то и другое без ошибок и никаких строк не отсекает...
5 май 06, 13:52    [2634670]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Garryх
Может быть я чайник и что-то путаю, но только что тестировал внешние таблицы - в одной колонке использовал и точки и запятые в качестве разделителя целой/дробной части.... кушает и то и другое без ошибок и никаких строк не отсекает...

Ну так демочку приведите...
Обратите внимание, получить надо именно NUMBER, varchar2 - не интересно ;)
5 май 06, 13:54    [2634692]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
Garryх
Guest
Пример:
1. краткая инструкция как создавал таблицу
2. Пример работающей таблицы (наименования понятное дело изменены)

в файле таблицы изменены 2 запятые на точки при этом селект выполняется корректно при любых других изменениях.
тип поля number.

К сообщению приложен файл (Внешние таблицы.rar - 1Kb) cкачать
5 май 06, 14:17    [2634897]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Garryх
Пример:

Не, это не пример (не все смогут воспроизвести - например, по административным причинам).
Оформите примерно так, как делали, например, в этом топике.
И замените пожалуйста '.000' на '.123', а ',000' на ',456' - чтобы виднее было :)
5 май 06, 14:26    [2634976]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
Garryх
Guest
andrey_anonymous
Garryх
Пример:

Не, это не пример (не все смогут воспроизвести - например, по административным причинам).
Оформите примерно так, как делали, например, в этом топике.
И замените пожалуйста '.000' на '.123', а ',000' на ',456' - чтобы виднее было :)


вот и все-я как и предполагал оказался чайником к тому же невнимательным :)
все оказалось интереснее, как только я поставил '.123', так сразу перестала читаться эта строка...
спасибо за подсказку!!!, так бы сам и не проверил, т.к. у нас практически всегда именно эти значения-целые.

если можно - встречный оффтоп вопрос - можно ли сделать таким образом, чтобы при возникновении ошибки при селекте не просто генерился *.bad - файл, а возникала ошибка Oracle, т.е. не выполнялся селект, либо админ узнавал каким-либо другим образом об этом??
суть в том что все это планируется сделать в автоматическом режиме и смотреть каждый раз за каждым *.bad - файлом не очень удобно.
5 май 06, 14:45    [2635148]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Garryх
либо админ узнавал каким-либо другим образом об этом??

А что мешает настроить загрузку bad? :)
5 май 06, 14:51    [2635214]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
Garryх
Guest
мысль посещала :)
просто думал может есть другой способ :)

меня смущает следующая ситуация:
1. копируется файл внешней таблицы с ошибкой
2. выполняется запрос (данные из таблицы забираются по job'у)
3. генерится bad файл
4. админ узнает о возникновении ошибки просмотрев внешнюю таблицу с bad'ом, либо тот же job пробежав по этой таблице сигнализирует ему
5. приходит следующий файл с данными уже без ошибок
6. данные загружаются корректно, но админ всеравно "узнает что ошибка была".

файл придется удалять руками получается... или есть еще варианты?
5 май 06, 15:02    [2635322]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
M_IV
Member

Откуда:
Сообщений: 1303
BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production                          
CORE	10.2.0.1.0	Production                                      
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production         
NLSRTL Version 10.2.0.1.0 - Production
1.
SELECT * FROM v$version;
CREATE TABLE "IMP_PRODUCED_OMS" 
(
  "IDENT" NUMBER(17),
  "DATE_ACT" DATE,
  "CODE_ESSM" VARCHAR2(20),
  "NAME_ESSM" VARCHAR2(200),
  "CODE_ENS" VARCHAR2(20),
  "NAME_ENS" VARCHAR2(200),
  "STOR_REST" NUMBER(17,3),
  "RELEASED_D" NUMBER(17,3),
  "RELEASED_C" NUMBER(17,3),
  "PRODUCED_D" NUMBER(17,3),
  "PRODUCED_C" NUMBER(17,3),
  "PROD_PLAN_D" NUMBER(17,3),
  "PROD_PLAN_C" NUMBER(17,3),
  "PROD_PLAN_M" NUMBER(17,3)
)
ORGANIZATION EXTERNAL 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY EXT_DIR
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET CL8MSWIN1251
    BADFILE 'EXT_DIR':'IMP_PRODUCED_OMS.bad'
    LOGFILE 'EXT_DIR':'IMP_PRODUCED_OMS.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "|" LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "IDENT" CHAR(255)
        TERMINATED BY "|",
      "DATE_ACT" CHAR(255)
        TERMINATED BY "|",
      "CODE_ESSM" CHAR(255)
        TERMINATED BY "|",
      "NAME_ESSM" CHAR(255)
        TERMINATED BY "|",
      "CODE_ENS" CHAR(255)
        TERMINATED BY "|",
      "NAME_ENS" CHAR(255)
        TERMINATED BY "|",
      "STOR_REST" CHAR(255)
        TERMINATED BY "|",
      "RELEASED_D" CHAR(255)
        TERMINATED BY "|",
      "RELEASED_C" CHAR(255)
        TERMINATED BY "|",
      "PRODUCED_D" CHAR(255)
        TERMINATED BY "|",
      "PRODUCED_C" CHAR(255)
        TERMINATED BY "|",
      "PROD_PLAN_D" CHAR(255)
        TERMINATED BY "|",
      "PROD_PLAN_C" CHAR(255)
        TERMINATED BY "|",
      "PROD_PLAN_M" CHAR(255)
        TERMINATED BY "|"
    )
  )
  LOCATION 
  (
    'IMP_PRODUCED_OMS.XTB'
  )
)REJECT LIMIT UNLIMITED;
SELECT * FROM IMP_PRODUCED_OMS;

Table dropped.
Table created.
no rows selected
IMP_PRODUCED_OMS.log_xt



LOG file opened at 05/05/06 12:47:07

Field Definitions for table IMP_PRODUCED_OMS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields

Fields in Data Source:

IDENT CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
DATE_ACT CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
CODE_ESSM CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
NAME_ESSM CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
CODE_ENS CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
NAME_ENS CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
STOR_REST CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
RELEASED_D CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
RELEASED_C CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PRODUCED_D CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PRODUCED_C CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PROD_PLAN_D CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PROD_PLAN_C CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PROD_PLAN_M CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
error processing column DATE_ACT in row 1 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 2 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 3 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 4 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 5 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 6 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 7 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 8 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 9 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 10 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 11 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 12 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month
error processing column DATE_ACT in row 13 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01843: not a valid month

2.
DROP TABLE IMP_PRODUCED_OMS;

CREATE TABLE "IMP_PRODUCED_OMS" 
(
  "IDENT" NUMBER(17),
  "DATE_ACT" DATE,
  "CODE_ESSM" VARCHAR2(20),
  "NAME_ESSM" VARCHAR2(200),
  "CODE_ENS" VARCHAR2(20),
  "NAME_ENS" VARCHAR2(200),
  "STOR_REST" NUMBER(17,3),
  "RELEASED_D" NUMBER(17,3),
  "RELEASED_C" NUMBER(17,3),
  "PRODUCED_D" NUMBER(17,3),
  "PRODUCED_C" NUMBER(17,3),
  "PROD_PLAN_D" NUMBER(17,3),
  "PROD_PLAN_C" NUMBER(17,3),
  "PROD_PLAN_M" NUMBER(17,3)
)
ORGANIZATION EXTERNAL 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY EXT_DIR
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET CL8MSWIN1251
    BADFILE 'EXT_DIR':'IMP_PRODUCED_OMS.bad'
    LOGFILE 'EXT_DIR':'IMP_PRODUCED_OMS.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "|" LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "IDENT" CHAR(255)
        TERMINATED BY "|",
      "DATE_ACT" CHAR(10) DATE_FORMAT DATE MASK "mm.dd.yyyy" 
        ,
      "CODE_ESSM" CHAR(255)
        TERMINATED BY "|",
      "NAME_ESSM" CHAR(255)
        TERMINATED BY "|",
      "CODE_ENS" CHAR(255)
        TERMINATED BY "|",
      "NAME_ENS" CHAR(255)
        TERMINATED BY "|",
      "STOR_REST" CHAR(255)
        TERMINATED BY "|",
      "RELEASED_D" CHAR(255)
        TERMINATED BY "|",
      "RELEASED_C" CHAR(255)
        TERMINATED BY "|",
      "PRODUCED_D" CHAR(255)
        TERMINATED BY "|",
      "PRODUCED_C" CHAR(255)
        TERMINATED BY "|",
      "PROD_PLAN_D" CHAR(255)
        TERMINATED BY "|",
      "PROD_PLAN_C" CHAR(255)
        TERMINATED BY "|",
      "PROD_PLAN_M" CHAR(255)
        TERMINATED BY "|"
    )
  )
  LOCATION 
  (
    'IMP_PRODUCED_OMS.XTB'
  )
)REJECT LIMIT UNLIMITED;
SELECT * FROM IMP_PRODUCED_OMS;

Table dropped.
Table created.
no rows selected
IMP_PRODUCED_OMS.log_xt

LOG file opened at 05/05/06 12:57:26

Field Definitions for table IMP_PRODUCED_OMS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields

Fields in Data Source:

IDENT CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
DATE_ACT CHAR (10)
Date datatype DATE, date mask mm.dd.yyyy
Terminated by "|"
Trim whitespace same as SQL Loader
CODE_ESSM CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
NAME_ESSM CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
CODE_ENS CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
NAME_ENS CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
STOR_REST CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
RELEASED_D CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
RELEASED_C CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PRODUCED_D CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PRODUCED_C CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PROD_PLAN_D CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PROD_PLAN_C CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
PROD_PLAN_M CHAR (255)
Terminated by "|"
Trim whitespace same as SQL Loader
error processing column STOR_REST in row 1 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 2 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 3 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 4 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 5 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 6 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 7 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 8 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 9 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 10 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 11 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column STOR_REST in row 12 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number
error processing column RELEASED_D in row 13 for datafile D:\ext_tables\IMP_PRODUCED_OMS.XTB
ORA-01722: invalid number

Date Cache Statistics for table IMP_PRODUCED_OMS
Max Size: 1000
Entries : 1
Hits : 12
Misses : 0

5 май 06, 15:03    [2635336]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
Garryх
Guest
чем поможет маска если честно не совсем понял в данной ситуации?
5 май 06, 16:49    [2636203]     Ответить | Цитировать Сообщить модератору
 Re: Внешние таблицы - разделитель целой и дробной части  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Garryх
чем поможет маска если честно не совсем понял в данной ситуации?

Как минимум позволит корректно обработать дату :)
5 май 06, 17:10    [2636362]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить