Re: SQL*Loader has fixed ideas about numeric data

reddwarf_at_ wrote:
>I have trouble loading numeric data that doesn't fit SQL*Loader's
>definition of a number. For instance all INTEGERs have a maximum
>size of 4, meaning 4 digits wide. FLOATS likewise have a limit of 8 digits,
>and there is apparently no way to override this!

This sounds like a misunderstanding. INTEGER (without EXTERNAL) means the input field is a 4-byte binary integer in your platform's native orientation ("big-endian" or "little-endian"). FLOAT (without EXTERNAL) means the input field is a binary floating-point number in the size and style native to your platform (e.g. 8-byte, IEEE format). Loader (and Oracle) can accommodate the largest values that these kinds of fields can contain.

When you specify INTEGER EXTERNAL or FLOAT EXTERNAL you are saying the number is in character (human-readable) form and you have the option of specifying both length and delimiter information. These delimited fields have a maximum length of 255 bytes which is more than enough to reach Oracle's maximum internal number precision (38 digits).

>I have tried applying datatypes such as FLOAT EXTERNAL and SQL operators to
>deal with a 16 digit number -- but I get ORA-1008 ("not all variables bound")

This sounds a bit odd but I suspect it will go away if you adjust your Loader control field per the above.

/b

--
Bill Manry - Mainframe & Integration Technologies - Oracle Corp. USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.