Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

External table.How to load numbers (decimal and scientific notation format)

569536May 7 2010 — edited May 10 2010
Hi all, I need to load inside an external table records that contain 7 fields. The last field is called AMOUNT and it's represented in some records with the decimal format, in others records with the scientific notation format as, for example, below:

CY001_STATU;2009;Jan;11220020GR;'03900;CYZ900;-9,99999999839929e-03
CY001_STATU;2009;Jan;11200100;'60800;CYZ900;41380,77

The External table's script is the following:

CREATE TABLE HYP_DATA
(
COUNTRY VARCHAR2(50 BYTE),
YEAR VARCHAR2(20 BYTE),
PERIOD VARCHAR2(20 BYTE),
ACCOUNT VARCHAR2(50 BYTE),
DEPT VARCHAR2(20 BYTE),
ACTIVITY_LOC VARCHAR2(20 BYTE),
AMOUNT VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY HYP_DATA_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE 'HYP_BAD_DIR':'HYP_LOAD.bad'
DISCARDFILE 'HYP_DISCARD_DIR':'HYP_LOAD.dsc'
LOGFILE 'HYP_LOG_DIR':'HYP_LOAD.log'
SKIP 0
FIELDS TERMINATED BY ";"
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"COUNTRY" Char,
"YEAR" Char,
"PERIOD" Char,
"ACCOUNT" Char,
"DEPT" Char,
"ACTIVITY_LOC" Char,
"AMOUNT" Char
)
)
LOCATION (HYP_DATA_DIR:'Total.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

If, for the field AMOUNT I use the datatype VARCHAR (as above), the table is loaded but I have some records rejected, and all these records contain the last field AMOUNT with the scientific notation as:

CY001_STATU;2009;Jan;11220020GR;'03900;CYZ900;-9,99999999839929e-03
CY001_STATU;2009;Feb;11220020GR;'03900;CYZ900;-9,99999999839929e-03
CY001_STATU;2009;Mar;11220020GR;'03900;CYZ900;-9,99999999839929e-03
CY001_STATU;2009;Dec;11220020GR;'03900;CYZ900;-9,99999999839929e-03

All the others records with a decimal AMOUNT are loaded correctly.

So, my problem is that I NEED to load all the records (with the decimal and the scientific notation format) together (without records rejected), but I don't know which datatype I have to use for the AMOUNT field....

Anybody has any idea ???
Any help would be appreciated

Thanks in advance

Alex
This post has been answered by Sudhakar_B on May 7 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 7 2010
Added on May 7 2010
3 comments
2,280 views