How can I get my Oracle load script to recognize decimal data with a trailing minus sign?
I am writing an Oracle control file (load script) that will read a data file that contains multiple decimal fields that have trailing minus signs for negative numbers (there is a space for positive numbers - there is no plus sign). The records are fixed length, but the decimal numbers aren't a consistent size, so I can't write it to look for the minus sign in a certain column. (Here is an example of the input):
123123123 1.25- 123123123123123
344312334 10.50- 345345345234622
111111122 5.00 545251454545244
333332221 5000.00- 231454545245455
Does anyone know if Oracle has a mask that will let me load these numbers, or if there is another way to do it?
Thanks,
Bill
Software/Hardware used:
Software/Hardware used:
ASKED:
October 2, 2008 8:31 PM
UPDATED:
October 3, 2008 2:53 PM
Answer Wiki:
Format mask for trailing minus sign (negative values) or trailing space (positive numbers) is, for example, 9999.99MI (the number of nines should match your data). So in your control file you would do something like
LOAD DATA
...
,yournumberfield "to_date(:yournumberfield,'9999.99MI)"
,...
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
