SQL*Loader Question - please

[Andre Norman]

Hi ,
Im trying to use SQL*Loader's direct load method to load several million
records into my oracle table.

My datafile contains numeric values such as the following:

+000000000376.04
-000000000154.70

The following entry is in my ctl file,

AMOUNT_BALANCE POSITION(133:148) DECIMAL (14,2)

, which according to the documentation, should work.
but this is what I get:

ORA-02357: Packed Decimal conversion error
ORA-07409: slpdtb: invalid packed decimal nibble.
Record 99975: Rejected - Error on table TEST.TABLE1, column AMOUNT_BALANCE

Any help with this is appreciated.

Thanks,

[Scott Mattes]

You chose the packed decimal, you need to use external decimal.

Here is from the manual at tahiti.oracle.com (note the words below "These
datatypes are the human-readable, character form of numeric data")

Numeric EXTERNAL
The numeric EXTERNAL datatypes are the numeric datatypes (INTEGER, FLOAT,
DECIMAL, and ZONED) specified with the EXTERNAL keyword, with optional
length and delimiter specifications.

These datatypes are the human-readable, character form of numeric data.
Numeric EXTERNAL may be specified with lengths and delimiters, just like
CHAR data. Length is optional, but if specified, overrides position.

The syntax for the numeric EXTERNAL datatypes is shown as part of
datatype_spec.

Note: The data is a number in character form, not binary representation.
Therefore, these datatypes are identical to CHAR and are treated
identically, except for the use of DEFAULTIF. If you want the default to be
null, use CHAR; if you want it to be zero, use EXTERNAL. See also Setting a
Column to Null or Zero and DEFAULTIF Clause.

FLOAT EXTERNAL data can be given in either scientific or regular notation.
Both "5.33" and "533E-2" are valid representations of the same value.

"Andre Norman" <andre....@sympatico.ca> wrote in message
news:3RwBa.6278$y77.6...@news20.bellglobal.com...

[Andre Norman]

Thanks for the tip Scott but I've been over the documentation several times.
Unfortunately, With all DATATYPES, I get either an invalid number or the
error i mentioned earlier with the exception of FLOAT. In this case I get
the following warning.
SQL*Loader-307: Warning: conflicting lengths 16 and 4 specified for column
AMOUNT_BALANCE table TEST.TABLE1
And when I query the column, every row contained the same value. The wrong
one at that!!!!

The question remains unanswered. Surely someone has tried to load these
types of values into a number field using sqlldr with the direct=true
option....anyone???

Perhaps there is a way to use FLOAT but how do I change the 4 in the warning
above?

Regards,
Andre

"Scott Mattes" <Sc...@TheMattesFamily.ws> wrote in message
news:MjxBa.1285$cp6.9...@news1.news.adelphia.net...

[FC]

Below you can find a couple of control files loading the data in the
required format.
The former is using field separators, just in case you want to try another
method, while the latter uses fixed positions. You can see also that DECIMAL
EXTERNAL or FLOAT EXTERNAL do not affect the loaded values in any way.
Both control files work as expected either with direct path or conventional
path, are you sure you're specifying the right POSITIONs?

Bye,
Flavio

create table test_dec (num1 number(14,2), num2 number(14,2));

---- version 1

load data

infile *

into table test_dec

truncate

FIELDS TERMINATED BY ','

(num1 DECIMAL EXTERNAL,

num2 FLOAT EXTERNAL

)

BEGINDATA

+000000000376.04, -000000000154.70

-000000000376.03, +000000000154.71

--- version 2

load data

infile *

into table test_dec

truncate

(num1 POSITION(1:16) DECIMAL EXTERNAL,

num2 POSITION(17:32) FLOAT EXTERNAL

)

BEGINDATA

+000000000376.04-000000000154.70

-000000000376.03+000000000154.71

[Andre Norman]

I've identified the problem. It was the NLS_LANG setting for the database.
The table column was expecting a "," and not a decimal. I changed the NLS
setting and the data loaded as expected.
Thanks for your reply.
Andre

"FC" <fla...@tin.it> wrote in message
news:bbkCa.25139$pR3.5...@news1.tin.it...