How can I get my Oracle load script to recognize decimal data with a trailing minus sign?

5 pts.
Tags:
Oracle
Oracle control file
Oracle load script
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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)”
,…

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following