9iR2 External Tables importing Number-cols with german decimal-point
Oracle 9iR2 ( i like it !)
WinNT 2000 ( ok, i like it too )
Hi i wish to import following data with external Tables
Code:
2002.11.21;ONLINE;ORSK@BDDAT128K;PERMANENT;5.733,27;3.398,77;59,28
2002.11.21;ONLINE;ORSK@BDDAT4M;PERMANENT;6.401,24;3.909,24;61,07
2002.11.21;ONLINE;ORSK@BDIDX128K;PERMANENT;3.028,35;2.551,98;84,27
Here is te External Table-DDL
Code:
--
-- Creating external Table ...
DROP TABLE TS_USED_SIZE_EXT;
CREATE TABLE TS_USED_SIZE_EXT (
sel_date DATE,
status VARCHAR2(09),
Ts_NAME VARCHAR2(30),
CONTENT VARCHAR2(09),
Size_M NUMBER (11, 02),
Used_M NUMBER (11, 02),
Used_percent NUMBER (05, 02)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY OraStage
ACCESS PARAMETERS (
records delimited BY NEWLINE
BADFILE OraStageBad:'TS_USED_SIZE_FMT_SCR.BAD'
LOGFILE OraStageLog:'TS_USED_SIZE_FMT_SCR.LOG'
fields terminated BY ";" (
sel_date char date_format date mask "YYYY.MM.DD",
status,
Ts_NAME,
CONTENT,
Size_M CHAR "TO_NUMBER( '99999990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')",
Used_M CHAR "TO_NUMBER( '99999990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')",
Used_percent CHAR "TO_NUMBER( '990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')"
)
)
location ('TS_USED_SIZE_FMT_SCR.OUT')
)
REJECT LIMIT 1000
;
But selecting on it gives no rows in the log :
Code:
Field Definitions for table TS_USED_SIZE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
SEL_DATE CHAR (10)
Date datatype DATE, date mask YYYY.MM.DD
Terminated by ";"
Trim whitespace same as SQL Loader
STATUS CHAR (255)
Terminated by ";"
Trim whitespace same as SQL Loader
TS_NAME CHAR (255)
Terminated by ";"
Trim whitespace same as SQL Loader
CONTENT CHAR (255)
Terminated by ";"
Trim whitespace same as SQL Loader
SIZE_M CHAR (255)
Terminated by ";"
Trim whitespace same as SQL Loader
USED_M CHAR (255)
Terminated by ";"
Trim whitespace same as SQL Loader
USED_PERCENT CHAR (255)
Terminated by ";"
Trim whitespace same as SQL Loader
error processing column SIZE_M in row 1 for datafile F:\Temp\DownLoad\OraStage\TS_USED_SIZE_FMT_SCR.OUT
ORA-01722: invalid number
error processing column SIZE_M in row 2 for datafile F:\Temp\DownLoad\OraStage\TS_USED_SIZE_FMT_SCR.OUT
ORA-01722: invalid number
error processing column SIZE_M in row 3 for datafile F:\Temp\DownLoad\OraStage\TS_USED_SIZE_FMT_SCR.OUT
How can i set the Field Definitions to get nor error in Number-conversion with decimal-pint(german)?
Thanks in advice
Orca
